外部キー

出典: フリー百科事典『ウィキペディア（Wikipedia）』

外部キー (がいぶ—、FK、英: foreign key) は、コンピュータの関係データベースの関係モデルの文脈において、2つの関係変数 (テーブル) の間の参照整合性制約をいう。外部キーは、参照する側の関係変数で1つの属性 (列、カラム) もしくは2つ以上の属性の集合を同定し、参照する側の関係変数は、参照される側の関係変数の1つの属性もしくは2つ以上の属性の集合を参照する。参照する側の関係変数の属性の1つの組 (タプル、行) の値は、参照される側の関係変数の組の値として出現しなければならない。このため、参照する側の関係変数の組には、参照される側の関係変数に現れない値を、含むことはできない。このような参照関係は、2つの情報の関連づけるために作成され、関係の正規化の本質的な部分をなしている。参照する側の関係変数の複数の組が、参照される側の関係変数の同一の組を、参照することができる。

参照する側の関係変数と参照される側の関係変数が、同一の関係変数であっても良い。すなわちこの場合は外部キーは自分自身の関係変数を参照する。このような外部キーは、データベース言語標準 SQL:2003 では「自己参照」外部キーあるいは「再帰的」外部キーとして規定されている。

一つの関係変数には複数の外部キーを含むことができる。そしてこのような外部キーのそれぞれが異なる関係変数を参照する。各々の外部キーは関係データベース管理システム (RDBMS) によりそれぞれ別個に強制適用される。このため、関係変数間の連鎖した関連を外部キーを使って定義することができる。

不適切な外部キー/主キー関連や外部キー/主キー関連の非強制は、データベースおよびデータモデリングについての多くの問題の原因となっていることが多い。

[編集] 外部キーを定義する

外部キーは、データベース言語 ANSI/ISO SQL 標準において、FOREIGN KEY 制約として規定されている。既に存在する関係変数 (テーブル) に外部キー制約を追加する構文は SQL:2003 で次のように規定されている。なお、属性 (column) のリストを REFERENCES 句で省略すると、外部キーは参照される側の関係変数の主キーを参照することを暗黙に示す。

ALTER TABLE <table identifier> 
   ADD [ CONSTRAINT <constraint identifier> ] 
      FOREIGN KEY ( <column expression> {, <column expression>}... )
      REFERENCES <table identifier> [ ( <column expression> {, <column expression>}... ) ]
      [ ON UPDATE <referential action> ]
      [ ON DELETE <referential action> ]

同様に、外部キーは CREATE TABLE SQL 文の一部としても定義することができる。

