SQL 外键是指跨多个表的主键？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/668921/
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 referring to primary keys across multiple tables?
提问by Thomas Padron-McCarthy
I have to two tables namely employees_ce and employees_sn under the database employees.
我有两个表，即employees_ce 和employees_sn 数据库雇员。
They both have their respective unique primary key columns.
它们都有各自唯一的主键列。
I have another table called deductions, whose foreign key column I want to reference to primary keys of employees_ce as well as employees_sn. Is this possible?
我有另一个表叫做 deductions，我想引用它的外键列来引用雇员_ce 和雇员_sn 的主键。这可能吗？
for example
例如
employees_ce
--------------
empid name
khce1 prince
employees_sn
----------------
empid name
khsn1 princess
so is this possible?
那么这可能吗？
deductions
--------------
id name
khce1 gold
khsn1 silver
回答by Thomas Padron-McCarthy
Assuming that I have understood your scenario correctly, this is what I would call the rightway to do this:
假设我已经正确理解了你的场景，这就是我所说的正确方法：
Start from a higher-level description of your database! You have employees, and employees can be "ce" employees and "sn" employees (whatever those are). In object-oriented terms, there is a class "employee", with two sub-classes called "ce employee" and "sn employee".
从数据库的更高级别描述开始！你有员工，员工可以是“ce”员工和“sn”员工（无论是什么）。在面向对象的术语中，有一个类“员工”，有两个子类，称为“ce 员工”和“sn 员工”。
Then you translate this higher-level description to three tables: employees, employees_ceand employees_sn:
然后，您将此更高级别的描述转换为三个表：employees、employees_ce和employees_sn：
employees(id, name)employees_ce(id, ce-specific stuff)employees_sn(id, sn-specific stuff)
employees(id, name)employees_ce(id, ce-specific stuff)employees_sn(id, sn-specific stuff)
Since all employees are employees (duh!), every employee will have a row in the employeestable. "ce" employees also have a row in the employees_cetable, and "sn" employees also have a row in the employees_sntable. employees_ce.idis a foreign key to employees.id, just as employees_sn.idis.
由于所有员工都是员工（废话！），每个员工都会在employees表中占一行。“ce”员工在employees_ce表中也有一行，“sn”员工在employees_sn表中也有一行。employees_ce.id是 的外键employees.id，原样employees_sn.id。
To refer to an employee of any kind (ce or sn), refer to the employeestable. That is, the foreign key you had trouble with should refer to that table!
要指代任何类型的员工（ce 或 sn），请参阅下employees表。也就是说，您遇到问题的外键应该引用该表！
回答by derobert
You can probably add two foreign key constraints (honestly: I've never tried it), but it'd then insist the parent row exist in both tables.
您可能可以添加两个外键约束（老实说：我从未尝试过），但它会坚持在两个表中都存在父行。
Instead you probably want to create a supertype for your two employee subtypes, and then point the foreign key there instead. (Assuming you have a good reason to split the two types of employees, of course).
相反，您可能希望为您的两个员工子类型创建一个超类型，然后将外键指向那里。（当然，假设您有充分的理由将两种类型的员工分开）。
employee
employees_ce ———————— employees_sn
———————————— type ————————————
empid —————————> empid <——————— empid
name /|\ name
|
|
deductions |
—————————— |
empid ————————+
name
typein the employee table would be ceor sn.
type在员工表中将是ceor sn。
回答by LittleC
Actually I do this myself. I have a table called 'Comments' which contains comments for records in 3 other tables. Neither solution actually handles everything you probably want it to. In your case, you would do this:
其实这个是我自己做的。我有一个名为“评论”的表格，其中包含对其他 3 个表格中记录的评论。这两种解决方案实际上都不能处理您可能想要的一切。在你的情况下，你会这样做：
Solution 1:
解决方案1：
Add a tinyint field to employees_ce and employees_sn that has a default value which is different in each table (This field represents a 'table identifier', so we'll call them tid_ce & tid_sn)
Create a Unique Index on each table using the table's PK and the table id field.
Add a tinyint field to your 'Deductions' table to store the second half of the foreign key (the Table ID)
Create 2 foreign keys in your 'Deductions' table (You can't enforce referential integrity, because either one key will be valid or the other...but never both:
ALTER TABLE [dbo].[Deductions] WITH NOCHECK ADD CONSTRAINT [FK_Deductions_employees_ce] FOREIGN KEY([id], [fk_tid]) REFERENCES [dbo].[employees_ce] ([empid], [tid]) NOT FOR REPLICATION GO ALTER TABLE [dbo].[Deductions] NOCHECK CONSTRAINT [FK_600_WorkComments_employees_ce] GO ALTER TABLE [dbo].[Deductions] WITH NOCHECK ADD CONSTRAINT [FK_Deductions_employees_sn] FOREIGN KEY([id], [fk_tid]) REFERENCES [dbo].[employees_sn] ([empid], [tid]) NOT FOR REPLICATION GO ALTER TABLE [dbo].[Deductions] NOCHECK CONSTRAINT [FK_600_WorkComments_employees_sn] GO employees_ce -------------- empid name tid khce1 prince 1 employees_sn ---------------- empid name tid khsn1 princess 2 deductions ---------------------- id tid name khce1 1 gold khsn1 2 silver ** id + tid creates a unique index **
向雇员_ce和雇员_sn添加一个tinyint字段，该字段在每个表中具有不同的默认值（该字段表示“表标识符”，因此我们将它们称为tid_ce和tid_sn）
使用表的 PK 和表 id 字段在每个表上创建一个唯一索引。
将 tinyint 字段添加到您的“Deductions”表以存储外键的后半部分（表 ID）
在您的“扣除”表中创建 2 个外键（您不能强制执行参照完整性，因为其中一个键将有效或另一个...但永远不会同时有效：
ALTER TABLE [dbo].[Deductions] WITH NOCHECK ADD CONSTRAINT [FK_Deductions_employees_ce] FOREIGN KEY([id], [fk_tid]) REFERENCES [dbo].[employees_ce] ([empid], [tid]) NOT FOR REPLICATION GO ALTER TABLE [dbo].[Deductions] NOCHECK CONSTRAINT [FK_600_WorkComments_employees_ce] GO ALTER TABLE [dbo].[Deductions] WITH NOCHECK ADD CONSTRAINT [FK_Deductions_employees_sn] FOREIGN KEY([id], [fk_tid]) REFERENCES [dbo].[employees_sn] ([empid], [tid]) NOT FOR REPLICATION GO ALTER TABLE [dbo].[Deductions] NOCHECK CONSTRAINT [FK_600_WorkComments_employees_sn] GO employees_ce -------------- empid name tid khce1 prince 1 employees_sn ---------------- empid name tid khsn1 princess 2 deductions ---------------------- id tid name khce1 1 gold khsn1 2 silver ** id + tid creates a unique index **
Solution 2:This solution allows referential integrity to be maintained: 1. Create a second foreign key field in 'Deductions' table , allow Null values in both foreign keys, and create normal foreign keys:
解决方案 2：此解决方案允许维护参照完整性： 1. 在 'Deductions' 表中创建第二个外键字段，在两个外键中都允许 Null 值，并创建普通外键：
employees_ce
--------------
empid name
khce1 prince
employees_sn
----------------
empid name
khsn1 princess
deductions
----------------------
idce idsn name
khce1 *NULL* gold
*NULL* khsn1 silver
Integrity is only checked if the column is not null, so you can maintain referential integrity.
仅当列不为空时才检查完整性，因此您可以保持参照完整性。
回答by Brian Sallee
I know this is long stagnant topic, but in case anyone searches here is how I deal with multi table foreign keys. With this technique you do not have any DBA enforced cascade operations, so please make sure you deal with DELETEand such in your code.
我知道这是一个长期停滞不前的话题，但如果有人在这里搜索，这是我处理多表外键的方式。使用这种技术，您没有任何 DBA 强制级联操作，因此请确保DELETE在您的代码中处理诸如此类。
Table 1 Fruit
pk_fruitid, name
1, apple
2, pear
Table 2 Meat
Pk_meatid, name
1, beef
2, chicken
Table 3 Entity's
PK_entityid, anme
1, fruit
2, meat
3, desert
Table 4 Basket (Table using fk_s)
PK_basketid, fk_entityid, pseudo_entityrow
1, 2, 2 (Chicken - entity denotes meat table, pseudokey denotes row in indictaed table)
2, 1, 1 (Apple)
3, 1, 2 (pear)
4, 3, 1 (cheesecake)
SO Op's Example would look like this
SO Op 的示例看起来像这样
deductions
--------------
type id name
1 khce1 gold
2 khsn1 silver
types
---------------------
1 employees_ce
2 employees_sn
回答by Sascha
Technically possible. You would probably reference employees_ce in deductions and employees_sn. But why don't you merge employees_sn and employees_ce? I see no reason why you have two table. No one to many relationship. And (not in this example) many columns.
技术上可行。您可能会在扣除和雇员_sn 中引用雇员_ce。但是你为什么不合并employees_sn 和employees_ce 呢？我看不出你有两张桌子的原因。没有一对多的关系。并且（不是在这个例子中）很多列。
If you do two references for one column, an employee musthave an entry in both tables.
如果对一列进行两次引用，则员工必须在两个表中都有一个条目。
回答by vmarquez
Yes, it is possible. You will need to define 2 FKs for 3rd table. Each FK pointing to the required field(s) of one table (ie 1 FK per foreign table).
对的，这是可能的。您需要为第 3 个表定义 2 个 FK。每个 FK 指向一个表的必填字段（即每个外部表 1 个 FK）。
回答by r00fus
Assuming you must have two tables for the two employee types for some reason, I'll extend on vmarquez's answer:
假设出于某种原因您必须为两种员工类型提供两个表，我将扩展 vmarquez 的回答：
Schema:
架构：
employees_ce (id, name)
employees_sn (id, name)
deductions (id, parentId, parentType, name)
Data in deductions:
扣减数据：
deductions table
id parentId parentType name
1 1 ce gold
2 1 sn silver
3 2 sn wood
...
This would allow you to have deductions point to any other table in your schema. This kind of relation isn't supported by database-level constraints, IIRC so you'll have to make sure your App manages the constraint properly (which makes it more cumbersome if you have several different Apps/services hitting the same database).
这将允许您将推论指向架构中的任何其他表。数据库级约束 IIRC 不支持这种关系，因此您必须确保您的应用程序正确管理约束（如果您有多个不同的应用程序/服务访问同一个数据库，这会变得更加麻烦）。
