MySQL 外部キー制約の基本

MySQLで外部キー周りでハマったので基礎からおさらいする.
本記事ではそもそも外部キーってなに? からスタート.
最終的には実際にDBをいじりながら, 次の外部キーオプションを確認してゆく.

RESTRICT
NO ACTION
CASCADE
SET NULL

概要は次の通り.

概要

参考
動作環境
外部キーとは
外部キーを使う条件
外部キー制約とは
RESTRICT
CASCADE
SET NULL
まとめ

参考

外部キー制約の使用 | dev.mysql.com

動作環境

windows10 HOME x64
MySQL 5.7.15
centos6.8

前提条件

次の条件下で作業を行う.

VirtualBox, Vagrantで作成したローカル開発環境
ホストマシンがWin, ゲストマシンがcentos
tstDB というDBを予め作成しておく

作業方針

次の2テーブルを作成して, 外部キーオプションの違いを確認する方針.

DB.users
- id
- name
- created_at
- modified
DB.kinds
- id
- user_id #外部キー
- created_at
- modified

外部キーとは

次のような動作を実現するもの.

一方のテーブルに加えた変更が自動的に他テーブルにも反映する
外部キーを持つテーブル間に親子関係を定義する

外部キーはテーブルに親子関係を持たせるもの、って認識でok.

ここではもう少し外部キーについて解説.
概要は次の通り.

外部キーを使ってできること
親テーブル, 子テーブル

外部キーを使ってできること

次のような例で考える.
概要は次の通り.

テーブルの親子関係を把握
テーブル設計時にやりたいことを把握

テーブルの親子関係を把握

次のような2つのテーブルを考える.

users (親テーブル)
- id
- name
- password
kinds (子テーブル)
- id
- user_id # <- ココは DB.users の id と常に同じにしたい
- name

usersが親テーブル.
kinds が子テーブル.

user_id が外部キー.

テーブル設計時にやりたいことを把握

箇条書きで.

DB.kinds内のuser_idカラムは, DB.users内のidじゃないとダメ
DB.users の id が変更されたら, kinds の user_id も自動で変更してほしい
DB.users テーブル自体が削除されたら, kinds の user_id には NULL を設定したい

外部キー制約 を利用するとこれらが実現できる.

親テーブル, 子テーブル

ここで少し用語について.

外部キーの

参照元(親テーブル)
参照先(子テーブル)

によってテーブルの呼称が変わる.

今回の例では

親テーブル
- users
子テーブル
- kinds

外部キーを使う条件

外部キーを使うためにはいくつか条件がある.

親テーブル, 子テーブルそれぞれが InnoDB であること
参照元, 参照先のキーの型が同じであること

MySQLのストレージエンジンは大きく次の2種類.

InnoDB
MyISAM

この点は作業に影響がないから深く突っ込まない.

外部キー制約とは

そもそも外部キー制約とは? について.
概要は次の通り.

そもそも外部キー制約ってなに
外部キー制約設定方法

そもそも外部キー制約ってなに

次の例のように, 複数のテーブルにまたがる値を関連付けたいことがある.

DB.kinds内の user_id カラムは, DB.users内のidじゃないとダメ

kindsテーブルについて条件を課している.
つまり, kindsテーブルの user_id カラムには制約があるってこと.

これが外部キー制約.

外部キー制約設定方法

次に, 外部キー制約設定方法について.

たとえば, ここでは外部キー制約によって次のようなことを実現する.

親テーブルが変更されたら子テーブルも変更される
親テーブルが削除されたら子テーブルの該当カラムに NULL を格納

基本的には次のようなコマンドで実現可能.

$ ALTER TABLE 子テーブル ADD CONSTRAINT 外部キーID FOREIGN KEY (外部キー) REFERENCES 親テーブル (id) ON UPDATE オプション ON DELETE オプション;

制約名(constraint)を指定しないとエラーになるので注意.

ちなみに, さっきの

DB.kinds内の user_id カラムは, DB.users内のidじゃないとダメ

を実現するコマンドはこれ.

$ ALTER TABLE kinds ADD CONSTRAINT users_fk_1 FOREIGN KEY (user_id) REFERENCES users (id) ON UPDATE CASCADE ON DELETE SET NULL;

