0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@shalXXXX(shal 0618)

ON DELETE の CASCADE・SET NULL・RESTRICT・NO ACTION の違いを実際に消して確かめる

0
Posted at

外部キーの ON DELETE には CASCADE・SET NULL・SET DEFAULT・RESTRICT・NO ACTION の 5 つがあります。前の 3 つは名前のとおりですが、RESTRICT と NO ACTION はどちらも「消させない」ので、違いがわかりにくい組み合わせです。

この記事では、同じテーブルで親の行を実際に消し、子の行がどうなるかを PostgreSQL と SQLite で確かめます。RESTRICT と NO ACTION の違いが出る場面も再現します。

計測環境

  • PostgreSQL: PGlite 0.5.8（PostgreSQL 18.3 の WebAssembly 版）、Node.js 22
  • SQLite: 3.45.1（Python 3.13 の sqlite3 モジュール）
  • データ: 顧客 2〜3 件、受注 3 件程度の小さなテーブル。挙動の確認が目的なので、速度は測っていません

結論

指定 親を消したときの子の行 検査のタイミング
CASCADE 一緒に消える —
SET NULL 外部キー列が NULL になる —
SET DEFAULT 外部キー列が列の既定値になる —
RESTRICT 子があればエラー 親を消した時点（遅延できない）
NO ACTION（既定） 子が残っていればエラー 文の終わり。遅延制約なら COMMIT 時

RESTRICT と NO ACTION の違いはいつ検査するかだけです。普段の DELETE 1 文では、どちらも同じようにエラーになります。

5 つの動作を実際に確かめる

次のテーブルで、顧客 1（佐藤）を削除します。ON DELETE の部分だけを入れ替えて 5 回実行しました。

CREATE TABLE customers (id int PRIMARY KEY, name text NOT NULL);
CREATE TABLE orders (
  id int PRIMARY KEY,
  customer_id int DEFAULT 0 REFERENCES customers(id) ON DELETE CASCADE  -- ここを入れ替える
);
INSERT INTO customers VALUES (0, '(退会済み)'), (1, '佐藤'), (2, '鈴木');
INSERT INTO orders VALUES (101, 1), (102, 1), (103, 2);

DELETE FROM customers WHERE id = 1;
SELECT * FROM orders ORDER BY id;

PostgreSQL での結果です。

ON DELETE DELETE の結果 削除後の orders
CASCADE 1 件削除 (103, 2) だけが残る
SET NULL 1 件削除 (101, NULL), (102, NULL), (103, 2)
SET DEFAULT 1 件削除 (101, 0), (102, 0), (103, 2)
RESTRICT エラー 変化なし
NO ACTION エラー 変化なし

SQLite（PRAGMA foreign_keys = ON; を実行した接続）でも、子の行の結果は同じでした。

CASCADE でも「削除件数」は親の分だけ

CASCADE で受注 2 件も消えていますが、DELETE が返す件数は 1 でした（PGlite の affectedRows、SQLite の changes() とも 1）。連鎖して消えた子の行は件数に含まれません。「1 件消しただけ」のつもりで、実際には子・孫まで消えていることがあるので、CASCADE の付いた親を消すときは影響範囲を先に確認してください（後述のクエリで一覧できます）。

RESTRICT と NO ACTION はエラーの種類が違う

PostgreSQL では、エラーメッセージと SQLSTATE が異なります。

-- RESTRICT（SQLSTATE 23001 restrict_violation）
ERROR: update or delete on table "customers" violates RESTRICT setting of foreign key constraint "orders_customer_id_fkey" on table "orders"
DETAIL: Key (id)=(1) is referenced from table "orders".

-- NO ACTION（SQLSTATE 23503 foreign_key_violation）
ERROR: update or delete on table "customers" violates foreign key constraint "orders_customer_id_fkey" on table "orders"
DETAIL: Key (id)=(1) is still referenced from table "orders".

アプリで SQLSTATE 23503 を見て「参照されているので消せません」と表示している場合、制約を RESTRICT に変えると 23001 になり、その分岐に入らなくなります。

SQLite ではどちらも FOREIGN KEY constraint failed で、区別はつきません。

RESTRICT と NO ACTION の違いが出る場面

NO ACTION は「文の終わり（遅延制約なら COMMIT 時）に、まだ子が親を参照しているか」を検査します。RESTRICT は親の行を消した時点で検査します。そのため、消した親が同じ文・同じトランザクションの中で戻ってくる場合に結果が分かれます。

1 つの文の中で消して入れ直す（PostgreSQL）

データ変更を含む CTE で、顧客 1 を消して同じ id で入れ直します。

