外部キーの作成 (SQL の場合)

Interactive SQL では、CREATE TABLE 文と ALTER TABLE 文を使用して、外部キーを作成、修正できます。これらの文によって、カラムの制約や検査など、多くのテーブル属性を設定できます。

前提条件

テーブルの所有者に基づいて外部キーを作成するのに必要な権限は次のとおりです。

参照されるテーブル (プライマリキー) と参照するテーブル (外部キー) を所有 権限は必要ありません。
参照されるテーブルではなく、参照するテーブルを所有 そのテーブルに対する REFERENCES 権限を持っているか、または CREATE ANY INDEX と CREATE ANY OBJECT のどちらか一方のシステム権限を持っていることが必要です。
参照するテーブルではなく、参照されるテーブルを所有
- ALTER ANY OBJECT または ALTER ANY TABLE のシステム権限が必要です。
- あるいは、そのテーブルに対する ALTER 権限に加え、COMMENT ANY OBJECT、CREATE ANY OBJECT、または CREATE ANY TABLE のシステム権限が必要です。
- さらに、そのテーブルに対する SELECT 権限または SELECT ANY TABLE システム権限も必要です。
どちらのテーブルも所有していない
- そのテーブルに対する REFERENCES 権限を持っているか、または CREATE ANY INDEX と CREATE ANY OBJECT のどちらか一方のシステム権限を持っていることが必要です。
- ALTER ANY OBJECT または ALTER ANY TABLE のシステム権限が必要です。
- あるいは、そのテーブルに対する ALTER 権限に加え、COMMENT ANY OBJECT、CREATE ANY OBJECT、または CREATE ANY TABLE のシステム権限が必要です。
- さらに、そのテーブルに対する SELECT 権限または SELECT ANY TABLE システム権限も必要です。

そのテーブルに対する SELECT 権限または SELECT ANY TABLE システム権限が必要です。

さらに、そのテーブルの所有者であるか、または次のいずれかの権限を持っていることが必要です。

そのテーブルに対する ALTER 権限に加え、COMMENT ANY OBJECT、CREATE ANY OBJECT、または CREATE ANY TABLE のシステム権限
ALTER ANY TABLE システム権限
ALTER ANY OBJECT システム権限

内容と備考

外部テーブルの作成時に外部キーを作成する必要はありません。外部キーは自動的に作成されます。

♦ タスク

データベースに接続します。

次のような ALTER TABLE 文を実行します。

ALTER TABLE table-name ADD FOREIGN KEY foreign-key-name 
( column-name ASC ) REFERENCES table-name ( column-name )

結果

テーブルが更新され、その外部キー定義がテーブルに含まれます。

例

次の例では、スキルの一覧を格納する Skills というテーブルを作成し、次に Skills テーブルに対して外部キーの関係を持つ EmployeeSkills というテーブルを作成します。EmployeeSkills.SkillID は、Skills テーブルのプライマリキーカラム (Id) と外部キーの関係があります。

CREATE TABLE Skills (
   Id INTEGER PRIMARY KEY,
   SkillName CHAR(40),
   Description CHAR(100) 
);
CREATE TABLE EmployeeSkills (
   EmployeeID INTEGER NOT NULL,
   SkillID INTEGER NOT NULL,
   SkillLevel INTEGER NOT NULL,
   PRIMARY KEY( EmployeeID ),
   FOREIGN KEY (SkillID) REFERENCES Skills ( Id )
);

テーブルを作成した後で、ALTER TABLE 文を使用して外部キーを追加することもできます。次の例では、前の例で作成したテーブルに似たテーブルを作成します。ただし、テーブルの作成後に外部キーを追加します。

CREATE TABLE Skills2 (
   ID INTEGER PRIMARY KEY,
   SkillName CHAR(40),
   Description CHAR(100) 
);
CREATE TABLE EmployeeSkills2 (
   EmployeeID INTEGER NOT NULL,
   SkillID INTEGER NOT NULL,
   SkillLevel INTEGER NOT NULL,
   PRIMARY KEY( EmployeeID ),
);
ALTER TABLE EmployeeSkills2
   ADD FOREIGN KEY SkillFK ( SkillID )
   REFERENCES Skills2 ( ID );

外部キーを作成するとき、そのプロパティを指定できます。たとえば、次の文は例 2 と同じ外部キーを作成しますが、この外部キーは、データの更新または削除に対する制限がある NOT NULL として定義されています。

ALTER TABLE Skills2
ADD NOT NULL FOREIGN KEY SkillFK ( SkillID )
REFERENCES Skills2 ( ID )
ON UPDATE RESTRICT
ON DELETE RESTRICT;

外部キーカラム名とプライマリキーのカラム名とは、1 対 1 で対応する 2 つのリスト位置に従ってペアになります。外部キーの定義時にプライマリテーブルのカラム名が指定されていない場合、プライマリキーカラムが使用されます。たとえば、次のようにして 2 つのテーブルを作成するとします。

CREATE TABLE Table1( a INT, b INT, c INT, PRIMARY KEY ( a, b ) );
CREATE TABLE Table2( x INT, y INT, z INT, PRIMARY KEY ( x, y ) );

次に、外部キー fk1 を作成し、2 つのテーブル間でのカラムのペア方法を厳密に指定します。

ALTER TABLE Table2 ADD FOREIGN KEY fk1( x,y ) REFERENCES Table1( a, b );

次の文を使用して、外部テーブルカラムのみを指定することで、2 つ目の外部キー fk2 を作成します。データベースサーバは、これらの 2 つのカラムを、プライマリテーブル上のプライマリキーにある最初の 2 つのカラムと自動的にペアにします。

ALTER TABLE Table2 ADD FOREIGN KEY fk2( x, y ) REFERENCES Table1;

次の文を使用して、プライマリテーブルと外部テーブルのいずれに対してもカラムを指定せずに外部キーを作成します。

ALTER TABLE Table2 ADD FOREIGN KEY fk3 REFERENCES Table1;

参照元カラムを指定していないため、データベースサーバは、プライマリテーブル (Table1) のカラムと同じ名前を持つカラムを外部テーブル (Table2) で検索します。同じ名前のカラムが存在する場合、データベースサーバはデータ型が一致することを確認し、それらのカラムを使用して外部キーを作成します。カラムが存在しない場合は、Table2 に作成されます。この例では、Table2 には a や b というカラムが存在しないため、Table1.a および Table1.b と同じデータ型で作成されます。このように自動的に作成されたカラムは、外部テーブルのプライマリーキーの一部になることはできません。

参照

DocCommentXchange で意見交換できます