Oracle可延迟约束Deferable的使用
欢迎进入Oracle社区论坛，与200万技术人员互动交流 >>进入 标准规定，约束可以是deferrable或not deferrable（默认）。 not deferrable 约束在每一个DML语句后检查； deferrable 约束可以在每一个insert，delete，或update（即时模式）后立即检查，或者在事
欢迎进入Oracle社区论坛，与200万技术人员互动交流 >>进入
标准规定，约束可以是deferrable或not deferrable（默认）。
not deferrable 约束在每一个DML语句后检查；
deferrable 约束可以在每一个insert，delete，或update（即时模式）后立即检查，或者在事务末尾检查（延迟模式）
当没有按特定顺序执行数据加载时，这项功能特别有用――它允许先把数据载入子表，然后再装入父表。
另一种用法是在加载不符合某个check约束的数据之后，对其进行适当的更新。
语法如下：
[ [not] deferrable [initially {immediate | deferred} ] ]
或
[ [initially {immediate | deferred} ] [not] deferrable ]
1 deferrable介绍
1.1 deferrable的两个选项区别
deferrable表示该约束是可延迟验证的。 它有两个选项：
Initially immediate（默认）： 立即验证， 执行完一个sql后就进行验证；
Initially deferred: 延迟验证， 当事务提交时或调用set constraint[s] immediate语句时才验证。
区别是： 事务提交时验证不通过， 则立即回滚事务； set constraint[s] immediate时只验证， 不回滚事务。
1.2 not deferrable与deferrable区别
区别就在于： “立即验证的可延迟约束” 是可以根据需要设置成 “延迟验证的可延迟约束”的， 而“不可延迟验证”是不能改变的。
2 deferrable实例
2.1 建表
create table test1（a number（1） constraint check_a check（a > 0） deferrable
initially immediate,
b number（1） constraint check_b check（b > 0） deferrable
initially deferred）；
2.2 正常插入，没问题
SQL> insert into test1 values（1, 1）；
1 row inserted
2.3 检验立即验证：数据不能插入
SQL> insert into test1 values（-1, 1）；
insert into test1 values（-1, 1）
ORA-02290: 违反检查约束条件 （MYHR.CHECK_A）
2.4 检验延迟验证：可以执行
SQL> insert into test1 values（1, -1）；
1 row inserted
SQL> select * from test1;
A B
-- --
1 1
1 -1
2.5 提交延迟验证（commit）：验证失败，自动回滚
SQL> commit;
commit
ORA-02091: 事务处理已回退
ORA-02290: 违反检查约束条件 （MYHR.CHECK_B）
2.6 提交延迟验证（set constraint immediate）：验证失败，不回滚
SQL> insert into test1 values（1, -1）；
1 row inserted
SQL> set constraint check_b immediate;
set constraint check_b immediate
ORA-02290: 违反检查约束条件 （MYHR.CHECK_B）
或者将所有的约束做修改： alter session set constraints = immediate;
或者：set constraints all immediate;
2.7 将延迟验证设置为立即验证：则在插入时出错
SQL> set constraint check_b immediate;
Constraints set
SQL> insert into test1 values（1,-1）；
insert into test1 values（1,-1）
ORA-02290: 违反检查约束条件 （MYHR.CHECK_B）
[1] [2]