外部キー制約と参照アクション（ON DELETE / ON UPDATE）

本記事で使うデータ — category と item

外部キー制約（foreign key constraint、ある列の値が別のテーブルの主キーに必ず存在することを保証する制約）は、テーブル間の参照整合性（参照先が必ず存在する状態）を守るための仕組みです。

「商品が、存在しないカテゴリを指してしまう」「カテゴリを消したら、そのカテゴリの商品が宙に浮く」といった矛盾を、データベース側で防ぎます。

演習に入る前に、本記事で使う 2 つのテーブル — categoryとitem — の列定義とデータのサンプルを確認しておきます。

① PRAGMA table_info(category);とPRAGMA table_info(item);で両テーブルの列定義を確認してください。

② SELECT * FROM category;とSELECT * FROM item;で全行のデータをプレビューしてください。

③ PRAGMA foreign_keys;を実行して、外部キーチェックが現在どの状態か（数値で返ります）を確認してください。

外部キーチェックは PRAGMA foreign_keys=ON で有効になる

外部キー制約はREFERENCES 親テーブル(親列)を子テーブルの列定義に書いて宣言します。

item.cat_idにはREFERENCES category(cat_id)が付いているので、itemの各行のcat_idはcategoryに存在するcat_idでなければならない、というルールが定義されています。

ただし本講座のコンソールでは、外部キーチェックの初期状態は無効（PRAGMA foreign_keysが0）です。

この状態ではREFERENCESを書いていてもチェックが行われず、存在しないカテゴリを指す行も入ってしまいます。

外部キー制約を実際に効かせるには、接続ごとにPRAGMA foreign_keys=ON;を実行してチェックを有効化する必要があります。

-- 外部キーチェックを有効にする
PRAGMA foreign_keys=ON;

-- 状態を確認 (1 が返れば有効)
PRAGMA foreign_keys;

-- category に存在しない cat_id=99 を指す行を入れようとする
-- foreign_keys=ON のときは FOREIGN KEY constraint failed で拒否される
INSERT INTO item VALUES (5,'Mouse',99);

「商品が存在しないカテゴリを参照してしまう登録ミスを、データベース側で止めたい」という要件を想定します。外部キーチェックを有効にした状態で、わざと矛盾した行を入れてエラーになることを体感します。（正しく実行できれば解説が表示されます）

① 1 行目でPRAGMA foreign_keys=ON;を実行して、外部キーチェックを有効にしてください。

② 続けて、itemテーブルにitem_idが 5、item_nameが Mouse、cat_idがcategoryに存在しない値 99 の行をINSERTしてください。外部キー制約に違反するため、このINSERTはエラーになるのが正解です。

ON DELETE — 親行を削除したとき子行をどうするか

親（category）の行を削除したとき、その親を指していた子（item）の行をどう扱うかは、REFERENCES句に参照アクションを書いて決めます。

参照アクションには代表的なものが 3 つあります。

ON DELETE CASCADEは親を消すと子も一緒に消える（連鎖削除）、ON DELETE SET NULLは子の参照列を NULL にして残す、ON DELETE RESTRICTは子がいる親の削除そのものを拒否するという挙動です。

アクションを書かない場合の既定も RESTRICT 相当（参照中の親は削除できない）になります。

どの挙動が適切かは業務要件で決めます。

-- ON DELETE SET NULL の宣言例
CREATE TABLE cat_demo(cat_id INTEGER PRIMARY KEY, cat_name TEXT NOT NULL);
CREATE TABLE item_demo(
  item_id INTEGER PRIMARY KEY,
  item_name TEXT NOT NULL,
  cat_id INTEGER REFERENCES cat_demo(cat_id) ON DELETE SET NULL
);
INSERT INTO cat_demo VALUES (1,'Stationery'),(2,'Electronics');
INSERT INTO item_demo VALUES (1,'Pen',1),(2,'Phone',2),(3,'Cable',2);

-- 親 cat_id=2 を削除すると、Phone / Cable の cat_id が NULL になる
DELETE FROM cat_demo WHERE cat_id=2;
SELECT item_id, item_name, cat_id FROM item_demo ORDER BY item_id;

「カテゴリを削除したら、そのカテゴリに属する商品もまとめて削除したい」という要件を想定します。ON DELETE CASCADEを付けた親子を自己完結で作り、親の削除が子に連鎖することを観察します。

