外部キーと外部キー制約
二つ以上のテーブルを連携させる場合、WHERE 句や JOIN で単純に結合させることもできるが、外部キーを設定するとテーブルの結びつきを確定することができる。
テーブルの結びつきが確定することで、処理速度が速くなり、データの不整合が起きるレコード削除の際にはエラーを発生させて削除を防いだりすることができる。
最初から 2 つのテーブルは「連携しているもの」として扱われるようになり、データの構造もそのように調整される。
以下のような二つのテーブル (zaiko, shukko) を考える。
mysql> select * from zaiko;
+-------+-----------------------+-------+
| idzai | product | stock |
+-------+-----------------------+-------+
| 8001 | Pen Case Red | 1000 |
| 8002 | Pen Case Blue | 1000 |
| 8003 | Pen Case Green | 1000 |
| 8004 | Mannen Hitsu A | 2000 |
| 8005 | Mannen Hitsu B | 2000 |
| 8006 | Mannen Hitsu C | 2000 |
| 8007 | Ball Pen Black 1 case | 1500 |
| 8008 | Ball Pen Red 1 case | 1500 |
| 8009 | Eraser 1 case | 700 |
+-------+-----------------------+-------+
9 rows in set (0.03 sec)
mysql> select * from shukko;
+-----------+-------+--------+---------------------+
| idko | taiou | outnum | outdate |
+-----------+-------+--------+---------------------+
| 201890001 | 8001 | 300 | 2018-08-05 04:50:49 |
| 201890002 | 8002 | 300 | 2018-08-05 04:51:00 |
| 201890003 | 8003 | 300 | 2018-08-05 04:51:05 |
| 201890004 | 8004 | 500 | 2018-08-05 04:51:21 |
| 201890005 | 8005 | 500 | 2018-08-05 04:51:30 |
| 201890006 | 8006 | 500 | 2018-08-05 04:51:35 |
| 201890007 | 8007 | 200 | 2018-08-05 04:51:42 |
| 201890008 | 8008 | 200 | 2018-08-05 04:51:50 |
| 201890009 | 8009 | 150 | 2018-08-05 04:51:57 |
| 201890010 | 8001 | 100 | 2018-08-05 04:53:51 |
| 201890011 | 8008 | 30 | 2018-08-05 04:54:00 |
+-----------+-------+--------+---------------------+
11 rows in set (0.00 sec) ここで、「shukko における taiou は、zaiko における idzai 内のものにする」という約束を作る。
これは、shukko.taiou において zaiko.idzai 以外の値を挿入するとエラーにしたり、というアクションを定義することで実現が可能となる。
この際「他のレコードから参照されている場合 (親になっている場合) 、削除できない」「子には、実在する相手の値 (親にある値) のいずれかしか設定できない」などの制約が生まれることになるが、このことを外部キー制約という。
外部キー制約を実現させるためのアクションのことを参照アクションと呼び、以下のものがある。
| 参照アクション | 説明 |
|---|---|
| RESTRICT / NO ACTION | 親テーブルの削除または更新を拒否する。(デフォルト動作) |
| CASCADE | 親が削除または更新されると、子もそれに合わせて削除または更新される |
| SET NULL | 親テーブルを削除した場合、子デーブルの親を参照する部分には NULL を設定する |
実際にやってみる
テーブルは以下のように定義する。
mysql> CREATE TABLE zaiko (idzai INT NOT NULL AUTO_INCREMENT, product VARCHAR(50) NOT NULL, stock INT NOT NULL DEFAULT 0, PRIMARY KEY (idzai));
Query OK, 0 rows affected (0.03 sec)
mysql> CREATE TABLE shukko (idko INT NOT NULL, taiou INT NOT NULL, outnum INT NOT NULL, outdate DATETIME NOT NULL, PRIMARY KEY (idko), INDEX (taiou));
Query OK, 0 rows affected (0.03 sec)
mysql> select * from zaiko;
+-------+-----------------------+-------+
| idzai | product | stock |
+-------+-----------------------+-------+
| 8001 | Pen Case Red | 1000 |
| 8002 | Pen Case Blue | 1000 |
| 8003 | Pen Case Green | 1000 |
| 8004 | Mannen Hitsu A | 2000 |
| 8005 | Mannen Hitsu B | 2000 |
| 8006 | Mannen Hitsu C | 2000 |
| 8007 | Ball Pen Black 1 case | 1500 |
| 8008 | Ball Pen Red 1 case | 1500 |
| 8009 | Eraser 1 case | 700 |
+-------+-----------------------+-------+
9 rows in set (0.03 sec)
mysql> select * from shukko;
+-----------+-------+--------+---------------------+
| idko | taiou | outnum | outdate |
+-----------+-------+--------+---------------------+
| 201890001 | 8001 | 300 | 2018-08-05 04:50:49 |
| 201890002 | 8002 | 300 | 2018-08-05 04:51:00 |
| 201890003 | 8003 | 300 | 2018-08-05 04:51:05 |
| 201890004 | 8004 | 500 | 2018-08-05 04:51:21 |
| 201890005 | 8005 | 500 | 2018-08-05 04:51:30 |
| 201890006 | 8006 | 500 | 2018-08-05 04:51:35 |
| 201890007 | 8007 | 200 | 2018-08-05 04:51:42 |
| 201890008 | 8008 | 200 | 2018-08-05 04:51:50 |
| 201890009 | 8009 | 150 | 2018-08-05 04:51:57 |
| 201890010 | 8001 | 100 | 2018-08-05 04:53:51 |
| 201890011 | 8008 | 30 | 2018-08-05 04:54:00 |
+-----------+-------+--------+---------------------+
11 rows in set (0.00 sec) 次に、削除時と更新時の参照アクションとして RESTRICT を設定する。
mysql> ALTER TABLE shukko ADD FOREIGN KEY (taiou) REFERENCES zaiko(idzai) ON DELETE RESTRICT ON UPDATE RESTRICT;
Query OK, 0 rows affected (0.17 sec)
Records: 0 Duplicates: 0 Warnings: 0 ここで、親テーブルにおいてレコードを更新してみる。
mysql> UPDATE zaiko SET idzai = 8010 WHERE idzai = 8001;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`gaibu`.`shukko`, CONSTRAINT `shukko_ibfk_1` FOREIGN KEY (`taiou`) REFERENCES `zaiko` (`idzai`)) “Cannot delete or update a parent now: a foreign key constraint fails” というエラーが発生し、更新ができない。
次に、この shukko テーブルを DROP し、新たな参照アクションを定義した上で再作成する。
mysql> DROP TABLE shukko;
Query OK, 0 rows affected (0.02 sec)
mysql> CREATE TABLE shukko (idko INT NOT NULL, taiou INT NOT NULL, outnum INT NOT NULL, outdate DATETIME NOT NULL, PRIMARY KEY (idko), INDEX (taiou));
Query OK, 0 rows affected (0.03 sec)
mysql> ALTER TABLE shukko ADD FOREIGN KEY (taiou) REFERENCES zaiko(idzai) ON DELETE CASCADE ON UPDATE CASCADE;
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM shukko;
+-----------+-------+--------+---------------------+
| idko | taiou | outnum | outdate |
+-----------+-------+--------+---------------------+
| 201890001 | 8001 | 300 | 2018-08-05 05:18:04 |
| 201890002 | 8002 | 300 | 2018-08-05 05:18:04 |
| 201890003 | 8003 | 300 | 2018-08-05 05:18:04 |
| 201890004 | 8004 | 500 | 2018-08-05 05:18:04 |
| 201890005 | 8005 | 500 | 2018-08-05 05:18:04 |
| 201890006 | 8006 | 500 | 2018-08-05 05:18:04 |
| 201890007 | 8007 | 200 | 2018-08-05 05:18:04 |
| 201890008 | 8008 | 200 | 2018-08-05 05:18:04 |
| 201890009 | 8009 | 150 | 2018-08-05 05:18:04 |
| 201890010 | 8001 | 100 | 2018-08-05 05:18:04 |
| 201890011 | 8008 | 30 | 2018-08-05 05:18:04 |
+-----------+-------+--------+---------------------+
11 rows in set (0.00 sec) ここで、親テーブルにてレコードを更新すると、対応する子テーブルのレコードも更新されていることがわかる。
mysql> UPDATE zaiko SET idzai = 8010 WHERE idzai = 8001;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM zaiko;
+-------+-----------------------+-------+
| idzai | product | stock |
+-------+-----------------------+-------+
| 8002 | Pen Case Blue | 1000 |
| 8003 | Pen Case Green | 1000 |
| 8004 | Mannen Hitsu A | 2000 |
| 8005 | Mannen Hitsu B | 2000 |
| 8006 | Mannen Hitsu C | 2000 |
| 8007 | Ball Pen Black 1 case | 1500 |
| 8008 | Ball Pen Red 1 case | 1500 |
| 8009 | Eraser 1 case | 700 |
| 8010 | Pen Case Red | 1000 |
+-------+-----------------------+-------+
9 rows in set (0.00 sec)
mysql> SELECT * FROM shukko;
+-----------+-------+--------+---------------------+
| idko | taiou | outnum | outdate |
+-----------+-------+--------+---------------------+
| 201890001 | 8010 | 300 | 2018-08-05 05:18:04 |
| 201890002 | 8002 | 300 | 2018-08-05 05:18:04 |
| 201890003 | 8003 | 300 | 2018-08-05 05:18:04 |
| 201890004 | 8004 | 500 | 2018-08-05 05:18:04 |
| 201890005 | 8005 | 500 | 2018-08-05 05:18:04 |
| 201890006 | 8006 | 500 | 2018-08-05 05:18:04 |
| 201890007 | 8007 | 200 | 2018-08-05 05:18:04 |
| 201890008 | 8008 | 200 | 2018-08-05 05:18:04 |
| 201890009 | 8009 | 150 | 2018-08-05 05:18:04 |
| 201890010 | 8010 | 100 | 2018-08-05 05:18:04 |
| 201890011 | 8008 | 30 | 2018-08-05 05:18:04 |
+-----------+-------+--------+---------------------+
11 rows in set (0.01 sec)