MySQL外键使用多个字段来引用另一个表中的主键 [英] MySQL foreign key using more than one field to reference to a primary key from another table

问题描述

我想在一个表中引用2个我的列到另一个表中的一个主键。下面是它在db结构中的样子：
$ b $ pre $ user $ b $ uid（INT）name（VARCHAR）
1 John Doe
2 Jane Doe

SystemProcesses
uid（INT）name（VARCHAR）
1 Hitman
2 Cron

日志
uid（INT）modelType（VARCHAR）modelUID（INT）动作
1用户2 Jane Doe做了这个
2用户1 John Doe做了
3 SystemProcesses 1 Hitman刚刚杀死了John Doe

如何在Logs表中引用modelType和modelUID到那些用户和SystemProcesses？

如果不可能的话，还有什么办法可以解决？

解决方案

不要对两个外键使用相同的列。这有时被称为多态关联，它破坏了良好的数据库设计规则。

这应该是一个线索，这是一个糟糕的设计，一个FOREIGN KEY约束只支持一个被引用的表。不支持标准SQL中的多态关联。

创建两个列，一个用于引用Users，另一个用于引用SystemProcesses。每个引用的表一列。

$ $ p $ $ $ $ $ $ $ $ $ $ $ b 1 2 NULL Jane Doe做了这个
2 1 NULL John Doe做了
3 1 1 Hitman刚杀死了John Doe

如果没有用户或SystemProcesses列的相关引用，则使用NULL表示没有适用的值。

您可能希望查看其他有关多态关联的问题。

I want to reference 2 of my columns in one table to a primary key from another table. Here is how it looks like in the db structure:

Users
uid (INT)  name (VARCHAR)
1          John Doe
2          Jane Doe

SystemProcesses
uid (INT)  name (VARCHAR)
1          Hitman
2          Cron

Logs
uid (INT)  modelType (VARCHAR)  modelUID (INT)  Action
1          Users                2               Jane Doe did this
2          Users                1               John Doe did that
3          SystemProcesses      1               Hitman just killed John Doe

How do I reference modelType and modelUID in Logs table to those Users and SystemProcesses?

If it's not possible, what is the the alternative?

解决方案

Don't use the same column for both foreign keys. This is sometimes called polymorphic associations, and it breaks rules of good database design.

It should be a clue that it's a bad design, that a FOREIGN KEY constraint supports only one referenced table. There is no support for polymorphic associations in standard SQL.

Instead, create two columns, one for a reference to Users, the other for a reference to SystemProcesses. One column per referenced table.

Logs
uid (INT)  UsersID (INT)  SystemProcessesID (INT) Action
1          2              NULL                    Jane Doe did this
2          1              NULL                    John Doe did that
3          1              1                       Hitman just killed John Doe

If there is no relevant reference for either the Users or SystemProcesses column, use NULL to indicate there's no applicable value.

You may like to review other questions I have answered about polymorphic associations.

这篇关于MySQL外键使用多个字段来引用另一个表中的主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！