Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationEventAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
246
Help us understand the problem. What are the problem?

More than 1 year has passed since last update.

@suin(Craftsman Software)
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

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
246
Help us understand the problem. What are the problem?