mysql创两个表主键相同,mysql表中的多个外键与相同的主键

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.

解决方案

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;