SQL 外键可以为空吗？

Can Foreign Key be null?

提问by notfound90

In our database project we have a table Salethat has an primary key and two exclusive foreign keys: Vehicle_IDand Piece_ID. For example if we sell a vehicle we need Vehicle_IDas a foreign key but not Piece_ID. Can we put NULL to Piece_ID, could a foreign key be null? Or is there a way to do this job?

在我们的数据库项目中，我们有一个表Sale，它有一个主键和两个独占外键： Vehicle_ID和Piece_ID。例如，如果我们出售一辆车，我们需要Vehicle_ID作为外键而不是Piece_ID. 我们可以将 NULL 设置为Piece_ID，外键可以为空吗？或者有没有办法完成这项工作？

Thanks.

谢谢。

回答by APC

The column (or columns) of a primary key must be NOT NULL. A record cannot be uniquely identified by a NULL. So the ID columns on the referenced end of the foreign key must be defined as NOT NULL.

主键的列（或多列）必须为 NOT NULL。一条记录不能由 NULL 唯一标识。因此，外键引用端的 ID 列必须定义为 NOT NULL。

However, it is a legitimate design decision for a foreign key relationship to be optional, and the way to represent that is by making the referencing end of the key optional, i.e. allowing NULLs.

然而，外键关系是可选的，这是一个合理的设计决定，而表示这一点的方法是通过使键的引用端可选，即允许空值。

In data modelling terms what you have described is an (exclusive) arc: "a table ... with two or more foreign keys where one and only one of them can be non-null." In logical modelling arcs are perfectly acceptable, but there is a strong body of opinion in favour of implementing them as separate tables. In your scenario that would be a generic Saletable plus two sub-type tables, VehicleSaleand PieceSale.

在数据建模术语中，您所描述的是一个（独占）弧：“一个表......具有两个或多个外键，其中一个并且只有一个可以是非空的。” 在逻辑建模中，弧线是完全可以接受的，但有强烈的意见支持将它们作为单独的表来实现。在您的场景中，这将是一个通用Sale表加上两个子类型表，VehicleSale并且PieceSale.

The advantages of the separate table implementation are:

分表实现的优点是：

• easier to enforce the foreign key constraints;
• easier to add additional columns relating to (say) vehicle sales which don't apply to piece sales;
• easier to extend the model with additional sub-types;
• clearer data model, which can simplify application development.

• 更容易执行外键约束；
• 更容易添加与（例如）车辆销售相关的附加列，这些列不适用于计件销售；
• 使用其他子类型更容易扩展模型；
• 更清晰的数据模型，可以简化应用程序的开发。

However, the advantages aren't all one-way. While it is pretty easy to ensure that a Saleapplies either to a VehicleSaleor a PieceSalebut not both, enforcing a rule that a Salemusthave a child record actually gets pretty gnarly.

然而，优势并不都是单方面的。虽然很容易确保 aSale适用于 aVehicleSale或 aPieceSale但不能同时适用于两者，但强制执行Sale必须具有子记录的规则实际上变得非常粗糙。

So, the prevailing advice is that an exclusive arc is mistaken, and it is generally good advice. But it's not as clear as some make out.

因此，普遍的建议是独占弧是错误的，这通常是好的建议。但它并不像某些人所说的那么清楚。

回答by Branko Dimitrijevic

Answer:

回答：

Yes, you can do that - make the FKs themselves NULL-able, but add a CHECK to ensure exactly one of them contains a non-NULL value.

是的，您可以这样做 - 使 FK 本身可以为 NULL，但添加一个 CHECK 以确保其中一个包含非 NULL 值。

Elaboration:

细化：

A FK can be NULL-able, which models a 1..0:N relationship. In other words, a "child" row can(but is not required to) have a "parent" row.

FK 可以是 NULL-able，它模拟 1..0:N 关系。换句话说，一个“子”行可以（但不是必须）有一个“父”行。

A NOT NULL foreign key models a 1:N relationship. In other words, every child musthave a parent.

NOT NULL 外键模拟 1:N 关系。换句话说，每个孩子都必须有一个父母。

When a FK is composite¹, and at least one of its fields is NULL-able, a mix of NULL and non-NULL values is handled in a special way:

当一个 FK 是复合¹，并且它的至少一个字段可以为 NULL 时，NULL 和非 NULL 值的混合以一种特殊的方式处理：

• If the FK is MATCH FULL, either allvalues must be NULL or all values must be non-NULL and match some parent row.
• If the FK is MATCH PARTIAL, only those values that are non-NULL must match some parent row (NULLs are ignored).
• If the FK is MATCH SIMPLE, either all values are non-NULL and must match some parent row, or there is at least one NULL value (in which case the non-NULLs are not required to match).

• 如果 FK 是 MATCH FULL，则所有值必须为 NULL 或所有值必须为非 NULL 并匹配某个父行。
• 如果 FK 是 MATCH PARTIAL，则只有那些非 NULL 的值必须匹配某个父行（NULL 被忽略）。
• 如果 FK 是 MATCH SIMPLE，要么所有值都是非 NULL 并且必须匹配某个父行，要么至少有一个 NULL 值（在这种情况下，非 NULL 不需要匹配）。

Most DBMSes default to MATCH SIMPLE (with the notable exception of MS Access) and most don't support anything but the default.

大多数 DBMS 默认为 MATCH SIMPLE（MS Access除外），并且大多数不支持除默认值之外的任何内容。

---

---

¹Which you don't have here - just mentioning it for completeness.

¹你在这里没有 - 只是为了完整性而提及它。

回答by Walter Mitty

Depending on what you mean by "exclusive foreign keys", you might be thinking of vehicles and pieces as two subclasses of some larger superclass, call it saleable items.

根据您所说的“独占外键”的含义，您可能会将车辆和零件视为某个较大超类的两个子类，称之为可销售商品。

If you use a design pattern called "class table inheritance", you will have three tables, one for the superclass, and one for each subclass table. If in addition, you use a design called "shared primary key", you can use the same primary key for all three tables.

如果您使用称为“类表继承”的设计模式，您将拥有三张表，一张用于超类，一张用于每个子类表。此外，如果您使用称为“共享主键”的设计，则可以对所有三个表使用相同的主键。

This would enable your Sale table to have a single foreign key, Saleable_Item_Id, that references the Saleable_Item table and also either the Vehicle or the Piece table, depending on the case. This could work out better for you than the existing design.

这将使您的 Sale 表具有单个外键 Saleable_Item_Id，它引用 Saleable_Item 表以及 Vehicle 或 Piece 表，具体取决于具体情况。这可能比现有设计更适合您。

google "class table inheritance" and "shared primary key" for more details.

谷歌“类表继承”和“共享主键”了解更多详情。

回答by Andrew Walters

Oracle shouldn't complain if you have a null foreign key.

如果您有一个空外键，Oracle 不应该抱怨。

Were you running across some errors?

您是否遇到了一些错误？