「主キー」と「外部キー」。どちらも「キー」と付くので、なんとなく同じ仲間に見えてしまう。ところが試験問題では「外部キーに重複は許されるか」「NULLは入れられるか」と、細かい性質の違いが容赦なく問われる。編集部でデータベースの公式ドキュメントを読み直して整理してみると、2つは「自分を名乗る番号」と「相手を指し示す番号」という、向きの違う道具だった。
まず結論：主キーと外部キーは「指している相手」が違う
- 主キー：そのテーブルの中で、1行を確実に見分けるための列。重複もNULL（空っぽ）も許されない
- 外部キー：別のテーブルの主キー（または一意な列）を参照する列。「その相手が実在すること」を保証する
- 主キーは「自分は誰か」、外部キーは「相手は誰か」を表す。2つがそろうことで、表どうしのつながりが壊れにくくなる
主キーとは？――学校の「学籍番号」のようなもの
主キー（プライマリキー）は、テーブルの各行を一意に、つまり「ほかと絶対に取り違えないように」特定するための列。PostgreSQLの公式ドキュメントでも、主キー制約は値が「一意（UNIQUE）であり、かつNULLでない（NOT NULL）」ことを求めるものと説明されている。
学校の生徒名簿で考えてみる。同じクラスに「佐藤さん」が2人いることは珍しくない。名前だけで呼ぶと、どちらの佐藤さんか分からなくなる。そこで学校は一人ひとりに学籍番号を割り振る。学籍番号は全校で重複せず、番号のない生徒もいない。これがまさに主キーの性質だ。
主キーについて押さえておきたいポイントは次の3つ。
- 1つのテーブルに設定できる主キーは1つだけ
- 1つの列でなく、複数の列を組み合わせて主キーにすることもできる（複合主キー）
- 値は重複不可、NULL不可
外部キーとは？――部活の「入部届」に書く学籍番号のようなもの
外部キーは、あるテーブルの列が、別のテーブルの主キー（または一意性が保証された列）を参照していることを表す制約。参照される側を「親テーブル」、参照する側を「子テーブル」と呼ぶことが多い。
さっきの学校の例を続ける。サッカー部の入部届には、名前に加えて学籍番号を書く欄がある。部の名簿は「部員テーブル」、学校の生徒名簿は「生徒テーブル」と考えると、入部届の学籍番号欄が外部キーにあたる。
ここで大事なのは、外部キーが守ってくれるルールだ。生徒名簿に存在しない学籍番号「9999」を入部届に書いても、受け付けてもらえない。データベースでいえば、親テーブルにない値を子テーブルの外部キー列に登録しようとするとエラーになる。この「参照先が必ず実在する」という性質を、参照整合性と呼ぶ。
一方で、外部キーの列には同じ値が何度出てきてもかまわない。1人の生徒がサッカー部と美術部の両方に入れば、部員テーブルには同じ学籍番号が2回登場する。主キーとはここが大きく違う。
「主キーと外部キーの違い」を学校の名簿でわかりやすく解説
| 観点 | 主キー | 外部キー |
|---|---|---|
| 学校のたとえ | 生徒名簿の学籍番号 | 入部届に書く学籍番号 |
| 役割 | 自分のテーブルの1行を特定する | 別テーブルの行を指し示す |
| 重複 | 許されない | 許される（同じ相手を何度も指してよい） |
| NULL | 許されない | 制約上は許される（NOT NULLを付ければ禁止できる） |
| 1テーブルあたりの数 | 1つだけ | 複数設定できる |
| 守ってくれるもの | 行の一意性（取り違え防止） | 参照整合性（存在しない相手を指さない） |
| 参照先の条件 | なし（自分自身を表す） | 親テーブルの主キー、または一意性のある列 |
覚え方のコツは、「主」は自分、「外部」は自分の外にいる相手、と読むこと。外部キーという名前は、外のテーブルの主キーを借りてきて書いている、と考えるとしっくりくる。
なぜ主キーだけではダメなのか？
主キーさえあれば、各テーブルの中で行を見分けることはできる。それでも外部キーが必要になる理由は、テーブルとテーブルの「つながり」を守る仕組みが主キーにはないからだ。
外部キー制約を付けずに部員テーブルを作った場合を考えてみる。
- 生徒名簿にない学籍番号が、入力ミスで部員名簿に登録されてしまう
- 卒業した生徒を生徒名簿から消したのに、部員名簿には番号だけが残り、「誰でもない部員」が生まれる
どちらも、データを1件ずつ見ていても気づきにくい。集計のときに人数が合わない、画面に名前が表示されない、といった形で後から表に出てくる。外部キーは、こうした食い違いをデータベース自身に見張らせるための仕組みだと編集部では理解している。アプリケーション側のチェックだけに頼ると、別の経路からデータが書き込まれたときに抜け道が生まれる。最後の砦をデータベースに置いておく意味はそこにある。
親の行を削除したとき、子の行はどうなるのか
外部キーで特に出題されやすいのが、参照されている親の行を削除・更新したときの振る舞いだ。SQLでは外部キーを定義するときに、次のような動作を指定できる。
| 指定 | 親の行を削除したときの動作 | 学校のたとえ |
|---|---|---|
| RESTRICT / NO ACTION | 参照している子の行があれば、削除をエラーにする | 部活に在籍中の生徒は、名簿から消せない |
| CASCADE | 参照している子の行も一緒に削除する | 生徒を名簿から消すと、入部届も一緒に破棄される |
| SET NULL | 子の行の外部キー列をNULLにする | 入部届は残すが、学籍番号欄を空欄にする |
PostgreSQLのドキュメントでは、何も指定しない場合の既定はNO ACTIONで、参照している行が残っていればエラーになると説明されている。CASCADEは便利な反面、1件の削除が想定以上の範囲に波及することがある点に注意が必要だ。編集部としては、「消えてよいデータか」を業務の目線で決めてから選ぶべき設定だと考えている。
まとめ：3行で理解する主キーと外部キー
主キーは、テーブルの中で自分の行を名乗るための、重複もNULLもない番号。外部キーは、別テーブルの主キーを指し示し、相手が実在することを保証する番号。前者は取り違えを防ぎ、後者はつながりの食い違いを防ぐ。問題文で迷ったら、「この列は自分を名乗っているのか、それとも相手を指しているのか」と問い直してみてほしい。それだけで、重複やNULLの可否まで自然と答えが出てくる。
よくある質問
Q1. 外部キーは、必ず相手の主キーを参照しなければいけませんか？
主キーである必要はないが、一意性が保証された列である必要がある。PostgreSQLのドキュメントでは、外部キーが参照できるのは主キー、または一意制約（UNIQUE）が付いた列などと説明されている。重複があり得る列を参照すると「どの行を指しているのか」が定まらないため、この条件が付いている。実務でも試験でも、主キーを参照する形がもっとも一般的だ。
Q2. 1つのテーブルに主キーと外部キーの両方を持てますか？
持てる。むしろ多くのテーブルが両方を持っている。たとえば「注文明細」テーブルは、自分の行を特定する主キーと、どの注文か・どの商品かを指す2つの外部キーを持つことがよくある。さらに、注文番号と商品番号の組み合わせをそのまま複合主キーにし、それぞれの列が同時に外部キーでもある、という設計も珍しくない。
Q3. 主キーには「会員ID」のような連番と、「メールアドレス」のような意味のある値のどちらを使うべきですか？
一長一短がある。メールアドレスのように業務上の意味を持つ値を使う主キーを「自然キー」、意味を持たない連番などを「代理キー（サロゲートキー）」と呼ぶ。自然キーは分かりやすいが、利用者がメールアドレスを変更すると、主キーと、それを参照するすべての外部キーを書き換える必要が出てくる。こうした変更の影響を避けるため、主キーには意味を持たない値を使い、メールアドレスには別途一意制約を付ける設計がよく選ばれる。
Q4. 基本情報技術者試験では、どのように出題されますか？
主に2つの形がある。1つは性質を問う知識問題で、「主キーは重複・NULL不可」「外部キーは参照整合性を保つ」を正確に押さえていれば対応できる。もう1つは、表の定義とデータが示され、「この行を追加・削除できるか」「参照整合性制約に違反するのはどの操作か」を判断させる問題だ。後者は、親テーブルに存在しない値を子に入れる操作や、子から参照されている親の行を削除する操作がNGになる、という視点で読むと解きやすい。
コメント