FOREIGN KEY(外部キー)制約で悩んだら、ここを読め

FOREIGN KEY(外部キー)制約は、列に格納されているデータが親表に存在することを確約する制約です。親表にないデータは子表には格納できません。そのため、親表にないデータを含んだレコードをINSERTしたり、UPDATEしたりするとエラーになります。一方で親表データを削除しようとするときに、そのデータが子表に入っていると親表データは削除できません。エラーになります。このように2つ以上のテーブルのデータを密接に関連付ける制約がFOREIGN KEY制約になります。

覚えるべきキーワード

・CONSTRAINT 制約名 FOREIGN KEY(子列名) REFERENCES 親表(親列名)

・CONSTRAINT 制約名 REFERENCES 親表(親列名)

・ON DELETE CASCADE

・ON DELETE SET NULL

まずは制約を作るための構文です。FOREIGN KEY制約は列レベル制約と表レベル制約で書き方が違います。他の制約は書く場所は違いますが、書き方同じだったはずですが、なぜかFOREIGN KEY制約だけ違います。

CONSTRAINT 制約名 FOREIGN KEY(子列名) REFERENCES 親表(親列名)

まずは表レベルの書き方です。表レベルのキーワードはFOREIGN KEYとREFERENCESです。

CONSTRAINT 制約名 FOREIGN KEY(子列名) REFERENCES 親表(親列名)
CREATE TABLE employee
(id NUMBER(4),
name VARCHAR2(40),
CONSTRAINT emp_id_fk FOREIGN KEY(id) REFERENCES department(deptno));

employeeテーブルにNUMBER型で4桁までのid列とVARCHAR2型で40バイトまでの
name列を作成し、id列はemp_id_fkという制約名でFOREIGN KEY制約を指定しています。親表はdepartment表のdeptnoです。CONSTRAINTから制約を指定するまでの書き方は他の制約と大差ありませんが、その後REFERENCESの後に親表の参照列を指定します。REFERENCESは、参照という意味で、言葉の通りに参照する親表の列を記載します。

CONSTRAINT 制約名 REFERENCES 親表(親列名)

列レベルのFOREIGN KEY制約の書き方です。表レベルのキーワードはREFERENCESです。

CONSTRAINT 制約名 REFERENCES 親表(親列名)
CREATE TABLE employee
(id NUMBER(4) CONSTRAINT emp_id_fk REFERENCES department(deptno),
name VARCHAR2(40));

employeeテーブルにNUMBER型で4桁までのid列とVARCHAR2型で40バイトまでの
name列を作成し、id列はemp_id_fkという制約名でFOREIGN KEY制約を指定しています。親表はdepartment表のdeptnoです。列レベル制約では制約名の後にFOREIGN KEY(列名)を記載せずに、REFERENCESで親表の参照列を指定します。

このようにFOREIGN KEY制約は表レベル制約と列レベル制約で書き方が異なります。

FOREIGN KEY制約を指定するルール

FOREIGN KEY制約を指定するにはルールがあります。
ルール①親表の列はPRIMARY KEY制約もしくは、UNIQUE制約である
親表に指定する列はPRIMARY KEY制約か、UNIQUE制約が指定されている列である必要があります。親表列データが一意じゃないと、子表の列はどの親表列データを参照しているか分かりませんからね。

ルール②親表にないデータは子表に保存できないが、NULLは別である
親表にNULLが保存されていなくても子表では保存ができます。NULLは別です。PRIMARY KEYを指定している親表列であったとしても子表列ではNULLが保存できます。

ルール③ON DELETE CASCADEやON DELETE SET NULLを設定すれば、子表にデータがある親表のデータであっても削除できる

冒頭に申しました通り、原則、FOREIGN KEY制約では、親表データを削除しようとするときに、そのデータが子表に入っていると親表データは削除できません。しかしながら、子表データを削除、もしくはNULLにして良いなら、親表データは削除できます。

ON DELETE CASCADE

FOREIGN KEY制約を指定する時にON DELETE CASCADEも一緒に指定すれば、子表に親表を参照しているデータがあったとしても、その親表データは削除ができます。ただし、その場合は参照していた子表のデータも一緒に削除されます。ON DELETE CASCADEを表レベルで行ったCREATE文です。

ON DELETE CASCADE
CREATE TABLE employee
(id NUMBER(4),
name VARCHAR2(40),
CONSTRAINT emp_id_fk FOREIGN KEY(id) REFERENCES department(deptno) ON DELETE CASCADE);

REFERENCESでdepartment表のdeptnoを親表列に指定しています。その後に、ON DELETE CASCADEを書いていますので、仮にemployee表のidにdepartment表のdeptnoと同じデータがあった場合でもdeptnoのそのデータは削除ができます。ただし、employee表のidに保存されていた同じデータも一緒に削除されます。

ON DELETE SET NULL

FOREIGN KEY制約を指定する時にON DELETE SET NULLも一緒に指定すれば、子表に親表を参照しているデータがあったとしても、その親表データは削除ができます。ただし、その場合は参照していた子表のデータは親表データ削除と共にNULLになります。ON DELETE SET NULLを表レベルで行ったCREATE文です。

ON DELETE SET NULL
CREATE TABLE employee
(id NUMBER(4),
name VARCHAR2(40),
CONSTRAINT emp_id_fk FOREIGN KEY(id) REFERENCES department(deptno) ON DELETE SET NULL);

REFERENCESでdepartment表のdeptnoを親表列に指定しています。その後に、ON DELETE SET NULLを書いていますので、仮にemployee表のidにdepartment表のdeptnoと同じデータがあった場合でもdeptnoのそのデータは削除ができます。ただし、employee表のidに保存されていた同じデータはNULLに変更されます。

FOREIGN KEY制約は実際のシステム開発でも良く出てきます。表のデータを削除したいのにできない!という場面が発生したら、一度オブジェクト情報を取得して、制約を見てみましょう。案外、FOREIGN KEY制約が原因だったりします。

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です