MySQL 许多表之一的外键？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3383300/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Foreign key to one of many tables?
提问by never_had_a_name
The usual way of setting a foreign key constraint is to choose which table the foreign key will point to.
设置外键约束的常用方法是选择外键将指向哪个表。
I'm having a polymorphic relation between 1 table and a set of table.
我在 1 个表和一组表之间存在多态关系。
That means that this table will have a relation with one of those tables in the set.
这意味着该表将与集合中的其中一个表有关系。
eg.
例如。
images: person_id, person_type
subordinates: id, col1, col2...col9
products: id, colA, colB...colZ
In the above example, if person_type is "subordinates" then person_id should be a foreign key to subordinates.id and the same goes with products.
在上面的例子中，如果 person_type 是“subordinates”，那么 person_id 应该是下级的外键，产品也是如此。
So I wonder, is it possible to have a foreign key to one of many tables, or do you have to specifically set which table it points to when you assign one.
所以我想知道，是否有可能有一个外键指向许多表之一，或者你是否必须在分配一个表时专门设置它指向哪个表。
This question is for both MySQL and PostgreSQL.
这个问题适用于 MySQL 和 PostgreSQL。
Thanks
谢谢
回答by Bill Karwin
No, a foreign key constraint always references exactly one parent table.
不，外键约束总是只引用一个父表。
This question comes up frequently. Here are some of my past answers to it:
这个问题经常出现。以下是我过去的一些回答：
- Why can you not have a foreign key in a polymorphic association?
- Possible to do a MySQL foreign key to one of two possible tables?
- Referencing foreign keys in the same column
- In a StackOverflow clone, what relationship should a Comments table have to Questions and Answers?
- MySQL - Conditional Foreign Key Constraints
- How to handle an “OR” relationship in an ERD (table) design?
- MySQL: Two n:1 relations, but not both at once
- 为什么在多态关联中不能有外键？
- 可以对两个可能的表之一执行 MySQL 外键吗？
- 引用同一列中的外键
- 在 StackOverflow 克隆中，Comments 表应该与 Questions and Answers 有什么关系？
- MySQL - 条件外键约束
- 如何处理 ERD（表）设计中的“OR”关系？
- MySQL：两个 n:1 关系，但不能同时出现
For more on Polymorphic Associations, see my presentation Practical Object-Oriented Models in SQLor my book, SQL Antipatterns: Avoiding the Pitfalls of Database Programming.
有关多态关联的更多信息，请参阅我的演示文稿SQL 中的实用面向对象模型或我的书SQL 反模式：避免数据库编程的陷阱。
回答by Will A
A foreign-key by definition must point to either a primary- or candidate- key on one-and-only-one table - primary- only is available in a typical DBMS. You're better off having a single 'person' table and have tables that are related to this with e.g. manager information.
根据定义，外键必须指向一个且只有一个表上的主键或候选键 - 主键仅在典型的 DBMS 中可用。您最好拥有一个“人员”表，并拥有与此相关的表，例如经理信息。
回答by OMG Ponies
A column is only a placeholder for a value. A foreign key constraint means that the data stored within that column can only be a value that matches the table's column defined in the constraint. Foreign key constraints are per table...
列只是值的占位符。外键约束意味着存储在该列中的数据只能是与约束中定义的表列匹配的值。外键约束是每个表...
There is nothing to stop you from defining multiple foreign key constraints on a column. But this means that the only value allowed to be stored will be values that already exist in all of the other foreign related tables. IE: TABLE_1 has values 1 and 2, TABLE_2 has values 2 and 3 - TABLE_3 has foreign key relationships defined to tables 1 & 2 on TABLE_3's colcolumn... The only valid value I can insert into TABLE_3.colis 2, because it's in both tables (assuming colis not nullable).
没有什么可以阻止您在列上定义多个外键约束。但这意味着唯一允许存储的值将是所有其他外部相关表中已经存在的值。IE: TABLE_1 有值 1 和 2，TABLE_2 有值 2 和 3 - TABLE_3 有外键关系定义到表 1 和 2 在 TABLE_3 的col列......我可以插入的唯一有效值TABLE_3.col是 2，因为它在两个表中（假设col不可为空）。
回答by Mykroft
A foreign key can only ever point to a single table.
外键只能指向单个表。
It looks to me like what you really wanted to do here is create a parent id in your persons table. The subordinates would have a parent id pointing at their managers. If a subordinate needed to have multiple managers a separate joining table could be created with 2 columns each containing a person id one being the subordinate and the other being one of the managers.
在我看来，您真正想做的是在您的人员表中创建一个父 ID。下属将有一个指向他们的经理的父 ID。如果下属需要有多个经理，可以创建一个单独的连接表，其中包含 2 列，每列包含一个人 ID，一个是下属，另一个是经理之一。
If you wanted to restrict who could be assigned to the parentid field this could be done with a check constraint.
如果您想限制谁可以分配给 parentid 字段，这可以通过检查约束来完成。
