東京都で開催する「初心者向けデータベース設計入門」講座です。

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」に、次のデータを登録するDDL文を発行してみます。

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」に変更する、下記のDDL文を発行してみてください。

   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 本章のまとめ

ご不明点はお問い合わせフォームからご連絡ください。

セミナー一覧

法人向けプログラミング研修

Java、PHPの社員研修|新人研修
セミナー講師紹介

主席インストラクター
早稲田大学大学院 理工学部卒業
SanJose 州立大学、コンピューター工学部卒業
アメリカのシリコンバレーでは、プログラマー・SEとして活躍
講師の詳細はこちら

PAGETOP
Copyright © 2016 FusionOne Co.,Ltd. All rights reserved.