建立连接

介绍

外键描述了关系，而《正确性与约束》中介绍的实体关系图（ERD）则映射了这些外键的网络或图。在这些示例中，只有少量表和它们之间的关系，但当需要确保所有必需的关系都被考虑到时，一个可视化布局仍然是一个有用的参考。对于大型数据库而言，ERD 是无价之宝。许多数据库客户端都内置了生成图表的工具，尽管通常需要手动调整才能使其可读。

存在几种 ERD 符号表示法。完整的 “乌鸦脚” 表示法，作为最古老和最有影响力的表示法之一，定义了表示 0（一个环）、1（一条短划线）或多（如上所示，同名的乌鸦脚）记录的符号。每条线代表两个表之间的关系，并且在每一端都有不止一个，而是两个这样的符号，每对符号都确定了该侧的最小值和最大值。

这种对细节的关注至少部分是历史遗留产物，在过去，在工作站上运行数据库服务器是闻所未闻的，而在现代，很少有 ERD 会如此正式地指定。正如这里的图表所示，一个表示“最多一个”的符号和一个表示“零到多”的符号足以传达要点，而且现在也很少需要介于两者之间以及直接共享 SQL 脚本的层级了。

级联行为

将无效的 author_id 插入 books 并不是违反外键约束的唯一方式：对 authors 的更改也可能使 books 中现有数据失效。在《正确性与约束》中，未强制执行的外键导致《堂吉诃德》的一个副本带有一个虚假的 author_id。如何解决皮埃尔·梅纳德和米格尔·塞万提斯之间的矛盾？

如果梅纳德的记录可以从 authors 中删除，那么所讨论的《堂吉诃德》副本将不再具有有效的 author_id。数据库会拒绝此操作，因为不允许来自子表或父表的违规行为。要删除皮埃尔·梅纳德，必须首先处理掉《堂吉诃德》，无论是通过删除它还是更改其 author_id。

随着受约束关系网的扩大，清理这些依赖记录变得越来越复杂。删除一位作者需要删除他们的所有 books；删除一个图书馆需要做同样的事情，再加上删除它的 patrons —— 并且任何带有指向 books 或 patrons 的外键的表必须首先被删除，以免这些外键约束反过来被违反。

针对父表的 DELETE 操作通常旨在一次性修剪整个关系树：一个图书馆及其图书和其读者，一举完成（有时它并非如此，这使得了解你的 CASCADE 很重要！）。由于外键约束将这些关系具体化，使它们成为可操作的对象，它们还可以帮助自动化响应父表中的更改。声明 ON DELETE SET NULL 的约束将只清空第一个外键值，而不会进一步遍历关系图。ON DELETE CASCADE 确保对 authors 的 DELETE 将自动删除这些作者的 books，并继续通过任何将 books 声明为父表的外键进行删除。

有时，自然主键值也可能随着标准和格式的更新而改变，或者当自然键不可变的假设被证明是错误时。大多数关系型数据库管理系统（RDBMS）支持针对这种情况的 ON UPDATE CASCADE 行为。

键定位

图书馆和作者都先于他们分别借出和撰写的图书的任何有用记录而存在。这些情况对应于面向对象编程中的 “拥有” 关系类型，这在数据库设计中要求外键存储在从属表 books 中。

其他情况则不那么明确。假设有些图书本身是从外部馆藏借给图书馆的，并且它们的原始 来源 被单独追踪。那么所有 books 都应该有一个 provenance_id，还是 provenances 表应该有一个 book_id 列？

两种解决方案都可以达到追踪来源的目的。然而，在 books.provenance_id 的情况下，无法从来源链接回图书——必须在 books 中搜索匹配的 provenance_id。而且由于大多数图书没有特殊来源，所以 provenance_id 的大多数值将是 NULL。

