mysql表中的多个外键与相同的主键 [英] mysql Multiple Foreign Keys in a Table to the Same Primary Key

问题描述

我有一个以 userID 作为主键的表 user 。我有另一个名为 Friends 的表。在 Friends 表中，我有两个用户作为朋友，由列 UserID 和 FrndID 其中 UserID 和 FrndID 应该是 userID 在表 user 中。



我想强制执行数据完整性。我可以用这样的东西吗？
$ b $ pre $ 添加约束`ufd_users_fk` FOREIGN KEY（`userId`，`friendId`）
参考`用户`（`userId`，`userId`）ON DELETE CASCADE ON UPDATE CASCADE;


我想知道的是 REFERENCES code>（ userId ， userId ）正确地引用多个列？我不创建2个单独的约束的原因是，两个用户必须存在于表 user 中。

解决

$ b

 添加约束`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; 
  

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;

这篇关于mysql表中的多个外键与相同的主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！