建立连接

简介

外键描述关系，在正确性和约束中介绍的实体关系图 (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 行为。

键定位

图书馆和作者都先于他们借阅和撰写的书籍的任何有用记录而存在。这些情况对应于面向对象编程中的 “has-a” 关系类型，在数据库设计中，这需要将外键存储在依赖表 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 是流行的关系数据库中唯一将模式和数据库混淆的数据库，因此只要两个数据库都托管在同一服务器上，就允许跨数据库外键。其他数据库则不允许。

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