外部キーを含むテーブルの作成

外部キー

前回は楽曲テーブルと顧客テーブルを作成しました。次に注文テーブルと注文詳細テーブルを作成しますが、各テーブルの定義は以下のようになっています。

注文テーブル
注文ID 顧客ID 注文日
注文詳細テーブル
注文ID 楽曲ID 注文量

注文テーブルの「顧客ID」は顧客テーブル中にある一行を参照しています。
また、注文詳細テーブルの「書籍ID」は書籍テーブル中の一行、「注文ID」は注文テーブル中の一行を参照します。このように別テーブルの行を参照している列を外部キーと言います。

参照整合性制約

このような場合、外部キーが参照している先の行が削除されてしまうと不具合が生じます。例えば、注文詳細テーブルに楽曲ID123の注文があるのに、楽曲テーブルに楽曲ID123が存在しなければ何の注文かがわかりません。

このように参照先が必ず存在するという制約を参照整合性制約と言います。前回でも説明したように、CREATE TABLEコマンドで列を定義する際にREFERENCES制約を付けることにより、参照整合性を設定できます。また、オプションで参照先が削除ON DELETE、または、更新ON UPDATEされるときにどのような動作を行うかを設定できます。

REFERENCES 参照テーブル名(参照列名) [ON DELETE オプション] [ON UPDATE オプション]
オプション 内容
NO ACTION 何もしない(デフォルト)
※通常、参照整合性制約エラーが起きる
RESTRICT 参照している行があると削除、変更ができない
CASCADE 参照している行が合わせて削除、更新される
SET NULL NULLにする
SET DEFAULT デフォルト値にする(参照整合性制約を満たす必要がある)

動作に関しては、後で詳しく説明します。

テーブルを作成する

それでは外部キー(参照整合性制約)を含む注文テーブルorder_を作成してみましょう

※orderはPostgreSQLの予約語のためテーブル名に使えません。そういう場合は、アンダースコア(_)を付けるなどをして予約語を回避します。

カラム名 意味 データ型 オプション
id ID INTEGER PRIMARY KEY
customer_id 顧客ID INTEGER REFERENCES customer(id)
ON DELETE RESTRICT
ON UPDATE RESTRICT
date 注文日 DATE  
test_db=> CREATE TABLE order_ (
test_db(> id INTEGER PRIMARY KEY,
test_db(> customer_id INTEGER REFERENCES customer(id) ON DELETE RESTRICT ON UPDATE RESTRICT,
test_db(> date DATE);
CREATE TABLE

演習

今後の学習でも使用することを兼ねて、注文詳細テーブルorder_detailを作成してみましょう。

カラム名 意味 データ型 オプション
order_id 注文ID INTEGER REFERENCES order_(id)
ON DELETE CASCADE
ON UPDATE CASCADE
music_id 楽曲ID INTEGER REFERENCES music(id)
ON DELETE RESTRICT
ON UPDATE RESTRICT
amount 数量 INTEGER CHECK(amount > 0)

注文が削除(取り消し)されたら、関連する注文詳細も削除するため、注文IDにはCASCADEを指定しています。一方、楽曲に関しては注文がある限り削除できないようにRESTRICTを指定しています。

また、order_detailは他のテーブルと違い、注文IDと楽曲IDの組み合わせを主キーとします。このような複数列の組み合わせを主キーとする場合、PostgreSQLでは列定義の後にテーブル全体の制約という形で以下のように記述します。

CREATE TABLE order_detail (
定義...,
PRIMARY KEY (order_id, book_id)
);
test_db=> CREATE TABLE order_detail (
test_db(> order_id INTEGER REFERENCES order_(id) ON DELETE CASCADE ON UPDATE CASCADE,
test_db(> music_id INTEGER REFERENCES music(id) ON DELETE RESTRICT ON UPDATE RESTRICT,
test_db(> amount INTEGER CHECK (amount > 0),
test_db(> PRIMARY KEY (order_id, music_id)
test_db(> );
CREATE TABLE

プログラミングの必要性

フェイスブックのザッカーバーグ氏やマイクロソフトのビル・ゲイツ氏などが、プログラミングの必要性について語っています。

おすすめアイテム

「M570」以来、実に7年ぶりのロジクール製トラックボールマウス。プログラミングをするにあたって、一度使うともう元には戻れません!