constraint deferrable immediate check

-- define a deferrable constraint allow check constraint when a transaction is commited, by default with immediate constraint allow check constraint after DML operation .

-- allow primary key check deferrable.

alter table t1 add constraint pk_t1 primary key (id) initially deferred ;

insert into t1 (id) values (1) ;

insert into t1 (id) values (1) ;

commit ; -- report error here.

-- allow foreign key check deferrable.

alter table t2 add constraint fk_t2_t1 foreign key (dept_id) referencing t1

initially deferred ;

insert into t2 (id,dept_id) values (1,999) ;

commit ; -- report error here.

-- switch constraint check at which points.

alter session set constraint=deferrable ;

set constraints all deferrable /immediate;

set constraint pk_t1 deferrable /immediate;

[@more@]