外键可以为 NULL 和/或重复吗?
请为我澄清两件事:
- 外键可以为 NULL 吗?
- 外键可以重复吗?
据我所知，NULL 不应该用于外键，但在我的某些应用程序中，我可以在 Oracle 和SQL Server，我不知道为什么.
As fair as I know, NULL shouldn't be used in foreign keys, but in some application of mine I'm able to input NULL in both Oracle and SQL Server, and I don't know why.
推荐答案
简短回答:是的，可以为 NULL 或重复.
Short answer: Yes, it can be NULL or duplicate.
我想解释为什么外键可能需要为空或可能需要唯一或不唯一.首先记住外键只是要求该字段中的值必须首先存在于不同的表(父表)中.这就是 FK 的定义.根据定义，空不是一个值.Null 表示我们还不知道这个值是什么.
I want to explain why a foreign key might need to be null or might need to be unique or not unique. First remember a Foreign key simply requires that the value in that field must exist first in a different table (the parent table). That is all an FK is by definition. Null by definition is not a value. Null means that we do not yet know what the value is.
让我给你举一个真实的例子.假设您有一个存储销售建议的数据库.进一步假设每个提案只分配了一名销售人员和一名客户.因此，您的提案表将有两个外键，一个是客户 ID，另一个是销售代表 ID.但是，在创建记录时，并不总是分配销售代表(因为还没有人可以自由地处理它)，因此填写了客户 ID，但销售代表 ID 可能为空.换句话说，当您在输入数据时可能不知道它的值，但您知道表中需要输入的其他值时，通常您需要具有空 FK 的能力.要在 FK 中允许空值，通常您所要做的就是在具有 FK 的字段上允许空值.空值与其作为 FK 的想法是分开的.
Let me give you a real life example. Suppose you have a database that stores sales proposals. Suppose further that each proposal only has one sales person assigned and one client. So your proposal table would have two foreign keys, one with the client ID and one with the sales rep ID. However, at the time the record is created, a sales rep is not always assigned (because no one is free to work on it yet), so the client ID is filled in but the sales rep ID might be null. In other words, usually you need the ability to have a null FK when you may not know its value at the time the data is entered, but you do know other values in the table that need to be entered. To allow nulls in an FK generally all you have to do is allow nulls on the field that has the FK. The null value is separate from the idea of it being an FK.
是否唯一与表与父表是一对一还是一对多关系有关.现在，如果您有一对一的关系，您可以将所有数据都放在一个表中，但是如果表太宽或者数据属于不同的主题(员工 - 保险示例@tbone 给出了例如)，那么您需要带有 FK 的单独表.然后，您可能想让这个 FK 也成为 PK(保证唯一性)或对其施加唯一约束.
Whether it is unique or not unique relates to whether the table has a one-one or a one-many relationship to the parent table. Now if you have a one-one relationship, it is possible that you could have the data all in one table, but if the table is getting too wide or if the data is on a different topic (the employee - insurance example @tbone gave for instance), then you want separate tables with a FK. You would then want to make this FK either also the PK (which guarantees uniqueness) or put a unique constraint on it.
大多数 FK 都是一对多的关系，这就是您从 FK 中获得的，而无需在场上增加进一步的约束.例如，您有一个订单表和订单详细信息表.如果客户一次订购十件商品，则他有一个订单和十个订单明细记录，其中包含与 FK 相同的订单 ID.
Most FKs are for a one to many relationship and that is what you get from a FK without adding a further constraint on the field. So you have an order table and the order details table for instance. If the customer orders ten items at one time, he has one order and ten order detail records that contain the same orderID as the FK.
相关文章