Document Type | Technical Information
Category | Utility
Document Number | TUTTI011
Overview
The on delete cascade feature automatically deletes the data itself when the data in the table it references is deleted.
Note
Using this feature maintains referential integrity and can be enabled when creating a table.
Method
Example of applying 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);
Scenario
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;
Result
1. on delete cascade enabled
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.2. on delete cascade disabled
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.