SQL> create table t1(c1 number);

Table created

SQL> alter table t1 add constraint chk_t1 check(c1>5) not deferrable;

Table altered

SQL> create table t2(c1 number);

Table created

SQL> alter table t2 add constraint chk_t2 check(c1>5) deferrable initially immediate;

Table altered

SQL> create table t3(c1 number);

Table created

SQL> alter table t3 add constraint chk_t3 check(c1>5) deferrable initially deferred;

Table altered

SQL> insert into t1 values(1);

insert into t1 values(1)

ORA-02290: check constraint (TEST.CHK_T1) violated

SQL> insert into t1 values(6);

1 row inserted

SQL> rollback;

Rollback complete

SQL> insert into t2 values(1);

insert into t2 values(1)

ORA-02290: check constraint (TEST.CHK_T2) violated

SQL> insert into t2 values(6);

1 row inserted

SQL> rollback;

Rollback complete

SQL> insert into t3 values(1);

1 row inserted

SQL> insert into t3 values(6);

1 row inserted

SQL> commit;

commit

ORA-02091: transaction rolled back
ORA-02290: check constraint (TEST.CHK_T3) violated

对deferrable constraint 可在会话级定义check type;

SQL> alter session set constraint=immediate;

Session altered

SQL> insert into t1 values(1);

insert into t1 values(1)

ORA-02290: check constraint (TEST.CHK_T1) violated

SQL> insert into t2 values(1);

insert into t2 values(1)

ORA-02290: check constraint (TEST.CHK_T2) violated

SQL> insert into t3 values(1);

insert into t3 values(1)

ORA-02290: check constraint (TEST.CHK_T3) violated

会话级优先

SQL> alter session set constraint=deferred;

Session altered

SQL> insert into t1 values(1);

insert into t1 values(1)

ORA-02290: check constraint (TEST.CHK_T1) violated

SQL> insert into t2 values(1);

1 row inserted

SQL> commit;

commit

ORA-02091: transaction rolled back
ORA-02290: check constraint (TEST.CHK_T2) violated

SQL> insert into t3 values(1);

1 row inserted

SQL> commit;

commit

ORA-02091: transaction rolled back
ORA-02290: check constraint (TEST.CHK_T3) violated

SQL> set constraint chk_t1 deferred;

set constraint chk_t1 deferred

ORA-02447: cannot defer a constraint that is not deferrable

SQL> set constraint chk_t2 deferred;

Constraints set

SQL> insert into t2 values(1);

1 row inserted

SQL> commit;

commit

ORA-02091: transaction rolled back
ORA-02290: check constraint (TEST.CHK_T2) violated

SQL> set constraint chk_t3 immediate;

Constraints set

SQL> insert into t3 values(1);

insert into t3 values(1)

ORA-02290: check constraint (TEST.CHK_T3) violated