FOREIGN KEY制約(外部キー制約を設定する)

テーブルを作成する時にカラムに対して FOREIGN KEY 制約をつけると、対象となるカラムに格納できる値を別のテーブルに格納されているデータに限定することができます。ここでは PostgreSQL における FOREIGN KEY 制約の使い方について解説します。

(Last modified: 2023年01月07日)

外部キー制約の使い方

カラムに対して FOREIGN KEY 制約(外部キー制約)を設定すると、カラムに格納できる値を別に用意したテーブルの指定のカラムに格納されている値に限定することができます。書式は次の通りです。

CREATE TABLE [ IF NOT EXISTS ] table_name (
  column_name data_type
    REFERENCES reftable [ ( refcolumn ) ] [, ... ]
)

外部キー制約を設定するカラムに対して参照されるテーブル( reftable )と参照するカラム( refcolumn )を設定します。

また FOREIGN KEY 制約はカラム毎ではなくテーブルに対して設定することもできます。

CREATE TABLE [ IF NOT EXISTS ] table_name (
  column_name1 data_type1, 
  column_name2 data_type2,
  [... ,]
  FOREIGN KEY ( column_name1 [, ... ] )
    REFERENCES reftable [ ( refcolumn [, ... ] ) ]
)

外部キー制約を設定するカラム( column_name1 )と、参照するテーブル( reftable )と参照するカラム( refcolumn )を設定します。 FOREIGN KEY 制約によって参照されるカラム( refcolumn )には UNIQUE 制約または PRIMARY KEY 制約が設定されている必要があります。

-- --

それでは実際に試してみます。今回は参照される側のテーブルとして department テーブルを作成し部署に関するデータを登録します。そして参照する側のテーブルとして staff テーブルを作成し社員に関するデータを登録します。外部キー制約を使い、 staff テーブルの部署に関するカラムには department テーブルに格納されているデータしか登録できないように設定してみます。

最初に参照される側のテーブルを作成します。 mydb データベースの myschema スキーマの中に次のようなテーブルを作成しました。

create table myschema.department (
  name varchar(10) primary key
);

参照される側のテーブルの参照されるカラムには PRIMARY KEY 制約を設定しています。

department テーブルには次のようなデータを追加しておきました。

insert into myschema.department values
  ('Sales'), 
  ('Accounting'), 
  ('Marketing');

次に参照する側のテーブルを作成します。 mydb データベースの myschema スキーマの中に次のようなテーブルを作成しました。

create table myschema.staff(
  id integer, 
  staffname varchar(10), 
  depname varchar(10), 
  foreign key (depname) references myschema.department(name)
);

外部キー制約を設定するカラムとして depname 、そして参照先として myschema.department テーブルの name カラムを設定してテーブルを作成しています。

psql メタコマンドの \d コマンドを使って作成した参照する側のテーブルのカラムの情報を取得してみます。

欄外にテーブルに設定されている外部キー制約に関する情報が表示されています。

それでは staff テーブルにデータを追加してみます。最初に問題のないデータです。

insert into myschema.staff values
  (1, 'Yamada', 'Sales'), 
  (2, 'Suzuki', 'Marketing'), 
  (3, 'Honda', 'Sales');

staff カラムの depname カラムには外部キー制約が設定されているので、 department テーブルの name カラム格納されている値しか追加することはできません。今回追加した 3 つのデータはすべて問題がないため staff テーブルに正常に追加されました。

次に参照先のカラムに格納されていない値が含まれるデータを staff テーブルに追加してみます。

insert into myschema.staff values
  (4, 'Nakajima', 'Customer');

参照先のカラムにはない 'Customer' という値を外部キー制約が設定されたカラムに格納しようとしたため ERROR: テーブル"staff"への挿入、更新は外部キー制約"staff_depname_fkey"に違反しています とエラーが表示されてデータの追加が失敗しました。

このように外部キー制約を設定することで、カラムに格納できる値を制限することができます。

参照されるテーブルのデータが削除/更新された時の処理

外部キー制約を設定している場合、参照する側のテーブルには参照される側のテーブルに格納されているデータしか追加することができません。参照する側のテーブルにデータが追加されている状態で、参照される側のテーブルのデータが削除されたり更新されたりすると 2 つのテーブルの間で整合性が取れなくなります。

