More than 5 years have passed since last update.

MySQL　外部キー制約の種類と特徴

Last updated at 2019-01-08Posted at 2018-12-31

DB設計を行うとテーブルの依存関係から外部キー制約の検討もあると思います。
そこで、制約の種類と特徴を確認しておきましょう。

外部キー制約の種類

MySQLの外部キー制約は以下の種類があります。
・RESTRICT
・CASCADE
・SET NULL
・NO ACTION
どんな違いがあるのか確認してみましょう。

確認方法として

親テーブルとなる基礎データの作成を行い、各外部制約キーとなる子テーブルを作成し、テーブルの更新・削除にて確認を行う。

-- 親テーブルの作成
create table area(
id int, 
name varchar(10), 
index(name)
) engine=InnoDB;

※ 親テーブルには県コード一覧を登録

mysql> select * from area limit 10;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 北海道 |
|    2 | 青森県 |
|    3 | 岩手県 |
|    4 | 宮城県 |
|    5 | 秋田県 |
|    6 | 山形県 |
|    7 | 福島県 |
|    8 | 茨城県 |
|    9 | 栃木県 |
|   10 | 群馬県 |
+------+--------+
10 rows in set (0.06 sec)

RESTRICT

ON DELETE句やON UPDATE句にRESTRICTを指定した場合は、親テーブルに対して更新や削除を行うとエラーが発生します。

-- 子テーブルの作成（RESTRICT）
create table case_restrict(
id int, 
name varchar(10), 
in_date date, 
index(name),
foreign key(name) references area(name) on update restrict
) engine=InnoDB;

登録内容を確認

mysql> select * from area limit 10;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 北海道 |
|    2 | 青森県 |
|    3 | 岩手県 |
|    4 | 宮城県 |
|    5 | 秋田県 |
|    6 | 山形県 |
|    7 | 福島県 |
|    8 | 茨城県 |
|    9 | 栃木県 |
|   10 | 群馬県 |
+------+--------+
10 rows in set (0.00 sec)

mysql> select * from case_restrict;
+------+--------+------------+
| id   | name   | in_date    |
+------+--------+------------+
|    1 | 北海道 | 2018-12-31 |
|    2 | 青森県 | 2018-12-31 |
|    3 | 岩手県 | 2018-12-31 |
|    4 | 宮城県 | 2018-12-31 |
|    5 | 秋田県 | 2018-12-31 |
|    6 | 山形県 | 2018-12-31 |
|    7 | 北海道 | 2018-12-31 |
|    8 | 北海道 | 2018-12-31 |
|    9 | 青森県 | 2018-12-31 |
+------+--------+------------+
9 rows in set (0.00 sec)

areaを変更してみる

mysql> update area set name="北海道変更" where id=1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`db1`.`case_restrict`, CONSTRAINT `case_restrict_ibfk_1` FOREIGN KEY (`name`) REFERENCES `area` (`name`))

変更を行うことができませんでした。

areaから削除してみる

mysql> delete from area where id=1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`db1`.`case_restrict`, CONSTRAINT `case_restrict_ibfk_1` FOREIGN KEY (`name`) REFERENCES `area` (`name`))

更新時と同様にエラーとなりました。

CASCADE

ON DELETE句やON UPDATE句にCASCADEを指定した場合は、親テーブルに対して更新を行うと子テーブルで同じ値を持つカラムの値も合わせて更新されます。

-- 子テーブルの作成（CASCADE）
create table case_cascade(
id int, 
name varchar(10), 
in_date date, 
index(name),
foreign key(name) references area(name) on update cascade on delete cascade
) engine=InnoDB;

登録内容を確認

mysql> select * from area limit 10;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 北海道 |
|    2 | 青森県 |
|    3 | 岩手県 |
|    4 | 宮城県 |
|    5 | 秋田県 |
|    6 | 山形県 |
|    7 | 福島県 |
|    8 | 茨城県 |
|    9 | 栃木県 |
|   10 | 群馬県 |
+------+--------+
10 rows in set (0.00 sec)

