連載
» 2007年07月17日 00時00分 公開

外部キー制約を活用する今から始める MySQL入門(8)(2/3 ページ)

[鶴長鎮一,@IT]

データベースの設定内容と各テーブルの定義

 今回のサンプルに必要なデータベース、sample_db5では、前回作成した「簡易在庫管理システム」の「item」テーブルと、第5回の簡易オンラインストアで作成した「count」「order_main」「order_item」テーブルを使用します。

 第5回の「簡易オンラインストア」では、order_itemテーブルのitemカラムに注文した商品の商品名を挿入しました。

 しかし、商品名や単価などの商品情報はitemテーブルで管理するため、今回は、order_itemテーブルのitemカラムにはitemテーブルのidカラムの値を挿入するようにします。そのため、order_itemテーブルのitemカラムを「varchar(20)」型から「int(10) unsigned」型に変更しています。

 なお、第5回の仕様では注文数に0以下を挿入することができませんでしたが、今回は0以下の値の挿入が可能となるようunsignedを設定しません。itemテーブルも同様に、注文が過多となり在庫数が0以下になる場合を考慮し、qtyカラムを「int(10) unsigned」型から「int(10)」型に修正しています。

 order_mainテーブルには新たに、郵便番号を登録するzip_codeカラムを設けています。郵便番号は0から始まる場合があり、int型では冒頭の0が欠落するため「varchar(9)」型で定義します。各テーブルの詳細は以下のとおりです。

項目 内容
ユーザー名 php
パスワード password
データベース sample_db5

カラム名 備考
max_no int(10) unsigned 注文番号のカウントに使用*
countテーブル

カラム名 備考
order_no int(10) unsigned 注文番号*
order_date datetime 受注日時
name varchar(20) 氏名
zip_code varchar(9) 郵便番号
address varchar(100) 住所
order_mainテーブル

カラム名 備考
order_no int(10) unsigned 注文番号**
order_date datetime 受注日時
item int(10) unsigned 商品ID**
qty int(10) 注文個数
order_itemテーブル

カラム名 備考
id int(10) unsigned 商品ID*
name varchar(50) 商品名***
detail varchar(200) 商品詳細
price decimal(10,2) unsigned 値段
qty int(10) 在庫数
itemテーブル

注:

*には主キーを設定

**には複数カラムにまたがる一意キーを設定

***には一意キーを設定


外部キー制約の利用

 order_itemテーブルのitemカラムには、itemテーブルのidカラムの値が挿入されます。そのため、order_itemテーブルに値が存在している一方で、itemテーブルのidカラムの値が勝手に変更されたり削除されたりすると、商品のひも付けができなくなります。

 同様に、order_itemテーブルのorder_noカラムには、order_mainテーブルのorder_noカラムの値が挿入されます。こちらもorder_mainテーブルのorder_noカラムの値が勝手に変更されたり削除されたりすると、どの注文にひも付く商品なのかが分からなくなってしまいます。

 このようにテーブル間の値に一定の条件を設ける際には、「外部キー制約」を利用します。

 外部キー制約を設定することで、参照する側(この場合はorder_itemテーブルのitemカラム)に挿入されるデータを、参照される側(itemテーブルのidカラム)に存在しているデータのみに限定することができます。

 また、参照される側(itemテーブルのidカラム)の値を変更した場合には、エラーを表示するか、参照する側のデータを同様の値で更新またはNULL値を挿入することができます。同様に、参照される側(itemテーブルのidカラム)の値が削除される場合も、エラーを表示するか、参照する側のデータを削除またはNULL値を挿入することができます。

 外部キーの設定では「参照される側/する側」の関係を「親/子」に例えて説明することができます。参照する側(order_itemテーブル)と、参照される側(order_main、itemテーブル)の関係は次のようになります。

図1 外部キー制約は親子の関係に例えられる 図1 外部キー制約は親子の関係に例えられる

Copyright © ITmedia, Inc. All Rights Reserved.

RSSについて

アイティメディアIDについて

メールマガジン登録

@ITのメールマガジンは、 もちろん、すべて無料です。ぜひメールマガジンをご購読ください。