建立连接

简介

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

存在多种 ERD 符号。完整的“乌鸦脚”符号是历史最悠久、影响力最大的符号之一，它定义了 0（一个圆环）、1（一条短线）或多个（如上所示的标志性乌鸦脚）记录的符号。每条线代表两个表之间的关系，并且两端都有两个这样的符号，每对符号都建立了该端的最小和最大值。

这种对细节的关注至少部分是由于在工作站上运行数据库服务器是闻所未闻的时代的历史遗留物，而在现代，很少有 ERD 是如此正式指定的。就像这里的图表一样，“至多一个”和“零到多个”的符号足以表达要点，而且现在很少需要在两者之间以及共享 SQL 脚本本身之间进行细分。

级联行为

向 books 中插入无效的 author_id 并非违反外键约束的唯一方式：对 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 中的单个记录对应于一个人。两者不能同时为真。

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

“多对多”关系必须在专用表中表示，通常称为连接表或桥接表，还有其他名称。连接表维护对其所中介的每个表的外键，使其成为其与这些表关系中的“多”端。每个外键上的主键可防止相同关系的重复。

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 混淆了模式和数据库，因此只要两个数据库托管在同一服务器上，就允许跨数据库外键。其他数据库则不允许。

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