constraints for referential integrity

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.

CONTINENT

NAME
Africa

COUNTRY

NAME CONTINENT
Benin Africa

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

NAME

COUNTRY

NAME CONTINENT

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

NAME

COUNTRY

NAME CONTINENT
Benin  

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

NAME
EMEA

COUNTRY

NAME CONTINENT
Benin EMEA

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

11 thoughts on “constraints for referential integrity”

  1. John Flack

    To me, the better answer is to use system generated surrogate primary keys so that you never have to update a primary or foreign key. I might not bother to do this on your example, since we are not likely to really change the names of the continents. However, if there is ANY chance the key will be changed, I’d make the key a UNIQUE constraint and use a surrogate key for primary and foreign keys. Problem solved – change it at will, and the changes don’t have to cascade. Of course, if you WANT a cascade delete behavior, or a cascade NULL behavior, you can still specify it.

  2. Don Seiler

    I agree with John. Of course this is a contrived example, but it does illustrate one of the advantages of surrogate keys. Any field that you might modify in the future is not a good choice for primary/foreign keys.

  4. Jeremy

    Might be worth making a footnote for the googlers that a constraint can be DEFERRABLE without being INITIALLY DEFERRED. This would not change the default behavior of Oracle; but you could still use the SET CONSTRAINT[S] DEFERRED statement to enable the behavior demonstrated above… and the SET CONTRAINT[S] IMMEDIATE to change it back to the default behavior…

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

    Table altered.

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

    SQL> set constraint country_continent_fk deferred;

    Constraint set.

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

    1 row updated.

  5. Laurent Schneider Post author

    the better answer is to use system generated surrogate primary keys so that you never have to update a primary or foreign key

    Sometimes. But not always, because the generated key must be retrieved.

    Let’s imagine CONTINENT is loaded with sql loader. If you generate keys (for example with sequences), you will not be able to reload the table (sqlload/truncate) from an ASCII file or you will lose all your relationship.

    So I think natural keys are sometimes good.

    About renaming, well, one could decide to rename AUSTRALIA as OCEANIA for example 😎

  8. Karl Reitschuster

    I like the concept of deferred constraints. It helps that the application dealing with complex data model has not to know about ther order it puts the data into the table – but on commit all data/keys must be in place. We only use this feature if heavily linked data is processed.

    Karl

  9. Kamran

    Yes. Disable and Enable mechanism is good. But when I’ll insert child records with “no primary” keys, and when I enable constraint, will I get an error (no parent key found) or not?

Comments are closed.