MySQL PK error...

[heroine]

Hi All,

I have successfully parsed the dmoz dump files into mysql.
There are 4 tables: structure, content_description, content_links and datatypes.

Structure table has fields: catid, name, title.....
I set the primary key as CATID and it works fine.

When I tried to do the same for the rest of the tables such as datatypes (with fields: catid, type, resource) i always got this error:

SQL query: Edit

ALTER TABLE `datatypes` ADD PRIMARY KEY ( `catid` ) ;

MySQL said: Documentation
#1062 - Duplicate entry '1' for key 1

same goes to other tables (content_links with fields: catid,topic,type, resource).


Another problem, the 3rd table (content_description) has the following fields: externalpage, title, description,ages, mediadate,priority.

the question is which one of these fields should be the PK here?

hope to get your feedbacks....
thanks a lot..

[andreasblixt]

The reason you get the "Duplicate entry" error for the rest of the tables is most likely because their 'catid' fields refer back to the 'structure' table. So one row in 'structure' can have three corresponding rows in 'datatypes'. These three rows would have the same 'catid'. The best you can do with these fields is to put indexes on them to speed up access (and foreign keys if your database engine supports it.)

It looks as if the rest of the tables weren't made to have primary keys. A primary key should be something that uniquely identifies the row. If, for example, rows in 'content_description' must have a unique title, 'title' could be used as a primary key (but would not be as fast as having an integer field because of the data usage.)

[heroine]

I have tried putting indexes in all the tables...but still the same error come out...

I am using type: MyISAM

When creating tables, here is one of the queries :

$query = "CREATE TABLE content_links (
catid int(8) NOT NULL default '0',
topic varchar(100) NOT NULL,
type varchar(20) NOT NULL default '',
resource varchar(255) NOT NULL,
KEY catid (catid)
) TYPE=MyISAM;\n"

Here there is one key set to catid....and this doesn't show in the database, same goes to the rest of the table.

I set myself the Pk on the structure table and it worked fine...

thanks....

[andreasblixt]

I can't say much more than I already said in my previous post. You're trying to put a primary key on a field that isn't unique. The 'catid' column in the 'content_links' table is referring to the 'catid' column in the 'structure' table. The 'structure' table only has unique 'catid' values, whereas the 'content_links' table has more than one row for one 'catid'. That's the cause of your error. Either create the primary key on a column or a combination of columns that makes each row unique, add a new column with a unique value for each row or don't create a primary key at all.