Edited at

MySQLの外部キー制約RESTRICT,CASCADE,SET NULL,NO ACTIONの違いは?

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; -- 失敗