Oracle可延迟约束Deferable的使用

Oracle可延迟约束Deferable的使用

　　标准规定，约束可以是deferrable或not deferrable(默认)。
　　not deferrable 约束在每一个DML语句后检查;
　　deferrable 约束可以在每一个insert，delete，或update(即时模式)后立即检查，或者在事务末尾检查(延迟模式)

　　当没有按特定顺序执行数据加载时，这项功能特别有用——它允许先把数据载入子表，然后再装入父表。
　　另一种用法是在加载不符合某个check约束的数据之后，对其进行适当的更新。

　　语法如下：
　　[ [not] deferrable [initially {immediate | deferred} ] ]
　　或
　　[ [initially {immediate | deferred} ] [not] deferrable ]

一：deferrable介绍

1.deferrable的两个选项区别

　　deferrable表示该约束是可延迟验证的。 它有两个选项：
　　Initially immediate(默认)： 立即验证， 执行完一个sql后就进行验证;
　　Initially deferred: 延迟验证，当事务提交时或调用set constraint[s] immediate语句时才验证。
　　区别是：事务提交时验证不通过， 则立即回滚事务; set constraint[s] immediate时只验证， 不回滚事务。

2.not deferrable与deferrable区别

　　区别就在于： “立即验证的可延迟约束” 是可以根据需要设置成 “延迟验证的可延迟约束”的， 而“不可延迟验证”是不能改变的。

二：deferrable实例

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.正常插入，没问题

　　SQL> insert into test1 values(1, 1);
　　1 row inserted

3.检验立即验证：数据不能插入

　　SQL> insert into test1 values(-1, 1);
　　insert into test1 values(-1, 1)
　　ORA-02290: 违反检查约束条件 (MYHR.CHECK_A)

4.检验延迟验证：可以执行

　　SQL> insert into test1 values(1, -1);
　　1 row inserted

　　SQL> select * from test1;

　　A B
　　-- --
　　1 1
　　1 -1

5.提交延迟验证(commit)：验证失败，自动回滚

　　SQL> commit;
　　commit
　　ORA-02091: 事务处理已回退
　　ORA-02290: 违反检查约束条件 (MYHR.CHECK_B)

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;

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)

三：deferrable用途

1.物化视图

　　物化视图(快照)，这是它的主要用途。这些视图会使用延迟约束来进行视图刷新。在刷新物化视图的过程中，可能会破坏完整性，而且将不能逐句检验约束。但到执行COMMIT时，数据完整性就没问题了，而且能满足约束。没有延迟约束，物化视图的约束可能会使刷新过程不能成功进行。

2.级联更新

　　当预测是否需要更新父/子关系中的主键时，它有助于级联更新。看一下实际的例子：

　　SQL> create table t(tno number(10) constraint pk_t_tno primary key, tname varchar2(20));

　　SQL> create table s(sno number(10) constraint pk_s_tno primary key, sname varchar2(20)， tno number(10));

　　SQL> alter table s add constraint fk_s_tno foreign key (tno) references t(tno);

　　SQL> insert into t values(1,'yuechaotian');

　　SQL> insert into t values(2,'tianyuechao');

　　SQL> commit;

　　SQL> insert into s values(1,'stu_1', 1);

　　SQL> insert into s values(2,'stu_2', 1);

　　SQL> commit;

　　SQL> update t set tno=22 where tno=2;

　　SQL> update t set tno=11 where tno=1;

　　update t set tno=11 where tno=1
　　ORA-02292: 违反完整约束条件 (MYHR.FK_S_TNO) - 已找到子记录

　　SQL> select * from t;

　　TNO TNAME

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

　　1 yuechaotian

　　22 tianyuechao

　　SQL> select *from s;

　　SNO SNAME TNO

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

　　1 stu_1 1

　　2 stu_2 1

　　SQL> rollback;

　　SQL> alter table s drop constraint fk_s_tno;

　　SQL> alter table s add constraint fk_s_tno foreign key (tno) references t(tno) deferrable initially immediate;


　　SQL> select * from t;

　　TNO TNAME

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

　　1 yuechaotian

　　2 tianyuechao

　　SQL> select * from s;

　　SNO SNAME TNO

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

　　1 stu_1 1

　　2 stu_2 1


　　SQL> set constraint fk_s_tno deferred;

　　Constraints set

　　SQL> update t set tno=22 where tno=2;

　　SQL> update t set tno=11 where tno=1;

　　SQL> update s set tno = 11 where tno = 1;

　　SQL> commit;

　　SQL> select * from t;

　　TNO TNAME

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

　　11 yuechaotian

　　22 tianyuechao

　　SQL> select * from s;

　　SNO SNAME TNO

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

　　1 stu_1 11

　　2 stu_2 11


　　SQL> select a.constraint_name, a.deferrable, a.deferred from user_constraints a where a.constraint_name like '%TNO%';

　　CONSTRAINT_NAME DEFERRABLE DEFERRED

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

　　FK_S_TNO DEFERRABLE IMMEDIATE

　　PK_S_TNO NOT DEFERRABLE IMMEDIATE

　　PK_T_TNO NOT DEFERRABLE IMMEDIATE


原文出自【比特网】，转载请保留原文链接：http://soft.chinabyte.com/database/339/12370839.shtml