Help us understand the problem. What is going on with this article?

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

suin
Qiita 4位/TypeScript入門書執筆中/TypeScripterのための座談会「YYTypeScript」主催/『実践ドメイン駆動設計』書籍邦訳レビュア/分報Slack考案/YYPHP主催/CodeIQマガジン執筆/株式会社クラフトマンソフトウェア創設/Web自動テスト「ShouldBee」の開発/TypeScript/DDD/OOP
https://yyts.connpass.com/
shouldbee
開発者向けテスト支援サービスShouldBeeを開発・運営するスタートアップ(onlab第8期)
http://shouldbee.at
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした