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

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	エラーになり更新できない	エラーになり削除できない

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