复合主键中的可空列有什么问题？ [英] What's wrong with nullable columns in composite primary keys?

问题描述

ORACLE不允许在包含主键的任何列中包含NULL值。似乎大多数其他企业级系统也是如此。

ORACLE does not permit NULL values in any of the columns that comprise a primary key. It appears that the same is true of most other "enterprise-level" systems.

同时，大多数系统也允许唯一对可空列。

At the same time, most systems also allow unique contraints on nullable columns.

为什么唯一约束可以有NULL，但主键不能？

Why is it that unique constraints can have NULLs but primary keys can not? Is there a fundamental logical reason for this, or is this more of a technical limitation?

推荐答案

主键用于唯一标识行。这通过将键的所有部分与输入进行比较来完成。根据定义，NULL不能是比较的一部分 - 这样的比较的结果将总是NULL。

Primary keys are for uniquely identifying rows. This is done by comparing all parts of a key to the input. Per definition, NULL cannot be part of a comparison - the result of such a comparison would always be NULL again.

另外，外键中允许NULL，一个可选的关系。^（*）允许它在PK中也会破坏。

Additonally, NULL is allowed in a foreign key, to mark an optional relationship.^(*) Allowing it in the PK as well would break this.

如果有两个实体 A 和 B 其中 A 可以选择与 B ，清洁的解决方案是创建一个解析表（让我们说 AB ）。该表将链接 A 与 B ：如果是一个记录，如果有不是，那么它不会。

If there are two entities A and B where A can optionally be related to B, the clean solution is to create a resolution table (let's say AB). That table would link A with B: If there is a relationship then it would contain a record, if there isn't then it would not.

这篇关于复合主键中的可空列有什么问题？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！