Deferred constraints

DEFERRED forces the check to occur on COMMIT, whereas IMMEDIATE forces the check to occur on INSERT.

The following is an example;

SQL> create table t1 ( a number, b number);

Table created.

SQL> alter table t1 add constraint t1_pk primary key (a);

Table altered.

SQL> create table t2 (a number);

Table created.

SQL> alter table t2 add constraint t2_fk
  2  foreign key (a) references t1 (a)
  3  deferrable initially immediate;
 
Table altered.

SQL> insert into t1 values (1,1);

1 row created.

SQL> insert into t2 values (2);
insert into t2 values (2)
*
ERROR at line 1:
ORA-02291: integrity constraint (XDB_DEV3.T2_FK) violated - parent key not found

SQL> alter session set constraints = deferred;

Session altered.

SQL> insert into t2 values (2);

1 row created.

SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02291: integrity constraint (XDB_DEV3.T2_FK) violated - parent key not found