外键可以为空和/或重复吗？

Question

外键可以为空和/或重复吗？

sqlsql-serveroracleforeign-keys

459

请为我澄清两件事：

外键可以为空吗？
外键可以重复吗？

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

- jams

从权威渠道得知：“外键允许所有键值都为NULL，即使没有匹配的主键或唯一键” https://docs.oracle.com/cd/B28359_01/appdev.111/b28424/adfns_constraints.htm#sthref549 - tbone

权威消息：「外键允许所有键值为空，即使没有匹配的主键或唯一键。」来源：https://docs.oracle.com/cd/B28359_01/appdev.111/b28424/adfns_constraints.htm#sthref549 - tbone

19

不能删除此内容，因为问题和答案都是有用的。可以自由编辑问题以使其更好。 - Jeff Atwood

1

可以将带有外键的表列设置为NULL吗？ - Michael Freidgeim

11个回答

53

1 - 自 SQL Server 2000 起，是的。

2 - 是的，只要它不是唯一约束或与唯一索引相关联。

- JNK

2

那个链接已经失效了。 - Mike G

即使是 WaybackMachine 也没有任何记录。最早的快照日期为2016年，但即使那时它也显示该文档已被停用。 - Semo

24

是的，外键可以为空，如上述高级程序员所述...我想再增加一种场景，需要外键为空... 假设我们在一个允许在图片和视频上进行评论的应用中有评论、图片和视频三个表。在评论表中，我们可以有两个外键PicturesId和VideosId，以及主键CommentId。因此，当你在视频上发表评论时，只需要VideosId，而pictureId将为空...如果你在图片上发表评论，则只需要PictureId，而VideosId将为空...

- Touseef Ahmed Awan

1

我认为有更好的方法来解决这个问题。而不是创建新的列，你可以有两列，即“id”和“type”，它们将包含外键表的id和名称。例如，id=1，type=Picture将表示与id 1链接到Picture表。使用此解决方案的优点是，当评论添加到其他表时，您不必创建新的列。缺点是在数据库级别上没有外键约束，而约束将必须在应用程序级别上实现。 - Agent47DarkSoul

11

@Agent：我们已经在生产环境中使用了这个“方案”。不要这样做，它很糟糕。查询变得非常混乱，“如果是类型1，则连接到此表，否则连接到另一个表”。这对我们来说是一场噩梦。最终我们按照这个答案所说的做法，为每种类型的连接创建了一个新列。创建列是很便宜的。它唯一的缺点就是有很多列会让Toad难以使用，但这只是Toad的一个缺陷。 - user5670895

1

@FighterJet Rails提供了一个很棒的ORM框架，该解决方案可以处理甚至复杂的查询。 - Agent47DarkSoul

3

@Agent：也许它可以......但如果你可以简单地做到，为什么要复杂化呢？也许“噩梦”这个词用错了：只是非常不方便。我们没有遭受数据完整性问题（很多）。 - user5670895

16

这取决于该“外键”在您关系中扮演的角色。

如果此“外键”也是您关系中的“键属性”，那么它不能为 NULL。
如果此“外键”是您关系中的普通属性，则可以为 NULL。

- shinxg

14

"Key attribute" 的意思是 "关键属性"，指的是对于某个实体或事物来说最为重要的特征或属性。 - Kwaku Biney

1

@KwakuBiney，我认为他的意思是“如果记录中该字段是必填项”（记录中的关键属性可能更好）。 - Nishant

1

@KwakuBiney，我认为他的意思是“如果记录中的字段是必填的”（记录中的关键属性可能更好）。 - Nishant

4

这里是一个使用Oracle语法的例子：
首先让我们创建一个名为 COUNTRY 的表。

CREATE TABLE TBL_COUNTRY ( COUNTRY_ID VARCHAR2 (50) NOT NULL ) ;
ALTER TABLE TBL_COUNTRY ADD CONSTRAINT COUNTRY_PK PRIMARY KEY ( COUNTRY_ID ) ;

