美文网首页我是程序员;您好程先生;叫我序员就好了
禁用外键约束来解决输入数据出错的问题之解决方案三

禁用外键约束来解决输入数据出错的问题之解决方案三

作者: wswenyue | 来源:发表于2014-11-20 22:45 被阅读512次

    第三种解决方案:采用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(默认): 立即验证, 执行完一个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
    
    ```

    相关文章

      网友评论

        本文标题:禁用外键约束来解决输入数据出错的问题之解决方案三

        本文链接:https://www.haomeiwen.com/subject/tnxwtttx.html