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.

2 thoughts on “A little known RI clause

  1. 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s