ON DELETE/UPDATE ポリシー
親を消そうとしたら、エラーで止まった
categories を親、products を子として外部キーを張ったあと、要らなくなったカテゴリを消そうとします。
SQL クエリ
DELETE FROM categories WHERE id = 3;
-- ERROR: foreign key constraint violation止まったのは不具合ではありません。そのカテゴリを指している商品がまだ残っているので、消せば孤児が生まれます。外部キーは孤児を作らせないための仕組みなので、作ってしまう操作を拒否したわけです。
つまり外部キーを張った時点で、親を消したり親のキーを書き換えたりする操作は、子をどう扱うか決めないと通らなくなります。何も書かなければ既定は NO ACTION、「参照されている間は触らせない」です。
扱いは、外部キーの宣言の中に書く
指定する場所は REFERENCES 親(列) の直後です。
SQL クエリ
CREATE TABLE products (
id INT PRIMARY KEY,
category_id INT REFERENCES categories(id)
ON DELETE RESTRICT
ON UPDATE CASCADE
);書ける動きは 5 つあります。
NO ACTION— 参照されていれば拒否する。書かなかったときの既定RESTRICT— 同じく拒否する。判定のタイミングだけが違うCASCADE— 親に起きたことを、子にもそのまま起こすSET NULL— 子の外部キー列をNULLに書き換えるSET DEFAULT— 子の外部キー列を、その列の既定値に書き換える
ここで押さえるのは、書かなければ拒否になること、そして書く場所が子テーブルの宣言の中だということです。親の定義を見ても、どう扱われるかは分かりません。
削除と更新は、別々に決める
ON DELETE と ON UPDATE は独立しています。片方だけ書いても、両方書いても構いません。
親の主キーが後から変わることは、id のような連番なら滅多に起きません。効いてくるのは、商品コードやメールアドレスのように業務上の意味を持つ値を主キーにしている場合です。コードの体系を変えたとき、ON UPDATE CASCADE があれば子の参照値も一緒に書き換わります。無ければ親の更新が拒否され、子を全部手で直すことになります。
RESTRICT と NO ACTION は、見るタイミングが違う
どちらも結果は「拒否」ですが、いつ確かめるかが違います。RESTRICT は親への操作が来た瞬間に見ます。NO ACTION は SQL 文の終わりに見ます。
差が出るのは、1 つのトランザクションの中で子を消してから親を消すような書き方をしたときです。NO ACTION なら文末には子が居ないので通り、RESTRICT は先に弾くことがあります。ふだんは同じものとして扱って構いません。
テーブル構造
CREATE TABLE customers (
id INT PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE orders (
id INT PRIMARY KEY,
customer_id INT,
total INT NOT NULL,
CONSTRAINT fk_orders_customer
FOREIGN KEY (customer_id) REFERENCES customers(id)
ON DELETE CASCADE
);
INSERT INTO customers (id, name) VALUES
(1, '田中'),
(2, '佐藤'),
(3, '鈴木');
INSERT INTO orders (id, customer_id, total) VALUES
(101, 1, 3000),
(102, 1, 1500),
(103, 2, 5000),
(104, 3, 2000);
期待される出力
| id | customer_id | total |
|---|---|---|
| 103 | 2 | 5000 |
| 104 | 3 | 2000 |