search
LoginSignup
276

More than 1 year has passed since last update.

posted at

updated at

Organization

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

最後までお読みくださりありがとうございました。Twitterでは、Qiitaに書かない技術ネタなどもツイートしているので、よかったらフォローお願いします:relieved:Twitter@suin

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
What you can do with signing up
276