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

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