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

本文翻译自：Can a foreign key be NULL and/or duplicate?

Please clarify two things for me: 请为我澄清两件事：

1. Can a Foreign key be NULL? 外键可以为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. 据我所知，不应该在外键中使用NULL ，但是在我的某些应用程序中，我能够在Oracle和SQL Server中输入NULL ，但我不知道为什么。

---

#1楼

参考：https://stackoom.com/question/vMeG/外键可以为NULL和-或重复吗

---

#2楼

Here's an example using Oracle syntax: 这是一个使用Oracle语法的示例：
First let's create a table COUNTRY 首先，我们创建一个表COUNTRY

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

Create the table 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 ) ;

This runs perfectly fine in Oracle. 这在Oracle中运行得很好。 Notice the COUNTRY_ID foreign key in the second table doesn't have "NOT NULL". 请注意，第二个表中的COUNTRY_ID外键没有“ NOT NULL”。

Now to insert a row into the PROVINCE table, it's sufficient to only specify the PROVINCE_ID. 现在在PROVINCE表中插入一行，仅指定PROVINCE_ID就足够了。 However, if you chose to specify a COUNTRY_ID as well, it must exist already in the COUNTRY table. 但是，如果您还选择指定COUNTRY_ID，则它必须已经存在于COUNTRY表中。

---

#3楼

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

---

#4楼

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. 在注释表中，我们可以有两个外键PicturesId和VideosId以及主键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... 因此，当您在视频上发表评论时，仅视频ID是必填项，而pictureId将为null ...，如果您在图片上发表评论，则仅需PictureId，而VideosId将为null ...

---

#5楼

Simply put, "Non-identifying" relationships between Entities is part of ER-Model and is available in Microsoft Visio when designing ER-Diagram. 简而言之，实体之间的“非识别”关系是ER模型的一部分，并且在设计ER图时在Microsoft Visio中可用。 This is required to enforce cardinality between Entities of type " zero or more than zero", or "zero or one". 这是强制实施类型“零或大于零”或“零或一”的实体之间的基数。 Note this "zero" in cardinality instead of "one" in "one to many". 请注意基数上的此“零”，而不是“一对多”中的“一”。

Now, example of non-identifying relationship where cardinality may be "zero" (non-identifying) is when we say a record / object in one entity-A "may" or "may not" have a value as a reference to the record/s in another Entity-B. 现在，基数可能为“零”（非标识）的非标识关系的示例是当我们说一个实体中的记录/对象时，“可能”或“可能不会”具有一个值作为对记录的引用/ s在另一个实体B中。

As, there is a possibility for one record of entity-A to identify itself to the records of other Entity-B, therefore there should be a column in Entity-B to have the identity-value of the record of Entity-B. 由于实体A的一条记录有可能将自己标识为其他实体B的记录，因此实体B中应有一列具有实体B的记录的标识值。 This column may be "Null" if no record in Entity-A identifies the record/s (or, object/s) in Entity-B. 如果实体A中没有记录标识实体B中的记录（或对象），则此列可以为“ Null”。

In Object Oriented (real-world) Paradigm, there are situations when an object of Class-B does not necessarily depends (strongly coupled) on object of class-A for its existence, which means Class-B is loosely-coupled with Class-A such that Class-A may "Contain" (Containment) an object of Class-A, as opposed to the concept of object of Class-B must have (Composition) an object of Class-A, for its (object of class-B) creation. 在面向对象（现实世界）范式中，某些情况下，B类的对象不一定存在（依赖于（强烈耦合））A类对象，这意味着Class-B与Class-B松散耦合。使得A类可以“包含”（包含）A类对象，而不是B类对象的概念必须具有（Composition）A类对象，因为其（Class-object） B）创作。

From SQL Query point of view, you can query all records in entity-B which are "not null" for foreign-key reserved for Entity-B. 从SQL查询的角度来看，您可以查询实体B中为“实体B”保留的外键“不为空”的所有记录。 This will bring all records having certain corresponding value for rows in Entity-A alternatively all records with Null value will be the records which do not have any record in Entity-A in Entity-B. 这将为实体A中的行带来具有特定对应值的所有记录，或者所有具有Null值的记录将成为实体B中的实体A中没有任何记录的记录。

---

#6楼

By default there are no constraints on the foreign key, foreign key can be null and duplicate. 默认情况下，外键没有约束，外键可以为null和重复项。

while creating a table / altering the table, if you add any constrain of uniqueness or not null then only it will not allow the null/ duplicate values. 在创建表/更改表时，如果添加任何唯一性约束或不为null，则只有它将不允许null /重复值。