剥夺外键约束来缓解输入数据出错的难点之化解方案三

其三种缓解方案:采用Oracle 可顺延约束Deferable。

自律可以是deferrable或not deferrable(暗许)。
not deferrable 约束在每三个DML语句后检查;

deferrable
约束可以在每一个insert,delete,或update(即时方式)后立刻检查,可能在业务末尾检查(延迟情势)

当没有按一定顺序执行多少加载时,那项成效特别有用——它同意先把多少载入子表,然后再装入父表。

另一种用法是在加载不符合某些check约束的多寡之后,对其开展恰当的立异

语法如下:

[ [not] deferrable [initially {immediate | deferred} ] ]

或

[ [initially {immediate | deferred} ] [not] deferrable ]
  • deferrable介绍

deferrable的多少个挑选分化

deferrable代表该约束是可延迟验证的. 它有多个挑选:
Initially immediate(暗许): 立时表明, 执行完2个sql后就举办验证;
Initially deferred: 延迟验证, 当事务提交时或调用set constraint[s]
immediate语句时才验证.
有别于是: 事务提交时证实不通过, 则马上回滚事务; set constraint[s]
immediate时只验证, 不回滚事务.

not deferrable与deferrable区别
分别就在于: “即刻表明的可顺延约束” 是可以依据必要设置成
“延迟验证的可顺延约束”的, 而“不可延迟验证”是不恐怕更改的.

  • deferrable实例

建表

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);

常规插入,没难题

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

1 row inserted

查看霎时表达:数据不可以插入

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

insert into test1 values(-1, 1)

ORA-02290: 违反检查约束条件 (MYHR.CHECK_A)

检验延迟验证:能够执行

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

1 row inserted

SQL> select * from test1;

 A  B

-- --

 1  1

 1 -1

交由延迟证实(commit):验证失败,自动回滚

SQL> commit;

 commit

 ORA-02091: 事务处理已回退

ORA-02290: 违反检查约束条件 (MYHR.CHECK_B)

交付延迟认证(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;

将延迟验证设置为及时说明:则在插入时出错

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用途

物化视图

物化视图(快照),这是它的紧要用途。那几个视图会利用延缓约束来进展视图刷新。在刷新物化视图的经过中,大概会损坏完整性,而且将不可以逐句检验约束。但到实施COMMIT时,数据完整性就没难点了,而且能知足约束。没有延迟约束,物化视图的牢笼或者会使基础代谢进度无法得逞举行。

级联更新

当预测是不是必要更新父/子关系中的主键时,它推向级联更新。看一下其实的事例:

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

```

相关文章