MySQLの外部キー制約RESTRICT,CASCADE,SET NULL,NO ACTIONの違いを把握するため、実際の挙動を確認してみます。
まとめ
結論を先に書いておきます。
| UPDATE | DELETE | |
|---|---|---|
| RESTRICT | エラーになる | エラーになる |
| CASCADE | 参照先の変更に追従する | 参照先が無くなると同時に削除される |
| SET NULL | NULLに置き換わる | NULLに置き換わる |
| NO ACTION | RESTRICTと同じ | RESTRICTと同じ |
以下はやってみた検証の内容です。
検証用テーブル
検証用テーブルを作ります。
-- Create syntax for TABLE 'categories'
CREATE TABLE `categories` (
`id` int(11) unsigned NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- Create syntax for TABLE 'items'
CREATE TABLE `items` (
`id` int(11) unsigned NOT NULL,
`category_id` int(11) unsigned NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ON UPDATE RESTRICT
ALTER TABLE `items` ADD FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON UPDATE RESTRICT;
UPDATEすると、エラーになります。
-- データを入れる
DELETE FROM items;
DELETE FROM categories;
INSERT INTO categories VALUES (1), (2), (3);
INSERT INTO items VALUES (1, 3);
-- カテゴリIDをUPDATEしてみる
UPDATE categories SET id = 4 WHERE id = 2; -- 成功
UPDATE categories SET id = 6 WHERE id = 3; -- 失敗
ON UPDATE CASCADE
ALTER TABLE `items` ADD FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON UPDATE CASCADE;
UPDATEしても、エラーにならず、小テーブルは親テーブルの変更に追従します。
-- データを入れる
DELETE FROM items;
DELETE FROM categories;
INSERT INTO categories VALUES (1), (2), (3);
INSERT INTO items VALUES (1, 3);
-- カテゴリIDをUPDATEしてみる
UPDATE categories SET id = 4 WHERE id = 2; -- 成功
UPDATE categories SET id = 6 WHERE id = 3; -- 成功
itemsテーブル内容
id category_id
1 6
ON UPDATE SET NULL
-- ON UPDATE SET NULLを使うためには、nullを許すようにカラムを定義します
ALTER TABLE `items` CHANGE `category_id` `category_id` MEDIUMINT(8) UNSIGNED NULL;
-- 外部キー制約をセット
ALTER TABLE `items` ADD FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON UPDATE SET NULL;
UPDATEしても、エラーにはならないが、items.category_idにはNULLがセットされる。
-- データを入れる
DELETE FROM items;
DELETE FROM categories;
INSERT INTO categories VALUES (1), (2), (3);
INSERT INTO items VALUES (1, 3);
-- カテゴリIDをUPDATEしてみる
UPDATE categories SET id = 4 WHERE id = 2; -- 成功
UPDATE categories SET id = 6 WHERE id = 3; -- 成功
itemsテーブル内容
id category_id
1 NULL
ON UPDATE NO ACTION
ALTER TABLE `items` ADD FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON UPDATE NO ACTION;
ON UPDATE RESTRICTと同じ結果です。
-- データを入れる
DELETE FROM items;
DELETE FROM categories;
INSERT INTO categories VALUES (1), (2), (3);
INSERT INTO items VALUES (1, 3);
-- カテゴリIDをUPDATEしてみる
UPDATE categories SET id = 4 WHERE id = 2; -- 成功
UPDATE categories SET id = 6 WHERE id = 3; -- 失敗
ON DELETE RESTRICT
ALTER TABLE `items` ADD FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE RESTRICT;
-- データを入れる
DELETE FROM items;
DELETE FROM categories;
INSERT INTO categories VALUES (1), (2), (3);
INSERT INTO items VALUES (1, 3);
-- カテゴリをDELETEしてみる
DELETE FROM categories WHERE id = 2; -- 成功
DELETE FROM categories WHERE id = 3; -- 失敗
ON DELETE CASCADE
ALTER TABLE `items` ADD FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE CASCADE;
カテゴリの削除は成功し、そのカテゴリを参照しているアイテムも同時に消えます。
-- データを入れる
DELETE FROM items;
DELETE FROM categories;
INSERT INTO categories VALUES (1), (2), (3);
INSERT INTO items VALUES (1, 3);
-- カテゴリをDELETEしてみる
DELETE FROM categories WHERE id = 2; -- 成功
DELETE FROM categories WHERE id = 3; -- 成功
ON DELETE SET NULL
-- ON DELETE SET NULLを使うためには、nullを許すようにカラムを定義します
ALTER TABLE `items` CHANGE `category_id` `category_id` MEDIUMINT(8) UNSIGNED NULL;
-- 外部キー制約をセット
ALTER TABLE `items` ADD FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE SET NULL;
カテゴリのDELETEは成功し、それを参照するアイテムのcategory_idはNULLになります。
-- データを入れる
DELETE FROM items;
DELETE FROM categories;
INSERT INTO categories VALUES (1), (2), (3);
INSERT INTO items VALUES (1, 3);
-- カテゴリをDELETEしてみる
DELETE FROM categories WHERE id = 2; -- 成功
DELETE FROM categories WHERE id = 3; -- 成功
SELECT * FROM items;
itemsテーブル内容
id category_id
1 NULL
ON DELETE NO ACTION
ALTER TABLE `items` ADD FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE NO ACTION;
ON DELETE RESTRICTと同じ結果になります。
-- データを入れる
DELETE FROM items;
DELETE FROM categories;
INSERT INTO categories VALUES (1), (2), (3);
INSERT INTO items VALUES (1, 3);
-- カテゴリをDELETEしてみる
DELETE FROM categories WHERE id = 2; -- 成功
DELETE FROM categories WHERE id = 3; -- 失敗
最後までお読みくださりありがとうございました。Twitterでは、Qiitaに書かない技術ネタなどもツイートしているので、よかったらフォローお願いします
→Twitter@suin