SQL> 
SQL> -- Defer constraint checking until "commit" time,
SQL> --  instead of the default "execution" time.
SQL> 
SQL> drop table t3_a;
drop table t3_a
           *
ERROR at line 1:
ORA-00942: table or view does not exist 


SQL> create table t3_a as select * from test3 where 1=2;

Table created.

SQL> drop table t2_a;

Table dropped.

SQL> create table t2_a as select * from test2 where 1=2;

Table created.

SQL> insert into t2_a values('a',1);

1 row created.

SQL> insert into t2_a values('b',2);

1 row created.

SQL> insert into t2_a values('c',3);

1 row created.

SQL> insert into t3_a values('a',1);

1 row created.

SQL> insert into t3_a values('a',1);

1 row created.

SQL> insert into t3_a values('b',1);

1 row created.

SQL> alter table t2_a add constraint t2_a_pk primary key (c1)
  2        initially deferred;

Table altered.

SQL> 
SQL> -- PK, No error till commit
SQL> insert into t2_a values('a',1);

1 row created.

SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back 
ORA-00001: unique constraint (JYANG.T2_A_PK) violated 


SQL> 
SQL> alter table t2_a drop constraint t2_a_pk;

Table altered.

SQL> alter table t2_a add constraint t2_a_pk primary key (c1)
  2        initially immediate deferrable;

Table altered.

SQL> 
SQL> -- Error immediately
SQL> insert into t2_a values('a',1);
insert into t2_a values('a',1)
*
ERROR at line 1:
ORA-00001: unique constraint (JYANG.T2_A_PK) violated 


SQL> -- Unless constraint is set to "deferred"
SQL> set constraint t2_a_pk deferred;

Constraint set.

SQL> insert into t2_a values('a',1);

1 row created.

SQL> -- Error at commit
SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back 
ORA-00001: unique constraint (JYANG.T2_A_PK) violated 


SQL> 
SQL> 
SQL> alter table t3_a add constraint t3_a_fk foreign key (c1)
  2        references t2_a (c1) initially deferred;

Table altered.

SQL> select constraint_type,status,deferrable,deferred,validated
  2         from user_constraints where constraint_name='T3_A_FK';

C STATUS   DEFERRABLE     DEFERRED  VALIDATED                                   
- -------- -------------- --------- -------------                               
R ENABLED  DEFERRABLE     DEFERRED  VALIDATED                                   

SQL> 
SQL> -- No error...
SQL> insert into t3_a values('d',1);

1 row created.

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


SQL> 
SQL> alter table t3_a drop constraint t3_a_fk;

Table altered.

SQL> 
SQL> alter table t3_a add constraint t3_a_fk foreign key (c1)
  2        references t2_a (c1) initially immediate deferrable;

Table altered.

SQL> 
SQL> -- Error immediately...
SQL> insert into t3_a values('d',1);
insert into t3_a values('d',1)
*
ERROR at line 1:
ORA-02291: integrity constraint (JYANG.T3_A_FK) violated - parent key not found 


SQL> -- ...unless constraint is set to "deferred"
SQL> set constraint t3_a_fk deferred;

Constraint set.

SQL> insert into t3_a values('d',1);

1 row created.

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


SQL> 
SQL> drop table t3_a;

Table dropped.

SQL> drop table t2_a;

Table dropped.

SQL> 
SQL> spool off