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

Can a foreign key be NULL and/or duplicate?

提问by jams

Please clarify two things for me:

请为我澄清两件事：

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

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

As fair as I know, NULLshouldn't be used in foreign keys, but in some application of mine I'm able to input NULLin both Oracle and SQL Server, and I don't know why.

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

回答by HLGEM

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

简短回答：是的，它可以是 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.

我想解释为什么外键可能需要为空或可能需要唯一或不唯一。首先记住外键只是要求该字段中的值必须首先存在于不同的表（父表）中。这就是 FK 的定义。根据定义，空不是一个值。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.

让我给你举一个现实生活中的例子。假设您有一个存储销售建议的数据库。进一步假设每个提案只分配了一名销售人员和一名客户。因此，您的提案表将有两个外键，一个是客户 ID，另一个是销售代表 ID。但是，在创建记录时，并不总是分配销售代表（因为还没有人可以自由地处理它），因此填写了客户 ID，但销售代表 ID 可能为空。换句话说，当您在输入数据时可能不知道它的值，但您知道表中需要输入的其他值时，通常您需要具有空 FK 的能力。要在 FK 中允许空值，通常您所要做的就是在具有 FK 的字段上允许空值。空值与其作为 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.

是否唯一与表与父表是一对一还是一对多关系有关。现在，如果您有一对一的关系，您可以将所有数据都放在一个表中，但是如果表太宽或者数据属于不同的主题（员工 - 保险示例@tbone 给出了例如），那么您需要带有 FK 的单独表。然后，您可能想让这个 FK 也成为 PK（保证唯一性）或对其施加唯一约束。

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.

大多数 FK 都是一对多的关系，这就是您从 FK 中获得的，而无需在该领域增加进一步的约束。例如，您有一个订单表和订单详细信息表。如果客户一次订购十件商品，则他有一个订单和十个订单明细记录，其中包含与 FK 相同的订单 ID。

回答by JNK

1 - Yes, since at least SQL Server 2000.

1 -是的，至少从 SQL Server 2000 开始。

2 - Yes, as long as it's not a UNIQUEconstraint or linked to a unique index.

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

回答by tbone

From the horse's mouth:

从马嘴里说：

Foreign keys allow key values that are all NULL, even if there are no matching PRIMARY or UNIQUE keys

No Constraints on the Foreign Key

When no other constraints are defined on the foreign key, any number of rows in the child table can reference the same parent key value. This model allows nulls in the foreign key. ...

NOT NULL Constraint on the Foreign Key

When nulls are not allowed in a foreign key, each row in the child table must explicitly reference a value in the parent key because nulls are not allowed in the foreign key.

Any number of rows in the child table can reference the same parent key value, so this model establishes a one-to-many relationship between the parent and foreign keys. However, each row in the child table must have a reference to a parent key value; the absence of a value (a null) in the foreign key is not allowed. The same example in the previous section can be used to illustrate such a relationship. However, in this case, employees must have a reference to a specific department.

UNIQUE Constraint on the Foreign Key

When a UNIQUE constraint is defined on the foreign key, only one row in the child table can reference a given parent key value. This model allows nulls in the foreign key.

This model establishes a one-to-one relationship between the parent and foreign keys that allows undetermined values (nulls) in the foreign key. For example, assume that the employee table had a column named MEMBERNO, referring to an employee membership number in the company insurance plan. Also, a table named INSURANCE has a primary key named MEMBERNO, and other columns of the table keep respective information relating to an employee insurance policy. The MEMBERNO in the employee table must be both a foreign key and a unique key:

• To enforce referential integrity rules between the EMP_TAB and INSURANCE tables (the FOREIGN KEY constraint)

• To guarantee that each employee has a unique membership number (the UNIQUE key constraint)

UNIQUE and NOT NULL Constraints on the Foreign Key

When both UNIQUE and NOT NULL constraints are defined on the foreign key, only one row in the child table can reference a given parent key value, and because NULL values are not allowed in the foreign key, each row in the child table must explicitly reference a value in the parent key.

外键允许全为 NULL 的键值，即使没有匹配的 PRIMARY 或 UNIQUE 键

外键没有限制

当外键上没有定义其他约束时，子表中的任意数量的行都可以引用相同的父键值。此模型允许外键为空值。...

外键的 NOT NULL 约束

当外键中不允许出现空值时，子表中的每一行都必须显式引用父键中的一个值，因为外键中不允许出现空值。

子表中的任意多行都可以引用同一个父键值，因此该模型在父键和外键之间建立了一对多的关系。但是，子表中的每一行都必须有一个对父键值的引用；不允许在外键中缺少值（空值）。可以使用上一节中的相同示例来说明这种关系。但是，在这种情况下，员工必须参考特定部门。

外键的唯一约束

当在外键上定义了 UNIQUE 约束时，子表中只有一行可以引用给定的父键值。此模型允许外键为空值。

