One of the powers of RDBMS as we use them today are constraints. I use unique indexes, not null constraints and foreign keys on a regular basis and if you do any work with RDBMSs you probably do as well.

From time to time one comes at a point where you think “it would be nice to enforce this with some kind of constraint”, but you can’t. Well maybe you just didn’t try hard enough. This is the second part of the mini series about somewhat special constraints for (Oracle) databases.

Let's assume you want to model a system of superheroes an villain. Each superhero has an arch_villain and each villain has an arch superhero. Thats simple, isn't it?

create table super_hero (

  id number(10),

  name varchar2(200) not null unique,

  arch_villain_id number(10) not null,

  CONSTRAINT super_hero_pk PRIMARY KEY (id)

);

create table villain   (

  id number(10),

  name varchar2(200) not null unique,

  arch_hero_id number(10) not null,

  CONSTRAINT villain_pk PRIMARY KEY (id)

);
alter table super_hero

add constraint arch_villain_fk

foreign key(arch_villain_id)

references villain(id);
alter table villain

add constraint arch_hero_fk

foreign key(arch_hero_id)

references super_hero(id);

Not so fast. Try to insert a row into these tables. It doesn't work. Before you can insert the first super hero you have to insert a first villain to serve as a arch villain for our super hero. But this arch villain needs a super hero first for use as arch super hero. A vicious circle.

Looks like we have to drop at least one of our constraints. Actually no. We can make it a deferred constraint like so:

create table super_hero (

  id number(10),

  name varchar2(200) not null unique,

  arch_villain_id number(10) not null,

  CONSTRAINT super_hero_pk PRIMARY KEY (id)

);

create table villain   (

  id number(10),

  name varchar2(200) not null unique,

  arch_hero_id number(10) not null,

  CONSTRAINT villain_pk PRIMARY KEY (id)

);
alter table super_hero

add constraint arch_villain_fk

foreign key(arch_villain_id)

references villain(id)

deferrable initially deferred;
alter table villain

add constraint arch_hero_fk

foreign key(arch_hero_id)

references super_hero(id)

deferrable initially deferred;

Now we can enter our data in a pretty natural way:

insert into super_hero(id, name, arch_villain_id) values (1, 'Spider-Man', 2);

insert into villain(id, name, arch_hero_id) values(2, 'Green Goblin', 1);
commit;

What happend? Are the constraints still there? Yes they are:

insert into super_hero(id, name, arch_villain_id) values (1, 'Spider-Man', 2);

insert into villain(id, name, arch_hero_id) values(3, 'Green Goblin', 1);
commit; -- fails with ORA-02091: transaction rolled back ORA-02291: integrity constraint (USER_1DA56.ARCH_VILLAIN_FK) violated - parent key not found : commit

But they are only enforced on commit, not after every DML statement like it is done normaly.

Many Java developer know this feature due to a bug in hibernate that causes illegal intermediate inserts/updates. Most database developers know this feature more as a performance tuning tool. When you insert or update millions of rows Oracle has to executes additional statementes in order to verify constraints. This can cost a considerable amount of performance and often can be done much more efficient when one waits until all the data has its final state.