Deferred Constraints

Prior to Oracle8i, no way of entering child records without parent records, inserting duplicate records on primary key/unique key columns, deleting parent record before deleting child records. When we enter this kind of orphan records or bad records, it would fail right away and rollback the current transaction. Let us say, we entered records in parent table and entering records in child table which is not existing in parent table. Prior to oracle8i, it will fail and rollback the orphan records in child table. But parent table data will not be rolledback. This would lead into incomplete or partial data load.

To avoid all these issues, oracle8i introduced deferred constraints. Deferred constraint will let you load the data in reverse order(child first, parent next) and it will validate the data only at the time of commit. The data will be validated at the time of commit and rollback both parent and child at the same time if there is any orphan records. When we reload the same data again, we do not need to manually clean the partial data which we loaded previous time in the table.

For instance, we have parent table called MASTER and ten different child tables. Child tables are named as CHILD1,CHILD2,CHILD3... CHILD10. First we enter data in MASTER table and entering all the child tables. We are applying commit at the end of data load. Somehow, we entered orphan records in CHILD3 and CHILD7 tables. In regular non deferred constraints, data will be successfully loaded in all the tables except CHILD3 and CHILD7 tables. If we use deferred constraint, all the 11 tables(master and all child) transaction will be rolledback. There will not be any partial data load when we use deferred constraints.

Another thing, the loading time will be faster when we use deferred constraint. Because, it does not need to validate the constraint at the time of loading.

By default constraints are created as NON DEFERRABLE but this can be overidden using the DEFERRABLE keyword. If a constraint is created with the DEFERRABLE keyword, then it can act in one of two ways (INITIALLY IMMEDIATE, INITIALLY DEFERRED).

INITIALLY IMMEDIATE is same as non deferred regular constraint.
INITIALLY DEFERRED is deferred constraint which will validate at the time of commit.

The below cases are tested in oracle10gR2.

Case1. Let us create the deferred FK constraint and enter the orphan records. Deferred constraint validate at the time of commit and rollback both parent and child.

scott@orcl> CREATE TABLE MASTER(id NUMBER(10));

Table created.

scott@orcl> CREATE TABLE child(id NUMBER(10), master_id NUMBER(10));

Table created.

scott@orcl> ALTER TABLE master ADD PRIMARY KEY (id);

Table altered.

scott@orcl> ALTER TABLE child ADD CONSTRAINT fk_master
2 FOREIGN KEY (master_id)
3 REFERENCES master(id)
4 DEFERRABLE
5 INITIALLY DEFERRED;

Table altered.

scott@orcl> INSERT INTO child VALUES(1,2);

1 row created.

scott@orcl> INSERT INTO master VALUES(1);

1 row created.

scott@orcl> COMMIT;
COMMIT
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02291: integrity constraint (SCOTT.FK_MASTER) violated - parent key not
found

scott@orcl> select * from master;

no rows selected

scott@orcl> select * from child;

no rows selected

scott@orcl>

Case2. Let us create the deferred FK constraint and delete the parent without deleting child records. At the time of commit, it rollbacks the transaction in child table.

scott@orcl> CREATE TABLE MASTER(id NUMBER(10));

Table created.

scott@orcl> CREATE TABLE child(id NUMBER(10), master_id NUMBER(10));

Table created.

scott@orcl> ALTER TABLE master ADD PRIMARY KEY (id);

Table altered.

scott@orcl> ALTER TABLE child ADD CONSTRAINT fk_master
2 FOREIGN KEY (master_id)
3 REFERENCES master(id)
4 DEFERRABLE
5 INITIALLY DEFERRED;

Table altered.

scott@orcl> INSERT INTO child VALUES(1,1);

1 row created.

scott@orcl> INSERT INTO master VALUES(1);

1 row created.

scott@orcl> COMMIT;

Commit complete.

scott@orcl> delete master;

1 row deleted.

scott@orcl> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02292: integrity constraint (SCOTT.FK_MASTER) violated - child record found


scott@orcl> select * from master;

ID
----------
1

scott@orcl> select * from child;

ID MASTER_ID
---------- ----------
1 1

scott@orcl>

Case3. Let us create deferred primary key and enter the duplicate records.

scott@orcl> create table master(no number);

Table created.

scott@orcl> alter table master add constraint pk_master
2 primary key(no)
3 deferrable
4 initially deferred;

Table altered.

scott@orcl> insert into master values(1);

1 row created.

scott@orcl> insert into master values(1);

1 row created.

scott@orcl> insert into master values(2);

1 row created.

scott@orcl> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-00001: unique constraint (SCOTT.PK_MASTER) violated

scott@orcl> select * from master;

no rows selected

scott@orcl>

How do we change the constraint from deferred to regular and vice versa?

We can change the regular constraint to deferred and deferred constraint to regular constraint at the session level as long as the constraint is deferrable.

The below command will change the constraints to deferred/immediate for all the deferrable
constraint at the session level.

ALTER SESSION SET CONSTRAINTS = DEFERRED;
ALTER SESSION SET CONSTRAINTS = IMMEDIATE;

We can also change the specfic constraint to deferred/immediate.

set constraint fk_master deferred;
set constraint fk_master immediate;

What is ideal place to use deferred constraint?

When we have large volume of transaction involving multiple dependencies, we have a constraint that either load all the dependent tables successfully or rollback all the dependent tables if there is any orphan records. This would be ideal place to use deferred constraints.