Oracle の参照整合性制約

テーブル間でデータの整合性を保つために、関連付けを行う参照整合性制約がある。
親テーブルに従属する子テーブルに定義されている参照列を外部キー(Foreign Key)という。
主キーを PK と省略して言うように、よく外部キーのことを FK とも言われる。

指定方法

子テーブルの関連データに対するオプションとして、
on delete cascade や on delete set null がある。
オプションの使い方を間違えると消失リスクもあるので、ご注意を。

on 句なし

参照している子テーブルの該当行がある場合、親テーブルの行を削除できない。

on delete cascade

親テーブルの行が削除される場合、参照している子テーブルの該当行も削除される。

on delete set null

親テーブルの行が削除される場合、参照している子テーブルの該当行は null に更新される。

検証データ

ここでは部門マスタ(bumon)に登録されている3部が廃止され、
所属社員は1部、2部に配属されることを想定している。

/* 部門マスタ */
create table bumon(
  code char(2) primary key,
  bname varchar2(10)
);

insert into bumon values('01', '1部');
insert into bumon values('02', '2部');
insert into bumon values('03', '3部');
commit;

/* 社員マスタ */
create table syain(
  id char(5) primary key,
  uname varchar2(10),
  bcode char(2)
);

insert into syain values('10001', '社員1', '01');
insert into syain values('10002', '社員2', '01');
insert into syain values('10003', '社員3', '02');
insert into syain values('10004', '社員4', '02');
insert into syain values('10005', '社員5', '03');
insert into syain values('10006', '社員6', '03');
commit;

オプションによる違い

on 句なし

SQL> alter table syain add constraint fk foreign key (bcode) references bumon (code);
表が変更されました。
SQL> delete from bumon where code = '03';
ORA-02292: 整合性制約(HOGE.FK)に違反しました - 子レコードがあります

→ 従属する子レコード(ID:10005、10006)が存在するためエラーとなる。

-- 一旦、制約を削除
SQL> alter table syain drop constraint fk;
表が変更されました。

on delete set null

SQL> alter table syain add constraint fk foreign key (bcode) 
     references bumon (code) on delete set null;
表が変更されました。
SQL> delete from bumon where code = '03';
1行が削除されました。

→ 削除成功(親)

SQL> select * from bumon;

CODE BNAME
---- --------------------
01   1部
02   2部
SQL> select * from syain;
ID         UNAME          BCOD
---------- -------------- ----
10001      社員1          01
10002      社員2          01
10003      社員3          02
10004      社員4          02

→ 従属する子レコード(ID:10005、10006)が削除された。

-- 一旦、制約を削除
SQL> alter table syain drop constraint fk;
表が変更されました。

on delete cascade

SQL> alter table syain add constraint fk foreign key (bcode) 
     references bumon (code) on delete cascade;
表が変更されました。
SQL> delete from bumon where code = '03';
1行が削除されました。

→ 削除成功(親)

SQL> select * from bumon;

CODE BNAME
---- --------------------
01   1部
02   2部
SQL> select * from syain;
ID         UNAME          BCOD
---------- -------------- ----
10001      社員1          01
10002      社員2          01
10003      社員3          02
10004      社員4          02

→ 従属する子レコード(ID:10005、10006)が削除された。

-- 制約を削除
SQL> alter table syain drop constraint fk;
表が変更されました。

まとめ

on 句を指定する場合、対象行を NULL にするのか Update するのか、
削除してしまうのか、要件に応じて使い分ける必要がある。

この例では部が統合されただけなので、on delete cascade は妥当でない。
(社員5、6がクビということになってしまう!)

本来なら、以下のような方法を検討する。

on 句なしの場合

予め、社員マスタの対象行の bcode を ’01’か’02’に Update しておき、
部門マスタから3部を削除する。(従属対象行が0件)

on delete set null の場合

部門マスタから3部を削除した後、NULL がセットされた
社員マスタの対象行の bcode を ’01’か’02’に Update する。

関連記事