复合主键中的可为空列有什么问题? [英] 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?

推荐答案

主键用于唯一标识行.这是通过将密钥的所有部分与输入进行比较来完成的.

Primary keys are for uniquely identifying rows. This is done by comparing all parts of a key to the input.

根据定义，NULL 不能成为成功比较的一部分.甚至与自身的比较 (NULL = NULL) 也会失败.这意味着包含 NULL 的键将不起作用.

Per definition, NULL cannot be part of a successful comparison. Even a comparison to itself (NULL = NULL) will fail. This means a key containing NULL would not work.

此外，在外键中允许 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 word of caution: Having nullable foreign keys is not clean relational database design.

如果有两个实体 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屋！