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 する。