実行結果

mysql> ALTER TABLE kinds ADD CONSTRAINT users_fk_1 FOREIGN KEY (user_id) REFERENCES users (id) ON UPDATE CASCADE ON DELETE SET NULL;
Query OK, 0 rows affected (0.38 sec)
Records: 0  Duplicates: 0  Warnings: 0

次のコマンドで外部キーの設定確認が可能.

$ show create table kinds \G

mysql> show create table kinds \G
*************************** 1. row ***************************
       Table: kinds
Create Table: CREATE TABLE `kinds` (
  `id` int(11) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  KEY `users_fk_1` (`user_id`), #←ちゃんと設定できてる
  CONSTRAINT `users_fk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

外部キー設定時のオプション

さっきの例でいうところの

ON UPDATE
- 親テーブル(内の, 外部キーに関係するカラム)が更新された時の, 子テーブル外部キーの挙動設定
ON DELETE
- 親テーブル(内の, 外部キーに関係するカラム)が削除された時の, 子テーブル外部キーの挙動設定

で指定するオプションについて.

オプションには次の4つの値が指定可能.

値	意味
RESTRICT	親テーブルに変更を加えた時にエラーを返す. 子テーブル外部キーの変更はok
NO ACTION	`RESTRICT` と一緒
CASCADE	親テーブルの変更がそのまま子テーブルへも反映される. 親テーブルのデータを削除 -> 子テーブルのデータ(レコード)が削除される親テーブルのデータ変更 -> 子テーブルのデータも変更される
SET NULL	親テーブルの更新・削除 -> 子テーブルへ `NULL` が設定される

この表だけ見てても実感がわかないので, 手を動かして確認してみる.

NO ACTION は RESTRICT と一緒らしいので省略.
今回実験するのは次の3つにする.

RESTRICT
CASCAFD
SET NULL

早速実験.

RESTRICT

これは ON DELETE, ON UPDATE を指定しない場合のデフォルト設定.

RESTRICT: 親テーブルに対する削除または更新操作を拒否します。RESTRICT (または NO ACTION) を指定することは、ON DELETE または ON UPDATE 句を省略することと同じです。

引用: 外部キー制約の使用 | dev.mysql.com

実際にテーブルを作成しながら試してみる.

概要は次の通り.

テーブル作成
外部キー設定
親テーブル更新
親テーブル削除

テーブル作成

一先ず次の2テーブルを作成する.

users
kinds

まずは users.
次のコマンドで作成.

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NULL,
  `created_at` datetime DEFAULT NULL,
  `modified_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

次に kinds.
この時点ではテーブルを作るだけ.
外部キー設定はこの後の手順で行う.

次のコマンドで kinds を作成.

CREATE TABLE `kinds` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `created_at` datetime DEFAULT NULL,
  `modified_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ちゃんとテーブルができているかどうかは次のコマンドで確認可能.

$ desc users
$ desc kids

たとえば, users はこんな感じ.

mysql> desc users;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| name        | varchar(255) | YES  |     | NULL    |                |
| created_at  | datetime     | YES  |     | NULL    |                |
| modified_at | datetime     | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

このままだとデータがない空のテーブルなので, いくつかデータを挿入する.

まずは users にダミーデータを挿入.

insert into tstDB.users (name, created_at, modified_at) values 
("takamoto", CURRENT_TIMESTAMP, CURRENT_TIMESTAMP), # たかもと
("takimoto", CURRENT_TIMESTAMP, CURRENT_TIMESTAMP), # たきもと
("takumoto", CURRENT_TIMESTAMP, CURRENT_TIMESTAMP), # たくもと
("takemoto", CURRENT_TIMESTAMP, CURRENT_TIMESTAMP), # たけもと
("takomoto", CURRENT_TIMESTAMP, CURRENT_TIMESTAMP); # たこもと

確認.

mysql> select * from users;
+----+----------+---------------------+---------------------+
| id | name     | created_at          | modified_at         |
+----+----------+---------------------+---------------------+
|  1 | takamoto | 2017-06-25 08:31:56 | 2017-06-25 08:31:56 |
|  2 | takimoto | 2017-06-25 08:31:56 | 2017-06-25 08:31:56 |
|  3 | takumoto | 2017-06-25 08:31:56 | 2017-06-25 08:31:56 |
|  4 | takemoto | 2017-06-25 08:31:56 | 2017-06-25 08:31:56 |
|  5 | takomoto | 2017-06-25 08:31:56 | 2017-06-25 08:31:56 |
+----+----------+---------------------+---------------------+
5 rows in set (0.01 sec)

次に, kinds にデータ挿入.

insert into tstDB.kinds (user_id, name, created_at, modified_at) values 
(1, "red", CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(2, "yellow", CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(3, "blue", CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(4, "white", CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(5, "black", CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);

確認

mysql> select * from kinds;
+----+---------+--------+---------------------+---------------------+
| id | user_id | name   | created_at          | modified_at         |
+----+---------+--------+---------------------+---------------------+
|  1 |       1 | red    | 2017-06-25 08:36:08 | 2017-06-25 08:36:08 |
|  2 |       2 | yellow | 2017-06-25 08:36:08 | 2017-06-25 08:36:08 |
|  3 |       3 | blue   | 2017-06-25 08:36:08 | 2017-06-25 08:36:08 |
|  4 |       4 | white  | 2017-06-25 08:36:08 | 2017-06-25 08:36:08 |
|  5 |       5 | black  | 2017-06-25 08:36:08 | 2017-06-25 08:36:08 |
+----+---------+--------+---------------------+---------------------+

外部キー設定

次に外部キーの設定.
今回は kindsテーブルの user_id が外部キー.

次のコマンドで外部キーを RESTRICT として設定する.

$ ALTER TABLE kinds ADD CONSTRAINT users_fk_1 FOREIGN KEY (user_id) REFERENCES users (id) ON UPDATE RESTRICT ON DELETE RESTRICT;

mysql> ALTER TABLE kinds ADD CONSTRAINT users_fk_1 FOREIGN KEY (user_id) REFERENCES users (id) ON UPDATE RESTRICT ON DELETE RESTRICT;
Query OK, 0 rows affected (0.74 sec)
Records: 0  Duplicates: 0  Warnings: 0

ちゃんと設定されているか確認.

mysql> mysql> show create table kinds \G
*************************** 1. row ***************************
       Table: kinds
Create Table: CREATE TABLE `kinds` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `created_at` datetime DEFAULT NULL,
  `modified_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `users_fk_1` (`user_id`),
  CONSTRAINT `users_fk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

親テーブル更新

親テーブルである users の id を更新してみる.

次コマンドで id=1 を id=10 に変更してみる.

$ UPDATE users SET id=id*10 WHERE id=1;

mysql> UPDATE users SET id=id*10 WHERE id=1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`tstDB`.`kinds`, CONSTRAINT `users_fk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`))

ちゃんと RESTRICT の設定が機能している.

ちなみに, users の他カラムは自由に変更可能.
たとえば, id=1 の takamoto を tmp に変更してみる.

$ update users set name="tmp" where id=1;

mysql> update users set name="tmp" where id=1;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from users;
+----+----------+---------------------+---------------------+
| id | name     | created_at          | modified_at         |
+----+----------+---------------------+---------------------+
|  1 | tmp      | 2017-06-25 08:31:56 | 2017-06-25 08:31:56 |
|  2 | takimoto | 2017-06-25 08:31:56 | 2017-06-25 08:31:56 |
|  3 | takumoto | 2017-06-25 08:31:56 | 2017-06-25 08:31:56 |
|  4 | takemoto | 2017-06-25 08:31:56 | 2017-06-25 08:31:56 |
|  5 | takomoto | 2017-06-25 08:31:56 | 2017-06-25 08:31:56 |
+----+----------+---------------------+---------------------+
5 rows in set (0.01 sec)

kindsテーブルで指定された外部キーのみが制約を受けていることが分かる.

親テーブル削除

次に, 親テーブルを削除してみる.

drop table users;

mysql> drop table users;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

RESTRICT で外部キーを設定したテーブルを削除しようとした場合, エラーを返す.

外部キー制約を RESTRICT にした場合の挙動については以上.

続いて, CASCADE を指定した場合の挙動について見てゆく.

CASCADE

テーブル作成方法は省略.
さっきと同じなので.

ここでは次の点を紹介.

外部キー設定
親テーブル更新
親テーブル削除

外部キー設定

次の手順で外部キーを設定しなおす.

先程設定した外部キーを削除
外部キーを再設定

先程設定した外部キーを削除

まず, 次のコマンドで指定した外部キーを削除する.

まず, 次のコマンドで設定された外部キーの状態を確認.

$ show create table kinds \G

mysql> show create table kinds \G
*************************** 1. row ***************************
       Table: kinds
Create Table: CREATE TABLE `kinds` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `created_at` datetime DEFAULT NULL,
  `modified_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `users_fk_1` (`user_id`),
  CONSTRAINT `users_fk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

続いて, 次のコマンドで外部キーの削除

$ alter table 外部キーを含むテーブル drop foreign key 外部キーID;

ここでは kinds テーブルに設定した外部キーを削除する.

$ alter table kinds drop foreign key users_fk_1;

再度テーブルの状態を確認.

mysql> show create databases kinds \G
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'databases kinds' at line 1
mysql> show create table kinds \G
*************************** 1. row ***************************
       Table: kinds
Create Table: CREATE TABLE `kinds` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `created_at` datetime DEFAULT NULL,
  `modified_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `users_fk_1` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

削除前と比べると, 次の一文がなくなっているのが分かる.

CONSTRAINTusers_fk_1FOREIGN KEY (user_id) REFERENCESusers(id)

ここまでで外部キーの削除はok.

外部キーを再設定

続いて外部キーの再設定.
ここでは CASCADE を設定する.

$ ALTER TABLE kinds ADD CONSTRAINT users_fk_1 FOREIGN KEY (user_id) REFERENCES users (id) ON UPDATE CASCADE ON DELETE CASCADE;

実行結果

mysql> show create table kinds \G
*************************** 1. row ***************************
       Table: kinds
Create Table: CREATE TABLE `kinds` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `created_at` datetime DEFAULT NULL,
  `modified_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `users_fk_1` (`user_id`),
  CONSTRAINT `users_fk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

次の一文があるので, 期待通りに外部キーの設定が完了していることが分かる.

CONSTRAINTusers_fk_1FOREIGN KEY (user_id) REFERENCESusers(id) ON DELETE CASCADE ON

親テーブル更新

親テーブルの変更で期待する子テーブルの動作は次の通り.

値	意味
CASCADE	親テーブルの変更がそのまま子テーブルへも反映される. 親テーブルのデータを削除 -> 子テーブルのデータ(レコード)が削除される親テーブルのデータ変更 -> 子テーブルのデータも変更される

まずは親テーブルを更新.

現状の users, kinds は次の通り.

users

mysql> select * from users;
+----+----------+---------------------+---------------------+
| id | name     | created_at          | modified_at         |
+----+----------+---------------------+---------------------+
|  1 | takamoto | 2017-06-25 08:31:56 | 2017-06-25 08:31:56 |
|  2 | takimoto | 2017-06-25 08:31:56 | 2017-06-25 08:31:56 |
|  3 | takumoto | 2017-06-25 08:31:56 | 2017-06-25 08:31:56 |
|  4 | takemoto | 2017-06-25 08:31:56 | 2017-06-25 08:31:56 |
|  5 | takomoto | 2017-06-25 08:31:56 | 2017-06-25 08:31:56 |
+----+----------+---------------------+---------------------+

kinds

mysql> select * from kinds;
+----+---------+--------+---------------------+---------------------+
| id | user_id | name   | created_at          | modified_at         |
+----+---------+--------+---------------------+---------------------+
|  1 |       1 | red    | 2017-06-25 08:36:08 | 2017-06-25 08:36:08 |
|  2 |       2 | yellow | 2017-06-25 08:36:08 | 2017-06-25 08:36:08 |
|  3 |       3 | blue   | 2017-06-25 08:36:08 | 2017-06-25 08:36:08 |
|  4 |       4 | white  | 2017-06-25 08:36:08 | 2017-06-25 08:36:08 |
|  5 |       5 | black  | 2017-06-25 08:36:08 | 2017-06-25 08:36:08 |
+----+---------+--------+---------------------+---------------------+

次コマンドで users の id=1 を id=10 に変更してみる.

$ UPDATE users SET id=id*10 WHERE id=1;

データの確認.

users

mysql> select * from users;
+----+----------+---------------------+---------------------+
| id | name     | created_at          | modified_at         |
+----+----------+---------------------+---------------------+
|  2 | takimoto | 2017-06-25 08:31:56 | 2017-06-25 08:31:56 |
|  3 | takumoto | 2017-06-25 08:31:56 | 2017-06-25 08:31:56 |
|  4 | takemoto | 2017-06-25 08:31:56 | 2017-06-25 08:31:56 |
|  5 | takomoto | 2017-06-25 08:31:56 | 2017-06-25 08:31:56 |
| 10 | takamoto | 2017-06-25 08:31:56 | 2017-06-25 08:31:56 | # 変わってる
+----+----------+---------------------+---------------------+

kinds

mysql> select * from kinds;
+----+---------+--------+---------------------+---------------------+
| id | user_id | name   | created_at          | modified_at         |
+----+---------+--------+---------------------+---------------------+
|  1 |      10 | red    | 2017-06-25 08:36:08 | 2017-06-25 08:36:08 | # こっちも変わってる
|  2 |       2 | yellow | 2017-06-25 08:36:08 | 2017-06-25 08:36:08 |
|  3 |       3 | blue   | 2017-06-25 08:36:08 | 2017-06-25 08:36:08 |
|  4 |       4 | white  | 2017-06-25 08:36:08 | 2017-06-25 08:36:08 |
|  5 |       5 | black  | 2017-06-25 08:36:08 | 2017-06-25 08:36:08 |
+----+---------+--------+---------------------+---------------------+

期待通りに変わっている.

親テーブルの特定レコード削除

次のコマンドで users の id=3 を削除してみる.

$ delete from users where id=3;

mysql> delete from users where id=3;
Query OK, 1 row affected (0.01 sec)

それぞれのテーブルを確認する.

users

mysql> select * from users;
+----+----------+---------------------+---------------------+
| id | name     | created_at          | modified_at         |
+----+----------+---------------------+---------------------+
|  2 | takimoto | 2017-06-25 08:31:56 | 2017-06-25 08:31:56 |
|  4 | takemoto | 2017-06-25 08:31:56 | 2017-06-25 08:31:56 | # id=3 が削除された
|  5 | takomoto | 2017-06-25 08:31:56 | 2017-06-25 08:31:56 |
| 10 | takamoto | 2017-06-25 08:31:56 | 2017-06-25 08:31:56 |
+----+----------+---------------------+---------------------+

kinds

mysql> select * from kinds;
+----+---------+--------+---------------------+---------------------+
| id | user_id | name   | created_at          | modified_at         |
+----+---------+--------+---------------------+---------------------+
|  1 |      10 | red    | 2017-06-25 08:36:08 | 2017-06-25 08:36:08 |
|  2 |       2 | yellow | 2017-06-25 08:36:08 | 2017-06-25 08:36:08 |
|  4 |       4 | white  | 2017-06-25 08:36:08 | 2017-06-25 08:36:08 | # id=3 が削除されている
|  5 |       5 | black  | 2017-06-25 08:36:08 | 2017-06-25 08:36:08 |
+----+---------+--------+---------------------+---------------------+

親テーブルから特定のレコードを削除すると, 子テーブルからも同様に削除されていることが確認できた.

親テーブル削除

今度は親テーブルを削除しようとしたらどうなるかを確認.

次のコマンドで users を削除してみる.

$ drop table users;

実行結果

mysql> drop table users;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

怒られた.
外部キーを設定している親テーブルは勝手に削除しちゃだめなんだね.

子テーブル削除

外部キーが貼られている状態で子テーブルは削除できるのか？
実験.

次のコマンドで kinds を削除してみる.

$ drop table kinds;

mysql> drop table kinds;
Query OK, 0 rows affected (0.01 sec)

あ、できた.

mysql> show tables;
+-----------------+
| Tables_in_tstDB |
+-----------------+
| users           |
+-----------------+
1 row in set (0.00 sec)

外部キーが設定されている 子テーブル (kinds) は削除可能 なんだね.

SET NULL

最後は SET NULL を設定した場合について.

期待する動作は次の通り.

値	意味
SET NULL	親テーブルの更新・削除 -> 子テーブルへ `NULL` が設定される

概要は次の通り.

テーブル作成
外部キー設定
親テーブル更新
親テーブル削除

テーブル作成

改めてテーブルを作成し直す.
現時点で存在するテーブルは次の通り.

$ show tables;

mysql> show tables;
+-----------------+
| Tables_in_tstDB |
+-----------------+
| users           |
+-----------------+

users テーブルのみが存在している.
これを削除する.

$ drop table users;

mysql> drop table users;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
Empty set (0.00 sec)

改て users, kinds を作成し直す.
手順はこれまでと同様.

users

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NULL,
  `created_at` datetime DEFAULT NULL,
  `modified_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

kinds

CREATE TABLE `kinds` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `created_at` datetime DEFAULT NULL,
  `modified_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

作成されたテーブルを確認.

$ show tables;

mysql> show tables;
+-----------------+
| Tables_in_tstDB |
+-----------------+
| kinds           |
| users           |
+-----------------+
2 rows in set (0.00 sec)

さらに, ダミーデータを追加.

users

insert into tstDB.users (name, created_at, modified_at) values 
("takamoto", CURRENT_TIMESTAMP, CURRENT_TIMESTAMP), # たかもと
("takimoto", CURRENT_TIMESTAMP, CURRENT_TIMESTAMP), # たきもと
("takumoto", CURRENT_TIMESTAMP, CURRENT_TIMESTAMP), # たくもと
("takemoto", CURRENT_TIMESTAMP, CURRENT_TIMESTAMP), # たけもと
("takomoto", CURRENT_TIMESTAMP, CURRENT_TIMESTAMP); # たこもと

kinds

insert into tstDB.kinds (user_id, name, created_at, modified_at) values 
(1, "red", CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(2, "yellow", CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(3, "blue", CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(4, "white", CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(5, "black", CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);

ここまでテーブル生成は完了.

外部キー設定

今回は外部キーオプションとして SET NULL を指定.
ただ, 親テーブルの更新時・削除時で異なる挙動にしたい.

実現したい挙動は次の通り.

親テーブル更新時
- 子テーブルには親テーブルと同じ変更を適用
親テーブル特定レコードを削除時
- 子テーブルには NULL を格納

これを実現するために次のコードを実行する.

$ ALTER TABLE kinds ADD CONSTRAINT users_fk_1 FOREIGN KEY (user_id) REFERENCES users (id) ON UPDATE CASCADE ON DELETE SET NULL;

設定確認

mysql> show create table kinds \G
*************************** 1. row ***************************
       Table: kinds
Create Table: CREATE TABLE `kinds` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `created_at` datetime DEFAULT NULL,
  `modified_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `users_fk_1` (`user_id`),
  CONSTRAINT `users_fk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

親テーブル更新

親テーブルである users の id を更新してみる.

次のコマンドで id=1 を id=10 に変更.

$ UPDATE users SET id=id*10 WHERE id=1;

確認.

users

mysql> select * from users;
+----+----------+---------------------+---------------------+
| id | name     | created_at          | modified_at         |
+----+----------+---------------------+---------------------+
|  2 | takimoto | 2017-06-27 01:49:13 | 2017-06-27 01:49:13 |
|  3 | takumoto | 2017-06-27 01:49:13 | 2017-06-27 01:49:13 |
|  4 | takemoto | 2017-06-27 01:49:13 | 2017-06-27 01:49:13 |
|  5 | takomoto | 2017-06-27 01:49:13 | 2017-06-27 01:49:13 |
| 10 | takamoto | 2017-06-27 01:49:13 | 2017-06-27 01:49:13 | # id=1 -> 10 に変化
+----+----------+---------------------+---------------------+

kinds

mysql> select * from kinds;
+----+---------+--------+---------------------+---------------------+
| id | user_id | name   | created_at          | modified_at         |
+----+---------+--------+---------------------+---------------------+
|  1 |      10 | red    | 2017-06-27 01:49:25 | 2017-06-27 01:49:25 | # id->10 に変化
|  2 |       2 | yellow | 2017-06-27 01:49:25 | 2017-06-27 01:49:25 |
|  3 |       3 | blue   | 2017-06-27 01:49:25 | 2017-06-27 01:49:25 |
|  4 |       4 | white  | 2017-06-27 01:49:25 | 2017-06-27 01:49:25 |
|  5 |       5 | black  | 2017-06-27 01:49:25 | 2017-06-27 01:49:25 |
+----+---------+--------+---------------------+---------------------+

親テーブルの挙動に合わせて, 子テーブルも変化している.
これは期待通りの動作.

親テーブルの特定レコード削除

次のコマンドで users の id=3 を削除してみる.

$ delete from users where id=3;

データを確認してみる.

users

mysql> select * from users;
+----+----------+---------------------+---------------------+
| id | name     | created_at          | modified_at         |
+----+----------+---------------------+---------------------+
|  2 | takimoto | 2017-06-27 01:49:13 | 2017-06-27 01:49:13 |
|  4 | takemoto | 2017-06-27 01:49:13 | 2017-06-27 01:49:13 | # id=3 が削除された
|  5 | takomoto | 2017-06-27 01:49:13 | 2017-06-27 01:49:13 |
| 10 | takamoto | 2017-06-27 01:49:13 | 2017-06-27 01:49:13 |
+----+----------+---------------------+---------------------+

kinds

mysql> select * from kinds;
+----+---------+--------+---------------------+---------------------+
| id | user_id | name   | created_at          | modified_at         |
+----+---------+--------+---------------------+---------------------+
|  1 |      10 | red    | 2017-06-27 01:49:25 | 2017-06-27 01:49:25 |
|  2 |       2 | yellow | 2017-06-27 01:49:25 | 2017-06-27 01:49:25 |
|  3 |    NULL | blue   | 2017-06-27 01:49:25 | 2017-06-27 01:49:25 | # NULL が格納された
|  4 |       4 | white  | 2017-06-27 01:49:25 | 2017-06-27 01:49:25 |
|  5 |       5 | black  | 2017-06-27 01:49:25 | 2017-06-27 01:49:25 |
+----+---------+--------+---------------------+---------------------+

親テーブルの特定レコードが削除されると, 子テーブルには NULL が格納された.
期待通り.

親テーブル削除

じゃあ親テーブルを削除したらどうなるのか?
実験.

$ drop table users;

mysql> drop table users;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

怒られた.

親子関係のある親テーブルは外部キー制約のある状態では削除できない.

じゃあ, 外部キーを外せば削除できるのか?
実験.

まず, 次のコマンドで外部キーを外す.

$ alter table kinds drop foreign key users_fk_1;

次に親テーブルである users を削除してみる.

$ drop table users;

mysql> drop table users;
Query OK, 0 rows affected (0.01 sec)

お、できた.

まとめ

外部キーの基本的な操作を紹介した.

個人的な発見は次の通り.

外部キーが貼られている親テーブルは, 外部キーを外さない限り削除できない

今回は以上.

参考

動作環境

前提条件

作業方針

外部キーとは

外部キーを使ってできること

テーブルの親子関係を把握

テーブル設計時にやりたいことを把握

親テーブル, 子テーブル

外部キーを使う条件

外部キー制約とは

そもそも外部キー制約ってなに

外部キー制約 設定方法

外部キー設定時のオプション

RESTRICT

テーブル作成

外部キー設定

親テーブル更新

親テーブル削除

CASCADE

外部キー設定

先程設定した外部キーを削除

外部キーを再設定

親テーブル更新

親テーブルの特定レコード削除

親テーブル削除

子テーブル削除

SET NULL

テーブル作成

外部キー設定

親テーブル更新

親テーブルの特定レコード削除

親テーブル削除

まとめ

外部キー制約設定方法