oracle非延迟约束,Oracle可延迟约束Deferable的使用

标准规定，约束可以是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)

3 deferrable用途

3.1 物化视图

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

3.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