创建表 PROVINCE

CREATE TABLE TBL_PROVINCE(
PROVINCE_ID VARCHAR2 (50) NOT NULL ,
COUNTRY_ID  VARCHAR2 (50)
);
ALTER TABLE TBL_PROVINCE ADD CONSTRAINT PROVINCE_PK PRIMARY KEY ( PROVINCE_ID ) ;
ALTER TABLE TBL_PROVINCE ADD CONSTRAINT PROVINCE_COUNTRY_FK FOREIGN KEY ( COUNTRY_ID ) REFERENCES TBL_COUNTRY ( COUNTRY_ID ) ;

这在Oracle中可以正常运行。请注意第二个表中的COUNTRY_ID外键没有“NOT NULL”。

现在，要向PROVINCE表插入一行，只需指定PROVINCE_ID即可。但是，如果您选择同时指定COUNTRY_ID，则必须已经存在于COUNTRY表中。

- Mouhcine

3

默认情况下，外键没有任何约束条件，外键可以为null和重复。

在创建表格/更改表格时，如果添加任何唯一性或非空约束，则只有它不允许null/重复值。

- nitin lalwani

1

简单来说，“非识别”实体之间的关系是ER模型的一部分，并且在设计ER图时在Microsoft Visio中可用。这是为了强制实体类型之间的基数关系为“零或多个”或“零或一个”。请注意，基数中的“零”而不是“一对多”的“一”。现在，非识别关系的示例，其中基数可能为“零”（非识别），是当我们说一个实体-A中的记录/对象“可能”或“可能不”具有指向另一个实体-B中记录的值时。由于实体-A的一个记录可能会将自身标识为实体-B的记录，因此实体-B中应该有一个列来存储实体-B的记录的标识值。如果实体-A中没有记录标识实体-B中的记录/对象，则此列可以为“Null”。

在面向对象（现实世界）范例中，有时候Class-B的一个对象并不一定依赖于Class-A的对象来存在（强耦合），这意味着Class-B与Class-A之间是松散耦合的，Class-A可以“包含”（Containment）一个Class-A的对象，而不是Class-B的对象必须具有（Composition）Class-A的对象才能创建Class-B的对象。

从SQL查询的角度看，您可以查询实体B中所有为实体B保留的外键“非空”的记录。这将带来所有具有Entity-A行对应值的记录，或者所有具有Null值的记录都是在Entity-B中没有任何记录的记录。

- Fakhar

1

一个外键可以为NULL吗？

现有的答案都集中在单列情况下。如果我们考虑多列外键，我们可以使用SQL标准中定义的MATCH [SIMPLE | PARTIAL | FULL]子句来获得更多选项：

将插入到引用列中的值与参考表和参考列的值使用给定的匹配类型进行匹配。有三种匹配类型：MATCH FULL、MATCH PARTIAL和MATCH SIMPLE（默认）。 MATCH FULL不允许多列外键中的一列为空，除非所有外键列都为空；如果它们都为空，则不需要在参考表中具有匹配行。MATCH SIMPLE允许任何外键列为空；如果它们中的任何一个为空，则该行不需要在参考表中具有匹配行。MATCH PARTIAL尚未实现。

（当然，可以对引用列应用NOT NULL约束以防止出现这些情况。）

示例：

CREATE TABLE A(a VARCHAR(10), b VARCHAR(10), d DATE , UNIQUE(a,b));
INSERT INTO A(a, b, d) 
VALUES (NULL, NULL, NOW()),('a', NULL, NOW()),(NULL, 'b', NOW()),('c', 'b', NOW());

CREATE TABLE B(id INT PRIMARY KEY, ref_a VARCHAR(10), ref_b VARCHAR(10));

-- MATCH SIMPLE - default behaviour nulls are allowed
ALTER TABLE B ADD CONSTRAINT B_Fk FOREIGN KEY (ref_a, ref_b) 
REFERENCES A(a,b) MATCH SIMPLE;

