oracle Deferrable constraint 详解以及用法.

最新推荐文章于 2021-04-16 00:38:52 发布

原创最新推荐文章于 2021-04-16 00:38:52 发布 · 7.9k 阅读

10 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #deferrable #constraint

Oracle 专栏收录该内容

88 篇文章

订阅专栏

本文深入探讨了Oracle数据库中约束验证时机的两种类型：即时验证和延时验证。通过实例展示了每种类型的具体应用和区别，帮助读者理解如何在不同场景下灵活运用这些验证机制。

1. 两种验证时机.

Oracle的constraints(约束) 根据验证时机可以分成两种.

case 1. 在每一句insert statement 执行时就会马上验证, 如果约束验证失败, 则这句sql statement 会执行失败.

case 2. 执行insert statements 时不会验证, 在commit的时候验证, 如果验证失败, 则整个Transaction 回滚.

2.constraints的分类

对应地, oracle 的 constraints 也可以分成两大类.

一种是not deferrable (不可以延时的) . 这种情况下只能执行 case1 的验证时机(即时验证)

另一种是 defferable (可以设置成延时的). 这种情况下可以执行 case 1 或 case2 的验证时机. 但需要设置.

对于第二种defferable 分类, 还可以分成两小类.

一种是 initially immediate , 意思时默认情况下执行case 1.

另一种是initially deferred, 意思是默认情况下执行case2.

也就是可以分成三种

1. not deferrable

2. deferrable initially immediate

3. deferrable initially deferred.

如下图:

下面会举一些例子来详细解释这个3种 constraints的区别:

2.1 not deferrable constraints

这种最常见也最简单. 如果在增加1个constraint 时不指定验证时机属性. 默认情况下就会被设为not deferrable.

既然constraint 是不可以延时验证的, 所以也不用设定它的初始属性(实际上就是initially immediate)

例子:

首先我们建立1张空表:

create table T1 (id number(10,0),
                   nm varchar(10));

接下来为id列增加1个唯一约束t1_id:

SQL> alter table T1 add constraint t1_id  unique(id);

Table altered.

注意这时并没有指定 Deferrable 属性, 所以默认情况下就是 not deferrable 的. 我们可以通过user_constraints视图来检查它的属性:

SQL> select table_name, constraint_name, status, deferrable, deferred, validated from user_constraints where table_name = 'T1';

TABLE_NAME CONSTRAINT_NAME STATUS   DEFERRABLE	   DEFERRED  VALIDATED
---------- --------------- -------- -------------- --------- -------------
T1	   T1_ID	   ENABLED  NOT DEFERRABLE IMMEDIATE VALIDATED

注意Deferrable 属性是 not deferrable. 而 Deferred 属性(Initially 初始属性) 是immediate.

这时我们连续执行3句insert sql:

SQL> insert into T1 values(1, 'abc1');

1 row created.

SQL> insert into T1 values(2, 'abc2');

1 row created.

SQL> insert into T1 values(2, 'abc3');
insert into T1 values(2, 'abc3')
*
ERROR at line 1:
ORA-00001: unique constraint (BILL.T1_ID) violated

可以见到第一第二句insert statement 能正常执行, 但第三句执行失败, 因为id=2 与第二句重复了, 约束验证失败.

所以说验证时机属于case1, 即时验证.

这种验证时机也是最常见的.

2.2 deferrable initially immediate constraints

这种约束默认情况下等于第一种约束(not deferrable), 但是它可以在事务中改变设置, 变成延时验证的约束.

我们可以这样理解.

第一个属性deferrable 表示这个约束可以被设置成延时验证.

第二个属性initially immediate 表示这个约束默认情况下是即时验证.

我们继续利用上面的例子.

首先, 清空上面那个例子表. 由于上面操作没有提交, 所以回滚就ok了.

SQL> rollback;

Rollback complete.

然后我们修改(删除再建立)那个约束, 射程deferrable initiallly immediate 模式.

SQL> alter table T1 drop constraint t1_id;

Table altered.

SQL> alter table T1 add constraint t1_id unique(id) initially immediate deferrable ;

