MySQL 外部キー制約の基本

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

• RESTRICT
• NO ACTION
• 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)

お、できた.

まとめ

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

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

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

今回は以上.