そこで参照される側のテーブルのデータが更新されたり削除されたりしたときに、参照する側のテーブルのデータをどうするのかを設定することができます。次の書式を使用します。

CREATE TABLE [ IF NOT EXISTS ] table_name (
  column_name data_type
    REFERENCES reftable [ ( refcolumn ) ] 
    [ ON DELETE action ] [ ON UPDATE action ]
)

参照される側のテーブルが削除された場合の動作を設定するのが ON DELETE action 、更新された時の動作を設定するのが ON UPDATE action です。

カラム単位ではなくテーブルに対して外部キー制約を設定している場合も同様に設定できます。

CREATE TABLE [ IF NOT EXISTS ] table_name (
  column_name1 data_type1, 
  column_name2 data_type2,
  [... ,]
  FOREIGN KEY ( column_name1 [, ... ] )
    REFERENCES reftable [ ( refcolumn [, ... ] ) ]
    [ ON DELETE action ] [ ON UPDATE action ]
)

それぞれに対して action に設定できる値は次の通りです。

それではこの中から NO ACTION とCASCADE を設定した場合について試してみます。なお特に指定しなかった場合は NO ACTION が設定されたものとして扱われます。

NO ACTION

最初に NO ACTION を指定した場合です。参照元のテーブルからデータが削除されたり更新された場合、そのデータが実際に参照されていた場合にはエラーが表示されます。

参照される側のテーブルは前回と同じです。次のようなデータを格納してあります。

参照する側のテーブルは ON DELETE および ON UPDATE ともに NO ACTION を指定して次のように作成しました。

create table myschema.staff(
  id integer, 
  staffname varchar(10), 
  depname varchar(10), 
  foreign key (depname) references myschema.department(name)
    on delete no action
    on update no action
);

myschema.staff テーブルには次のようなデータを格納してあります。

それでは参照される側の department テーブルのデータを 1 つ削除してみます。

delete from myschema.department
  where name = 'Marketing';

削除しようとしたデータは staff テーブルから参照されていたため ERROR: テーブル"department"の更新または削除は、テーブル"staff"の外部キー制約"staff_depname_fkey"に違反します というエラーが表示されてデータの削除に失敗しました。

それでは参照される側の department テーブルのデータを 1 つ新しい値に更新してみます。

update myschema.department set name = 'Publicity'
  where name = 'Marketing';

更新しようとしたデータは staff テーブルから参照されていたため ERROR: テーブル"department"の更新または削除は、テーブル"staff"の外部キー制約"staff_depname_fkey"に違反します というエラーが表示されてデータの削除に失敗しました。

CASCADE

次に CASCADE を指定した場合です。参照元のテーブルからデータが削除された場合、参照しているデータも削除されます。参照元のデータが更新された場合、参照しているデータも同じ値に更新されます。

参照される側のテーブルは前回と同じです。次のようなデータを格納してあります。

参照する側のテーブルは ON DELETE および ON UPDATE ともに NO ACTION を指定して次のように作成しました。

create table myschema.staff(
  id integer, 
  staffname varchar(10), 
  depname varchar(10), 
  foreign key (depname) references myschema.department(name)
    on delete cascade
    on update cascade
);

myschema.staff テーブルには次のようなデータを格納してあります。

それでは参照される側の department テーブルのデータを 1 つ削除してみます。

delete from myschema.department
  where name = 'Marketing';

データの削除が完了しました。今回は参照側の staff テーブルの ON DELETE に CASCADE が指定されているので、参照される側のテーブルで削除された値を参照していたデータが staff テーブルからも自動で削除されています。それでは staff テーブルのデータを再度取得してください。

参照元のテーブルからもデータが削除されているのが確認できました。

それでは次に参照される側の department テーブルのデータを 1 つ新しい値に更新してみます。

update myschema.department set name = 'Finance'
  where name = 'Accounting';

データの更新が完了しました。今回は参照側の staff テーブルの ON UPDATE に CASCADE が指定されているので、参照される側のテーブルで更新された値を参照していたデータが staff テーブルでも同じ値に更新されています。それでは staff テーブルのデータを再度取得してください。

参照元のテーブルのデータも更新されているのが確認できました。

-- --

FOREIGN KEY 制約の使い方について解説しました。

著者 / TATSUO IKURA

これから IT 関連の知識を学ばれる方を対象に、色々な言語でのプログラミング方法や関連する技術、開発環境構築などに関する解説サイトを運営しています。