ゼロから始めるPHP講座Vol.36　データベースの結合〜３つ以上のテーブル結合〜

ゼロから始めるPHP講座データーベースの結合３つ以上のテーブル結合

3つ以上のテーブル結合

これまでの結合は2つのテーブルのみでしたが、3つ以上のテーブルも内部結合や外部結合により結合できます。

例としてorder_table、order_detail_table、goods_tableを結合し、全レコードを表示するSQLは以下となります。 最初は全レコード選択のほうが理解しやすいため*(アスタリスク)を使用していますが、本来アスタリスクは非推奨です。

実際にphpMyAdminより実行し確認しましょう。

図で示したように、2つのテーブルをまず結合した後、結合したテーブルと3つ目のテーブルを結合します。

4つ以上結合する場合も考え方は同じです。また今回は内部結合を行いましたが、外部結合で3つ以上のテーブルを結合することも可能です。

外部キー(FOREIGN KEY)

複数テーブルがあった際、1つのテーブルのあるカラムの値が、他のテーブルのカラムに存在する値でなければならない場合、前者のカラムは後者のカラムを参照するといいます。

例えばorder_tableとcustomer_tableの場合、以下のような関係となります。order_tableにあるcustomer_idは、customer_tableにあるcustomer_idに存在する値でなければなりません。

もし存在しなかった場合、2つのテーブルを結合しても商品を発注した顧客の情報が分からないレコードができてしまいます。

よってこのように別なテーブルのカラムの値を参照しているカラムには、外部キーを設定します。

外部キーを設定すると、そのカラムには参照先のカラムにある値しか入力できなくなります。

また参照先に関しても、参照されているカラムの削除ができなくなります。

実際にcustomer_tableのcustomer_idに外部キーを設定してみましょう。order_tableの構造を表示し、以下の手順を行ってください。

※サンプルではインデックスを選択を「その他 → インデックス」としていますが、インデックスがどこに表示されるかは画面サイズにより変わります。

これで外部キーの設定は完了しました。

では実際に外部キー設定による制約を確認してみましょう。顧客番号(customer_id)が1の顧客情報(customer_table)を削除するSQLを作成し実行してください。「#1451 - Cannot delete or update a parent row: a foreign key constraint fails」と表示されました。

これは外部キー(FOREIGN KEY)の制約により、削除又はアップデートができませんというエラー文言になります。

次は参照先のカラムに存在しない値(customer_idが4)を、外部キーに設定してるテーブルに追加してみましょう。「#1452 - Cannot add or update a child row: a foreign key constraint fails」と表示されました。

これは外部キー(foreign key)の制約により、追加又はアップデートができませんというエラー文言になります。

このように外部キーを設定することで、参照する値が存在しないという状態にならないよう制限することができ、これによりデータの整合性を保てます。

外部キーは基本的に主キー(PRIMARY KEY)を参照するカラムに設定します。

正規化の際にテーブルを複数に分けることが何度かあったと思いますが、その際に主キーと参照関係にあるカラムには、外部キーを設定します。

これにより、正規化の目的である「テーブルの整合性を保ったまま、データの冗長性を排除して、データを効率的に管理できるようにすること」をより確かなものにできます。

外部キーについてまとめると以下になります。

外部キー
別テーブルにある主キーを参照しているカラムには、外部キーを設定する

実際に今回外部キーを設定したorder_tableと、参照先のカラムがあるcustomer_tableの関係は次のようになります。

外部キーは設定を忘れがちですが、正規化の際に「主キー&外部キー」とセットで覚えておくようにしましょう。

課題

正規化した下記4つのテーブルに対し、適切な外部キー設定をしてください。外部キーの設定が完了したら、レコード削除などにより外部キーによる制約を確認してください。次と同じ結果を表示するSQLを作成してください。

• 発注に関して、customer_idとgoods_idを除く全情報を取得
• 佐藤一郎さんの発注した商品情報を取得
• コーラの売上情報を取得
• 1回あたりの購入数が多い順に全商品の売上情報を取得

NEXT LESSON ☛ データベースの便利機能

PREV LESSON ☛ データベースの結合〜外部結合〜