WITH d AS (
  DELETE FROM customers WHERE id = 1 RETURNING id, name
)
INSERT INTO customers SELECT id, name || '（再登録）' FROM d;
  • RESTRICT：DELETE の時点でエラー（violates RESTRICT setting ...）。何も変わらない
  • NO ACTION：成功。文の終わりには id = 1 の顧客が存在するので、受注 101・102 の参照は有効なまま

遅延制約（DEFERRABLE）で COMMIT まで待つ

外部キーに DEFERRABLE INITIALLY DEFERRED を付けて、トランザクションの中で消して入れ直します。

CREATE TABLE orders (
  id int PRIMARY KEY,
  customer_id int NOT NULL
    REFERENCES customers(id) ON DELETE NO ACTION  -- または RESTRICT
    DEFERRABLE INITIALLY DEFERRED
);

BEGIN;
DELETE FROM customers WHERE id = 1;
INSERT INTO customers VALUES (1, '佐藤（再登録）');
COMMIT;

PostgreSQL・SQLite とも、結果は次のとおりでした。

ON DELETE DELETE COMMIT
NO ACTION 通る 成功（親が戻っているので問題なし）
RESTRICT その場でエラー （トランザクションは失敗）

RESTRICT は DEFERRABLE を付けても検査が遅延されません。PostgreSQL 18 の公式ドキュメント（5.5 Constraints）にも、RESTRICT は検査をトランザクションの後のほうに遅らせられないと書かれています。

なお、遅延した NO ACTION で親を消したまま戻さないと、エラーは DELETE ではなく COMMIT で起きます。PostgreSQL で確かめると、DELETE のあとの SELECT は普通に実行でき、COMMIT で violates foreign key constraint になりました。SQLite も同じく COMMIT で FOREIGN KEY constraint failed になります。

SET NULL・SET DEFAULT の落とし穴

NOT NULL の列に SET NULL は付けられるが、消すと失敗する

customer_id int NOT NULL の列に ON DELETE SET NULL を付けても、テーブルは作れます。エラーになるのは親を消したときです。

-- PostgreSQL
ERROR: null value in column "customer_id" of relation "orders" violates not-null constraint
-- SQLite
NOT NULL constraint failed: orders.customer_id

結果として RESTRICT と同じように削除が止まりますが、意図した動きではないはずです。SET NULL を付けるなら列は NULL を許可し、「親がいない子」をアプリで扱えるようにしておきます。

SET DEFAULT の既定値は親に存在している必要がある

SET DEFAULT は、既定値に置き換えたあとでもう一度外部キーを検査します。既定値 0 の顧客が customers にないと、PostgreSQL では次のエラーで削除が失敗しました。

ERROR: insert or update on table "orders" violates foreign key constraint "orders_customer_id_fkey"
DETAIL: Key (customer_id)=(0) is not present in table "customers".

最初の例で SET DEFAULT が成功したのは、id = 0 の「(退会済み)」顧客を用意していたからです。

複合外部キーの SET NULL は全列を NULL にする（PostgreSQL は列を指定できる）

マルチテナントで (tenant_id, customer_id) の複合外部キーを使っている場合、ON DELETE SET NULL は tenant_id も NULL にしようとします。tenant_id が NOT NULL なら、削除は失敗します。

PostgreSQL 15 以降は、NULL にする列を指定できます。

CREATE TABLE customers (tenant_id int, id int, PRIMARY KEY (tenant_id, id));
CREATE TABLE orders (
  id          int PRIMARY KEY,
  tenant_id   int NOT NULL,
  customer_id int,
  FOREIGN KEY (tenant_id, customer_id)
    REFERENCES customers (tenant_id, id)
    ON DELETE SET NULL (customer_id)
);
INSERT INTO customers VALUES (10, 1), (10, 2);
INSERT INTO orders VALUES (101, 10, 1), (102, 10, 2);

DELETE FROM customers WHERE tenant_id = 10 AND id = 1;
指定 結果
ON DELETE SET NULL null value in column "tenant_id" ... violates not-null constraint で失敗
ON DELETE SET NULL (customer_id) 成功。受注 101 は (101, 10, NULL) になる

CASCADE は途中に止める制約があると全体が止まる

CASCADE を何段もつなげていても、途中に 1 つでも NO ACTION（既定）の外部キーがあり、そこに子がいれば、削除全体が取り消されます。

CREATE TABLE customers   (id int PRIMARY KEY);
CREATE TABLE orders      (id int PRIMARY KEY, customer_id int NOT NULL REFERENCES customers(id) ON DELETE CASCADE);
CREATE TABLE order_items (id int PRIMARY KEY, order_id int NOT NULL REFERENCES orders(id) ON DELETE CASCADE);
CREATE TABLE invoices    (id int PRIMARY KEY, order_id int NOT NULL REFERENCES orders(id));  -- 指定なし = NO ACTION

