冒頭でインストールしたsample_db5データベースでは、すでに外部キー制約の設定が完了しています。
さっそく試しに、親テーブルであるitemのidカラム値のアップデートを行ってみましょう。親に対して行った修正が、子側にも反映されていることが確認できます。
mysql> SELECT * FROM item; ←更新前の状態を確認
+----+------------------------------------+--------------------------------------------
| id | name | detail
+----+------------------------------------+--------------------------------------------
| 1 | 半熟卵のMySQL風チキンドリア | チキンたっぷりのリゾットに半熟卵をトッピン
| 2 | 地鶏とキノコのBIND9風フェットチーネ| きしめん状の幅広パスタがBIND9の堅牢さによく
| 3 | 有機野菜のApache風グリーンサラダ | 有機野菜の鮮度そのままに，酸味を抑えたドレ
+----+------------------------------------+--------------------------------------------
（※表示を一部省略しています）
mysql> SELECT * FROM order_item; ←更新前の状態を確認
+------------+---------------------+------+-----+
| order_no | order_date | item | qty |
+------------+---------------------+------+-----+
| 1000000000 | 2007-06-28 20:45:46 | 1 | 1 |
| 1000000000 | 2007-06-28 20:45:46 | 2 | 16 |
| 1000000000 | 2007-06-28 20:45:46 | 3 | 4 |
+------------+---------------------+------+-----+
mysql> UPDATE item SET id = '5' WHERE id = '1';
←「半熟卵のMySQL風チキンドリア」のidを「5」に変更（「親」側の更新）
mysql> SELECT * FROM item; ←更新後の状態を確認
+----+------------------------------------+--------------------------------------------
| id | name | detail
+----+------------------------------------+--------------------------------------------
| 2 | 地鶏とキノコのBIND9風フェットチーネ| きしめん状の幅広パスタがBIND9の堅牢さによく
| 3 | 有機野菜のApache風グリーンサラダ | 有機野菜の鮮度そのままに，酸味を抑えたドレ
| 5 | 半熟卵のMySQL風チキンドリア | チキンたっぷりのリゾットに半熟卵をトッピン
| | | ←idが1から5へ変更されている
+----+------------------------------------+--------------------------------------------
（※表示を一部省略しています）
mysql> SELECT * FROM order_item; ←子側の状態を確認
+------------+---------------------+------+-----+
| order_no | order_date | item | qty |
+------------+---------------------+------+-----+
| 1000000000 | 2007-06-28 20:45:46 | 2 | 16 |
| 1000000000 | 2007-06-28 20:45:46 | 3 | 4 |
| 1000000000 | 2007-06-28 20:45:46 | 5 | 1 |
| ←itemが1から5へ変更されている
+------------+---------------------+------+-----+
|
親側のデータを削除しようとした場合、子側にデータが残っていれば、外部キー制約によってエラーになります。
mysql> DELETE FROM item WHERE id = '3';
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`sample_db5/order_item`, CONSTRAINT `fk_2` FOREIGN KEY (`item`) REFERENCES `item` (`id`) ON UPDATE CASCADE)
※MySQLがエラー番号「1451」を出力している点に注目します。
次回紹介するサンプル「ItemDao.php」で利用します |
外部キー制約ではまた、更新や削除の際の動作パターンを設定することもできます。
先ほど、親側のitemテーブルのデータを削除した際、子側に同じデータが残っているとエラーが生じ、削除を行うことができませんでした。
今度は、もう1つの親側のorder_mainテーブルのデータの削除を試みます。order_itemテーブルのorder_noカラムは、order_mainテーブルのorder_noカラムを親に持つよう外部キー制約を設定しています。
mysql> SELECT * FROM order_main; ←更新前の状態を確認
+------------+---------------------+--------------------------+----------+------
| order_no | order_date | name | zip_code | addre
+------------+---------------------+--------------------------+----------+------
| 1000000000 | 2007-06-28 20:45:46 | アイティメディア | 1000005 | 東京
+------------+---------------------+--------------------------+----------+------
（※表示を一部省略しています）
mysql> SELECT * FROM order_item; ←更新前の状態を確認
+------------+---------------------+------+-----+
| order_no | order_date | item | qty |
+------------+---------------------+------+-----+
| 1000000000 | 2007-06-28 20:45:46 | 2 | 16 |
| 1000000000 | 2007-06-28 20:45:46 | 3 | 4 |
| 1000000000 | 2007-06-28 20:45:46 | 5 | 1 |
+------------+---------------------+------+-----+
mysql> DELETE FROM order_main WHERE order_no = "1000000000";
←親側を削除
mysql> SELECT * FROM order_main; ←親側の削除を確認
Empty set (0.00 sec)
mysql> SELECT * FROM order_item; ←子側も同時に削除されている
Empty set (0.00 sec) |
今度は、親側の削除と同時に子側も削除されていることが分かります。
こうした外部キーの動作パターンは、以下のように、子側のorder_itemテーブル構築時に設定します。
mysql> SHOW CREATE TABLE order_item;
CREATE TABLE `order_item` (
`order_no` int(10) unsigned NOT NULL,
`order_date` datetime NOT NULL,
`item` int(10) unsigned NOT NULL,
`qty` int(10) NOT NULL,
UNIQUE KEY `uk1` (`order_no`,`item`),
KEY `item` (`item`),
CONSTRAINT `fk_1` FOREIGN KEY (`order_no`) REFERENCES `order_main` (`order_no`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_2` FOREIGN KEY (`item`) REFERENCES `item` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
（※表示を一部省略しています） |
まず「CONSTRAINT....CASCADE」で外部キー制約を設定しています。「fk_1」がorder_noカラムとorder_mainテーブルのorder_noカラムとの外部キー制約、「fk_2」がitemカラムとitemテーブルのidカラムとの外部キー制約になります。
fk_1の後半では「ON DELETE CASCADE ON UPDATE CASCADE」で削除（ON DELETE）時に「CASCADE」、更新（ON UPDATE）時にもCASCADEが設定されています。CASCADEを指定することで、更新時には親側の更新に合わせて子側も同様の値で更新を行い、削除時には親側の削除に合わせ該当する子側のデータも削除されます。
fk_2では「ON UPDATE CASCADE」で更新時にはCASCADEが設定されていますが、削除（ON DELETE）時の動作は設定されていません。この場合、デフォルトの「RESTRICT」が適用されます。RESTRICTでは、子側にデータが残っていて親側のデータを削除しようとした場合、エラーになります。
CONSTRAINT `制約名` FOREIGN KEY (`子側カラム名`)
REFERENCES `親側テーブル名` (`親側カラム名`)
ON DELETE 削除時の動作 ON UPDATE 更新時の動作
|
CASCADEやRESTRICTのほかに「SET NULL」も用意されています。外部キー制約を利用することで、PHPスクリプト側の負担を軽減することができます。
|
ON UPDATE |
ON DELETE |
| RESTRICT |
エラー |
エラー |
| CASCADE |
親側の同じ値で子側を更新 |
親側の削除と同時に子側も削除 |
| SET NULL |
子側をNULL値で更新 |
子側をNULL値で更新 |
| 外部キー制約の設定 |
このように外部制約キーを活用することにより、複数のテーブルにまたがる値を関連付けていくことができます。
さて次回は、これまで作成した「簡易在庫管理システム」と、DTO、DAOパターンを使って修正した「オンラインストア」を統合させ、ショッピングストアの処理を完成させます。
ITmediaはアイティメディア株式会社の登録商標です。