接続を作成する

はじめに

外部キーはリレーションシップを記述し、正確性と制約で導入された実体関連図（ERD）は、それらの外部キーのネットワークまたはグラフをマッピングします。これらの例では、テーブルとその間のリレーションシップはごくわずかですが、必要なすべてのリレーションシップが考慮されていることを確認する際には、視覚的なレイアウトが依然として役立つ参照となります。より大規模なデータベースでは、ERDは非常に貴重です。多くのデータベースクライアントには図を生成するための組み込みツールがありますが、読みやすくするためには通常、手動での調整が必要です。

複数のERD表記が存在します。最も古く、最も影響力のあるものの1つである完全な「カラスの足跡（crow's foot）」表記は、0（リング）、1（ダッシュ）、または多数（上記のような、その名の通りのカラスの足跡）のレコードの記号を定義します。各線は2つのテーブル間のリレーションシップを表し、両端にこれら記号のうち1つだけでなく2つ持ち、各ペアがその側の最小値と最大値を確立します。

この細部への注意は、ワークステーションでデータベースサーバーを実行することが考えられなかった時代からの歴史的遺物の一部であり、現代ではそこまで厳密に指定されたERDはほとんどありません。ここにある図と同様に、「最大1つ」の記号と「0から多」の記号があれば要点は伝わり、それとSQLスクリプト自体を共有することの間にレベルが必要となることはほとんどありません。

カスケード動作

無効なauthor_idをbooksに挿入することだけが外部キー制約に違反する方法ではありません。authorsへの変更も、books内の既存のデータを無効にする可能性があります。正確性と制約に戻ると、強制されていない外部キーが、偽のauthor_idを持つ『ドン・キホーテ』のコピーにつながりました。ピエール・メナールとミゲル・セルバンテスの間の矛盾をどのように解決すればよいでしょうか？

もしメナールのレコードをauthorsから削除できた場合、該当する『キホーテ』のコピーは有効なauthor_idを持たなくなります。データベースはこれを拒否します。なぜなら、子テーブルからも親テーブルからも違反は許されないからです。ピエール・メナールを削除するには、まず『ドン・キホーテ』を削除するか、そのauthor_idを変更する必要があります。

制約付きリレーションシップのウェブが大きくなるにつれて、そのような依存レコードのクリーンアップはますます複雑になります。著者（author）を削除すると、そのすべてのbooksも削除されます。図書館（library）を削除するには、同じくそのpatronsも削除する必要があります。さらに、booksやpatronsへの外部キーを持つテーブルは、それらの外部キー制約が次々と違反されるのを防ぐため、まず最初に削除されなければなりません。

親テーブルに対するDELETEは、多くの場合、リレーションシップのツリー全体を剪定することを意図しています。つまり、図書館とその本、そして利用者などを一気に削除することです（時にはこれを意図しない場合もあり、その場合はCASCADEがどこにあるかを知ることが非常に重要になります！）。外部キー制約はこれらのリレーションシップを具現化し、それらを操作されるオブジェクトにするため、親テーブルの変更に対する応答を自動化するのにも役立ちます。ON DELETE SET NULLを宣言する制約は、最初の外部キー値のみを無効にし、リレーションシップグラフをそれ以上たどることはありません。ON DELETE CASCADEは、authorsに対するDELETEが、その著者たちのbooksも自動的に削除し、さらにbooksを親テーブルと宣言する他の外部キーにも連鎖的に削除されることを保証します。

時には、標準やフォーマットの更新、あるいはナチュラルキーが不変であるという仮定が誤りであったことが判明した場合、ナチュラルプライマリーキーの値も変更されることがあります。ほとんどのリレーショナルデータベース管理システム（RDBMS）は、この事態に対応するためにON UPDATE CASCADE動作をサポートしています。

キーの配置

図書館と著者はいずれも、それぞれ貸し出しや執筆する本の有用な記録よりも先に存在します。これらのケースは、オブジェクト指向プログラミングにおける「has-a」リレーションシップタイプに対応しており、データベース設計では外部キーを依存テーブルであるbooksに保存する必要があります。

他のケースはそれほど明確ではありません。一部の本が外部コレクションから図書館に貸し出されており、その元々の来歴（provenances）が別途追跡されていると想像してください。すべての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の特殊化（specialization）と呼ぶでしょう。これは、親テーブルのレコードに、同じプライマリーキーによって識別される補足情報を追加するテーブルです。booksとprovenancesの間の接続は「一対一」の関係です。なぜなら、いずれのテーブルにおいても、いかなるbook_id値も1つしか存在できないからです。

CREATE TABLE provenances (
  book_id INT NOT NULL PRIMARY KEY,
  collection TEXT NOT NULL
);

厳密に言えば、来歴（provenance）とは、最後に誰が所有していたかだけでなく、アーティファクトの所有権の全履歴を含みます。私たちの目的のために必要であれば、これは状況をいくぶん複雑にします。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値を持っています。これは、非常に重要であり、おそらく非常に間違った仮定を表しています。すなわち、ある人物は1つの図書館のみを利用するという仮定です。もし誰かが別の図書館に行けば、すべての情報を最初から入力し直さなければなりません。これは、別の重要な仮定、つまりpatrons内の単一のレコードが単一の人物に対応するという仮定に違反します。両方が真であることはありえません。

類似の解決策には別の問題があります。本の貸し出し状況をまだ追跡していません。1人の利用者が複数の本を借りることができ、1冊の本が複数回貸し出されることもあります。構造的に見ると、これは1つの図書館に複数の利用者がいて、その利用者が複数の図書館から借りる可能性があるというケースとほぼ同じです。

「多対多」の関係は、専用のテーブルで表現する必要があります。このテーブルは、その他の名称の他に、ジャンクション（junction）テーブルまたはブリッジ（bridge）テーブルと呼ばれることが多いです。ジャンクションテーブルは、それが仲介する各テーブルへの外部キーを保持し、これらのテーブルとの関係において「多」側となります。各外部キーをまたがるプライマリーキーは、同じ関係の重複を防ぎます。

ジャンクションテーブルの典型的な例である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)は有効なプライマリーキーではありません。

構成要素

多対多の関係は、2つの主要なリレーションシップタイプの可能な構成の1つにすぎません。これらは十分に一般的であるため、図ではlibrary_patronsのようなジャンクションテーブルを完全に省略し、両端を「多」の記号で表現することがよくあります。しかし、テーブル間の接続のすべてのネットワークは、どんなに複雑であっても、その構成要素である一対一および一対多の関係に還元することができます。

境界

単一のデータベースは、複数の外部キーリレーションシップのネットワークを含むことができます（そしてしばしば含みます）。しかし、その逆は通常当てはまりません。一般的なリレーショナルデータベースの中で、MySQLとMariaDBだけがスキーマとデータベースを混同しており、そのため、両方のデータベースが同じサーバーでホストされている限り、クロスデータベースの外部キーを許可しています。他のデータベースは許可していません。

データベース内でのテーブルやスキーマの編成については後で詳しく説明しますが、特定の概念の結合された属性がテーブルレイアウトの基礎を形成するのと同じように、複数テーブル間のリレーションシップグラフをデータベースレイアウトの不可分な単位と考えることは有用です。