개요
on delete cascade 기능은 자신이 참조하고 있는 테이블의 데이터가 삭제되면 자동으로 자신도 삭제되는 기능입니다.
참고
해당 기능을 사용하면 참조 무결성을 지킬수 있으며 테이블을 생성할 때 기능을 활성화 시킬수 있습니다.
방법
on delete cascade 적용 예시
create table tbl_b (
a char(10) primary key,
n number,
constraints tbl_b_fk foreign key (n) references tbl_a (n) on delete cascade);
SQL
복사
시나리오
drop table tbl_a cascade constraints; drop table tbl_b cascade constraints;
create table tbl_a (
n number primary key);
create table tbl_b (
a char(10) primary key,
n number,
constraints tbl_b_fk foreign key (n) references tbl_a (n) on delete cascade);
insert into tbl_a values (1); insert into tbl_a values (2); insert into tbl_a values (3);
insert into tbl_b values ('a', 1); insert into tbl_b values ('b', 2); insert into tbl_b values ('c', 3);
select * from tbl_a;
select * from tbl_b; delete tbl_a where n=3;
select * from tbl_a;
select * from tbl_b;
SQL
복사
결과
1.
on delete cascade 활성화
SQL> create table tbl_a (
n number primary key); 2
Table 'TBL_A' created.
SQL> create table tbl_b (
a char(10) primary key,
n number,
constraints tbl_b_fk foreign key (n)
references tbl_a (n) on delete cascade); 2 3 4 5
Table 'TBL_B' created.
SQL> insert into tbl_a values (1);
1 row inserted.
SQL> insert into tbl_a values (2);
1 row inserted.
SQL> insert into tbl_a values (3);
1 row inserted.
SQL> insert into tbl_b values ('a', 1);
1 row inserted.
SQL> insert into tbl_b values ('b', 2);
1 row inserted.
SQL> insert into tbl_b values ('c', 3);
1 row inserted.
SQL> select * from tbl_a;
N
----------
1
2
3
3 rows selected.
SQL> select * from tbl_b;
A N
---------- ----------
a 1
b 2
c 3
3 rows selected.
SQL> delete tbl_a where n=3;
1 row deleted.
SQL> select * from tbl_a;
N
----------
1
2
2 rows selected.
SQL> select * from tbl_b;
A N
---------- ----------
a 1
b 2
2 rows selected.
SQL
복사
2.
on delete cascade 비활성화
SQL> create table tbl_a (
n number primary key); 2
Table 'TBL_A' created.
SQL> create table tbl_b (
a char(10) primary key,
n number,
constraints tbl_b_fk foreign key (n)
references tbl_a (n)); 2 3 4 5
Table 'TBL_B' created.
SQL> insert into tbl_a values (1);
1 row inserted.
SQL> insert into tbl_a values (2);
1 row inserted.
SQL> insert into tbl_a values (3);
1 row inserted.
SQL> insert into tbl_b values ('a', 1);
1 row inserted.
SQL> insert into tbl_b values ('b', 2);
1 row inserted.
SQL> insert into tbl_b values ('c', 3);
1 row inserted.
SQL> select * from tbl_a;
N
----------
1
2
3
3 rows selected.
SQL> select * from tbl_b;
A N
---------- ----------
a 1
b 2
c 3
3 rows selected.
SQL> delete tbl_a where n=3;
TBR-10009: INTEGRITY constraint violation ('TIBERO'.'TBL_B_FK'): foreign key exists.
SQL
복사