交差テーブルを参照するテーブルの外部キー

SQLAP

3章 IDリクワイヤド - 3.5.3 自然キーと複合キーの活用 より

現実問題として、複合主キーがどのようにフレームワークでサポートされているか等については良く知りません。

1つ注意すべき点は、複合主キーを参照する外部キーもまた、列の組み合わせでなければならないことです。このため、従属テーブル側では同じ列定義が重複することになり、簡潔さを欠いてしまいます。

これは何を言っているのかというと、もし交差テーブルBugsProductsと1対多の関係となるテーブルがあった場合その外部キーをどうするかという話。 BugsProductsの主キーに疑似キーidを使っていれば、従属テーブル側の外部キーはBugsProducts(id)を参照すればよい。

CREATE TABLE BugsProducts (
  id SERIAL PRIMARY KEY,
  bug_id BIGINT,
  product_id BIGINT,
...
);

CREATE TABLE BugsProductsHoges (
  id SERIAL PRIMARY KEY,
  bug_product_id BIGINT,
...
  FOREIGN KEY (bug_product_id) REFERENCES BugsProducts(id)
);

一方BugsProductsの主キーに複合キーをつかっている場合、従属テーブルの外部キーも複合キーとなる。 また、このとき、従属テーブルBugsProductsHogesの主キーは、

  • (bug_id, product_id)を含む複合主キーにする
  • 疑似キーを導入する

のどちらかになる。つまり、

CREATE TABLE BugsProducts (
  bug_id BIGINT,
  product_id BIGINT,
...
);

に対して

CREATE TABLE BugsProductsHoges (
  bug_id BIGINT,
  product_id BIGINT,
  hoge_id BIGINT,
...
  PRIMARY KEY (bug_id, product_id, hoge_id),
  FOREIGN KEY (bug_id, product_id) REFERENCES BugsProducts(bug_id, product_id)
);

とするか、あるいは

CREATE TABLE BugsProductsHoges (
  id SERIAL PRIMARY KEY,
  bug_id BIGINT,
  product_id BIGINT,
...
  FOREIGN KEY (bug_id, product_id) REFERENCES BugsProducts(bug_id, product_id)
);

となる。

ちょっと話は変わるが、このように外部キーが複合主キーの一部になっているのを親子関係、そうでないのを参照関係と呼ぶ（と、たぶん渡辺幸三さんの本で読んだ）。

BugsとBugsProductsの関係も、主キーが疑似キー(id)であれば参照関係、複合主キー(bug_id, product_id)であれば親子関係といえる。交差テーブルの場合、親子関係か参照関係かという比較はそのまま複合主キーか疑似キーかという話になる。

交差テーブルではない、多対一の関係で参照関係、親子関係のどちらを選択するか、どのように判断すればよいだろう（ほとんどの場合参照関係が使われているように思うが）。

サンプルデータベースではBugsProducts、Screenshots、Tagsで複合主キーが使われている。それぞれ異なる使われ方で面白い。

CREATE TABLE Screenshots (
  bug_id            BIGINT UNSIGNED NOT NULL,
  image_id          BIGINT UNSIGNED NOT NULL,
  screenshot_image  BLOB,
  caption           VARCHAR(100),
  PRIMARY KEY      (bug_id, image_id),
  FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id)
);

CREATE TABLE Tags (
  bug_id            BIGINT UNSIGNED NOT NULL,
  tag               VARCHAR(20) NOT NULL,
  PRIMARY KEY      (bug_id, tag),
  FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id)
);
  • BugsProductsでは、交差テーブルとして使われている。
  • Screenshotsは親子関係なのだろう。image_idを主キーにする参照関係でも良かったように思える。image_idはどのように振るのだろう。
  • Tagsも親子関係のようだがtagそのものをキーに含めている。疑似キーと自然キーの組み合わせの複合キーといった感じか