SQL 单个列的多个外键
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1186931/
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
Multiple foreign keys to a single column
提问by Aaron Powell
I'm defining a database for a customer/ order system where there are two highly distinct types of customers. Because they are so different having a single customer table would be very ugly (it'd be full of null columns as they are pointless for one type).
我正在为客户/订单系统定义一个数据库，其中有两种高度不同的客户类型。因为它们是如此不同，只有一个客户表会非常难看（它会充满空列，因为它们对于一种类型毫无意义）。
Their orders though are in the same format. Is it possible to have a CustomerIdcolumn in my Order table which has a foreign key to both the Customer Types? I have set it up in SQL server and it's given me no problems creatingthe relationships, but I'm yet to try inserting any data.
他们的订单虽然格式相同。是否可以CustomerId在我的 Order 表中有一列对两种客户类型都有外键？我已经在 SQL Server 中设置了它，并且在创建关系时没有问题，但我还没有尝试插入任何数据。
Also, I'm planning on using nHibernate as the ORM, could there be any problems introduced by doing the relationships like this?
另外，我打算使用 nHibernate 作为 ORM，这样的关系会不会带来任何问题？
回答by Guffa
No, you can't have a single field as a foreign key to two different tables. How would you tell where to look for the key?
不，您不能将单个字段作为两个不同表的外键。你怎么知道去哪里找钥匙？
You would at least need a field that tells what kind of user it is, or two separate foreign keys.
您至少需要一个字段来说明它是哪种用户，或者需要两个单独的外键。
You could also put the information that is common for all users in one table and have separate tables for the information that is specific for the user types, so that you have a single table with user id as primary key.
您还可以将所有用户通用的信息放在一个表中，并为特定于用户类型的信息使用单独的表，这样您就有一个以用户 ID 作为主键的表。
回答by Marc Gravell
A foreign key can only reference a single primary key, so no. However, you could use a bridge table:
外键只能引用一个主键，所以不能。但是，您可以使用桥接表：
CustomerA <---- CustomerA_Orders ----> Order
CustomerB <---- CustomerB_Orders ----> Order
So Order doesn't even havea foreign key; whether this is desirable, though...
所以 Order 甚至没有外键；这是否是可取的，虽然......
回答by Dave G.
I inherited a SQL Server database where this was done (a single column used in four foreign key relationships with four unrelated tables), so yes, it's possible. My predecessor is gone, though, so I can't ask why he thought it was a good idea.
我继承了一个完成此操作的 SQL Server 数据库（在四个外键关系中使用的单个列与四个不相关的表），所以是的，这是可能的。不过，我的前任已经走了，所以我不能问为什么他认为这是个好主意。
He used a GUID column ("uniqueidentifier" type) to avoid the ambiguity problem, and he turned off constraint checking on the foreign keys, since it's guaranteed that only one will match. But I can think of lots of reasons that you shouldn't, and I haven't thought of any reasons you should.
他使用了一个 GUID 列（“唯一标识符”类型）来避免歧义问题，并且他关闭了对外键的约束检查，因为它保证只有一个匹配。但是我能想到很多你不应该的理由，我也没有想到你应该的任何理由。
Yours does sound like the classical "specialization" problem, typically solved by creating a parent table with the shared customer data, then two child tables that contain the data unique to each class of customer. Your foreign key would then be against the parent customer table, and your determination of which type of customer would be based on which child table had a matching entry.
您的问题听起来确实像经典的“专业化”问题，通常通过创建一个包含共享客户数据的父表和两个包含每个客户类别唯一数据的子表来解决。然后，您的外键将针对父客户表，并且您将根据哪个子表具有匹配条目来确定哪种类型的客户。
回答by Raj
You can create a foreign key referencing multiple tables. This feature is to allow vertical partioining of your table and still maintain referential integrity. In your case however, this is not applicable.
您可以创建引用多个表的外键。此功能是为了允许您的表垂直分区并仍然保持参照完整性。但是，在您的情况下，这不适用。
Your best bet would be to have a CustomerType table with possible columns - CustomerTypeID, CustomerID, where CustomerID is the PK and then refernce your OrderID table to CustomerID.
您最好的选择是拥有一个包含可能的列的 CustomerType 表 - CustomerTypeID、CustomerID，其中 CustomerID 是 PK，然后将您的 OrderID 表引用到 CustomerID。
Raj
拉吉
回答by Walter Mitty
Two distinct types of customer is a classic case of types and subtypes or, if you prefer, classes and subclasses. Hereis an answer from another question.
两种不同类型的客户是类型和子类型的经典案例，或者，如果您愿意，可以使用类和子类。 这是另一个问题的答案。
Essentially, the class-table-inheritance technique is like Arnand's answer. The use of the shared-primary-key technique is what allows you to get around the problems created by two types of foreign key in one column. The foreign key will be customer-id. That will identify one row in the customer table, and also one row in the appropriate kind of customer type table, as the case may be.
本质上，类表继承技术就像 Arnand 的答案。共享主键技术的使用使您可以解决由一列中的两种类型的外键产生的问题。外键将是客户 ID。这将标识客户表中的一行，以及适当类型的客户类型表中的一行，视情况而定。
回答by Jay
As noted, if the key is, say, 12345, how would you know which table to look it up in? You could, I suppose, do something to insure that the key values for the two tables never overlapped, but this is too ugly and painful to contemplate. You could have a second field that says which customer type it is. But if you're going to have two fields, why not have one field for customer type 1 id and another for customer type 2 id.
如前所述，如果键是 12345，您怎么知道要在哪个表中查找？我想，你可以做一些事情来确保两个表的键值永远不会重叠，但这太丑陋和痛苦了，无法考虑。您可以有第二个字段来说明它是哪种客户类型。但是，如果您将有两个字段，为什么不为客户类型 1 id 设置一个字段，为客户类型 2 id 设置另一个字段。
Without knowing more about your app, my first thought is that you really should have a general customer table with the data that is common to both, and then have two additional tables with the data specific to each customer type. I would think that there must be a lot of data common to the two -- basic stuff like name and address and customer number at the least -- and repeating columns across tables sucks big time. The additional tables could then refer back to the base table. As there is then a single key for the base table, the issue of foreign keys having to know which table to refer to evaporates.
在不了解您的应用程序的更多信息的情况下，我的第一个想法是您确实应该有一个包含两者通用数据的通用客户表，然后有两个附加表，其中包含特定于每种客户类型的数据。我认为这两者必须有很多共同的数据——至少是姓名、地址和客户编号等基本内容——并且跨表重复列很费时间。附加表然后可以引用回基表。由于基表只有一个键，因此外键必须知道要引用哪个表的问题就消失了。
回答by Anand
- Create a "customer" table include all the columns that have same data for both types of customer.
- Than create table "customer_a" and "customer_b"
Use "customer_id" from "consumer" table as foreign key in "customer_a" and "customer_b"
customer | --------------------------------- | | cusomter_a customer_b
- 创建一个“客户”表，其中包含对两种类型的客户具有相同数据的所有列。
- 比创建表“customer_a”和“customer_b”
使用“consumer”表中的“customer_id”作为“customer_a”和“customer_b”中的外键
customer | --------------------------------- | | cusomter_a customer_b
