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

More than 1 year has passed since last update.

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; -- 失敗
Sign up for free and join this conversation.
Sign Up
If you already have a Qiita account log in.