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;

では実際に、「bookinfo」のISBN番号「00007」のデータを番号「00008」に変更してみます。まず「orderinfo」のISBN番号「00007」と「00008」のデータを検索し、現在の状態を確認しておきましょう。

　　　SELECT * FROM orderinfo WHERE isbn IN ('00007', '00008');

この状態で、「bookinfo」のISBN番号「00007」のデータを番号「00008」に変更し、再度「orderinfo」を確認してみましょう。

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

　　　SELECT * FROM orderinfo WHERE isbn IN ('00007', '00008');

「orderinfo」のデータも、変更されていることが確認できます。次に、先程変更した「bookinfo」のISBN番号「00008」のデータを削除し、「orderinfo」のデータを確認してみます。

　　　DELETE FROM bookinfo WHERE isbn = '00008';

　　　SELECT * FROM orderinfo WHERE isbn IN ('00007', '00008');

データが削除されていることがわかります。
※「Empty set」は検索結果が0件であることを意味します。

NEXT>> 5.3 本章のまとめ