接続の確立

導入

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

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

この細部へのこだわりは、ワークステーションでデータベースサーバーを実行することが考えられなかった時代からの歴史的な遺物であり、現代では、そのように正式に指定されたER図はほとんどありません。ここにある図と同様に、「最大1つ」の記号と「ゼロから多数」の記号があれば、要点を伝えるのに十分であり、それ以上のレベルと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の場合、その出所から書籍へのリンクをたどる方法はありません。一致するprovenance_idについてbooksを検索する必要があります。そして、ほとんどの書籍には特別な出所がないため、provenance_idのほとんどの値はNULLになります。

この状況では、provenances.book_idアプローチが明らかに優れています。book_idリンクは追跡可能であり、カラムは効率的に使用され、provenances.book_idはプライマリキーでもあります。単一の書籍が複数の場所からライブラリに来るべきではないためです。De HaanとKoppelaarsは、provenancesをbooksの特殊化と呼び、同じプライマリキーで識別される親のレコードに補足情報を追加するテーブルです。booksとprovenancesの間の接続は「一対一」のリレーションシップです。任意のbook_id値の1つだけがどちらのテーブルにも存在できるためです。

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値を持っています。これは非常に重要な仮定、そしておそらく非常に間違った仮定を表しています。つまり、すべての人は1つのライブラリのみをひいきにするということです。誰かが別のライブラリに行く場合、すべての情報を最初から入力する必要があります。これは別の重要な仮定、つまりpatronsの単一のレコードが単一の人に対応するという仮定に違反します。両方が真実であるはずはありません。

同様のソリューションには2番目の問題があります。書籍をチェックアウトした人をまだ追跡していません。1人の利用者は多くの書籍を借りることができますが、1冊の書籍は何度もチェックアウトできます。構造的には、これは1つのライブラリに多くの利用者がいて、その利用者自身が複数のライブラリから借りることができるケースとほぼ同じです。

「多対多」のリレーションシップは、専用のテーブルで表現する必要があります。これは、他の名前の中でも、多くの場合、ジャンクションテーブルまたはブリッジテーブルと呼ばれます。ジャンクションテーブルは、仲介する各テーブルへの外部キーを保持し、それらのテーブルへのリレーションシップの「多」側になります。各外部キーにわたるプライマリキーは、同じリレーションシップの重複を防ぎます。

ジャンクションテーブルの教科書的な例である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だけが、一般的なリレーショナルデータベースの中でスキーマとデータベースを混同しているため、両方のデータベースが同じサーバーでホストされている限り、データベース間の外部キーを許可します。他のデータベースは許可しません。

後でデータベース内およびデータベース内のスキーマでのテーブルの編成に戻りますが、特定の概念の結合された属性がテーブルレイアウトの基礎を形成するのと同じように、マルチテーブルリレーションシップグラフをデータベースレイアウトの不可分な単位と見なすことが役立ちます。