mysql> select * from case_cascade;
+------+--------+------------+
| id   | name   | in_date    |
+------+--------+------------+
|    1 | 北海道 | 2018-12-31 |
|    2 | 青森県 | 2018-12-31 |
|    3 | 岩手県 | 2018-12-31 |
|    4 | 宮城県 | 2018-12-31 |
|    5 | 秋田県 | 2018-12-31 |
|    6 | 山形県 | 2018-12-31 |
|    7 | 北海道 | 2018-12-31 |
|    8 | 北海道 | 2018-12-31 |
|    9 | 青森県 | 2018-12-31 |
+------+--------+------------+
9 rows in set (0.00 sec)

areaを変更してみる

mysql> update area set name='北海道変更' where id=1;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from case_cascade;
+------+------------+------------+
| id   | name       | in_date    |
+------+------------+------------+
|    1 | 北海道変更 | 2018-12-31 |
|    2 | 青森県     | 2018-12-31 |
|    3 | 岩手県     | 2018-12-31 |
|    4 | 宮城県     | 2018-12-31 |
|    5 | 秋田県     | 2018-12-31 |
|    6 | 山形県     | 2018-12-31 |
|    7 | 北海道変更 | 2018-12-31 |
|    8 | 北海道変更 | 2018-12-31 |
|    9 | 青森県     | 2018-12-31 |
+------+------------+------------+
9 rows in set (0.00 sec)

areaの変更からcase_cascadeの内容も同時に変更されました。

areaから削除してみる

mysql> delete from area where id=1;
Query OK, 1 row affected (0.12 sec)

mysql> select * from case_cascade;
+------+--------+------------+
| id   | name   | in_date    |
+------+--------+------------+
|    2 | 青森県 | 2018-12-31 |
|    3 | 岩手県 | 2018-12-31 |
|    4 | 宮城県 | 2018-12-31 |
|    5 | 秋田県 | 2018-12-31 |
|    6 | 山形県 | 2018-12-31 |
|    9 | 青森県 | 2018-12-31 |
+------+--------+------------+
6 rows in set (0.00 sec)

areaを削除同時に対象となるcase_cascadeも同時に削除されました

SET NULL

ON DELETE句やON UPDATE句にSET NULLを指定した場合は、親テーブルに対して更新を行うと子テーブルで同じ値を持つカラムの値がNULLに更新されます。

-- 子テーブルの作成（SET NULL）
create table case_set_null(
id int, 
name varchar(10), 
in_date date, 
index(name),
foreign key(name) references area(name) on update set null on delete set null
) engine=InnoDB;

登録内容を確認

mysql> select * from area limit 10;
+------+--------+
| id   | name   |
+------+--------+
|    2 | 青森県 |
|    3 | 岩手県 |
|    4 | 宮城県 |
|    5 | 秋田県 |
|    6 | 山形県 |
|    7 | 福島県 |
|    8 | 茨城県 |
|    9 | 栃木県 |
|   10 | 群馬県 |
|   11 | 埼玉県 |
+------+--------+
10 rows in set (0.00 sec)

mysql> select * from case_set_null;
+------+--------+------------+
| id   | name   | in_date    |
+------+--------+------------+
|    1 | 北海道 | 2018-12-31 |
|    2 | 青森県 | 2018-12-31 |
|    3 | 岩手県 | 2018-12-31 |
|    4 | 宮城県 | 2018-12-31 |
|    5 | 秋田県 | 2018-12-31 |
|    6 | 山形県 | 2018-12-31 |
|    7 | 北海道 | 2018-12-31 |
|    8 | 北海道 | 2018-12-31 |
|    9 | 青森県 | 2018-12-31 |
+------+--------+------------+
9 rows in set (0.00 sec)

areaを変更してみる

