5.1 简介
Internet 上充斥着关于应如何在关系数据库中选择和使用键的教条式规则。有时争论甚至会演化为 holivars：应该使用自然密钥还是人工密钥？自动递增整数或 UUID？
在阅读了 64 篇文章，翻阅了 5 本书的章节，并在 IRC 和 StackOverflow 上提出了大量问题之后，我（Joe “begriffs” Nelson，原文章的作者）似乎已经把拼图的各个部分放在一起并且现在可以调和对手。很多关键性的争论其实都是因为误解了别人的观点而产生的。
让我们把问题分开，最后把它重新组合起来。首先，让我们问一个问题——什么是“钥匙”？
让我们暂时忘记主键，我们对更一般的想法感兴趣。键是在行中没有重复值的列（column）或列。此外，列必须是不可简化的唯一性，即列的子集不具有这种唯一性。
但首先，一些理论：
首要的关键
首要的关键直接用于标识表中的行。它必须遵守以下限制：
- 主键必须始终是唯一的。
- 它必须始终存在于表中并具有值。
- 它不应该经常改变它的价值。理想情况下，它根本不应该改变值。
通常，主键表示表的单个列，但它也可以是由多个列组成的组合键。
组合键
自定义键- 唯一标识每个表行的属性（列）组合。它可以是所有列，也可以是几列，也可以是一列。在这种情况下，不应重复包含这些属性值的行。
潜在钥匙
候选键- 表示关系（表）的最小组合键，即满足若干条件的一组属性：
- 不可约性：它不能被约化，它包含最小可能的属性集。
- 唯一性：无论行如何变化，它都必须具有唯一值。
- 值的存在：它不能有空值，即它必须有一个值。
5.2 主键的奇怪情况
我们在上一节中所说的“键”通常被称为“候选键”。术语“候选”意味着所有这些键都在竞争“主键”（primary key）的荣誉角色，其余的则分配给“替代键”（alternate keys）。
SQL 实现需要一段时间才能克服键与关系模型之间的不匹配问题，并且最早的数据库面向主键的低级概念。需要此类数据库中的主键来标识一行在顺序存储介质上的物理位置。以下是 Joe Celko 的解释：
术语“键”是指文件排序键，它是在顺序文件系统上执行任何处理操作所必需的。一套穿孔卡片只按一个顺序阅读；回去是不可能的。早期的磁带驱动器模仿相同的行为并且不允许双向访问。也就是说，原始的 Sybase SQL Server 需要将表“倒带”到开头以读取上一行。
在现代 SQL 中，您不需要关注信息的物理表示、表模型关系，行的内部顺序根本不重要。然而，即使是现在，SQL Server 也默认为主键创建聚集索引，并且根据旧传统，以物理方式排列行的顺序。
在大多数数据库中，主键已成为过去，仅提供反射或物理位置。例如，在 PostgreSQL 表中，声明主键会自动强制执行约束NOT NULL并定义默认外键。此外，主键是 operator 的首选列JOIN。
主键不会覆盖声明其他键的可能性。同时，如果没有键被指定为主键，那么该表仍然可以正常工作。无论如何，闪电不会击中你。
5.3 寻找自然键
上面讨论的键被称为“自然”，因为它们是建模对象的属性，它们本身很有趣，即使没有人想用它们制作键。
在为可能的自然键检查表时要记住的第一件事是尽量不要太聪明。StackExchange 上的用户 sqlvogel 给出了以下建议：
有些人很难选择“自然”键，因为他们会想出某个特定键可能不是唯一的假设情况。他们不明白这项任务的真正意义。键的含义是定义规则，根据该规则，属性在任何给定时间必须并且始终在特定表中是唯一的。该表包含特定且易于理解的上下文中的数据（在“主题区域”或“话语区域”中），唯一的含义是限制在该特定区域中的应用。
实践表明，当列对于可用值是唯一的并且在可能的场景中将保持这种情况时，有必要引入键约束。如果有必要，可以取消限制（如果这让您感到困扰，那么下面我们将讨论密钥稳定性。）
例如，爱好俱乐部成员的数据库可能在两列中具有唯一性 - first_name, last_name. 对于少量数据，重复是不可能的，在真正的冲突出现之前，使用这样的密钥是很合理的。
随着数据库的增长和信息量的增加，选择自然键会变得更加困难。我们存储的数据是对外部现实的简化，不包含区分世界上物体的某些方面，例如它们随时间变化的坐标。如果一个物体没有任何代码，你怎么能区分两罐饮料或两盒燕麦片的空间排列或重量或包装的细微差别？
这就是标准化机构为产品创建和应用独特标记的原因。车辆印有车辆识别码 (VIN)，书籍印有ISBN，食品包装有UPC。你可能会反对这些数字看起来不自然。那么为什么我称它们为自然键呢？
数据库中独特属性的自然性或人为性是相对于外部世界而言的。在标准机构或政府机构中创建时是人为的密钥对我们来说变得很自然，因为它成为全世界的标准和/或印在物体上。
有许多针对各种主题的行业、公共和国际标准，包括货币、语言、金融工具、化学品和医疗诊断。下面是一些经常用作自然键的值：
- ISO 3166 国家代码
- ISO 639 语言代码
- 符合 ISO 4217 的货币代码
- 股票代号 ISIN
- UPC/EAN、VIN、GTIN、ISBN
- 登录名
- 电子邮件地址
- 房间号码
- 网络MAC地址
- 地球表面点的纬度、经度
我建议尽可能合理地声明键，甚至每个表可能有多个键。但请记住，以上所有情况都可能有例外。
- 并非每个人都有电子邮件地址，尽管在某些数据库条件下这可能是可以接受的。此外，人们会不时更改他们的电子邮件地址。（稍后将详细介绍密钥稳定性。）
- ISIN 股票代码会不时更改，例如代码 GOOG 和 GOOGL 不能准确描述公司从 Google 到 Alphabet 的重组。有时会出现混淆，例如 TWTR 和 TWTRQ，一些投资者在 Twitter IPO 期间错误地购买了后者。
- 社会安全号码仅供美国公民使用，具有隐私限制，并且在死后可以重复使用。此外，文件被盗后，人们可以获得新的号码。最后，同一个数字可以识别一个人和一个所得税标识符。
- 邮政编码对于城市来说是一个糟糕的选择。有些城市有一个共同的指标，反之亦然，一个城市有多个指标。
5.4 人工钥匙
鉴于键是每一行都有唯一值的列，创建它的一种方法是作弊——你可以在每一行中写入虚构的唯一值 \u200b\u200bin。这些是人造键：用于引用数据或对象的发明代码。
代码是从数据库本身生成的，并且除了数据库用户之外任何人都不知道，这一点非常重要。这就是人工密钥与标准化自然密钥的区别。
虽然自然键的优势在于防止表中出现重复或不一致的行，但人工键很有用，因为它们使人类或其他系统更容易引用该行，并且它们加快了查找和连接的速度，因为它们不使用字符串（或多列）比较。键。
代理人
人工键用作锚点——无论规则和列如何变化，始终可以以相同的方式识别一行。用于此目的的人工密钥称为“代理密钥”，需要特别注意。我们将在下面考虑代理人。
非代理人工键对于从数据库外部引用行很有用。人工密钥可简要标识数据或对象：它可以指定为 URL、附在发票上、通过电话口述、从银行获得或印在车牌上。（汽车的牌照对我们来说是一把天然钥匙，但被政府设计成了一把人造钥匙。）
选择合成密钥时应考虑可能的传输方式，以尽量减少错别字和错误。应该注意的是，密钥可以说出、阅读打印、通过 SMS 发送、阅读手写、从键盘键入和嵌入 URL。此外，一些人工密钥（例如信用卡号）包含校验和，因此如果出现某些错误，它们至少可以被识别。
例子：
- 对于美国车牌，有关于使用模糊字符的规则，例如 O 和 0。
- 考虑到医生的笔迹，医院和药房必须特别小心。
- 您是否通过短信发送确认码？不要超出 GSM 03.38 字符集。
- 与编码任意字节数据的 Base64 不同，Base32 使用有限的字符集，便于人类在较旧的计算机系统上使用和处理。
- Proquints 是可读、可写和可发音的标识符。这些是明确理解的辅音和元音的 PRO-nouncable QUINT-uplets。
请记住，一旦您向世界介绍您的人造钥匙，人们就会奇怪地开始给予它特别的关注。只要看看“小偷”车牌或创建可发音标识符的系统，它已成为臭名昭著的自动诅咒生成器。
即使我们局限于数字键，也有像第十三层那样的禁忌。虽然 proquints 每个语音音节的信息密度更高，但数字在很多方面也很好：在 URL、pin 键盘和手写笔记中，只要接收者知道密钥只是数字。
但是，请注意，您不应在公共数字键中使用连续顺序，因为这允许您翻阅资源（/videos/1.mpeg、/videos/2.mpeg 等）并且还会泄露有关数字的信息数据。在数字序列上叠加 Feistel 网络并在隐藏数字顺序的同时保持唯一性。
反对声明额外键的唯一论据是每个新键都会带来另一个唯一索引并增加写入表的成本。当然，这取决于数据的正确性对您有多重要，但最有可能的是，仍应声明密钥。
如果有的话，也值得声明几个人工密钥。例如，一个组织有求职者（Applicants）和雇员（Employees）。每个员工都曾经是候选人，并通过自己的标识符引用候选人，这也应该是员工的关键。再比如，你可以在Employees中设置employee id和login name作为两个key。
5.5 代理键
如前所述，一种重要的人工密钥类型称为“代理密钥”。它不需要像其他人造键一样简洁和通行，而是用作始终标识字符串的内部标签。它在 SQL 中使用，但应用程序不会显式访问它。
如果您熟悉 PostgreSQL 的系统列，那么您几乎可以将代理项视为数据库实现参数（如 ctid），但是它永远不会改变。代理值每行选择一次，此后永远不会改变。
代理键与外键一样出色，必须指定级联约束ON UPDATE RESTRICT以匹配代理项的不变性。
另一方面，公共共享密钥的外键应标有ON UPDATE CASCADE，以提供最大的灵活性。级联更新运行在与周围事务相同的隔离级别，所以不要担心并发问题——如果您选择严格的隔离级别，数据库会很好。
不要让代理键“自然”。一旦您向最终用户显示代理键的值，或者更糟的是，让他们使用该值（特别是通过查找），您实际上就是在为键赋予一个值。然后，您数据库中显示的密钥可以成为其他人数据库中的自然密钥。
强制外部系统使用其他专为传输而设计的人工密钥，使我们能够根据需要更改这些密钥以满足不断变化的需求，同时通过代理保持内部参照完整性。
自增 INT/BIGINT
代理键最常见的用途是自动递增的“bigserial”列，也称为IDENTITY。（实际上，PostgreSQL 10 现在支持 IDENTITY 构造，Oracle 也是如此，请参阅 CREATE TABLE。）
但是，我认为自动递增的整数对于代理键来说不是一个好的选择。这个意见不受欢迎，所以让我解释一下。
串行密钥的缺点：
- 如果所有序列都从 1 开始并递增，那么来自不同表的行将具有相同的键值。这个选项并不理想，在表中使用不相交的键集仍然更可取，这样，例如，查询就不会意外混淆常量
JOIN并返回意外结果。（或者，为了确保没有交集，可以从不同素数的倍数构造每个序列，但这会相当费力。） - 在当今的分布式 SQL 中调用
nextval()生成序列会导致整个系统无法很好地扩展。 - 从同样使用顺序键的数据库中消费数据会导致冲突，因为顺序值在整个系统中不是唯一的。
- 从哲学的角度来看，数字的连续增加与隐含线条顺序的旧系统有关。如果您现在想要对行进行排序，请使用时间戳列或在您的数据中有意义的内容明确地进行排序。否则，违反了第一范式。
- 理由不充分，但这些简短的标识符很容易告诉别人。
UUID
让我们看看另一种选择：使用根据随机模式生成的大整数（128 位）。生成此类通用唯一标识符 (UUID) 的算法两次选择相同值的可能性极低，即使同时在两个不同的处理器上运行也是如此。
在那种情况下，UUID似乎是用作代理键的自然选择，不是吗？如果您想以独特的方式标记行，那么没有什么比独特的标签更好的了！
那么为什么不是每个人都在 PostgreSQL 中使用它们呢？这有几个人为的原因和一个可以解决的合乎逻辑的原因，我将提供基准来说明我的观点。
首先，我会谈谈牵强的原因。有些人认为 UUID 是字符串，因为它们是用带有破折号的传统十六进制表示法编写的：5bd68e64-ff52-4f54-ace4-3cd9161c8b7f。事实上，一些数据库没有紧凑的（128 位）uuid 类型，但 PostgreSQL 有并且大小为 two bigint，也就是说，与数据库中的其他信息量相比，开销可以忽略不计。
UUID 也被不公平地指责为笨重，但谁来发音、输入或阅读它们呢？我们说过显示人工密钥是有意义的，但没有人（根据定义）应该看到代理 UUID。UUID 可能会由开发人员在 psql 中运行 SQL 命令以调试系统来处理，但仅此而已。如果给定，开发人员还可以使用更方便的键来引用字符串。
UUID 的真正问题在于，高度随机化的值会导致写放大，因为整页写入预写日志 (WAL)。然而，性能下降实际上取决于 UUID 生成算法。
让我们测量写放大。实际上，问题出在较旧的文件系统中。当 PostgreSQL 写入磁盘时，它会更改磁盘上的“页面”。如果关闭计算机电源，大多数文件系统在数据安全存储到磁盘之前仍会报告写入成功。如果 PostgreSQL 天真地认为这样的操作已完成，那么数据库将在下一次系统引导期间损坏。
由于 PostgreSQL 不能信任大多数操作系统/文件系统/磁盘配置来提供连续性，因此数据库将更改的磁盘页面的完整状态保存到预写日志中，可用于从可能的崩溃中恢复。索引高度随机化的值（如 UUID）通常涉及一堆不同的磁盘页面，并导致将整个页面大小（通常为 4 或 8 KB）写入每个新条目的 WAL。这就是所谓的整页写（full-page write，FPW）。
一些 UUID 生成算法（例如 Twitter 的“snowflake”或 PostgreSQL 的 uuid-ossp 扩展中的 uuid_generate_v1()）在每台机器上生成单调递增的值。这种方法将写入合并到更少的磁盘页面并减少 FPW。
5.6 结论和建议
现在我们已经了解了不同类型的密钥及其用途，我想列出我在数据库中使用它们的建议。
对于每个表：
- 定义并声明所有自然键。
- 创建一个UUID
<table_name>_id类型的代理键，默认值为. 您甚至可以将其标记为主键。如果将表的名称添加到此标识符，这将简化，即 接收而不是. 不要将此密钥传递给客户端，也不要将其暴露在数据库之外。uuid_generate_v1()JOINJOIN foo USING (bar_id)JOIN foo ON (foo.bar_id = bar.id) - 对于通过 的中间表
JOIN，将所有外键列声明为单个复合主键。 - （可选）添加可在 URL 或其他字符串引用指示中使用的人工键。使用Feistel 网格或pg_hashids屏蔽掉自动递增的整数。
ON UPDATE RESTRICT使用代理 UUID 作为外键和人工外键指定级联约束ON UPDATE CASCADE。根据您自己的逻辑选择自然键。
这种方法确保内部密钥的稳定性，同时允许甚至保护自然密钥。此外，可见的人造键不会附加到任何东西上。正确理解所有内容后，您不能只挂在“主键”上并使用所有使用键的可能性。