【Oracle】【延迟约束】功能
一：延迟约束有两个选项：
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）建表
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
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字段的约束，验证延迟
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
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的区别
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 |
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的时候，那么那个约束则可以修改为立即验证或者延迟验证。
小结：
这个功能可以利用在，比如主、外键插入数据的时候，先后顺序也许不固定，那么就可以采用延迟验证的方式，只要事务结束的那一刻，主外键符合约束的话，那么就不会报错。