此模型在父键和外键之间建立一对一的关系，允许外键中存在未确定的值（空值）。例如，假设员工表有一个名为 MEMBERNO 的列，它指的是公司保险计划中的员工会员编号。此外，名为 INSURANCE 的表具有名为 MEMBERNO 的主键，该表的其他列保存与员工保险单相关的相应信息。雇员表中的 MEMBERNO 必须既是外键又是唯一键：

• 在 EMP_TAB 和 INSURANCE 表之间强制执行参照完整性规则（FOREIGN KEY 约束）

• 保证每个员工都有唯一的会员编号（UNIQUE 键约束）

外键的 UNIQUE 和 NOT NULL 约束

当在外键上同时定义了 UNIQUE 和 NOT NULL 约束时，子表中只有一行可以引用给定的父键值，并且由于外键中不允许 NULL 值，因此子表中的每一行都必须显式引用父键中的一个值。

See this:

看到这个：

Oracle 11g link

Oracle 11g 链接

回答by Touseef Ahmed Awan

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...

是的，正如上面的高级程序员所说，外键可以为空......我会添加另一个场景，其中外键需要为空......假设我们在一个允许对图片和图片进行评论的应用程序中有表格评论，图片和视频视频。在评论表中，我们可以有两个外键 PicturesId 和 VideosId 以及主键 CommentId。因此，当您对视频发表评论时，仅需要 VideosId 并且 pictureId 将为空...如果您对图片发表评论，则仅需要 PictureId 并且 VideosId 将为空...

回答by shinxg

it depends on what role this foreign keyplays in your relation.

这取决于这foreign key在你们的关系中扮演什么角色。

1. if this foreign keyis also a key attributein your relation, then it can't be NULL
2. if this foreign keyis a normal attribute in your relation, then it can be NULL.

1. 如果这foreign key也是key attribute您的关系中的一个，那么它不能为 NULL
2. 如果这foreign key是您关系中的正常属性，则它可以为 NULL。

回答by Mouhcine

Here's an example using Oracle syntax:
First let's create a table COUNTRY

这是使用 Oracle 语法的示例：
首先让我们创建一个表 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

创建表 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. Notice the COUNTRY_ID foreign key in the second table doesn't have "NOT NULL".

这在 Oracle 中运行得非常好。请注意，第二个表中的 COUNTRY_ID 外键没有“NOT NULL”。

Now to insert a row into the PROVINCE table, it's sufficient to only specify the PROVINCE_ID. However, if you chose to specify a COUNTRY_ID as well, it must exist already in the COUNTRY table.

现在要将一行插入到 PROVINCE 表中，只需指定 PROVINCE_ID 就足够了。但是，如果您也选择指定 COUNTRY_ID，则它必须已存在于 COUNTRY 表中。

回答by nitin lalwani

By default there are no constraints on the foreign key, foreign key can be null and duplicate.

默认情况下外键没有限制，外键可以为空和重复。

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.

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

回答by Fakhar

Simply put, "Non-identifying" relationships between Entities is part of ER-Model and is available in Microsoft Visio when designing ER-Diagram. 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".

简单地说，实体之间的“非识别”关系是 ER-Model 的一部分，在设计 ER-Diagram 时可在 Microsoft Visio 中使用。这是强制执行“零或大于零”或“零或一”类型的实体之间的基数所必需的。请注意基数中的“零”而不是“一对多”中的“一”。

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.

现在，基数可能为“零”（非识别）的非识别关系的例子是当我们说一个实体中的记录/对象时 - A“可能”或“可能不”有一个值作为对记录的引用/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. This column may be "Null" if no record in Entity-A identifies the record/s (or, object/s) in Entity-B.

由于实体 A 的一条记录有可能将自己标识为其他实体 B 的记录，因此实体 B 中应该有一列具有实体 B 记录的身份值。如果实体 A 中没有记录标识实体 B 中的记录（或对象），则此列可能为“空”。

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 类对象，这意味着 B 类与类松散耦合- A 类可以“包含”（Containment）A 类对象，而不是 B 类对象的概念必须具有（组合）A 类对象，因为它的（类对象- 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. 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.

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

回答by user9274383

I think it is better to consider the possible cardinality we have in the tables. We can have possible minimum cardinality zero. When it is optional, the minimum participation of tuples from the related table could be zero, Now you face the necessity of foreign key values to be allowed null.

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

But the answer is it all depends on the Business.

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

回答by SQLDev

The idea of a foreign key is based on the concept of referencing a value that already exists in the main table. That is why it is called a foreign key in the other table. This concept is called referential integrity. If a foreign key is declared as a null field it will violate the the very logic of referential integrity. What will it refer to? It can only refer to something that is present in the main table. Hence, I think it would be wrong to declare a foreign key field as null.

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