MySQL 外部キー制約の基本

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

RESTRICT
NO ACTION
CASCADE
SET NULL

概要は次の通り.

1 参考
2 動作環境
3 外部キーとは
4 外部キーを使う条件
5 外部キー制約とは
6 RESTRICT
7 CASCADE
8 SET NULL
9 まとめ

参考

外部キー制約の使用 | 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;

+-------------+--------------+------+-----+---------+----------------+

+-------------+--------------+------+-----+---------+----------------+

| id | int(11) | NO | PRI | NULL | auto_increment |

+-------------+--------------+------+-----+---------+----------------+

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;

+----+----------+---------------------+---------------------+

+----+----------+---------------------+---------------------+

| 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;

+----+---------+--------+---------------------+---------------------+

+----+---------+--------+---------------------+---------------------+

| 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;

+----+----------+---------------------+---------------------+

+----+----------+---------------------+---------------------+

| 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