Posted by 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.
Learning is not a spectator sport 
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’
Yes. To be frank I was not aware of this optons before. Thank you!
Hi Connor,
We have had a strange issue regarding a index supporting a Foreign Key on which I like to have you thoughts. Let me explain:
We are on Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
We have a parent child relationship with a cascade delete FK constraint, which is supported by an index.
Both tables are partitioned on period.
Last release we modified some of the partitions to indexing=OFF and rebuild the indexes as partial, resulting in dropping index segments on these partitions. So far, so good.
But when we deleted all the children and then the parent, we received the following error on the FK index of the child : ORA-01502 index or partition of such index is in unusable state.
I can imagine that the constraint needs to check if children exists, but why is this raising an error? It won’t raise an error when I select on the FK column of the child table?
Thats an interesting one. Do you have a test case you could share?