主キーと外部キーは何のためにありますか｜技術面接問題

こう聞かれます

「主キーと外部キーは何のためにありますか」
「利用者と注文の表を作るなら、どんなキーを置きますか」
「メールアドレスを主キーにしてもよいですか」(一意制約との違いを確かめる形)

面接官が見ている点

主キーは「行を見分ける」、外部キーは「ほかの表の行を正しく指す」と、役目で話せるか
外部キーがあると、存在しない相手を指す行を DB が断る、と分かっているか
主キーと一意制約の違いを言えるか

短い答え

主キーは表の 1 行を見分けるための列で、重複も NULL (空の値) も許されません。
外部キーは、ほかの表の主キーなどを指す列で、存在しない相手を指す行を DB が断ります。
主キーで行を見分け、外部キーで表どうしのつながりが壊れないように守ります。

少し詳しい答え

利用者と注文の例

1 人の利用者が何件も注文する形です。注文の user_id が、利用者の id を指します。

SQL クエリ

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  email VARCHAR(255)
    NOT NULL UNIQUE
);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL,
  FOREIGN KEY (user_id)
    REFERENCES users(id)
);

この形にすると、DB は次のような書き込みを断ります。

いない利用者 (たとえば id が 99) を指す注文を入れる
注文が残っている利用者を消す (何もしないと、消された人を指す注文が残るため)
同じ id や同じメールアドレスの利用者をもう 1 人入れる

同じ利用者の注文が何件あっても構いません。外部キーの列には、同じ値が何度も入ってよいからです。

主キーと一意制約

一意制約 (UNIQUE) も重複を断ります。違いは、主キーは表に 1 つだけで NULL を許さないこと、一意制約はいくつも置けて、NOT NULL を付けなければ NULL も入ることです。メールアドレスは変わることがあるので、主キーにはせず、一意制約で重複を防ぐ形がよく使われます。

作った物と結べると伝わりやすくなります。たとえば「投稿の表に利用者の外部キーを置いたので、画面の側で確かめ忘れても、いない人の投稿は DB が入れなかった」のように話します。

よくある間違い

「外部キーは表をつなげて検索するために要る」と言う結合は外部キーが無くても書けます。外部キーの役目は、つながりが壊れないように守ることです
「主キーと一意制約は同じ」と言う主キーは表に 1 つで NULL を許しません。一意制約はいくつも置け、NULL の扱いも違います
重複のチェックを画面の側だけで済ませる 2 人が同時に登録すると、どちらも「まだ無い」と判断して通ることがあります。DB の制約でも守ります

深掘りの問い

「利用者を消すとき、注文はどうしますか」消すのを断る、注文もいっしょに消す、注文の利用者を NULL にする、を外部キーの設定で選べます。売上の記録を残したいなら消さない、のように業務で決める、と答えます
「メールアドレスを主キーにすると、何が困りますか」メールを変えると、それを指している表の値も全部直す必要が出ます。変わらない番号を主キーにする、と答えます
「主キーは連番とランダムな ID のどちらがよいですか」連番は短く並びも素直ですが、URL に出すと件数を推測されます。外に出すかどうかで選ぶ、と答えます