建立连接

简介

外键描述关系，实体关系图 (ERD) 在正确性和约束中介绍，它映射了这些外键的网络或图。在这些示例中，只有几个表以及它们之间的关系，但是当需要确保每个必需的关系都被考虑在内时，可视化布局仍然是一个有用的参考。对于更大的数据库，ERD 是非常宝贵的，完全停止。许多数据库客户端都有内置工具来生成图表，尽管通常需要手动调整才能使其可读。

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

对细节的这种关注至少部分是历史产物，源于在工作站上运行数据库服务器是闻所未闻的时代，而在现代，很少有 ERD 被如此正式地指定。与此处的图表一样，“最多一个”的符号和“零到多个”的符号足以传达要旨，并且很少需要在该级别与共享 SQL 脚本本身之间进行权衡。

级联行为

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

如果可以从 authors 中删除梅纳德记录，则有问题的 Quixote 副本将不再具有有效的 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 在流行的关系数据库中混淆了模式和数据库，因此允许跨数据库外键，只要两个数据库都托管在同一服务器上。其他数据库则不然。

稍后我们将回到在数据库和数据库内的模式中组织表，但将多表关系图视为数据库布局的不可分割的单元是有用的，这与给定概念的组合属性形成表布局的基础的方式相同。