主キーと外部キーによる参照

 リレーションシップとは、列の間の参照に関して設定するものであることを述べました。
 その参照というのは基本的に主キーと外部キーの関係を指しており、Excelの関数のようにどこか別のセルを指定して値を引用することとは意味合いが違います。ここでは、具体的な設定を説明する前に、リレーションシップの設定の対象となる”主キーと外部キーによる参照”の例を見ていきます。

 はじめの例として、主キーの節で現れたテーブルを再掲します。メーカーテーブルの主キーはメーカーコード、商品テーブルの主キーは商品コードとなっています。

メーカーテーブル
メーカーコードメーカー名
1篠山飲料
2山本フーズ
3東亜ビバレッジ

商品テーブル
商品コードメーカーコード商品名
A000011100%ドリアンジュース
A000021ホット専用冬のコーラ
A000032半生茶
A000042ミラクルエナジードレイン
A000053水素水コーヒー
A000063荒川深層水
A000073はちみつメロン

 再度説明しますが、この例では、メーカーテーブルだけでなく、商品テーブルにもメーカーコードという列があり、両方のテーブルで同じ値を記録しています。これの意味するところは明らかですが、商品テーブル側のメーカーコードはその商品を製造したメーカーを表しており、それがどのような会社かを知りたい場合は、メーカーコードをもとにメーカーテーブルを参照することによりメーカーをただ1つに特定し、その情報を得ることができるというわけです。つまり、商品テーブルのメーカーコードはメーカーテーブルのメーカーコードを参照している、というわけです。
 このとき、商品テーブル側のメーカーコードを外部キーといいます。なお、メーカーテーブル側のメーカーコードは主キーとなっていますので同じ値は1つしか現れませんが、外部キーである商品テーブル側のメーカーコードは主キーではなく、同じ値が何度も現れます。1つのメーカーは一般に多数の商品を作っているので、これは常識に照らして当然の結果といえます。
 ただし、このようなテーブルを作っただけでは、両者に関係があるものと解釈できる、というだけにすぎません。実際に、外部キーとして主キーの値と整合しない値が記録される恐れがあり、そうなった場合には(例えばメーカーテーブルのメーカーコードとして1,2,3という値しか存在しないにもかかわらず、商品テーブルのメーカーコードとして7とか15などといった値が記録されたとしたら)両方のテーブルに矛盾が生じ、本当にメーカーとその商品の関係を記録しているものなのかどうか判断できない状態となってしまいます。そこで、後で見るように、これらの列の間でリレーションシップの設定を行うことにより、主キーと外部キーによる参照を具体的に定義づけ、テーブル間の矛盾を防ぐことができるというわけです。
 なお、後で改めて触れますが、正確には主キーと外部キーの間以外にもリレーションシップは設定できます。しかし、主キーと外部キーの間に設定することが基本であり、それ以外の列どうしに設定する必要性はあまりありません。

 ところで、次の例はどうでしょうか。

時間外勤務テーブル
社員番号年月日開始時刻終了時刻
12014/10/717:0020:00
12014/10/1017:0018:00
32014/10/2117:0021:00
32014/10/317:0022:30
52014/10/217:0019:00
52014/10/1413:0017:00

所有資格テーブル
社員番号資格コード取得年
1A1022008
3B2022011
3F3112010
4G1012012
5C2102014

時間外勤務テーブルの主キーは{社員番号,年月日}、所有資格テーブルの主キーは{社員番号,資格コード}です。どちらのテーブルにも社員番号という列があり、同じ番号であれば同じ社員を指しています。
 さて、確かに両方のテーブルに共通する社員番号が現れています(1,3,5)。しかし、それはたまたまです。残業した社員が何か資格を持っている必然性はありませんし、逆に資格を持っている社員が必ず残業をするという必然性もありません。つまり両方のテーブルにある社員番号の値の整合性というものを考慮する必要がないのです。このような列は参照関係にあるとはいえず、リレーションシップの設定を行う意味もありません。
 ただし、次のようになると少し様子が違ってきます。上記の例に「社員テーブル」が加わったものです。

社員テーブル
社員番号氏名性別
1吉田 素子
2小芝 悦郎
3神崎 真由
4駒田 美樹
5篠崎 圭吾

時間外勤務テーブル
社員番号年月日開始時刻終了時刻
12014/10/717:0020:00
12014/10/1017:0018:00
32014/10/2117:0021:00
32014/10/317:0022:30
52014/10/217:0019:00
52014/10/1413:0017:00

所有資格テーブル
社員番号資格コード取得年
1A1022008
3B2022011
3F3112010
4G1012012
5C2102014

時間外勤務テーブルも所有資格テーブルも会社の社員について記録するためにあります。つまり社員テーブルに存在しない者を記録することはありませんので、時間外勤務テーブルに記録される社員番号は社員テーブルの社員番号として存在するものでなければなりませんし、所有資格テーブルの社員番号についても同様です。つまりこれらの列の値には整合性が求められるのです。時間外勤務テーブルの社員番号は社員テーブルの社員番号を参照していますし、所有資格テーブルの社員番号も社員テーブルの社員番号を参照しているということになります。これらはそれぞれリレーションシップの設定に適した列となっています。

 では、次の例はどうでしょうか。契約テーブル(主キーは契約ID)において1件の契約ごとに1人の担当者のコードが記録されており、その値は社員テーブル(主キーは社員コード)の社員コードに相当しています。つまり、1件の契約ごとに1人の社員が担当者として割り当てられていることを表しています。

契約テーブル
契約ID顧客コード担当者コード
10001A02231
10002A60051
10003A02233
10004B51514
10005A61222

社員テーブル
社員コード氏名所属
1木村 康市営業1課
2阿部 隆平営業1課
3紺野 美佳営業2課
4富沢 凜営業2課
5望月 羽海営業2課

この場合、担当者コード、社員コードというように列の名前は異なりますが、社員コードという主キーと担当者コードという外部キーによる参照となっています。これらはリレーションシップの設定に適した列といえます。