MYSQL Duplicate Entry on Key 1 (of 2) Problem

[JaseAU]

I have a mysql table that was working fine. I'll try to be brief.

I have a table that has two primary keys, both are also foreign keys to other tables. The primary keys are qid and pid. If I try to enter a record, using pid > 10, I get an error, for example, if the pid was 15, "Duplicate Entry '15' for key 1'.

Qid is INT(4) and Pid is also INT(4)
I have tried checking indexes, entering manually a record in using the command line, etc.

I am using MYSQL 5.0.45.

I can't seem to figure out why this problem is occuring.

Any help is much appreciated.

[Atli]

Hi.

When you say that it has two primary keys, do you mean they are a joint primary key?
Because you can only have a single PK, so to use more then one row as the PK, you have to make a composite PK.

Judging by your error, I would guess that your table only has one column as the PK: the pid column.
If the two columns were a two-column composite PK, the error message would have been more like: "Duplicate Entry '15-1' for key 1", specifying a value like column1-column2.

Could you show us the exact structure of the table?
The output from SHOW CREATE TABLE would be best.

[JaseAU]

Hi Atil, I have solved the problem. Another forum helped me out.
Yes it was a 2 column primary key, sorry for the confustion. I think the conclusion we reached was that the index had remained for a particular entry, and when I added the entry again (after deleting it), it was already there. I added a single column primary key to solve the issue and used the UNIQUE constraint for these two columns.

Cheers

[Atli]

Ok. I'm glad you found a solution ;-)