Laurent Schneider

On the developpez.net forums I answered a question about referential integrity. How can you delete/update a parent row when the child exist ?

SQL> create table continent(
  2    name varchar2(10),
  3    constraint continent_pk primary key(name));

Table created.

SQL> create table country(
  2    name varchar2(10),
  3    continent varchar2(10),
  4    constraint country_pk
  5      primary key(name),
  6    constraint country_continent_fk
  7      foreign key(continent) references continent);

Table created.

SQL> insert into continent values('Africa');

1 row created.

SQL> insert into country values('Benin', 'Africa');

1 row created.

COUNTRY

So in this default constellation, I cannot delete a parent row where the child exist.

SQL> delete continent where name='Africa';
delete continent where name='Africa'
*
ERROR at line 1:
ORA-02292: integrity constraint (AUDBA.COUNTRY_CONTINENT_FK)
violated - child record found

I could specify CASCADE to delete the child rows automatically

SQL> alter table country
  2    drop constraint country_continent_fk;

Table altered.

SQL> alter table country
  2    add constraint country_continent_fk
  3    foreign key(continent)
  4    references continent
  5    on delete cascade;

Table altered.

SQL> delete continent where name='Africa';

1 row deleted.

CONTINENT

COUNTRY

but this is dangerous. When I read 1 row deleted., I am not informed that I have deleted rows in the child table.

I could rather set the column to null

SQL> rollback;

Rollback complete.

SQL> alter table country
  2    drop constraint country_continent_fk;

Table altered.

SQL> alter table country
  2    add constraint country_continent_fk
  3    foreign key(continent)
  4    references continent
  5    on delete set null;

Table altered.

SQL> delete continent where name='Africa';

1 row deleted.

CONTINENT

COUNTRY

Ok, but what If I need to rename Africa to EMEA ? I can do this neither on the child nor on the parent

SQL> rollback;

Rollback complete.

SQL> 
SQL> update continent
  2  set name='EMEA'
  3  where name='Africa';
update continent
*
ERROR at line 1:
ORA-02292: integrity constraint (AUDBA.COUNTRY_CONTINENT_FK) 
violated - child record found

SQL> update country
  2  set continent ='EMEA'
  3  where continent ='Africa';
update country
*
ERROR at line 1:
ORA-02291: integrity constraint (AUDBA.COUNTRY_CONTINENT_FK) 
violated - parent key not found

So I could defer the constraint validation to the commit

SQL> alter table country
  2    drop constraint country_continent_fk;

Table altered.

SQL> alter table country
  2    add constraint country_continent_fk
  3    foreign key(continent)
  4    references continent
  5    deferrable initially deferred;

Table altered.

SQL> update continent
  2  set name='EMEA'
  3  where name='Africa';

1 row updated.

SQL> update country
  2  set continent='EMEA'
  3  where continent ='Africa';

1 row updated.

SQL> commit;

Commit complete.

CONTINENT

COUNTRY

You can use triggers too, but constraints are more efficient than triggers.

dba, sql