【延迟约束】功能的测试

一：延迟约束有两个选项：

1）Initially immediate(默认): 立即验证, 执行完一个sql后就进行验证；

2）Initially deferred: 延迟验证, 当事务提交时或调用set constraint[s]， 事务提交时如果验证不通过，那么立即回滚事务。 当调用set constraint[s] immediate时如果验证不通过，不回滚事务，只做验证操作

注意：

调用set constraint[s] immediate/deferred时，如果一个事务结束，那么set则失效，如果想让某个会话所有的延迟约束都改为立即验证(延迟验证)，那么可以采取下面的方式：

alter session set constraints = immediate;

alter session set constraints = deferred;

二：延迟约束测试

1）建表

SCOTT@ORA11GR2>create table t_check(

a number(1) constraint ck_t_check_a check (a>=0) deferrable initially immediate,

b number(1) constraint ck_t_check_b check (b>=0) deferrable initially deferred,

c number(1) constraint ck_t_check_c check (c>=0) initially immediate,

d number(1) constraint ck_t_check_d check (d>=0) initially deferred,

e number(1) constraint ck_t_check_e check (e>=0)

);

Table created.

SCOTT@ORA11GR2> SELECT table_name,constraint_name,deferrable,deferred FROM user_constraints where table_name='T_CHECK';

TABLE_NAME   CONSTRAINT_NAME      DEFERRABLE     DEFERRED

------------ -------------------- -------------- ---------

T_CHECK      CK_T_CHECK_A         DEFERRABLE     IMMEDIATE

T_CHECK      CK_T_CHECK_B         DEFERRABLE     DEFERRED

T_CHECK      CK_T_CHECK_C         NOT DEFERRABLE IMMEDIATE

T_CHECK      CK_T_CHECK_D         DEFERRABLE     DEFERRED

T_CHECK      CK_T_CHECK_E         NOT DEFERRABLE IMMEDIATE

SCOTT@ORA11GR2>

创建约束的时候，默认的情况下

DEFERRABLE： NOT DEFERRABLE

DEFERRED： IMMEDIATE

即不允许延迟约束，立即验证的方式

2）利用a、b字段的约束，验证延迟

a字段是立即验证； b字段是延迟验证，当事务提交时验证

SCOTT@ORA11GR2>insert into t_check(a) values(-1);

insert into t_check(a) values(-1)

*

ERROR at line 1:

ORA-02290: check constraint (SCOTT.CK_T_CHECK_A) violated

SCOTT@ORA11GR2>insert into t_check(b) values(-1);

1 row created.

SCOTT@ORA11GR2>select * from t_check;

         A          B          C          D          E

---------- ---------- ---------- ---------- ----------

                   -1

SCOTT@ORA11GR2>commit;

commit

*

ERROR at line 1:

ORA-02091: transaction rolled back

ORA-02290: check constraint (SCOTT.CK_T_CHECK_B) violated

SCOTT@ORA11GR2>

3）利用a字段的约束，验证set constraint[s] deferred

SCOTT@ORA11GR2>select * from t_check;

no rows selected

SCOTT@ORA11GR2>insert into t_check(a) values(-1);

insert into t_check(a) values(-1)

*

ERROR at line 1:

ORA-02290: check constraint (SCOTT.CK_T_CHECK_A) violated

SCOTT@ORA11GR2>set constraint ck_t_check_a deferred;

Constraint set.

SCOTT@ORA11GR2>insert into t_check(a) values(-1);

1 row created.

SCOTT@ORA11GR2>select * from t_check;

         A          B          C          D          E

---------- ---------- ---------- ---------- ----------

        -1

SCOTT@ORA11GR2>commit;

commit

*

ERROR at line 1:

ORA-02091: transaction rolled back

ORA-02290: check constraint (SCOTT.CK_T_CHECK_A) violated

SCOTT@ORA11GR2>

4）利用b字段的约束，验证set constraint[s] immediate

SCOTT@ORA11GR2>select * from t_check;

no rows selected

SCOTT@ORA11GR2>insert into t_check(b) values(-1);

1 row created.

SCOTT@ORA11GR2>select * from t_check;

         A          B          C          D          E

---------- ---------- ---------- ---------- ----------

                   -1

SCOTT@ORA11GR2>commit;

commit

*

ERROR at line 1:

ORA-02091: transaction rolled back

ORA-02290: check constraint (SCOTT.CK_T_CHECK_B) violated

SCOTT@ORA11GR2>select * from t_check;

no rows selected

SCOTT@ORA11GR2>set constraint ck_t_check_b immediate;

Constraint set.

SCOTT@ORA11GR2>insert into t_check(b) values(-1);

insert into t_check(b) values(-1)

*

ERROR at line 1:

ORA-02290: check constraint (SCOTT.CK_T_CHECK_B) violated

SCOTT@ORA11GR2>

5）利用c、d字段的约束，验证NOT DEFERRABLE/DEFERRABLE的区别

SCOTT@ORA11GR2>SELECT table_name,constraint_name,deferrable,deferred FROM user_constraints where table_name='T_CHECK';

TABLE_NAME   CONSTRAINT_NAME      DEFERRABLE     DEFERRED

------------ -------------------- -------------- ---------

T_CHECK      CK_T_CHECK_A         DEFERRABLE     IMMEDIATE

T_CHECK      CK_T_CHECK_B         DEFERRABLE     DEFERRED

T_CHECK      CK_T_CHECK_C         NOT DEFERRABLE IMMEDIATE

T_CHECK      CK_T_CHECK_D         DEFERRABLE     DEFERRED

T_CHECK      CK_T_CHECK_E         NOT DEFERRABLE IMMEDIATE

SCOTT@ORA11GR2>select * from t_check;

no rows selected

SCOTT@ORA11GR2>insert into t_check(c) values(-1);

insert into t_check(c) values(-1)

*

ERROR at line 1:

ORA-02290: check constraint (SCOTT.CK_T_CHECK_C) violated

SCOTT@ORA11GR2>set constraint ck_t_check_c deferred;

set constraint ck_t_check_c deferred

*

ERROR at line 1:

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

SCOTT@ORA11GR2>insert into t_check(d) values(-1);

1 row created.

SCOTT@ORA11GR2>commit;

commit

*

ERROR at line 1:

ORA-02091: transaction rolled back

ORA-02290: check constraint (SCOTT.CK_T_CHECK_D) violated

SCOTT@ORA11GR2>set constraint ck_t_check_d immediate;

Constraint set.

SCOTT@ORA11GR2>insert into t_check(d) values(-1);

insert into t_check(d) values(-1)

*

ERROR at line 1:

ORA-02290: check constraint (SCOTT.CK_T_CHECK_D) violated

SCOTT@ORA11GR2>

通过上面的测试我们发现，DEFERRABLE字段表示是否可以延迟约束，当为NOT DEFERRABLE的时候，那么那个约束是没有办法改变它的延迟验证的。当为DEFERRABLE的时候，那么那个约束则可以修改为立即验证或者延迟验证。

小结：

这个功能可以利用在，比如主、外键插入数据的时候，先后顺序也许不固定，那么就可以采用延迟验证的方式，只要事务结束的那一刻，主外键符合约束的话，那么就不会报错。

如下链接：

http://space.itpub.net/685769/viewspace-743493

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/685769/viewspace-743492/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/685769/viewspace-743492/