在这种情况下，provenances.book_id 的方法显然更优越。book_id 链接可追踪，列的使用效率高，并且 provenances.book_id 甚至是一个主键，因为一本书不应从多个地方进入图书馆。De Haan 和 Koppelaars 会将 provenances 称为 books 的专业化，这是一个向其父表中由相同主键标识的记录添加补充信息的表。books 和 provenances 之间的连接是“一对一”关系，因为任何 book_id 值在任一表中只能存在一个。

CREATE TABLE provenances (
  book_id INT NOT NULL PRIMARY KEY,
  collection TEXT NOT NULL
);

严格来说，来源包括文物完整的保管链，而不仅仅是最后保管者。如果为了我们的目的有必要，这会使情况变得有些复杂：在 provenances 中每本书有多个记录时，book_id 不再是主键。一个记录不（或不只）由外键标识的 provenances 表不再是专业化表，而是一个“一对多”关系中的“多”方——或者，从另一个方向看，是一个“多对一”关系中的“多”方。

CREATE TABLE provenances (
  book_id INT NOT NULL REFERENCES books (book_id),
  -- a numeric index (most recent, second most recent, third,
  -- and so on) is not strictly required, since the duration
  -- could form part of the primary key. However, a range in
  -- the primary key makes certain queries, like "who last
  -- held most of our books?", more difficult to formulate.
  custody_index INT NOT NULL DEFAULT 1,
  collection TEXT NOT NULL,
  duration DATERANGE NOT NULL,
  PRIMARY KEY (book_id, custody_index),
  -- custody of the same book shouldn't overlap; remember
  -- that the btree_gist Postgres extension is required!
  EXCLUDE USING GIST (
    book_id WITH =,
    duration WITH &&
  )
);

多对多关系

在示例模式的其他地方，patrons 具有 library_id 值。这表达了一个非常重要——而且很可能是非常错误——的假设：任何人都只会在一个图书馆借阅。如果一个人去另一个图书馆，他们将不得不再次输入所有信息。这违反了另一个重要假设，即 patrons 中的单个记录对应于一个人。两者不能同时为真。

类似的解决方案还存在第二个问题：我们尚未追踪谁借出了书。一个读者可以借阅多本书，而一本书可以被借出多次。从结构上看，这与一个图书馆有许多读者，而这些读者本身又可能从多个图书馆借阅的情况几乎相同。

“多对多”关系必须在一个专用表中表示，这个表通常被称为联结表（junction table）或桥接表（bridge table），以及其他名称。联结表维护着指向其所连接的每个表的外键，使其成为与这些表关系的“多”方。跨每个外键的主键可以防止相同关系的重复。

library_patrons，一个联结表的典型示例，如下所示：

CREATE TABLE library_patrons (
  library_id INT NOT NULL REFERENCES libraries (library_id),
  patron_id INT NOT NULL REFERENCES patrons (patron_id),
  PRIMARY KEY (library_id, patron_id)
);

你可能已经注意到，checkouts 并没有遵循与 library_patrons 相同的命名约定——它不是 patron_books 或反之。那是因为它不仅仅是一个联结表。与 library_patrons 一样，checkouts 维护着指向它在多对多关系中连接的表的外键，但它还必须包含每个读者-图书连接的信息：借出日期、到期或归还日期、是否已批准延期。同一个人借阅同一本书多次也是完全可能的，因此 (patron_id, book_id) 不是一个可行的主键。

构建块

多对多关系只是两种主要关系类型的一种可能组合。它们非常常见，以至于图表通常会完全省略 library_patrons 这种联结表，转而用“多”符号表示两端。但是，表之间无论多么复杂的连接网络，都可以分解为它的一对一和一对多关系。

边界

单个数据库可能（并且经常）包含多个外键关系网络。然而，反之通常不成立。在流行的关系型数据库中，只有 MySQL 和 MariaDB 将模式和数据库混为一谈，因此只要两个数据库托管在同一服务器上，就允许跨数据库外键。其他数据库则不允许。

我们稍后会回到数据库中的表组织以及数据库内模式的组织，但将多表关系图视为数据库布局的不可分割单元是有用的，就像给定概念的组合属性构成表布局的基础一样。