外键可以为空吗？ [英] Can Foreign Key be null?

问题描述

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

谢谢。

解决方案

p>主键的列（或列）必须不为空。无法通过NULL唯一标识记录。因此，外键的引用端的ID列必须定义为NOT NULL。

然而，外键关系可选的合法设计决策，并且通过使key的引用结束可选，即允许NULL来表示的方式。

在数据建模术语中，您所描述的是一个（独占） arc：一个表，有两个或更多个外键，其中只有一个可以是非空的。在逻辑建模中，弧是完全可以接受的，但有强烈的意见支持将它们实现为单独的表。在您的场景中，将是一个通用的销售表加上两个子类型表，VehicleSale 。 / p>

独立表实现的优点是：

• 更容易实施外部关键限制;


• 更容易添加与不适用于零件销售的（例如）车辆销售相关的附加列;


• 更容易扩展具有其他子类型的模型;


• 更清晰的数据模型，可以简化应用程序开发。

但是，优点并不都是单向的。虽然很容易确保销售适用于 VehicleSale 或 PieceSale 但不能同时执行一个销售 必须的规则实际上会变得非常好用。

所以，普遍的建议是，排除弧是错误的，一般都是好的建议。但是，这并不像有些人所说的那么清楚。

In our database project we have a table Sale that has an primary key and two exclusive foreign keys: Vehicle_ID and Piece_ID . For example if we sell a vehicle we need Vehicle_ID as 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?

Thanks.

解决方案

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.

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 Sale table plus two sub-type tables, 'VehicleSaleand '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 Sale applies either to a VehicleSale or a PieceSale but not both, enforcing a rule that a Sale must a child record actually gets pretty gnarly.

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.

这篇关于外键可以为空吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！