Table altered.

SQL>

这时我们去user_constraints视图检查属性:

SQL> select table_name, constraint_name, status, deferrable, deferred, validated from user_constraints where table_name = 'T1';

TABLE_NAME CONSTRAINT_NAME STATUS   DEFERRABLE	   DEFERRED  VALIDATED
---------- --------------- -------- -------------- --------- -------------
T1	   T1_ID	   ENABLED  DEFERRABLE	   IMMEDIATE VALIDATED

可以见到只有1个属性与Not Defferable constraint有区别, 就是 deferrable 属性是 deferrable了. 但是 deferred (初始属性) 还是 immediate.

我们继续尝试插入上面的三行数据:

SQL> insert into T1 values(1, 'abc1');

1 row created.

SQL> insert into T1 values(2, 'abc2');

1 row created.

SQL> insert into T1 values(2, 'abc3');
insert into T1 values(2, 'abc3')
*
ERROR at line 1:
ORA-00001: unique constraint (BILL.T1_ID) violated

可以到执行结果与上面一模一样. 没有区别. 也就是说也是case 1模式.

我们检索这张表, 只有两行数据, 然后回滚, 那么就清空这张表了.

SQL> select * from T1;

	ID NM
---------- ----------
	 1 abc1
	 2 abc2

SQL> rollback;

Rollback complete.

SQL> select * from T1;

no rows selected

SQL>

好了, 现在就是重点了, deferrable可以在事务中临时改变成另1种模式.

也就是说可以由case1 临时设置为 case 2.

syntax:

set constraint <constraint_name> deferred/immediate

注意上面的语句并没有改变这个constraint的任何属性, 只不过是切换为另一种模式

也就是说初始是immediate模式的, 执行上面的语句后就临时变成deferred模式了.

我们接回上面的例子, 执行另一只模式:

SQL> set constraint t1_id deferred;

Constraint set.

我们查下这个constraint的属性, 发现属性完全无改变, Deferred属性还是immediate. 因为Deferred这个属性指的是初始属性.

SQL> select table_name, constraint_name, status, deferrable, deferred, validated from user_constraints where table_name = 'T1';

TABLE_NAME CONSTRAINT_NAME STATUS   DEFERRABLE	   DEFERRED  VALIDATED
---------- --------------- -------- -------------- --------- -------------
T1	   T1_ID	   ENABLED  DEFERRABLE	   IMMEDIATE VALIDATED

这时我们insert上面的三条记录. 发现3条insert都可以被插入到数据表中.

SQL> insert into T1 values(1, 'abc1');

1 row created.

SQL> insert into T1 values(2, 'abc2');

1 row created.

SQL> insert into T1 values(2, 'abc3');

1 row created.

SQL> select * from T1;

	ID NM
---------- ----------
	 1 abc1
	 2 abc2
	 2 abc3

SQL>

则说明这时在insert 语句执行时不会执行这个约束的验证, 三行都被插入到表中了, 但是在提交时回验证.

SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-00001: unique constraint (BILL.T1_ID) violated

见到提交失败, 而且整个事务会回滚, 也就是说这时表里面连第一第二句的insert记录也没有了.

SQL> select * from T1;

no rows selected

这个跟not defferable constraint有区别啊, 那种在每一句sql statement执行验证, 而且验证失败时并不会回滚事务.

好了下面也是重点:

当我们commit失败后被回滚, 也就是代表当前的事务结束了.

而上面我们提到过, 使用 set constraint 语句切换constraint到另一种模式只会在当前事务中生效.

也就是指, 事务结束后, 这个constraint就会返回初始模式!

我们再尝试插入三条语句:

SQL> insert into T1 values(1, 'abc1');

1 row created.

SQL> insert into T1 values(2, 'abc2');

1 row created.

SQL> insert into T1 values(2, 'abc3');
insert into T1 values(2, 'abc3')
*
ERROR at line 1:
ORA-00001: unique constraint (BILL.T1_ID) violated

发现再次在第三句执行时被执行即时验证, 因为在新事务中, constraint t1_id又切换回 immediate 模式了.