物理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 でないシステムは、すでにどこかで整合性が壊れています。
手を動かす
外部キーのある表と無い表を並べ、同じ不正なデータを入れてみて、結果の差を数えます。
テーブル構造
CREATE TABLE stores (
id integer PRIMARY KEY,
name text NOT NULL
);
CREATE TABLE orders_with_fk (
id integer PRIMARY KEY,
store_id integer NOT NULL REFERENCES stores (id)
);
CREATE TABLE orders_no_fk (
id integer PRIMARY KEY,
store_id integer NOT NULL
);
INSERT INTO stores VALUES (1, '渋谷店'), (2, '梅田店');
INSERT INTO orders_with_fk VALUES (1, 1), (2, 1), (3, 2);
INSERT INTO orders_no_fk VALUES (1, 1), (2, 1), (3, 2);
期待される出力
| design | orphans |
|---|---|
| no_fk | 1 |
| with_fk | 0 |