連載
» 2007年07月17日 00時00分 公開

外部キー制約を活用する今から始める MySQL入門(8)(3/3 ページ)

[鶴長鎮一,@IT]
前のページへ 1|2|3       

外部キー制約の動作を確認する

 冒頭でインストールした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パターンを使って修正した「オンラインストア」を統合させ、ショッピングストアの処理を完成させます。


前のページへ 1|2|3       

Copyright © ITmedia, Inc. All Rights Reserved.

RSSについて

アイティメディアIDについて

メールマガジン登録

@ITのメールマガジンは、 もちろん、すべて無料です。ぜひメールマガジンをご購読ください。