A little known RI clause

April 16, 2016

2–3 minutes

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.

7 responses to “A little known RI clause”

Patrick Wolf

April 18, 2016 at 3:47 pm

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

Reply
connormcdonald

April 18, 2016 at 5:37 pm

Thanks for dropping by Patrick with the interesting use case.

Reply
Andrew

June 19, 2018 at 5:05 pm

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 🙂

Reply
Ravin Maharaj

October 14, 2019 at 9:15 pm

Hi Connor,
Can a table be altered with a ‘ON DELETE SET NULL’ or ‘ON DELETE CASCADE’

Reply
Luis Santos

October 17, 2019 at 10:39 pm

Yes. To be frank I was not aware of this optons before. Thank you!

Reply
John Nagtzaam

October 8, 2021 at 4:08 pm

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?

Reply
1. Connor McDonald
  
  October 22, 2021 at 9:10 am
  
  Thats an interesting one. Do you have a test case you could share?
  
  Reply