INSERT INTO B(id, ref_a, ref_b) VALUES (1, NULL, 'b');  

-- (NULL/'x') 'x' value does not exists in A table, but insert is valid
INSERT INTO B(id, ref_a, ref_b) VALUES (2, NULL, 'x');  

ALTER TABLE B DROP CONSTRAINT IF EXISTS B_Fk; -- cleanup

-- MATCH PARTIAL - not implemented
ALTER TABLE B ADD CONSTRAINT B_Fk FOREIGN KEY (ref_a, ref_b) 
REFERENCES A(a,b) MATCH PARTIAL;
-- ERROR:  MATCH PARTIAL not yet implemented

DELETE FROM B; ALTER TABLE B DROP CONSTRAINT IF EXISTS B_Fk; -- cleanup

-- MATCH FULL nulls are not allowed
ALTER TABLE B ADD CONSTRAINT B_Fk FOREIGN KEY (ref_a, ref_b) 
REFERENCES A(a,b) MATCH FULL;

-- FK is defined, inserting NULL as part of FK
INSERT INTO B(id, ref_a, ref_b) VALUES (1, NULL, 'b');
-- ERROR:  MATCH FULL does not allow mixing of null and nonnull key values.

-- FK is defined, inserting all NULLs - valid
INSERT INTO B(id, ref_a, ref_b) VALUES (1, NULL, NULL);

db<>fiddle示例

- Lukasz Szozda

-1

我认为最好考虑表中可能存在的基数。我们可能会有最小基数为零的情况。当它是可选的时候，相关表中元组的最小参与度可以为零，现在你面临的问题是允许外键值为空的必要性。

但答案是一切都取决于业务。

- user9274383

-6

外键的概念基于在主表中引用已经存在的值。这就是为什么在其他表中称之为外键。这个概念被称为参照完整性。如果将外键声明为空字段，它会违反参照完整性的逻辑。它将指向什么？它只能引用主表中存在的内容。因此，我认为将外键字段声明为空是错误的。

- SQLDev

2

它可以引用“nothing”或者你还不知道它的值NULL，但是引用完整性要求如果它引用了“something”，那么这个“something”必须存在。 - yaxe

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- HLGEM · Accepted Answer

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

我想解释一下为什么一个外键可能需要为空或需要唯一或不唯一。首先记住，一个外键只需要该字段中的值必须首先存在于另一个表（父表）中，这就是定义中的FK。按照定义，空值并不是一个值。空值意味着我们尚不知道该值是什么。

让我举个现实生活的例子。假设您有一个存储销售提案的数据库。更进一步地说，假设每个提案只分配了一个销售人员和一个客户。因此，您的提案表将具有两个外键，一个与客户ID相关联，另一个与销售代表ID相关联。但是，在创建记录时，并不总是会分配销售代表（因为没有人有时间去处理它），因此填写客户ID，而销售代表ID可能为空。换句话说，通常情况下，当您在输入数据时可能不知道其值，但是您确实知道其他需要输入的表格中的值时，需要能够具有一个空的FK。要允许FK中的空值，通常只需允许在具有FK的字段上允许空值即可。空值与FK的概念是分开的。

它是否唯一或不唯一与该表是否具有一对一或一对多与父表的关系有关。如果您有一个一对一的关系，那么将所有数据都存储在一个表格中是可能的，但是如果表格变得太宽或者数据属于不同的主题（例如@tbone给出的“员工-保险”示例），那么就需要使用带有FK的单独的表格。然后，您要么希望将此FK也作为PK（这可以保证唯一性），要么在其上放置唯一约束。

大多数FK是用于一对多关系的，这就是从FK中获取而无需在该字段上添加进一步约束的内容。例如，您有一个订单表和一个订单详细信息表。如果客户一次订购10件物品，则他有一个订单和十个包含相同订单ID（FK）的订单详细记录。