Learning is not a spectator sport
Connor McDonald on SQL and the Oracle Database
A little known RI clause
Most people are aware of this standard referential integrity control, namely you can’t delete the parent if children exist
SQL> drop table PAR cascade constraints purge;
Table dropped.
SQL> create table PAR ( p int primary key, data int);
Table created.
SQL> insert into PAR
2 select rownum, rownum
3 from dual connect by level <= 7;
7 rows created.
SQL>
SQL> drop table CHD purge;
Table dropped.
SQL> create table CHD ( c int primary key, p int references PAR ( p ) );
Table created.
SQL>
SQL> insert into CHD
2 select rownum, mod(rownum,4)+1
3 from dual connect by level <= 12;
12 rows created.
SQL>
SQL> delete from PAR
2 where p = 2;
delete from PAR
*
ERROR at line 1:
ORA-02292: integrity constraint (MCDONAC.SYS_C0018225) violated - child record found
And most people are also aware that you can head to the other extreme, and wipe out the children when you wipe out the parent
SQL> drop table PAR cascade constraints purge;
Table dropped.
SQL> create table PAR ( p int primary key, data int);
Table created.
SQL> insert into PAR
2 select rownum, rownum
3 from dual connect by level <= 7;
7 rows created.
SQL>
SQL> drop table CHD purge;
Table dropped.
SQL> create table CHD ( c int primary key, p int references PAR ( p ) ON DELETE CASCADE);
Table created.
SQL>
SQL> insert into CHD
2 select rownum, mod(rownum,4)+1
3 from dual connect by level <= 12;
12 rows created.
SQL>
SQL> delete from PAR
2 where p = 2;
1 row deleted.
SQL> select * from CHD;
C P
---------- ----------
2 3
3 4
4 1
6 3
7 4
8 1
10 3
11 4
12 1
9 rows selected.
But don’t forget, there is also a third option that you can implement declaratively
SQL> drop table PAR cascade constraints purge;
Table dropped.
SQL> create table PAR ( p int primary key, data int);
Table created.
SQL> insert into PAR
2 select rownum, rownum
3 from dual connect by level <= 7;
7 rows created.
SQL>
SQL> drop table CHD purge;
Table dropped.
SQL> create table CHD ( c int primary key, p int references PAR ( p ) ON DELETE SET NULL);
Table created.
SQL>
SQL> insert into CHD
2 select rownum, mod(rownum,4)+1
3 from dual connect by level <= 12;
12 rows created.
SQL>
SQL> delete from PAR
2 where p = 2;
1 row deleted.
SQL> select * from CHD;
C P
---------- ----------
1
2 3
3 4
4 1
5
6 3
7 4
8 1
9
10 3
11 4
12 1
12 rows selected.
SQL>
SQL>
I don’t think I’ve seen this used in a Production instance. If you’ve seen it, please add a comment – I’d be curious to see a use case.
We are using it for the Oracle APEX meta data. An example would be the Interactive Report Groups which are assigned to Interactive Report Columns. That relationship is really just used to layout the columns. If a group gets deleted, that reference should really just be cleared, but the column has to stay.
Regards
Patrick
Thanks for dropping by Patrick with the interesting use case.
I have never used this, but an obvious one would be an employee and manager relationship. A manager leaves a company and hence some employees have no manager but the employees are still within the company – hence we cannot cascade the delete – maybe 🙂
Hi Connor,
Can a table be altered with a ‘ON DELETE SET NULL’ or ‘ON DELETE CASCADE’