Why is this compound primary key not working as expected?

Question

I have a table "tbl_project_user_assignment" with a compound primary key.

It is made up of project_id and user_id

Each of these are also a foreign key to the project and user tables respectively.

At the moment, I have 2 entries in this table as below...

project_id | user_id
--------------------
1          | 1
1          | 2

When I run this sql query...

INSERT INTO tbl_project_user_assignment (project_id, user_id) VALUES (2, 1);

...I get the following error message:

Integrity constraint violation: 1062 Duplicate entry '1' for key 'FK_project_user'

The FK_project_user key is the one linking the project_id to the tbl_project id.

This doesn't make sense to me because the values I'm inserting are unique...

Any ideas?

Answer 1

It looks like FK_project_user is a unique key. Try dropping temporarily that constraint and perform the insert again.

If insert works, recreate the constraint making sure it's not flagged as unique anymore.

Answer 2

I think you should have:

• a foreign key relation to project
• a foreign key relation to user
• a unique constraint on (project, user)
• possibly a primary key on the combination of (project, user) OR a separate key field If you choose a combined PK you wouldn't need an extra constraint of course.

And it seems you have a unique constraint on user (by itself).

Posting the show create table statement would help even more.