5.2　外部キー制約

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');