建立连接

简介

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

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

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

级联行为

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

如果 Menard 记录可以从 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 将模式和数据库混为一谈，因此只要两个数据库都托管在同一服务器上，就允许跨数据库的外键。其他数据库则不行。

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