① 外部キーチェックを有効化し、cat_x（cat_id, cat_name）と、cat_idにcat_xへのON DELETE CASCADE付き外部キーを持つitem_x（item_id, item_name, cat_id）を、一度DROP TABLE IF EXISTSしてから作り直してください。

② cat_xに 2 件（Stationery / Electronics）、item_xに 3 件（cat_id 1 が 1 件、cat_id 2 が 2 件）を投入してください。

③ cat_xから cat_id が 2 のカテゴリを削除し、item_xを全件SELECTして、cat_id=2 を指していた商品が連鎖削除されたことを確認してください。

SET NULL と RESTRICT — 残す・拒否するを選ぶ

ON DELETE SET NULLは、親が消えても子の行は残し、参照していた列だけをNULL に置き換えます。

「カテゴリ未設定の商品として残しておきたい」ように、子レコード自体は消したくないときに使います。

子の参照列がNOT NULLだと NULL を入れられないので、SET NULL を使う列は NULL 許容にしておく必要があります。

ON DELETE RESTRICTは、子がまだ参照している親の削除そのものを拒否します。

安全側に倒したいとき（誤って参照中のマスタを消さない）に使います。

子をすべて消す・付け替えるなどして参照が無くなれば、親を削除できます。

次の演習では SET NULL と RESTRICT を 1 つのコンソールで作り分けて挙動の違いを確認します。

-- RESTRICT の宣言例: 子がいる親は削除できない
CREATE TABLE cat_r(cat_id INTEGER PRIMARY KEY, cat_name TEXT NOT NULL);
CREATE TABLE item_r(
  item_id INTEGER PRIMARY KEY,
  item_name TEXT NOT NULL,
  cat_id INTEGER REFERENCES cat_r(cat_id) ON DELETE RESTRICT
);
INSERT INTO cat_r VALUES (1,'Stationery'),(2,'Electronics');
INSERT INTO item_r VALUES (1,'Pen',1);

-- cat_id=1 は item_r に参照されているので削除は拒否される
DELETE FROM cat_r WHERE cat_id=1;

-- cat_id=2 は誰も参照していないので削除できる
DELETE FROM cat_r WHERE cat_id=2;
SELECT * FROM cat_r ORDER BY cat_id;

「カテゴリを 1 つ廃止しても、その配下の商品は『未分類』として残し、他のカテゴリの商品はまったく影響を受けないようにしたい」という要件を想定します。ON DELETE SET NULLを付けた 3 つのカテゴリ・5 つの商品で、廃止対象の子だけが NULL になり、別カテゴリの子は無傷であることを観察します。

① 外部キーチェックを有効化し、cat_s（cat_id, cat_name）と、cat_idにcat_sへのON DELETE SET NULL付き外部キーを持つitem_s（item_id, item_name, cat_id）を、DROP TABLE IF EXISTSしてから作り直してください。item_s.cat_idは NULL を許容する必要があります。

② cat_sに 3 件（Office=1 / Gadget=2 / Drink=3）、item_sに 5 件（Marker と Tape は Office、Tablet と Charger は Gadget、Coffee は Drink）を投入してください。

③ cat_sから Gadget（cat_id=2）だけを削除し、item_sを全件SELECTして、Tablet と Charger の cat_id だけが NULL になり、Office / Drink 配下の商品は元の cat_id のまま 5 行とも残っていることを確認してください。

「商品がまだ複数残っているカテゴリを、誤って削除してしまうのを防ぎたい」という要件を想定します。ON DELETE RESTRICTを付けた親子を自己完結で作り、子を 3 件抱えた親を削除しようとしてもエラーで止まることを体感します。

① 外部キーチェックを有効化し、cat_t（cat_id, cat_name）と、cat_idにcat_tへのON DELETE RESTRICT付き外部キーを持つitem_t（item_id, item_name, cat_id）を、DROP TABLE IF EXISTSしてから作り直してください。

② cat_tに Stationery（cat_id 1）、item_tにその配下の商品を 3 件（例: Pen / Pencil / Notebook）投入してください。

③ cat_tから cat_id が 1 のカテゴリをDELETEしてください。子が 3 件も参照しているため、このDELETEはエラーになるのが正解です。