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

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

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

　今回のサンプルに必要なデータベース、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テーブル）の関係は次のようになります。