外键可以是NULL和/或重复吗？

问题描述

请为我澄清两件事：

1. 外键可以为NULL吗？
   
2. 外键是否可以重复？

就我所知， NULL 不应该在外键中使用，但在我的一些应用程序中，我可以在Oracle和SQL Server中输入 NULL ，我不知道为什么。

解决方案

简单的回答：是的，它可以是NULL或重复。

让我给你一个真实的例子。假设您有一个存储销售建议的数据库。进一步假设每个提案只有一个销售人员和一个客户。所以你的建议表将有两个外键，一个是客户端ID，另一个是销售代表ID。但是，在创建记录时，销售代表并不总是被分配（因为没有人可以自由处理），所以客户ID被填写，但销售代表ID可能为空。换句话说，通常你需要在输入数据的时候不知道数值的能力，但是你知道表中需要输入的其他数值。为了允许FK中的空值，通常所有你需要做的就是允许有FK的字段上的空值。空值与作为FK的想法是分开的。

是唯一的还是不唯一涉及到表是与父表有一对一还是一对多的关系。现在，如果你有一对一的关系，你可能会把所有的数据全部放在一张表中，但是如果表格变得太宽泛，或者数据是在不同的话题上（@tbone雇员保险示例给出的例如），那么你想要与FK分开的表。然后你会想要使这个FK或者PK（保证唯一性）或者给它一个唯一的约束。

大多数FK是一对多的关系，这就是你从FK得到的，而不是在这个领域增加一个限制。所以你有一个订单表和订单详细信息表。如果客户一次订购10件商品，则他有一个订单和十个订单明细记录，其中包含与FK相同的订单ID。

Please clarify two things for me:

1. Can a Foreign key be NULL?
2. Can a Foreign key be duplicate?

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.

解决方案

Short answer: Yes, it can be NULL or duplicate.

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.

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.

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.

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.

这篇关于外键可以是NULL和/或重复吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！