顧客 1 の受注 101・102 に明細 10 件、受注 102 に請求書 1 件がある状態で顧客 1 を消すと、PostgreSQL では次のエラーになりました。

ERROR: update or delete on table "orders" violates foreign key constraint "invoices_order_id_fkey" on table "invoices"
DETAIL: Key (id)=(102) is still referenced from table "invoices".

エラーの対象が customers ではなく orders になっている点に注目してください。連鎖した先で止まっています。このとき受注・明細は 1 件も消えておらず（受注 3 件・明細 10 件のまま）、請求書を消してから再実行すると、顧客 1・受注 2 件・明細 10 件がまとめて消えました。

「請求書があるなら顧客は消させない」という意図どおりの動きですが、どこで止まったかはエラーの on table "orders" を見ないとわかりません。

既存のスキーマの ON DELETE を一覧する

どの外部キーに何が付いているかは、カタログから確認できます。

PostgreSQL

SELECT c.conrelid::regclass  AS child_table,
       c.confrelid::regclass AS parent_table,
       c.conname             AS foreign_key,
       CASE c.confdeltype
         WHEN 'a' THEN 'NO ACTION'
         WHEN 'r' THEN 'RESTRICT'
         WHEN 'c' THEN 'CASCADE'
         WHEN 'n' THEN 'SET NULL'
         WHEN 'd' THEN 'SET DEFAULT'
       END                   AS on_delete
FROM pg_constraint c
WHERE c.contype = 'f'
ORDER BY 2, 1;

前の節のテーブルに、ON DELETE SET NULL の reviews を足して実行した結果です。

 child_table | parent_table |        foreign_key        | on_delete
-------------+--------------+---------------------------+-----------
 orders      | customers    | orders_customer_id_fkey   | CASCADE
 reviews     | customers    | reviews_customer_id_fkey  | SET NULL
 order_items | orders       | order_items_order_id_fkey | CASCADE
 invoices    | orders       | invoices_order_id_fkey    | NO ACTION

SQLite

SELECT m.name    AS child_table,
       f."table" AS parent_table,
       f."from"  AS column_name,
       f.on_delete
FROM sqlite_master AS m
JOIN pragma_foreign_key_list(m.name) AS f
WHERE m.type = 'table'
ORDER BY 2, 1;

同じスキーマで実行すると、on_delete 列に CASCADE・SET NULL・NO ACTION がそれぞれ出ます。

SQLite は PRAGMA foreign_keys = ON; を接続ごとに実行しないと、外部キーの検査も ON DELETE の動作も行いません。この設定なしで同じ CASCADE のテーブルから顧客を消すと、受注はそのまま残りました。

MySQL（InnoDB）の違い

MySQL は手元で動かしていないため、MySQL 8.4 リファレンスマニュアル（FOREIGN KEY Constraints）の記述だけを紹介します。

  • NO ACTION は InnoDB では RESTRICT と同じで、即座に拒否される。この記事の「消して入れ直す」のような区別はない
  • SET DEFAULT は構文としては受け付けるが、InnoDB と NDB は ON DELETE SET DEFAULT を含むテーブル定義を拒否する
  • ON DELETE を省略したときの既定は NO ACTION
  • CASCADE などで連鎖した変更では、トリガーは動かない

どれを選ぶか

  • CASCADE：子が親の一部で、親なしでは意味がないもの（受注と受注明細、投稿と添付ファイル）
  • SET NULL：親がいなくなっても子は残したいもの（退会したユーザーのレビューを残す）。列は NULL 可にする
  • RESTRICT / NO ACTION：子があるなら親を消させたくないもの（請求書のある受注）。遅延制約や同じ文の中での入れ替えをしないなら、動きは同じ。エラーの SQLSTATE をアプリで見ているなら、どちらを使うか揃えておく
  • SET DEFAULT：既定値の行（「未分類」カテゴリなど）を親テーブルに必ず置ける場合だけ。MySQL（InnoDB）では使えない

迷ったら既定の NO ACTION のままにしておき、消してよいとはっきりしている関係にだけ CASCADE を付けるのが安全です。CASCADE で消える範囲は DELETE の件数に出ないので、付けた関係は上のクエリで一覧して把握しておいてください。

筆者は、DBML でスキーマを書いて ER 図・DDL・テーブル定義書を生成する DB 設計ツール「Joinery」を開発しています。外部キーの設計や ER 図の書き方は DB 設計ガイド にもまとめています。

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?