mysql Multiple Foreign Keys in a Table to the Same Primary Key

Question

I have a table user with userID as the primary key. I have another table called Friends. In the Friends table, I have two Users as friends represented by the columns UserID and FrndID where both UserID and FrndID should be a userID in table user.

I want to enforce data integrity. Could I use something like this?

ADD CONSTRAINT `ufd_users_fk` FOREIGN KEY (`userId`, `friendId`)
REFERENCES `users` (`userId`, `userId`) ON DELETE CASCADE ON UPDATE CASCADE;

I want to know is REFERENCESusers(userId,userId) referencing a column multiple times correctly? The reason I am not creating 2 separate constraints, is that both users must exist in table user.

Chummy

Answer 1

No, you should create two foreign keys:

ADD CONSTRAINT `ufd_users_fk` FOREIGN KEY (`userId`) 
  REFERENCES `users` (`userId`) 
  ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `ufd_users_fk` FOREIGN KEY (`friendId`) 
  REFERENCES `users` (`userId`) 
  ON DELETE CASCADE ON UPDATE CASCADE;

Radioman

Karwin Alright but the thing is that If I delete a user than all friend related to that user will be deleted too.. – Chummy

No, only their rows in the Friends table will be deleted. The rows in the Users table will not be deleted. – Radioman

when I write the above commands I get this error , data model already has an element named ufd_users_fk , should I choose different constraint names? – Blynn

@user2320445, Yes, in SQL even if the constraint of that name belongs to another table, constraint names conflict if they're in the same schema. I've never seen that particular error message with MySQL, but it appears to be common in Microsoft SQL Server. See #3294875 for more information on this error and how to fix it. – Radioman