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

広告
eyecatch

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

外部キー制約の使い方

カラムに対して 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
);

外部キー制約の使い方(1)

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

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

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

外部キー制約の使い方(2)

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

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

外部キー制約の使い方(3)

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

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

¥d myschema.staff

外部キー制約の使い方(4)

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

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

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

外部キー制約の使い方(5)

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

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

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

外部キー制約の使い方(6)

参照先のカラムにはない '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
エラーを表示します(デフォルト)

RESTRICT
エラーを表示します(検査が遅延できない点を除き、NO ACTIONと同じ)

CASCADE
削除の場合、削除されたデータを参照しているデータを全て削除
更新の場合、更新されたデータを参照しているデータを新しい値に更新

SET NULL
削除または更新されたデータを参照していたデータを NULL に設定

SET DEFAULT
削除または更新されたデータを参照していたデータをデフォルト値に設定
デフォルト値が NULL でない場合は設定されるデフォルト値に外部キー制約が適用されるので参照元のテーブルにデータが格納されている必要があります

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

NO ACTION

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

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

外部キー制約の使い方/NO ACTION(1)

参照する側のテーブルは 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
);

外部キー制約の使い方/NO ACTION(2)

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

外部キー制約の使い方/NO ACTION(3)

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

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

外部キー制約の使い方/NO ACTION(4)

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

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

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

外部キー制約の使い方/NO ACTION(5)

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

CASCADE

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

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

外部キー制約の使い方/CASCADE(1)

参照する側のテーブルは 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
);

外部キー制約の使い方/CASCADE(2)

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

外部キー制約の使い方/CASCADE(3)

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

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

外部キー制約の使い方/CASCADE(4)

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

外部キー制約の使い方/CASCADE(5)

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

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

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

外部キー制約の使い方/CASCADE(6)

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

外部キー制約の使い方/CASCADE(7)

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

-- --

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

PostgreSQLの使い方の他の記事を見てみる

( Written by Tatsuo Ikura )

関連記事 (一部広告含む)
Profile
profile_img

著者 / TATSUO IKURA

初心者~中級者の方を対象としたプログラミング方法や開発環境の構築の解説を行うサイトの運営を行っています。