リレーションシップとは、列の間の参照に関して設定するものであることを述べました。
その参照というのは基本的に主キーと外部キーの関係を指しており、Excelの関数のようにどこか別のセルを指定して値を引用することとは意味合いが違います。ここでは、具体的な設定を説明する前に、リレーションシップの設定の対象となる”主キーと外部キーによる参照”の例を見ていきます。
はじめの例として、主キーの節で現れたテーブルを再掲します。メーカーテーブルの主キーはメーカーコード、商品テーブルの主キーは商品コードとなっています。
メーカーテーブル
メーカーコード | メーカー名 |
1 | 篠山飲料 |
2 | 山本フーズ |
3 | 東亜ビバレッジ |
商品テーブル
商品コード | メーカーコード | 商品名 |
A00001 | 1 | 100%ドリアンジュース |
A00002 | 1 | ホット専用冬のコーラ |
A00003 | 2 | 半生茶 |
A00004 | 2 | ミラクルエナジードレイン |
A00005 | 3 | 水素水コーヒー |
A00006 | 3 | 荒川深層水 |
A00007 | 3 | はちみつメロン |
再度説明しますが、この例では、メーカーテーブルだけでなく、商品テーブルにもメーカーコードという列があり、両方のテーブルで同じ値を記録しています。これの意味するところは明らかですが、商品テーブル側のメーカーコードはその商品を製造したメーカーを表しており、それがどのような会社かを知りたい場合は、メーカーコードをもとにメーカーテーブルを参照することによりメーカーをただ1つに特定し、その情報を得ることができるというわけです。つまり、商品テーブルのメーカーコードはメーカーテーブルのメーカーコードを参照している、というわけです。
このとき、商品テーブル側のメーカーコードを外部キーといいます。なお、メーカーテーブル側のメーカーコードは主キーとなっていますので同じ値は1つしか現れませんが、外部キーである商品テーブル側のメーカーコードは主キーではなく、同じ値が何度も現れます。1つのメーカーは一般に多数の商品を作っているので、これは常識に照らして当然の結果といえます。
ただし、このようなテーブルを作っただけでは、両者に関係があるものと解釈できる、というだけにすぎません。実際に、外部キーとして主キーの値と整合しない値が記録される恐れがあり、そうなった場合には(例えばメーカーテーブルのメーカーコードとして1,2,3という値しか存在しないにもかかわらず、商品テーブルのメーカーコードとして7とか15などといった値が記録されたとしたら)両方のテーブルに矛盾が生じ、本当にメーカーとその商品の関係を記録しているものなのかどうか判断できない状態となってしまいます。そこで、後で見るように、これらの列の間でリレーションシップの設定を行うことにより、主キーと外部キーによる参照を具体的に定義づけ、テーブル間の矛盾を防ぐことができるというわけです。
なお、後で改めて触れますが、正確には主キーと外部キーの間以外にもリレーションシップは設定できます。しかし、主キーと外部キーの間に設定することが基本であり、それ以外の列どうしに設定する必要性はあまりありません。
ところで、次の例はどうでしょうか。
時間外勤務テーブル
社員番号 | 年月日 | 開始時刻 | 終了時刻 |
1 | 2014/10/7 | 17:00 | 20:00 |
1 | 2014/10/10 | 17:00 | 18:00 |
3 | 2014/10/21 | 17:00 | 21:00 |
3 | 2014/10/3 | 17:00 | 22:30 |
5 | 2014/10/2 | 17:00 | 19:00 |
5 | 2014/10/14 | 13:00 | 17:00 |
所有資格テーブル
社員番号 | 資格コード | 取得年 |
1 | A102 | 2008 |
3 | B202 | 2011 |
3 | F311 | 2010 |
4 | G101 | 2012 |
5 | C210 | 2014 |
時間外勤務テーブルの主キーは{社員番号,年月日}、所有資格テーブルの主キーは{社員番号,資格コード}です。どちらのテーブルにも社員番号という列があり、同じ番号であれば同じ社員を指しています。
さて、確かに両方のテーブルに共通する社員番号が現れています(1,3,5)。しかし、それはたまたまです。残業した社員が何か資格を持っている必然性はありませんし、逆に資格を持っている社員が必ず残業をするという必然性もありません。つまり両方のテーブルにある社員番号の値の整合性というものを考慮する必要がないのです。このような列は参照関係にあるとはいえず、リレーションシップの設定を行う意味もありません。
ただし、次のようになると少し様子が違ってきます。上記の例に「社員テーブル」が加わったものです。
社員テーブル
社員番号 | 氏名 | 性別 |
1 | 吉田 素子 | 女 |
2 | 小芝 悦郎 | 男 |
3 | 神崎 真由 | 女 |
4 | 駒田 美樹 | 女 |
5 | 篠崎 圭吾 | 男 |
時間外勤務テーブル
社員番号 | 年月日 | 開始時刻 | 終了時刻 |
1 | 2014/10/7 | 17:00 | 20:00 |
1 | 2014/10/10 | 17:00 | 18:00 |
3 | 2014/10/21 | 17:00 | 21:00 |
3 | 2014/10/3 | 17:00 | 22:30 |
5 | 2014/10/2 | 17:00 | 19:00 |
5 | 2014/10/14 | 13:00 | 17:00 |
所有資格テーブル
社員番号 | 資格コード | 取得年 |
1 | A102 | 2008 |
3 | B202 | 2011 |
3 | F311 | 2010 |
4 | G101 | 2012 |
5 | C210 | 2014 |
時間外勤務テーブルも所有資格テーブルも会社の社員について記録するためにあります。つまり社員テーブルに存在しない者を記録することはありませんので、時間外勤務テーブルに記録される社員番号は社員テーブルの社員番号として存在するものでなければなりませんし、所有資格テーブルの社員番号についても同様です。つまりこれらの列の値には整合性が求められるのです。時間外勤務テーブルの社員番号は社員テーブルの社員番号を参照していますし、所有資格テーブルの社員番号も社員テーブルの社員番号を参照しているということになります。これらはそれぞれリレーションシップの設定に適した列となっています。
では、次の例はどうでしょうか。契約テーブル(主キーは契約ID)において1件の契約ごとに1人の担当者のコードが記録されており、その値は社員テーブル(主キーは社員コード)の社員コードに相当しています。つまり、1件の契約ごとに1人の社員が担当者として割り当てられていることを表しています。
契約テーブル
契約ID | 顧客コード | 担当者コード |
10001 | A0223 | 1 |
10002 | A6005 | 1 |
10003 | A0223 | 3 |
10004 | B5151 | 4 |
10005 | A6122 | 2 |
社員テーブル
社員コード | 氏名 | 所属 |
1 | 木村 康市 | 営業1課 |
2 | 阿部 隆平 | 営業1課 |
3 | 紺野 美佳 | 営業2課 |
4 | 富沢 凜 | 営業2課 |
5 | 望月 羽海 | 営業2課 |
この場合、担当者コード、社員コードというように列の名前は異なりますが、社員コードという主キーと担当者コードという外部キーによる参照となっています。これらはリレーションシップの設定に適した列といえます。