リレーショナルデータベースの基本概念

本章では、リレーショナルデータベースの基本的な概念について解説します。
本章の目標
  • リレーショナルデータベースの概念を理解する
  • 外部キー制約の考え方を理解する
ページの先頭へ
ページの先頭へ

1.リレーショナルデータベースとは

まずリレーショナルデータベースの考え方を押さえます。

1. リレーショナルデータベースとは

リレーショナルデータベースは、1970年にIBM社のEdgar F. Codd博士によって提唱されたリレーショナルデータモデルの理論に従ったデータ管理方式の一つです。
1件のデータ(レコード)を複数の項目(フィールド)の集合として表現し、データの集合をテーブルと呼ばれる表で表す方式で、 ID番号や名前などのキーとなるデータを利用して、データの結合や抽出を容易に行なうことができるのが特徴です。

2. テーブルの概念

いままで見てきた通り、列(フィールド)が集まって行(レコード)を形成し、これが集まってテーブルとなります。
「bookinfo」を参考にすると、「isbn」「title」「price」がそれぞれフィールドを表し、これが集まって1件のレコードとなり、さらにレコードの集まりがテーブル「bookinfo」となります。
リレーショナルデータベースでは、テーブルがデータの入れ物の基本単位となります。

表(bookinfo)


3. リレーショナルの意味

これまでは、単一のテーブルを個別に扱ってきましたが、この個々のテーブルを互いに関係(リレーション)付け、 複数のテーブルを連携して管理する方式が、リレーショナルデータベースなのです。
今まで出てきた「bookinfo」と「orderinfo」を関連付けてみましょう。


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

この2つの表は「isbn」で関連付けられています。
つまり、「orderinfo」の注文番号「1」で注文された書籍は「SQL入門テキスト」、注文番号「5」で注文された書籍は「SQL基礎テキスト」ということになります。
この2つのテーブルは下記のように1つにまとめることもできます。

ordernouserisbn titlepricequantity date
1kanda00001 SQL入門テキスト10501 2010-07-01
5kanda00002 SQL基礎テキスト23001 2010-08-22

しかしこのようなテーブルは冗長であり、例えば「SQL入門テキスト」の注文が1万件あった場合、titleの「SQL入門テキスト」とpriceの1050円というデータが1万件できることになります。
しかしこれを「bookinfo」と「orderinfo」に分けることによって、「SQL入門テキスト」とpriceの1050円というデータは「bookinfo」の1件で済みます。
これは、データの容量を抑えるとともに、データに変更があった場合の保守性の観点からも有用です。
「bookinfo」を例にとれば、仮に書籍名「SQL入門テキスト」が変更になった場合でも、1万件のデータを更新せずに済みます。



このように冗長な表を複数の表に分割して冗長さをなくすことを「正規化」と呼びます。
正規化の手法は本テキストの範囲を超えますので説明は省略しますが、興味のある方は専門の書籍等を参照してください。

ページの先頭へ

2.外部キー制約


1. 外部キー制約の基本

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


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

テーブル(bookinfo)
isbn title price
00001SQL入門テキスト1050
00002SQL基礎テキスト2300
00003Java基礎テキスト1050
00004PHP基礎テキスト2100
00005Java Struts基礎テキスト3800
00006Android基礎テキスト2000
00007NULL2800

この時、「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文を発行してみます。

ordernouserisbnquantitydate
31kanda000812010-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';



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

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件であることを意味します。

ページの先頭へ

3.本章のまとめ

本章ではリレーショナルデータベースの基本概念と外部キー制約について解説しました。
「SQL入門講座テキスト」にも記述しましたが、リレーショナルデータベースは、現在使用されているデータベースの中で主流となっており、 本章で解説したことはその中心的な概念となっていますので、必ず理解しておくようにしてください。

ページの先頭へ