CASCADE vs RESTRICT vs SET NULL
1 行消したつもりが、20 万行消えていた
ON DELETE CASCADE は「親を消したら子も消す」です。テストデータで試すと、後片付けが要らない便利な設定に見えます。
ところが本番では、1 件の顧客に数千件の注文がぶら下がり、その注文に数万件の明細がぶら下がっています。CASCADE は孫にも連鎖するので、顧客を 1 行消すつもりで打った DELETE が、テーブルをロックしたまま数十万行を消していきます。そして消えたものは戻りません。CASCADE は気軽に付ける既定値ではなく、意識して選ぶものです。
出発点は「消させない」に置く
迷ったらまず RESTRICT から考えます。
SQL クエリ
CREATE TABLE orders (
id INT PRIMARY KEY,
customer_id INT REFERENCES customers(id) ON DELETE RESTRICT
);こうしておくと、顧客を消そうとした瞬間にエラーで止まります。止まるのは不便ですが、不便には気づけます。CASCADE の事故は逆で、静かに成功して、後から数字が合わなくなって発覚します。取り返しがつかない側を既定にしない、というのが選び方の芯です。
RESTRICT を選ぶと、親を消す手順は 2 段階になります。子を先に片付けてから親を消す、という順序をアプリか運用手順に持たせることになります。その代わり、何件が道連れになるのかを、子を消す時点で自分の目で確認できます。
CASCADE は「子が親の一部」のときだけ
子が親から切り離すと意味を失う関係に限り、CASCADE が正しい選択になります。典型は注文と注文明細です。
SQL クエリ
CREATE TABLE order_items (
id INT PRIMARY KEY,
order_id INT REFERENCES orders(id) ON DELETE CASCADE
);明細だけが残っても、誰の何の明細なのか分かりません。この関係なら、親が消えたら子も消えるのが自然です。逆に「顧客と注文」は違います。顧客が退会しても注文の履歴は会計上必要なので、ここで CASCADE を選ぶと売上そのものが消えます。同じ親子でも答えが反対になります。
親が居なくても子が意味を持つなら SET NULL
担当者と案件のような関係では、担当者が退職しても案件は残ります。
SQL クエリ
CREATE TABLE contracts (
id INT PRIMARY KEY,
title TEXT,
owner_id INT REFERENCES staff(id) ON DELETE SET NULL
);親が消えると子の owner_id が NULL になり、行そのものは残ります。使うときの条件が 2 つあります。1 つは外部キーの列が NULL を許していること。NOT NULL とは両立しません。もう 1 つは、NULL が業務上どういう状態なのかを決めておくことです。「担当が外れた」なのか「まだ決めていない」なのかで、画面の出し方も絞り込みの条件も変わります。
| 親を消したとき | 子はどうなるか | 向いている関係 |
|---|---|---|
RESTRICT | 消せない。エラーで止まる | 顧客と注文、商品マスタと在庫 |
CASCADE | 子も一緒に消える | 注文と明細、投稿と添付ファイル |
SET NULL | 子は残り、参照だけ NULL になる | 担当者と案件、レビュアと文書 |
決めたら、本番へ入れる前に検証環境で親を 1 行消してみて、何行が道連れになるかを数えてください。想定と違うなら、それは設計が業務と合っていない合図です。
テーブル構造
CREATE TABLE users (
id INT PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE tasks (
id INT PRIMARY KEY,
title TEXT NOT NULL,
assignee_id INT,
CONSTRAINT fk_tasks_assignee
FOREIGN KEY (assignee_id) REFERENCES users(id)
ON DELETE SET NULL
);
INSERT INTO users (id, name) VALUES
(1, '田中'),
(2, '佐藤'),
(3, '鈴木');
INSERT INTO tasks (id, title, assignee_id) VALUES
(101, '見積もり作成', 1),
(102, '請求書送付', 1),
(103, '顧客訪問', 2),
(104, '報告書レビュー', 3);
期待される出力
| id | title | assignee_id |
|---|---|---|
| 101 | 見積もり作成 | NULL |
| 102 | 請求書送付 | NULL |
| 103 | 顧客訪問 | 2 |
| 104 | 報告書レビュー | 3 |