ID设计与主键

# ID设计与主键，第一部分 来源：https://anchorsandlinks.com/posts/primary-keys/ 作者：Alexey Makhotkin[squadette@gmail\.com](mailto:[email protected])，*（约2300字）* 这是关于数据库设计中主键的系统性讨论的第一部分。与往常一样，我们以与传统方法不同的方式呈现材料。 这基本上是《数据库设计手册》(https://databasedesignbook.com/)的附加章节。本文的目标是教你如何根据业务需求设计主键。 在**第一部分**中，我们从逻辑层面开始。 - 我们引入**外部ID**的概念。 - 我们讨论**锚点ID**及其要求；特别是，锚点ID何时可以用作外部ID。 - 然后我们讨论处理**外部系统生成的外部ID**；特别是，哪些外部ID可以用作锚点ID。 然后我们转向物理层面。 - 我们讨论**主键**的概念，它完全独立于其业务含义； - 然后我们讨论一个简单的用例：一个小型内容管理系统，展示了使用整数主键的简单锚点表设计； - 此外，我们讨论**唯一性约束**及其与外部ID的关系。 ## 目录 - 外部ID (https://anchorsandlinks.com/posts/primary-keys/#external-ids) - 锚点ID (https://anchorsandlinks.com/posts/primary-keys/#anchor-ids) - 作为外部ID的锚点ID (https://anchorsandlinks.com/posts/primary-keys/#anchor-ids-as-external-ids) - 来自外部系统的外部ID (https://anchorsandlinks.com/posts/primary-keys/#external-ids-from-outside-systems) - 作为锚点ID的外部ID (https://anchorsandlinks.com/posts/primary-keys/#external-ids-as-anchor-ids) - 主键 (https://anchorsandlinks.com/posts/primary-keys/#primary-keys) - 简单锚点表 (https://anchorsandlinks.com/posts/primary-keys/#simple-anchor-tables) - 唯一性约束 (https://anchorsandlinks.com/posts/primary-keys/#uniqueness-constraints) - 结论 (https://anchorsandlinks.com/posts/primary-keys/#conclusion) 在**第二部分(https://anchorsandlinks.com/posts/primary-keys-2/)**中，我们将讨论复合主键以及它们在数据库设计中的使用。 --- *在此订阅以接收更新：* --- ## 外部ID 让我们暂时忘记数据库、表、主键和其他存在于物理层面的东西。 我们需要首先关注**业务需求**，以及可以从这些需求中提取的逻辑模型。 在许多面向业务的系统中，某些实体需要有唯一标识符。一些例子： - 备件可能有一个或多个零件号； - 人有一个唯一的纳税人识别号，例如美国的SSN或荷兰的BSN； - 内容管理系统中的页面可以有像`/about`这样的URL，或者只是`/content\.php?id=25`； - 工单跟踪系统使用熟悉的字符串，如`FOOBAR\-123`； - 等等，等等。 我们将这样的唯一标识符称为**外部ID**。它们可以被外部使用：通过电子邮件发送、打印在纸上、通过电话告知。外部ID有三个定义性特征： 1. **外部ID唯一标识一个实体**：每个外部ID恰好对应一个实体。 2. 相反的情况可能并不总是成立：**一个实体可能没有外部ID、有一个外部ID，或者有多个外部ID**。例如，许多孩子没有护照。护照也可以重新签发，但我们可以通过旧护照号码识别一个人。 3. **外部ID可以更改**。好吧，我们需要使第一个属性更精确：“在任何给定时刻，每个外部ID恰好对应一个实体”。例如，你可能想更改你的社交媒体用户名，然后其他人可能会使用你的旧用户名。因此，今天的用户*@alice*可能是未来的另一个Alice。 一个实体可能有不止一种类型的外部ID。例如，如果我们在亚马逊上销售备件，它们将既有零件号（由供应商分配）也有ASIN（由亚马逊分配）。 ## 锚点ID 现在我们可以再次想起我们有一个数据库，但谈论表和主键还为时过早。 在《数据库设计手册》中，我们使用术语“锚点”。锚点大致类似于实体，但我们不喜欢“实体”这个词，因为它太模糊了。 锚点ID是可靠且明确地识别锚点实例所必需的。 假设我们维护一个书籍数据库，数据库中有100个书名。我们需要一种方法来识别这100本书中的每一本，使得**每本书都有一个锚点ID**，并且**每个锚点ID恰好对应一本书**。 我们不能使用ISBN，因为有些书没有ISBN。我们也不能使用书名：也许我们的馆藏中有五本不同的圣经，等等。 这个问题的常见解决方案是使用从1、2、3等开始的整数。因此我们会有一本书*ID=1*，一本书*ID=2*，等等。我们可以在实际的数据库表中使用这些整数。它们本身没有意义。 对锚点ID的另一个要求是它不可变：它的值永远不会改变。无意义的整数满足这个要求，因为你根本不需要改变它们：ID=2并不比ID=3更好或更差。 简单的整数是最常见的解决方案，但有时我们有其他选择： - **唯一字符串**，如“fr”或“CHF”； - **元组**：两个或多个整数或字符串的组合； - **UUID**，尽管我们可以将它们视为只是大的非连续整数； 我们将在本系列文章的后面讨论这些场景。 ## 作为外部ID的锚点ID 原则上，任何锚点ID都可以用作外部ID，并且这经常发生。 然而，有时这是不可取的。考虑一个电子商务系统，用户下订单。每个订单都有一个订单ID。很有可能，我们有一个“订单”表，其中有一个“orders\.id”列，包含自动递增的整数ID。我们能在确认邮件等中使用这些数字吗？ 从技术上讲我们可以，但这会造成工业间谍活动的可能性。我们的竞争对手可以通过定期下单来分析连续数字增长的速度。这使他们能够跟踪你的业务结果，而你可能不希望这样。 为了规避这一点，你可以生成基于日期+随机数的ID，如“*20261016\-32767*”，并在外部使用它们。它们将作为Order锚点的一个属性存储，但仅用于你和客户之间指代一个订单。 在数据库中的其他所有地方，你将使用无意义的整数，因为这在技术上通常是最方便的。（我们将在后面讨论什么时候可能不是这样。） 注意，即使这些外部ID是由我们自己的系统生成的，我们的系统仍然需要验证和认证它们。例如，如果有人提交取消预订QIE3CB的请求，我们需要确保他们有权限这样做。也许他们只是偷听了别人的预订号。 ## 来自外部系统的外部ID 有些外部ID是由我们自己的系统生成的。我们知道它们的含义并且信任它们。我们只需要验证和认证它们。 但也有外部系统生成的外部ID。它们有很多潜在的问题。 首先，看起来像外部ID的东西可能根本就不是一个合适的外部ID。例如，两个生活在不同国家的人可能碰巧有相同的护照号码。因此，单凭护照号码本身可能根本就不是一个好的外部ID，因为根据定义，我们希望每个ID只对应一个实体。 ID也可能是伪造的，如上所述。 在许多情况下，你可能认为这不是一个外部ID，而**只是另一个锚点的属性值**。例如，假设你正在构建一个航空公司预订系统。你要求客户输入他们的护照号码——这有多可靠？也许你只需要将此作为你的Reservation锚点的一个属性：*“此预订提供的护照号码是什么？”* 你甚至不会为护照单独创建一个锚点，你只有属性值。 ## 作为锚点ID的外部ID 假设我们已经证明某个标识符满足上面描述的要求。或者，它是由我们自己的系统生成的，因此在认证和验证之后我们可以信任它。 然而，*外部ID*通常不满足*锚点ID*的要求： - 有时多个外部ID可以引用同一个实体； - 有时一个实体没有外部ID； - 有时外部ID的值可以更改； 然而，在某些情况下，这些额外的要求也得到了满足，我们最终得到一个合适的外部ID，可以用作锚点ID。不需要无意义的数字，对吧？ 稍后我们将讨论一些可以实现这一点的用例。此外，我们需要讨论为什么你想这样做。 ## 主键 现在让我们暂时忘记业务需求和逻辑模型，完全进入主键所在的物理层面。 想象一个关系数据库中的物理表。让我们打乱表名和列名，以便我们可以讨论主键的本质。 这是这个表中的一些示例数据： 表名：`prawnges` 主键：`iro` `iro`值 | `stoog`值 | `qonts`值 --- | --- | --- *5* | *“awoult”* | *“the mome raths outgrabe”* *27* | *NULL* | *“the slithy toves did gyre”* *430* | *“quux”* | *“gimble in the wabe”* ... | ... | ... 下面是这个表的定义，包括列名、数据类型、主键定义和唯一性约束： ``` CREATE TABLE prawnges ( iro INTEGER NOT NULL PRIMARY KEY, stoog VARCHAR(64) NULL, qonts TEXT NOT NULL, UNIQUE (stoog) ); ``` 主键由一列或多列组成，唯一标识表中的每一行。这里，*iro=5*对应第一行数据；*iro=27*对应第二行数据，依此类推。 你不能在`iro`列中放入NULL值，你需要一个明确的整数值。此外，你也不能再添加另一行，比如*iro=5*：数据库将拒绝此操作并给出“主键冲突”错误。 再说一次，在这个例子中，我们使用了单列主键，但它们也可以是复合主键。我们可以添加一个名为“b”的非NULL列，并声明以下主键：`(iro, b)`。那么两列值的组合需要是唯一的：*(5, 10)*、*(5, 5)*、*(10, 10)*等等。 我们将在本系列的第二部分更详细地讨论复合键。 ## 简单锚点表 想象一个最小的内容管理系统。它支持网页，每个页面可以有一个可读的URL，如`/about`，或者只是`/content\.php?id=25`。 这是该系统的逻辑模型，使用《数据库设计手册》中介绍的符号。它只有一个锚点： *锚点* | *ID示例* | *物理表* | *ID存储方式* --- | --- | --- | --- *Page* | *1, 2, 3, ...* | `pages` | `pages\.id` 和两个属性： *锚点* | *问题* | *逻辑类型* | *示例值* | *物理存储* --- | --- | --- | --- | --- Page | 这个Page的**URL slug**是什么？ | 字符串，外部ID | *“about”* | `pages\.slug` Page | 这个Page的**内容**是什么？ | 字符串 | *“Our chief weapon is surprise...”* | `pages\.content` 我们使用了基线表设计策略： - 锚点获得自己的表； - 属性获得自己的列； - 我们使用1, 2, 3, ...作为锚点ID； - 锚点ID获得自己的列，该列也是主键； - “slug”属性是外部ID，因此它获得唯一性约束； 对于这个小小的表格来说，文字太多了，不是吗： ``` CREATE TABLE pages ( id INTEGER NOT NULL PRIMARY KEY, slug VARCHAR(64) NULL, content TEXT NOT NULL, UNIQUE (slug) ); ``` 等等，这看起来不是似曾相识吗？让我们看看示例数据集： 表名：`pages` 主键：`id` `id`值 | `slug`值 | `content`值 --- | --- | --- *5* | *“about”* | *“Our company was founded in 2003 and is a...”* *27* | *NULL* | *“We’re happy to announce that...”* *430* | *“features”* | *“Here is a list of main features of our product: ...”* ... | ... | ... 好的，这绝对是前面章节中的“`prawnges`”的未打乱版本。 ## 唯一性约束 几乎所有数据库都支持唯一性约束。当你设计一个表模式时，你可以在该表的一个列上定义唯一性约束。这意味着该列中的值需要是唯一的。如果你尝试插入一个在该列中具有重复值的新行，你将从数据库收到唯一性约束冲突错误。更改现有行中的值也是如此。 主键包含一个隐式的唯一性约束。这就是为什么你永远不会在表中获得重复的主键。 单个表上可能有几个唯一性约束。此外，唯一性约束可以覆盖多个列，与复合主键类似。唯一性约束不能跨两个或多个表定义。 在我们讨论过的“pages”表中有一个唯一性约束。 让我们看看“`slug`”列的定义（第#3行和第#5行）： ``` CREATE TABLE pages ( id INTEGER NOT NULL PRIMARY KEY, slug VARCHAR(64) NULL, -- #3 content TEXT NOT NULL, UNIQUE (slug) -- #5 ); ``` 我们看到这个列可以为NULL，并且定义为`UNIQUE`。在大多数现代数据库中，你可以将一个可为空的列定义为唯一。对于可为空的列，它是这样工作的： - 如果值不为NULL，则此值必须在所有其他非NULL值中是唯一的； - 否则，可以有多行包含NULL值。 > 历史上，NULL值与唯一性之间的相互作用有些复杂，并没有很好的理由。我们将在“吹毛求疵”部分更详细地讨论这一点。 页面slug属性被定义为外部ID。请注意，这是我们的业务决策：只有我们知道slug是唯一的。 在物理层面，外部ID是通过唯一性约束实现的：直接地，或通过主键隐式地。 --- ## 《数据库设计手册》（2025） [](https://databasedesignbook.com/) #### 学习如何从业务需求走向数据库模式 如果这篇文章对你有用，你可能会发现这本书也有用。 目录和示例章节 (https://databasedesignbook.com/) 书籍长度：145页，约32,000字。提供PDF和EPUB格式。 购买价格：€32 (https://databasedesignbook.com/) --- ## 结论 **外部ID**在数据库设计中特别重要。它们完全存在于逻辑层面，但与物理表设计的关系更为紧密，比普通属性更接近。 **锚点ID**存在于逻辑和物理层面之间，对于表设计至关重要。在大多数情况下，它们可以使用最常用的方法：简单的整数。在第三部分，我们将讨论一些你拥有的有趣的替代选项。 锚点ID通常可以**直接用作外部ID**，由你的系统生成。然而，在许多重要的情况下，我们需要**单独的外部ID**。 **主键**是任何表所必需的。我们已经讨论了最常见的简单情况：具有简单整数主键的锚点表。 **唯一性约束**在逻辑层面上与外部ID密切相关。在物理层面上，每个主键都有一个关联的唯一性约束。 在第二部分 (https://anchorsandlinks.com/posts/primary-keys-2/)中，我们将讨论**复合主键**以及它们如何用于实现最常见的表设计策略，即： - 链接表； - 实体-属性-值（EAV）表； - 特设次级数据； - 具有复合主键的锚点表； - 特殊用途的特设案例； --- 我很乐意听取你的反馈和问题：Alexey Makhotkin[[email protected]](mailto:[email protected])