外部キー制約

5.2 外部キー制約

5.2.1 外部キー制約の基本

　リレーショナルデータベースにおいては、各表はデータを参照される側と参照する側で親子関係が成り立っています。このような場合、参照される側の表を親テーブル（またはマスターテーブル）、参照する側の表を子テーブル（またはトランザクションテーブル）と呼びます。例えば「bookinfo」と「orderinfo」の関係で言えば、「bookinfo」が親テーブル、「orderinfo」が子テーブルとなります。

　ここで問題なのが、親テーブルに無いデータを子テーブルに追加すると、親テーブルと子テーブルの整合性が崩れてしまいます。「bookinfo」と「orderinfo」を例にとると、現在「bookinfo」のデータは下記のようになっています。テーブル（bookinfo）

isbn	title	price
00001	SQL入門テキスト	1050
00002	SQL基礎テキスト	2300
00003	Java基礎テキスト	1050
00004	PHP基礎テキスト	2100
00005	Java Struts基礎テキスト	3800
00006	Android基礎テキスト	2000
00007	NULL	2800

　この時、「orderinfo」にISBN番号が「00008」のデータを登録した場合、「bookinfo」にはそのデータがないので、参照できず、整合性が崩れた状態になってしまいます。

　このような不整合を防ぐためにリレーショナルデータベースの表には、外部キー制約（または参照整合性制約）という制約をつけることが出来ます。　外部キー制約は、表作成時に参照する側の表につけます。また、参照先の列は、そのテーブルの主キーである必要があります。これは主キーが重複を許さず、「NOT NULL」であるというルールがあるためです。参照先の列に重複したデータがあるとどのデータと結びつけてよいかわかりませんし、NULLのデータがあると参照できないためです。　外部キー制約を付ける場合のDDL文の書式は下記の通りです。

　　　CREATE TABLE　テーブル名 ( 　　　列名データ型属性, 　　　列名データ型属性, 　　　列名データ型属性, 　　　・・・・, 　　　FOREIGN KEY (参照元の列名) REFERENCES 参照先のテーブル名(参照先の列名) 　　　);

　　復習　* 「orderinfo」テーブルの再作成　下表のテーブル「orderinfo」テーブルのisbn列に、「bookinfo」のisbn列を参照する外部キー制約をつけてテーブルを再作成してください。　　　　DROP TABLE orderinfo; 　　　CREATE TABLE orderinfo ( 　　　orderno INTEGER AUTO_INCREMENT PRIMARY KEY, 　　　user VARCHAR(20), 　　　isbn VARCHAR(20), 　　　quantity INTEGER, 　　　date date, 　　　FOREIGN KEY (isbn) REFERENCES bookinfo(isbn) 　　　)ENGINE=InnoDB; 　* 「orderinfo」テーブルのへのデータ登録　注文データを再登録してください。　　　INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('kanda','00001',1,'2010-07-01'); 　　　INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('shibuya','00001',2,'2010-07-15'); 　　　INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('akiba','00001',1,'2010-08-02'); 　　　INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('meguro','00001',3,'2010-07-17'); 　　　INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('kanda','00002',1,'2010-08-22'); 　　　INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('shibuya','00002',3,'2010-09-03'); 　　　INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('kanda','00002',1,'2010-07-25'); 　　　INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('meguro','00003',4,'2010-07-30'); 　　　INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('ueno','00003',1,'2010-08-12'); 　　　INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('shibuya','00003',1,'2010-08-21'); 　　　INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('tamachi','00004',2,'2010-09-14'); 　　　INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('meguro','00004',3,'2010-07-11'); 　　　INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('shibuya','00004',6,'2010-07-19'); 　　　INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('akiba','00004',1,'2010-08-19'); 　　　INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('kanda','00005',4,'2010-09-01'); 　　　INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('tamachi','00005',2,'2010-08-22'); 　　　INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('shibuya','00005',1,'2010-07-01'); 　　　INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('kanda','00006',1,'2010-07-15'); 　　　INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('osaki','00006',1,'2010-08-20'); 　　　INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('shibuya','00006',3,'2010-07-30'); 　　　INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('meguro','00006',2,'2010-09-01'); 　　　INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('tamachi','00006',6,'2010-09-30'); 　　　INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('kanda','00007',1,'2010-07-11'); 　　　INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('shibuya','00007',3,'2010-07-21'); 　　　INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('kanda','00007',2,'2010-08-15'); 　　　INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('meguro','00007',1,'2010-07-02'); 　　　INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('akiba','00007',4,'2010-07-25'); 　　　INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('ueno','00007',1,'2010-08-15'); 　　　INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('ueno','00001',1,'2010-08-14'); 　　　INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('ueno','00002',1,'2010-07-15'); 　* データの確認　データの中身を確認してみましょう。　　　SELECT * FROM orderinfo;

　さてそれでは、実際に外部キー制約が機能しているかどうかを確認してみましょう。　「orderinfo」に、次のデータを登録するDML文を発行してみます。

orderno	user	isbn	quantity	date
31	kanda	0008	1	2010-07-01

　　　INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('kanda','00008',1,'2010-07-01');

