建立連線

介紹

外部索引鍵描述了關係，而《正確性與約束》中介紹的實體關係圖（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 將模式和資料庫混為一談，因此只要兩個資料庫託管在同一伺服器上，就允許跨資料庫外部索引鍵。其他資料庫則不允許。

我們稍後會回到資料庫中的表組織以及資料庫內模式的組織，但將多表關係圖視為資料庫佈局的不可分割單元是有用的，就像給定概念的組合屬性構成表佈局的基礎一樣。