外部キー制約について

背景

なんとなく知っているけどあんまり意識して使ったことのないもの
その一つが外部キー制約だったりする。

外部キー制約とは

詳しくは調べればいくらでも情報はあるが、かみ砕くと
「他のテーブルのデータに参照（依存）するようにカラムにつける制約」
ということ。

参照されるのが親テーブル
参照するのが子テーブルと呼ぶ。

以下、例として、従業員を管理するデータベースにおいて、
部署テーブルを従業員テーブルが参照するって状況を説明する。

実例

MySQL 5.7で

部署テーブル

CREATE TABLE departments (
  department_id int primary key,
  department_name varchar(20)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

従業員テーブル

CREATE TABLE employees (
  employee_id int primary key,
  department_id int,
  name varchar(20),
  age int
) ENGINE=INNODB DEFAULT CHARSET=utf8;

といった設計の場合、
従業員テーブルのdepartment_idで、
その従業員がどの部署に在籍しているかを紐づけることがあると思う。

その場合外部キー制約をテーブル作成時に以下のように設定することができる

CREATE TABLE employees (
  employee_id int primary key,
  department_id int,
  name varchar(20),
  age int,
  CONSTRAINT fk_department_id
    FOREIGN KEY (department_id) 
    REFERENCES departments (department_id)
    ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=INNODB DEFAULT CHARSET=utf8;

この部分

  CONSTRAINT fk_department_id
    FOREIGN KEY (department_id) 
    REFERENCES departments (department_id)
    ON DELETE RESTRICT ON UPDATE RESTRICT

ちょっと解説

CONSTRAINT fk_department_id・・・制約の名前
FOREIGN KEY (department_id)・・・どのカラムにキーを制約つけるのか（参照元）
REFERENCES departments (department_id)・・・どのテーブルのどのカラムを参照するか（参照先）
ON DELETE RESTRICT・・・参照先をdeleteするときエラーにする
ON UPDATE RESTRICT・・・参照先をupdateするときエラーにする

そうするとどうなる？

例えば以下のようなデータが存在した場合

mysql> select * from departments;
+---------------+-----------------+
| department_id | department_name |
+---------------+-----------------+
|             1 | 開発部           |
|             2 | 営業部           |
|             3 | 管理部           |
+---------------+-----------------+

mysql> select * from employees;
+-------------+---------------+-----------------+------+
| employee_id | department_id | name            | age  |
+-------------+---------------+-----------------+------+
|           1 |             1 | 宇佐美 貴史       |   26 |
+-------------+---------------+-----------------+------+

宇佐美君の所属している開発部のdepartment_idを変更しようとすると（あまりこんなことないでしょうが）

mysql> update departments set department_id = 99 where department_id = 1;
ERROR 1451 (23000): Cannot delete or update a parent row:
a foreign key constraint fails
(`employees`, CONSTRAINT `fk_department_id` 
FOREIGN KEY (`department_id`) 
REFERENCES `departments` (`department_id`))

怒られる。

では、宇佐美君の所属している開発部を消滅させようとすると

mysql> delete from departments where department_id = 1;

ERROR 1451 (23000): Cannot delete or update a parent row:
 a foreign key constraint fails 
(`employees`, CONSTRAINT `fk_department_id` 
FOREIGN KEY (`department_id`) 
REFERENCES `departments` (`department_id`))

やはり怒られる。

これが参照整合性を保とうとしてる外部キー制約の働きである

これはこれで使えない

参照整合性が保たれているので、宇佐美君の所属している部署がなくなることはないのですが、
実際に一時無所属になったり、部門の分裂などでidが変わることもあるかとおもいます。（あるのか？）

で、そういったことも考えられるシステムの場合、外部キー制約をシステムにあった形に変更してやればいいです。

外部キー制約を張りなおそう

一気に変更できないようなので、まず削除する

ALTER TABLE employees DROP FOREIGN KEY fk_department_id;

で、追加すればよい

ALTER TABLE employees ADD CONSTRAINT fk_department_id 
FOREIGN KEY (department_id) REFERENCES departments (department_id) 
ON DELETE SET NULL ON UPDATE CASCADE;

張りなおした、制約は

ON DELETE SET NULL・・・参照先deleteすると参照元がnullにする
ON UPDATE CASCADE・・・参照先をupdateすると参照元も同じupdateがされる

そうするとどうなる？

再度、以下のようなデータに対して

mysql> select * from departments;
+---------------+-----------------+
| department_id | department_name |
+---------------+-----------------+
|             1 | 開発部           |
|             2 | 営業部           |
|             3 | 管理部           |
+---------------+-----------------+

mysql> select * from employees;
+-------------+---------------+-----------------+------+
| employee_id | department_id | name            | age  |
+-------------+---------------+-----------------+------+
|           1 |             1 | 宇佐美 貴史       |   26 |
+-------------+---------------+-----------------+------+

宇佐美君の所属している開発部のdepartment_idを変更しようとすると（あまりこんなことないでしょうが）

mysql> update departments set department_id = 99 where department_id = 1;

mysql> select * from departments;
+---------------+-----------------+
| department_id | department_name |
+---------------+-----------------+
|             2 | 営業部           |
|             3 | 管理部           |
|            99 | 開発部           |
+---------------+-----------------+



mysql> select * from employees;
+-------------+---------------+-----------------+------+
| employee_id | department_id | name            | age  |
+-------------+---------------+-----------------+------+
|           1 |            99 | 宇佐美　貴志      |   26 |
+-------------+---------------+-----------------+------+

宇佐美君の情報も変わる

では、宇佐美君の所属している開発部を消滅させようとすると

mysql> delete from departments where department_id = 1;


mysql> select * from departments;
+---------------+-----------------+
| department_id | department_name |
+---------------+-----------------+
|             2 | 営業部           |
|             3 | 管理部           |
+---------------+-----------------+


mysql> select * from employees;
+-------------+---------------+-----------------+------+
| employee_id | department_id | name            | age  |
+-------------+---------------+-----------------+------+
|           1 |          NULL | 宇佐美 貴史       |   26 |
+-------------+---------------+-----------------+------+

宇佐美君は無所属(NULL）となる。

まとめ

このように参照整合性が保たれるのでwebサーバ（ORMとか）側だけでなく、
データベース側にも外部キーを設定して、制御した方が良い

といいたいのだが、実は以下のように結構デメリットもあり実際にそこまで制約をかけての運用は難しい。

• シャーディングされたテーブルには外部キー制約はつけれない
• ON UPDATE CASCADEなどをきちんと認識しないと意図せぬ更新もありえる
• ON DELETE CASCADEなどをきちんと認識しないと意図せぬ削除もありえる
• INSERTするときに親テーブルに参照先がないとINSERTできない
• 親テーブルを同時にロックするのでデッドロックの発生が生まれやすくなる
• そもそもMySQLのMyIsamなど外部キー制約を張れないのものもある（張れても効かなかったはず）
  

などなど

結論

あった方が良いが、
何でもかんでも外部キー張るってもんでもない。
テーブルの用途によって検討しよう。