mysql> update area set name="北海道変更" where id=1;
Query OK, 1 row affected (0.09 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from case_set_null;
+------+--------+------------+
| id   | name   | in_date    |
+------+--------+------------+
|    1 | NULL   | 2018-12-31 |
|    2 | 青森県 | 2018-12-31 |
|    3 | 岩手県 | 2018-12-31 |
|    4 | 宮城県 | 2018-12-31 |
|    5 | 秋田県 | 2018-12-31 |
|    6 | 山形県 | 2018-12-31 |
|    7 | NULL   | 2018-12-31 |
|    8 | NULL   | 2018-12-31 |
|    9 | 青森県 | 2018-12-31 |
+------+--------+------------+
9 rows in set (0.00 sec)

変更を行った結果、case_set_nullで登録していた「北海道」はNULLに変更されました

areaから削除してみる

mysql> delete from area where id=1;
Query OK, 1 row affected (0.12 sec)

mysql>  select * from case_set_null;
+------+--------+------------+
| id   | name   | in_date    |
+------+--------+------------+
|    1 | NULL   | 2018-12-31 |
|    2 | 青森県 | 2018-12-31 |
|    3 | 岩手県 | 2018-12-31 |
|    4 | 宮城県 | 2018-12-31 |
|    5 | 秋田県 | 2018-12-31 |
|    6 | 山形県 | 2018-12-31 |
|    7 | NULL   | 2018-12-31 |
|    8 | NULL   | 2018-12-31 |
|    9 | 青森県 | 2018-12-31 |
+------+--------+------------+
9 rows in set (0.00 sec)

変更を行った結果、case_set_nullで登録していた「北海道」はNULLに変更されました。
この結果はareaを変更した場合と同じですね。

NO ACTION

ON DELETE句やON UPDATE句にNO ACTIONを指定した場合は、親テーブルに対して更新や削除を行うとエラーが発生します。

-- 子テーブルの作成（NO ACTION）
create table case_no_action(
id int, 
name varchar(10), 
in_date date, 
index(name),
foreign key(name) references area(name) on delete no action
) engine=InnoDB;

登録内容を確認

mysql> select * from area limit 10;
+------+--------+
| id   | name   |
+------+--------+
|    2 | 青森県 |
|    3 | 岩手県 |
|    4 | 宮城県 |
|    5 | 秋田県 |
|    6 | 山形県 |
|    7 | 福島県 |
|    8 | 茨城県 |
|    9 | 栃木県 |
|   10 | 群馬県 |
|   11 | 埼玉県 |
+------+--------+
10 rows in set (0.00 sec)

mysql> select * from case_no_action;
+------+--------+------------+
| id   | name   | in_date    |
+------+--------+------------+
|    1 | 北海道 | 2018-12-31 |
|    2 | 青森県 | 2018-12-31 |
|    3 | 岩手県 | 2018-12-31 |
|    4 | 宮城県 | 2018-12-31 |
|    5 | 秋田県 | 2018-12-31 |
|    6 | 山形県 | 2018-12-31 |
|    7 | 北海道 | 2018-12-31 |
|    8 | 北海道 | 2018-12-31 |
|    9 | 青森県 | 2018-12-31 |
+------+--------+------------+
9 rows in set (0.00 sec)

areaを変更してみる

mysql> update area set name="北海道変更" where id=1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`db1`.`case_no_action`, CONSTRAINT `case_no_action_ibfk_1` FOREIGN KEY (`name`) REFERENCES `area` (`name`) ON DELETE NO ACTION)

変更ができませんでした。

areaから削除してみる

mysql> delete from area where id=1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`db1`.`case_no_action`, CONSTRAINT `case_no_action_ibfk_1` FOREIGN KEY (`name`) REFERENCES `area` (`name`) ON DELETE NO ACTION)

削除ができませんでした。

結果として

上記の確認結果からこのように結果がまとまりました。

外部キー制約	親テーブル更新	親テーブル削除
RESTRICT	エラーになり更新できない	エラーになり削除できない
CASCADE	親テーブルの変更にともない、子テーブルも変更に追従する	親テーブルの削除にともない、参照先が無くなるので子テーブルも同時に削除される
SET NULL	親テーブルの変更にともない、子テーブルの参照先がなくなるのでNULLに置き換わる	親テーブルの変更にともない、子テーブルの参照先がなくなるのでNULLに置き換わる
NO ACTION	エラーになり更新できない	エラーになり削除できない

テーブルの依存性や、親テーブルとなるデータの内容から勘案して、テーブル定義として検討してみるのもいいですね

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up

MySQL 外部キー制約の種類と特徴

外部キー制約の種類

確認方法として

RESTRICT

CASCADE

SET NULL

NO ACTION

結果として

MySQL　外部キー制約の種類と特徴