MySQLで外部キー周りでハマったので基礎からおさらいする.
本記事では そもそも外部キーってなに? からスタート.
最終的には実際にDBをいじりながら, 次の外部キーオプションを確認してゆく.
- RESTRICT
- NO ACTION
- CASCADE
- SET NULL
概要は次の通り.
参考
動作環境
- 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 句を省略することと同じです。
実際にテーブルを作成しながら試してみる.
概要は次の通り.
- テーブル作成
- 外部キー設定
- 親テーブル更新
- 親テーブル削除
テーブル作成
一先ず次の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)
削除前と比べると, 次の一文がなくなっているのが分かる.
CONSTRAINT
users_fk_1FOREIGN KEY (
user_id) REFERENCES
users(
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)
次の一文があるので, 期待通りに外部キーの設定が完了していることが分かる.
CONSTRAINT
users_fk_1FOREIGN KEY (
user_id) REFERENCES
users(
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)
お、できた.
まとめ
外部キーの基本的な操作を紹介した.
個人的な発見は次の通り.
- 外部キーが貼られている親テーブルは, 外部キーを外さない限り削除できない
今回は以上.