请为我澄清两点:

外键可以为NULL吗? 外键可以被复制吗?

正如我所知道的那样,NULL不应该用于外键,但在我的一些应用程序中,我可以在Oracle和SQL Server中输入NULL,我不知道为什么。


当前回答

这取决于这个外键在关系中扮演什么角色。

如果这个外键也是你的关系中的键属性,那么它不能为NULL 如果这个外键是关系中的一个普通属性,那么它可以为NULL。

其他回答

1 -是的,因为至少SQL Server 2000。

2 -是的,只要它不是UNIQUE约束或链接到唯一索引。

Yes foreign key can be null as told above by senior programmers... I would add another scenario where Foreign key will required to be null.... suppose we have tables comments, Pictures and Videos in an application which allows comments on pictures and videos. In comments table we can have two Foreign Keys PicturesId, and VideosId along with the primary Key CommentId. So when you comment on a video only VideosId would be required and pictureId would be null... and if you comment on a picture only PictureId would be required and VideosId would be null...

我认为一个表的外键也是另一个表的主键。所以它不允许空值。所以外键没有空值的问题。

这取决于这个外键在关系中扮演什么角色。

如果这个外键也是你的关系中的键属性,那么它不能为NULL 如果这个外键是关系中的一个普通属性,那么它可以为NULL。

简单回答:是的,它可以是NULL或重复。

我想解释为什么外键可能需要为空,或者可能需要唯一或不唯一。首先记住,外键只是要求该字段中的值必须首先存在于另一个表(父表)中。这就是所有FK的定义。Null根据定义不是一个值。Null意味着我们还不知道值是什么。

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.

大多数FK是一对多关系,这就是你从FK中得到的,而不需要在字段上增加进一步的约束。例如,你有一个订单表和订单明细表。如果客户一次订购10件商品,那么他有一个订单和10个订单详细记录,其中包含与FK相同的orderID。