主キーと外部キー
主キーと外部キーとは
[[リレーショナルデータベース]] のテーブルには「行の順番」に意味がありません。配列のように「3行目を更新する」とは書けず、値で行を指し示すしかありません。この「指し示すための値」を決めるのが主キー(primary key)、別テーブルの主キーを指す列が外部キー(foreign key)です。正規化でテーブルを分けても、ER図で1対多を描いても、最後にそれをDBの制約として書き下すのはこの2つです。
主キー — 行を1つに決める約束
主キーを指定すると、次の3つが同時に決まります。
- その列の値は一意である(同じ値の行を2つ作れない)
- その列はNULLを許さない(空のままINSERTできない)
- 1つのテーブルに主キーは1組だけ
CREATE TABLE users (
id BIGINT PRIMARY KEY,
email VARCHAR(255) NOT NULL UNIQUE,
name VARCHAR(100) NOT NULL
);
初学者がまず混乱するのが、主キーと一意制約(UNIQUE)の違いです。
| 主キー | 一意制約(UNIQUE) | |
|---|---|---|
| 値の重複 | 不可 | 不可 |
| NULL | 不可 | 多くのDBで可(NULL同士は重複扱いしない) |
| 1テーブルあたり | 1組だけ | 何個でも付けられる |
| 役割 | 行を指し示す代表の値 | 業務ルール上の重複禁止 |
上の例では「メールは重複禁止」をUNIQUEで表し、行を指す代表は id に任せています。
もう1つ大事なのが不変性です。主キーの値を変えると、それを参照する外部キーもすべて直す必要があります。主キーには「業務が変わっても書き換えずに済む値」を選びます。
主キーには一意性チェックのため自動的にインデックスが作られます([[データベースインデックス]])。
複合主キー — 第2正規形が読めない理由
主キーは1列とは限りません。2列以上を組にして初めて一意になる場合、それを複合主キーと呼びます。ER図で多対多を分解した中間テーブルが典型例です。
CREATE TABLE enrollments (
student_id BIGINT NOT NULL,
course_id BIGINT NOT NULL,
grade INT,
PRIMARY KEY (student_id, course_id)
);
1人の学生は複数の講義を取るので student_id 単独では重複し、1つの講義は複数の学生が取るので course_id 単独でも重複します。組にして初めて1行に決まります。
これが分かると [[正規化]] の第2正規形が読めます。「主キーの一部にだけ依存する列を分ける」という定義は、主キーが複数列のときにしか意味を持ちません。上の受講テーブルに course_name を置くと、それは course_id だけで決まってしまう(主キーの一部にだけ依存する)ので、講義テーブルへ切り出す — これが第2正規形です。主キーが1列のテーブルには「一部」が存在しないため、第2正規形は自動的に満たされています。
サロゲートキーとナチュラルキー
主キーの選び方には2つの流派があります。
| ナチュラルキー | サロゲートキー | |
|---|---|---|
| 例 | メールアドレス、社員番号、ISBN | 連番のid、UUID |
| 出どころ | 業務上すでにある値 | DBのために用意した無意味な値 |
| 業務変更への強さ | 弱い | 強い |
| 可読性 | 高い(値を見れば分かる) | 低い |
「メールアドレスを主キーにしたら、変更のたびに注文テーブル数万行の外部キーを書き換える羽目になった」が典型的な失敗です。実務ではサロゲートキーを主キーに据え、メールアドレスや社員番号にはUNIQUE制約を別途付ける形が多数派です。
連番は短く扱いやすい一方、URLに出すと「/orders/1041」から総件数や他人のIDが推測できます。UUIDは推測されにくい代わりに値が長くなるため、内部は連番・外部公開はUUID列という併用もよく採られます。
外部キーと参照整合性
外部キーは、その列の値が参照先テーブルに実在することをDBに約束させる制約です。
CREATE TABLE orders (
id BIGINT PRIMARY KEY,
customer_id BIGINT NOT NULL,
FOREIGN KEY (customer_id) REFERENCES customers(id)
);
これだけで、存在しない customer_id の注文はINSERTできず、注文が残る顧客の行は削除できなくなります。この「参照先が必ず存在する」状態を参照整合性と呼びます。アプリ側のチェックだけに頼ると、バッチ処理や手動SQLといった別経路から簡単に壊れます。DBの制約は最後の防波堤です。
親テーブルの行を消したとき子テーブルをどうするかは、ON DELETE で指定します。
| 指定 | 親行を削除したときの挙動 |
|---|---|
| RESTRICT / NO ACTION | 子行が残っていれば削除自体をエラーにする |
| CASCADE | 子行も連鎖して削除する |
| SET NULL | 子行の外部キー列をNULLにする(NOT NULLの列では使えない) |
CASCADEは危険でもあります。顧客1行の削除が注文・注文明細・支払い履歴まで連鎖し、会計上残すべきデータが消えることがあります。「消してよいか」ではなく「連鎖の終点まで消えて困らないか」で判断し、業務データは物理削除ではなく deleted_at 列による論理削除にするのが安全側の設計です。
なお外部キー列をNULL許容にすると「まだ担当者が決まっていない」を表現できます。NULLのときは参照チェックがスキップされます。
初学者向けポイント
- 迷ったらまず連番のサロゲートキーを1本入れる。運用開始後に追加すると既存の全行へ値を埋める作業が発生する
- 子テーブルの外部キー列に索引が自動で作られるかは製品次第(MySQL/InnoDB は自動、PostgreSQL は作られない)。無いと親行の削除のたびに子テーブルのフルスキャンが起きるため、自分で貼る前提で確認する
- [[ER図とデータモデリング]] の「1対多では多側に外部キーを持たせる」は、外部キーが常に参照する側に置かれるから。1側に「子のID一覧」を列として持つことはできない
- 「テストでは通るのに本番投入で外部キー違反」の多くは投入順序が原因。親を先、子を後に入れ、削除は逆順にする
- 外部キーで結んだテーブルを組み合わせて取り出すのが [[sql-join]] です
関連技術とのつながり
- [[リレーショナルデータベース]] — 主キーと外部キーはテーブル同士を関連づける土台
- [[正規化]] — 複合主キーが分かって初めて第2正規形の定義が読める
- [[ER図とデータモデリング]] — カーディナリティの判断結果が、どちらに外部キーを置くかを決める
- [[データベースインデックス]] — 主キーには自動で作られ、外部キー列には自分で貼る
- [[SQL]] — CREATE TABLE の制約定義として書き、JOIN の結合条件として使う
Q: 主キーと一意制約(UNIQUE)の違いとして本文が挙げているのはどれ?
- [ ] 主キーは重複を許すが、UNIQUEは重複を許さない
- [x] 主キーはNULLを許さないが、UNIQUEは多くのDBでNULLを許す
- [ ] UNIQUEは1テーブルに1組しか付けられない
解説: どちらも重複は不可ですが、主キーはNULL不可で1テーブルに1組、UNIQUEは多くのDBでNULLを許し何個でも付けられます。
Q: 複合主キーが関係する正規形はどれ?
- [ ] 第1正規形。1つのマスに1つの値だけ入れる規則が複合主キーを要求するため
- [x] 第2正規形。「主キーの一部にだけ依存する列を分ける」は主キーが複数列のときにしか意味を持たないため
- [ ] どの正規形とも関係しない
解説: 主キーが1列だと「一部」が存在しないため、第2正規形は自動的に満たされます。複合主キーのときだけ判断が必要になります。
Q: 外部キーに ON DELETE CASCADE を指定した場合、親テーブルの行を削除するとどうなる?
- [ ] 子行が残っていれば削除がエラーになる
- [ ] 子行の外部キー列がNULLになる
- [x] 子行も連鎖して削除される
解説: エラーにするのはRESTRICT / NO ACTION、NULLにするのはSET NULLです。CASCADEは連鎖削除のため、終点まで消えて困らないかを確認して使います。