13

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.

Charity Leschinski
  • 2,886
  • 2
  • 23
  • 40

1 Answers1

14

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;
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Karwin Alright but the thing is that If I delete a user than all friend related to that user will be deleted too.. –  Sep 17 '13 at 00:53
  • No, only their rows in the Friends table will be deleted. The rows in the Users table will not be deleted. – Bill Karwin Sep 17 '13 at 00:58
  • 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? – Milad Qasemi Dec 20 '15 at 13:58
  • @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 http://stackoverflow.com/questions/3294375/model-already-has-an-element-errors-tsd04105-when-using-visual-studio-2008-d for more information on this error and how to fix it. – Bill Karwin Dec 20 '15 at 18:57