盐析白兔

这个屌丝很懒，什么也没留下！

热门标签

【延迟约束】功能的测试

作者：盐析白兔 | 2024-05-31 16:00:33

踩

alter session set constraints

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

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/

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/盐析白兔/article/detail/653106