　上の画面のように、エラーになったと思います。これは、参照先「bookinfo」の「isbn」列に「00008」というデータがないため、外部キー制約違反のエラーになったことを表しています。　また、親テーブルのデータも、勝手に変更や削除してしまうと子テーブルとの間でやはり不整合が起きます。　「bookinfo」と「orderinfo」の関係で言うと、「orderinfo」のデータにISBN番号が「00001」のデータがあるのに、「bookinfo」の「00001」のデータを削除したり、番号を変更してしまうと、「bookinfo」と「orderinfo」が不整合な状態となってしまいます。

　このような場合も、外部キー制約をつけておくと、整合性を保つように変更や削除ができなくなります。　試しに「bookinfo」のデータを変更、削除してみましょう。　まずは「bookinfo」のISBN番号「00001」を「00008」に変更する、下記のDML文を発行してみてください。

　　　UPDATE bookinfo SET isbn='00008' WHERE isbn='00001';

　やはり外部キー制約の違反でエラーになったと思います。　次に同じデータを削除してみましょう。

　　　DELETE FROM bookinfo WHERE isbn='00001';

　同じくエラーになりましたね。　このように外部キー制約をつけておくと、親テーブルと子テーブルの整合性が保証されるのです。

5.2.2 親テーブルデータの変更と削除

　外部キー制約が付いていた場合でも、親テーブルのデータを変更したり削除したりといったケースは出てきます。　このような場合、親テーブルと子テーブルの整合性を保ったまま行うには、ひとつは下記の手順で変更できます。　* データを変更する場合（「bookinfo」のISBN番号「00007」を「00008」に変更する場合）

　※簡略のために、データを一部だけ表示しています。

　* データを削除する場合（「bookinfo」のISBN番号「00007」を削除する場合）

　上記のような手順を踏めば、データの整合性を保ったまま、親テーブルデータの変更や削除が行なえますが、非常に手間がかかります。　このような場合、外部キー制約を設定するときに「ON UPDATE CASCADE」オプションと「ON DELETE CASCADE」オプションを付けておくと便利です。「ON UPDATE CASCADE」オプションを付けておくと、親テーブルのデータが変更されたときに、同じキーを持つ子テーブルのデータも自動的に変更されます。また、「ON DELETE CASCADE」オプションを付けておくと、親テーブルが削除されたときに、同じキーを持つ子テーブルのデータも自動的に削除されます。書式は下記の通りです。

　　　CREATE TABLE　テーブル名 ( 　　　列名データ型属性, 　　　列名データ型属性, 　　　列名データ型属性, 　　　・・・・, 　　　FOREIGN KEY (参照元の列名) REFERENCES 参照先のテーブル名(参照先の列名) 　　　ON UPDATE CASCADE ON DELETE CASCADE 　　　);

　　復習　* 「orderinfo」テーブルの再作成　「orderinfo」テーブルの外部キー制約に、「ON UPDATE CASCADE」オプションと「ON DELETE CASCADE」オプションをつけてテーブルを再作成してください。　　　DROP TABLE orderinfo; 　　　CREATE TABLE orderinfo ( 　　　orderno INTEGER AUTO_INCREMENT PRIMARY KEY, 　　　user VARCHAR(20), 　　　isbn VARCHAR(20), 　　　quantity INTEGER, 　　　date date, 　　　FOREIGN KEY (isbn) REFERENCES bookinfo(isbn) ON UPDATE CASCADE ON DELETE CASCADE 　　　)ENGINE=InnoDB; 　* 「orderinfo」テーブルのへのデータ再登録　注文データを再登録してください。　　　INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('kanda','00001',1,'2010-07-01'); 　　　INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('shibuya','00001',2,'2010-07-15'); 　　　INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('akiba','00001',1,'2010-08-02'); 　　　INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('meguro','00001',3,'2010-07-17'); 　　　INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('kanda','00002',1,'2010-08-22'); 　　　INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('shibuya','00002',3,'2010-09-03'); 　　　INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('kanda','00002',1,'2010-07-25'); 　　　INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('meguro','00003',4,'2010-07-30'); 　　　INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('ueno','00003',1,'2010-08-12'); 　　　INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('shibuya','00003',1,'2010-08-21'); 　　　INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('tamachi','00004',2,'2010-09-14'); 　　　INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('meguro','00004',3,'2010-07-11'); 　　　INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('shibuya','00004',6,'2010-07-19'); 　　　INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('akiba','00004',1,'2010-08-19'); 　　　INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('kanda','00005',4,'2010-09-01'); 　　　INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('tamachi','00005',2,'2010-08-22'); 　　　INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('shibuya','00005',1,'2010-07-01'); 　　　INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('kanda','00006',1,'2010-07-15'); 　　　INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('osaki','00006',1,'2010-08-20'); 　　　INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('shibuya','00006',3,'2010-07-30'); 　　　INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('meguro','00006',2,'2010-09-01'); 　　　INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('tamachi','00006',6,'2010-09-30'); 　　　INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('kanda','00007',1,'2010-07-11'); 　　　INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('shibuya','00007',3,'2010-07-21'); 　　　INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('kanda','00007',2,'2010-08-15'); 　　　INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('meguro','00007',1,'2010-07-02'); 　　　INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('akiba','00007',4,'2010-07-25'); 　　　INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('ueno','00007',1,'2010-08-15'); 　　　INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('ueno','00001',1,'2010-08-14'); 　　　INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('ueno','00002',1,'2010-07-15'); 　* データの確認　データの中身を確認してみましょう。　　　SELECT * FROM orderinfo;