物理FKと論理FK

外部キーを張らないという選択

ここまで、関係があれば外部キー制約を張ってきました。しかし実務では、あえて張らない現場もあります。

列としては store_id を持っていて、意味の上では stores.id を指している。しかし FOREIGN KEY は書かない。この状態を論理外部キー、制約まで書いたものを物理外部キーと呼び分けます。

張らない理由として挙げられるもの

張らない側の言い分は、だいたい次の3つです。

書き込みが速くなる。 外部キーがあると、INSERT のたびに参照先の存在を確かめる処理が入ります。

データを消しやすい。 テスト用にテーブルを空にしたいとき、外部キーがあると順番を守って消す必要があります。

アプリ側で保証している。 どうせプログラムが正しい id しか入れないので、二重に守る必要はない。

それでも張るべき理由

3つ目が一番あやしい言い分です。

データベースに書き込むのはアプリだけではありません。データ移行スクリプト、管理画面、障害対応の手打ち SQL、他チームが作ったバッチ。入口はいくつもあり、そのすべてが正しく振る舞う保証はありません。

そして、壊れたデータは壊れた瞬間には気づけません。存在しない店舗を指す注文が1件生まれても、エラーは出ません。数か月後に売上集計が合わなくなって初めて気づき、そのときにはもう、どの行がいつ壊れたのか分からなくなっています。

外部キー制約は、壊れた瞬間にその場で止めてくれる唯一の仕掛けです。

速さの話

書き込みが速くなるのは事実ですが、差が問題になるのは秒間何千件も書き込む規模の話です。そこに達していない段階で外部キーを外すのは、測っていない最適化のために整合性を捨てていることになります。

迷ったら張る。外すなら、測ったうえで、外した理由を書き残す。これが順番です。

孤児を数える

論理外部キーで運用しているシステムでは、参照先が消えた行、いわゆる孤児行が溜まっていることがあります。探し方は決まっています。

SELECT count(*) FROM orders o
LEFT JOIN stores s ON s.id = o.store_id
WHERE s.id IS NULL;

LEFT JOIN して相手が NULL になる行が孤児です。この件数が 0 でないシステムは、すでにどこかで整合性が壊れています。

張ったあとに決めること

外部キーを張ると、親を消す操作と親のキーを書き換える操作が、子をどう扱うか決めるまで通らなくなります。何も書かなければ既定は NO ACTION で、参照されている間は拒否です。

書く場所は子テーブルの宣言の中、REFERENCES 親(列) の直後です。親の定義を見ても、どう扱われるかは分かりません。

CREATE TABLE products (
  id          INT PRIMARY KEY,
  category_id INT REFERENCES categories(id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE
);

ON DELETE と ON UPDATE は独立していて、片方だけ書いても構いません。書ける動きは NO ACTION RESTRICT CASCADE SET NULL SET DEFAULT の 5 つです。

取り返しがつかない側を既定にしない

迷ったら RESTRICT から考えます。親を消そうとした瞬間にエラーで止まるので不便ですが、不便には気づけます。CASCADE の事故は逆で、静かに成功して、後から数字が合わなくなって発覚します。

CASCADE が正しいのは、子が親から切り離すと意味を失う関係に限られます。注文と注文明細がそれで、明細だけ残っても誰の何なのか分かりません。同じ親子でも顧客と注文は答えが反対です。顧客が退会しても注文の履歴は会計上必要なので、ここで CASCADE を選ぶと売上そのものが消えます。

親が消えても子が意味を持つなら SET NULL です。担当者と案件のような関係で、担当者が退職しても案件は残ります。使う条件が 2 つあり、外部キーの列が NULL を許していることと、NULL が業務上どういう状態なのかを決めておくことです。「担当が外れた」なのか「まだ決めていない」なのかで、画面も絞り込みも変わります。

ON UPDATE が効いてくるのは、商品コードやメールアドレスのように業務上の意味を持つ値を主キーにしている場合です。id のような連番なら親のキーはまず変わりません。

解説

決めたら、本番へ入れる前に検証環境で親を 1 行消してみて、何行が道連れになるかを数えてください。想定と違うなら、それは設計が業務と合っていない合図です。

手を動かす

外部キーのある表と無い表を並べ、同じ不正なデータを入れてみて、結果の差を数えます。