1つのテーブルにある複数の外部キーが同じ主キーを参照している場合の参照整合性等の設定

 1つのテーブルに複数の外部キーがあり、どちらも別のテーブルにある同じ主キーを参照している、という場合があります。この場合の参照整合性等の設定についてです。

 右のようなテーブルがあります。各事業所には安全管理担当者と防火担当者というものがそれぞれ設定されています。この担当者というのは…


 右のような社員テーブルの社員番号を指しています。つまり、安全管理担当者と防火担当者というのは共に、社員テーブルの社員番号を参照している外部キーとなっています。
 このときに最初のテーブルの安全管理担当者、防火担当者の各列に対し参照整合性を設定してみましょう。


 (一旦失敗例を示しますので、お急ぎの方は4つ下の画像に進んでください。)
 リレーションシップの画面です。これらの列に対しリレーションシップを2回にわけて設定しようとしても「リレーションシップが既に設定されています。」エラーメッセージが出てうまく設定できません。
 そこで、右のように1度に済ませられないか試してみます。まず参照整合性の設定は抜きにして、リレーションシップが作成できるかどうか試してみます。


 特にエラーも出ず、2本の線がひけました。ここまではうまくいっているっぽいのですが…


 改めて参照整合性を加えようとすると…


 「このリレーションシップでは参照整合性を設定できません。」と表示され、うまくいきません(3つ上の画像のときに参照整合性まで済ませようとしても同じになります)。


 さて、一度ひいた線を削除して元に戻ります。
 正しいやり方は、テーブルの表示ウインドウを開き、社員テーブルを2つ表示させる(1つ追加して表示させる)ことです。


 社員テーブルとは別に「社員テーブル_1」というものが表示されました。


 そして、社員テーブルの社員番号と事業所テーブルの安全管理担当者の間にリレーションシップを設定し、次に社員テーブル_1の社員番号と事業所テーブルの防火担当者の間にリレーションシップを設定していきます。


 無事に設定できました。これで社員テーブルの社員番号と、事業所テーブルの2つの列の整合性が保たれます。


 注意点として、クエリデザインで2つのテーブルを表示したときに右の画面のような結合線が自動的にひかれますが、このままだとデータの抽出がうまくできず、どのような列を指定してもほとんどの場合データシートビューには何も表示されません。


 社員テーブルからそれぞれの担当者の情報を取り出したい場合は、ここでも社員テーブルをもう1つ表示し、結合線を右のように直す必要があります。


 うまくデータの抽出ができました。