3.5. 参照整合性と外部キー

3.5.1. 外部キーと親キー

テーブルのある列の値が 1つ残らず、他のテーブルの列に存在しなければならない場合、前者の列は後者の列を参照するといいます。これは 2つの列の意義に直接的な関係があることを示します。

次の 3つの表を例にして具体的に説明します。

受注表、得意先表、商品表の 3つの表を想定します。得意先表では得意先コードにより得意先名が、商品表では商品コードから商品名とその単価が分かります。受注表の得意先コード列と商品コード列が示しているように、受注表の受注番号につき得意先名と商品名は一つに絞られます。したがって、受注表の得意先コード列及び商品コード列は外部キーであり、それを参照する得意先表の得意先コード列と商品表の商品コード列は親キーとなります。

外部キーはどのテーブルのどの行も、親キーを持つテーブルのたった 1つの行を参照することを示します。ただし、複数の外部キーの値が同じ親キーの値を参照する可能性があるため、親キーは一意性を確立する制約、すなわち PRIMARY KEY 制約または UNIQUE 制約を持っていなければなりません。一般的には前者になります。

3.5.2. FOREIGN KEY 制約

SQL は、FOREIGN KEY 制約による参照整合性をサポートしています。この制約は、外部キーと親キーに参照整合性の原則を守らせるために、ユーザがデータベースに入力できる値を制限します。FOREIGN KEY 制約の適用には、親キーにまだ存在しない値をはじくように、外部キー列を制限するという効果があります。

FOREIGN KEY は他の制約と同じように CREATE TABLE 文 (または ALTER TABLE 文) に使用します。FOREIGN KEY 制約には、参照する親キーを指定します。テーブル制約または列制約となり、テーブルの場合は複数の列を 1つの外部キーとして使用することができます。

FOREIGN KEY 制約の構文は次の通りです。

  /*テーブル制約*/
    FOREIGN KEY 列リスト REFERENCES 親テーブル(列リスト) ;

  /*列制約*/
    REFERENCES 親テーブル(列リスト) ;

構文を見て分かるように、列制約では FOREIGN KEY というワードが含まれていないので、REFERENCES 制約とも呼ばれています。ここで、受注表の得意先コードと商品コードに FOREIGN KEY を設定する時の SQL を sample.09-1 に示します。

/* FOREIGN KEYの設定 */
  /*テーブル制約*/
  CREATE TABLE 受注表
  (
     受注番号      INTEGER PRIMARY KEY ,
     得意先コード  CHAR(5) ,
     商品コード    CHAR(4) ,
     受注個数      INTEGER ,
     納品日        CHAR(8) ,
     FOREIGN KEY (得意先コード)
              REFERENCES 得意先表(得意先コード) ,
     FOREIGN KEY (商品コード)
              REFERENCES 商品表(商品コード)
  ) ;

/* FOREIGN KEYの設定 */
  /*列制約*/
  CREATE TABLE 受注表
  (
     受注番号      INTEGER PRIMARY KEY ,
     得意先コード  CHAR(5) 
                REFERENCES 得意先表(得意先コード) ,
     商品コード    CHAR(4) 
                REFERENCES 商品表(商品コード) ,
     受注個数      INTEGER ,
     納品日        CHAR(8)
  ) ;

テーブル制約を用いて、2つの列を外部参照することもできます。今、次のような製品取扱表と販売店取扱表があり、販売店取扱表のメーカー列と製品列が製品表のメーカー列と製品列を外部参照している場合、すなわち販売店取扱表にデータを入力する際に製品表に登録されたメーカーと製品の組合せの入力以外を許可しない場合、SQL では sample09-2 のように記述します。

/* FOREIGN KEYの設定 */
  CREATE TABLE 製品取扱表
  (
     メーカー CHAR(10) ,
     製品     CHAR(10) ,
     PRIMARY KEY (メーカー , 製品)
  ) ;

  CREATE TABLE 販売店取扱表
  (
     販売店   CHAR(10) NOT NULL ,
     メーカー CHAR(10) NOT NULL ,
     製品     CHAR(10) NOT NULL ,
     PRIMARY KEY (販売店 , メーカー , 製品) ,
     FOREIGN KEY (メーカー , 製品)
            REFERENCES 製品取扱表(メーカー , 製品)
  ) ;

実習課題 1

テーブル制約を用いて、あるテーブルの複数の列を参照しなければならない場合とは、どのような場合か? 例をあげて説明しなさい。

解答例はこちら

実習課題 2

3章 4節の実習課題で定義したテーブル employee に次のテーブル制約を加えるための SQL を記述しなさい。

• テーブル office の列 o_num を親キーとする外部キーを列 office に設定する

解答例はこちら