主キー・外部キー・候補キー

メールアドレスを主キーにした日から、変更が怖くなる

利用者の表の主キーをメールアドレスにしたとします。重複せず、必ず入力されていて、一見よくできた選択に見えます。

問題は、本人から「メールアドレスを変えたい」と言われた瞬間に起きます。利用者の表の 1 行を直すだけでは終わりません。注文にも、問い合わせにも、監査ログにも、そのメールアドレスが書き写されています。全部を同時に、途中で失敗させずに書き換える必要があります。

ここから、主キーが満たすべき条件が出てきます。重複しないこと、空でないこと、そして 一度決めたら変わらないこと です。3 つ目が抜けやすく、いちばん高くつきます。

2 つ目にも理屈があります。空の値は「等しいかどうか」を確かめられません。空同士が同じ行を指しているのか別の行なのかを、DB が判断できないからです。行を 1 つに特定するための列が特定に使えない、では話になりません。

行を特定できる列は、1 つとは限らない

行を 1 つに特定できる列の組み合わせは、たいてい複数あります。利用者の表なら ID でも決まるし、メールアドレスでも決まる。この「これ以上削れない、行を特定できる組み合わせ」を候補キーと呼びます。

候補キーの中から代表として選んだ 1 つが主キーです。選ばれなかったほうは代替キーで、重複を許さない制約を付けて守ります。特定はできるが余計な列まで抱えている組み合わせは、削っていくと候補キーになります。

選び方の定石は、業務上の意味を持たない番号を別に用意することです。社員番号や商品コードのように意味のある値は、桁数の変更や採番ルールの見直しといった業務側の都合で必ず揺れます。意味のない番号なら、業務が何をしても揺れません。値が小さいので索引も軽く、外部に見せても中身が漏れません。ただし連番をそのまま URL に出すと登録件数が推測できるので、外に見せる番号だけ別に持つこともあります。

意味のある値を主キーにしてはいけない、という話ではありません。すでに世の中で管理番号が決まっていて、それが変わらないと保証されているなら、それを使うのは自然です。問題は、保証しているのが自分たちではない点にあります。

存在しない相手を指す行を、どう防ぐか

別の表の主キーを指す列が外部キーです。これを宣言しておくと、存在しない利用者の ID を持つ注文は、書き込もうとした時点で DB が拒否します。親のない行がひとりでに生まれることがなくなります。

親が消えたとき子をどうするかも、宣言で決められます。一緒に消す、参照を空にする、子が残っているなら親の削除自体を拒む、の 3 通りです。業務システムで無難なのは 3 つ目です。消してよいかどうかの判断を、DB が握りつぶさずに人へ返してくれます。

外部キーを宣言せず、アプリのコードだけで守る運用もあります。書き込みが少し速くなり、表を分割するときも楽だからです。その代わり、書き込む経路が 1 つ増えるたびに守りの穴が増えます。管理画面からの一括削除、移行スクリプト、手作業で流した 1 文。宣言してあれば、どの経路から来ても同じように止まります。

なお、間に立てた表のように 2 列を組にした主キーも使えます。ただし結合の記述が長くなり、ORM も扱いにくくなるため、意味のない番号を主キーに立てて、2 列の組には重複禁止の制約を付ける形もよく見ます。