外部制約・外部キー制約
表のキーの値について、関連する表のキーの値と一致する場合にデータとして許可する。
表と表の論理的な関連付けを行い外部キーに対して値（親キー）が存在する事を保証する。
外部キーには、親表の値もしくはNULLをもつことが可能。
外部キーの親キーは主キーもしくは一意キーでなければならない。
親レコードと子レコードの関係で、レコード削除テストをしてみた。
/* 部門マスタ */
create table bumon(
code char(2) primary key,
bname varchar2(10)
);/* 社員マスタ */
create table syain(
id char(5) primary key,
uname varchar2(10),
bcode char(2)
);insert into bumon values('01', '1部');
insert into bumon values('02', '2部');
insert into bumon values('03', '3部');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句なし
--FK作成
alter table syain add constraint fk foreign key(bcode) references bumon(code);
--code03を削除してみる。
delete from bumon where code = '03';
--エラーが発生した。参照性制約違反とのこと。
子レコードが存在する場合、親レコードは削除できないのですね。
ORA-02292: integrity constraint (SQL_KATKKKGUONSSHTYWBKZNOREUP.FK) violated - child record found
--一旦、制約削除
alter table syain drop constraint fk;
- on delete set null
--制約作成
alter table syain add constraint fk foreign key (bcode)
references bumon (code) on delete set null;
delete from bumon where code='03';
1 row(s) deleted.--親レコードが削除できた^^
親テーブルと子テーブル確認。親テーブル削除すると子テーブルのレコードはnullになっている。
select * from bumon;
select * from syain;
CODE BNAME
01 1部
02 2部
2 rows selected.
ID UNAME BCODE
10001 社員1 01
10002 社員2 01
10003 社員3 -
10004 社員4 -
10005 社員5 -
10006 社員6 -
6 rows selected.
--一旦、制約削除
alter table syain drop constraint fk;
on delete cascade
--制約作成
alter table syain add constraint fk foreign key (bcode)
references bumon (code) on delete cascade;--レコード削除
delete from bumon where code='03';
--親レコード削除すると、親レコードも子レコードも削除されていることを確認。
select * from bumon;
select * from syain;CODE BNAME
01 1部
02 2部2 rows selected.
ID UNAME BCODE
10001 社員1 01
10002 社員2 01
10003 社員3 02
10004 社員4 024 rows selected.