CREATE TABLE table_name (
   id    INTEGER  PRIMARY KEY,
   col2  CHARACTER VARYING(20),
   col3  INTEGER,
   ...
   CONSTRAINT col3_fk FOREIGN KEY(col3)
      REFERENCING other_table(UNIQUE(key_col) ON DELETE CASCADE,
   ... )

外部キーが単一の属性だけから構成される場合は、その属性は次の構文を使うことにより外部キーと指定することもできる。

CREATE TABLE table_name (
   id    INTEGER  PRIMARY KEY,
   col2  CHARACTER VARYING(20),
   col3  INTEGER FOREIGN KEY REFERENCES other_table(column_name),
   ... )

[編集] 参照操作

関係データベース管理システム (RDBMS) あるいは SQL データベース管理システム (SQL DBMS) は参照整合性制約を強制適用するため、DBMS は参照される側の関係変数 (テーブル) の組 (タプル、行) が削除 (あるいは更新) される場合でも、データ整合性を維持しなければならない。その際、参照する側の関係変数に組が残っている場合、参照整合性はよく考慮しなければならない。データベース言語標準 SQL:2003 ではそのような場合に発生させる5種類の参照操作を規定している。

CASCADE
RESTRICT
NO ACTION
SET NULL
SET DEFAULT

[編集] CASCADE

参照される側の関係変数の組が削除された場合、参照する側の関係変数の対応するすべての組は削除される。同様に、参照される側の関係変数の組が更新された場合、参照する側の関係変数の外部キーの値は同じ値に更新される。

[編集] RESTRICT

参照する側の関係変数の組が残っている場合は、参照される側の組は更新することも削除することもできない。この場合、データの変更は全く行われない。

[編集] NO ACTION

参照される側の関係変数において UPDATE あるいは DELETE SQL 文が実行される。 DBMS は SQL 文の実行の終了時に参照整合性が満たされているかどうかを検査する。 RESTRICT との大きな違いは、トリガもしくはSQL文のセマンティクス自体が外部キーの制約を満たすであろうということである。このとき、SQL 文の実行は成功する。外部キーの制約が満たされない場合は SQL 文の実行は失敗する。

[編集] SET NULL

参照される側の関係変数において組が更新もしくは削除された場合、参照する側の関係変数の組の外部キーの値にはNULLが設定される。このオプションは参照する側の関係変数の外部キーにNULLを設定できる場合にのみ、定義可能である。 NULLのセマンティクスにより、参照する側の関係変数においてNULLのある組は、参照される側の関係変数の組を必要としない。

[編集] SET DEFAULT

SET NULL と似ているが、参照される側の関係変数の組が更新あるいは削除された場合、参照する側の関係変数の外部キーの値は属性の既定値 (デフォルト値) が設定される。

[編集] 例1

外部キーについて説明するための最初の例として、会計データベースがありその中に請求書関係変数 (請求書テーブル) があり、請求書関係変数の中の各々の請求書は個別の供給者 (サプライヤ) と関連づけられているとする。供給者の詳細 (住所など) の情報は供給者関係変数に格納されているとする。各々の供給者には、識別子として供給者番号 (SupplierNumber) が割り当てられている。各々の請求書は、その請求書の供給者番号を属性値としてもつ。供給者番号 (SupplierNumber) は供給者関係変数の主キーである。請求書関係変数の外部キーは、供給者関係変数の主キーを指し示す (参照する) 。関係モデルのスキーマは次のようになる。主キーは太字で示す。

  Supplier ( SupplierNumber, Name, Address, Type )
  Invoices ( InvoiceNumber, SupplierNumber, Text )

このスキーマに対応するデータ定義言語 (DDL) による定義は次のようになる。

 CREATE TABLE Supplier (
    SupplierNumber  INTEGER NOT NULL,
    Name            VARCHAR(20) NOT NULL,
    Address         VARCHAR(50) NOT NULL,
    Type            VARCHAR(10),
    CONSTRAINT supplier_pk PRIMARY KEY(SupplierNumber),
    CONSTRAINT number_value CHECK (SupplierNumber > 0) )
 
 CREATE TABLE Invoices (
    InvoiceNumber   INTEGER PRIMARY KEY,
    SupplierNumber  INTEGER NOT NULL,
    Text            VARCHAR(4096),
    CONSTRAINT invoice_pk PRIMARY KEY(InvoiceNumber),
    CONSTRAINT inumber_value CHECK (InvoiceNumber > 0),
    CONSTRAINT supplier_fk FOREIGN KEY(SupplierNumber)
       REFERENCES Supplier(SupplierNumber)
       ON UPDATE CASCADE ON DELETE RESTRICT )

[編集] 例2

ある企業にはいくつかの部署があり、その各々の社員はいずれかの部署に属している。この事象は、関係データベースにおいては社員関係変数 (社員テーブル) の外部キーにより強制適用されている。この外部キーは部署関係変数を参照している。部署関係変数の各々の部署は、部署名称をもち "部署ID" というその部署の一意識別子をもつ。社員関係変数は "部署ID" を属性としてもち、"部署ID" 属性は外部キーとして前述の部署関係変数の "部署ID" を参照する。関係データベース管理システム (RDBMS) あるいは SQL データベース管理システム (SQL DBMS) は、各々の社員の情報が部署関係変数の部署を参照せずに作成されることを強制的に防ぎ、部署関係変数のある部署を参照する社員が存在するときにその部署が削除されることを強制的に防ぐ。