• oracle约束约束状态和设计习惯


    oracle约束状态有几个项目,会让人迷惑,分别是:

    1. enable/disable--是否启用/禁用
    2. validate/invalidate--确认/不确认
    3. deferrable/not deferrable--可延迟/不可延迟

    所以,通过简单的实验,来确认它们之间的区别。

    以下实验在12.2.0.1中进行。

     1  drop table t_test_check purge;
     2  SELECT * FROM USER_CONSTRAINTS  where table_name='T_TEST_CHECK';
     3  select * from T_TEST_CHECK
     4  --1. enable
     5  create table t_test_check(
     6    id int   constraint   ck_id_not_null check( id is not null)  disable novalidate,
     7    name varchar2(30)  constraint ck_name_not_null check(name is not null)  
     8  )
     9  
    10  declare
    11    i int;
    12  begin
    13    for i in 1..100 loop
    14      insert into t_test_check(id, name)
    15        values(null, to_char(sysdate,'yyyy')  );
    16      dbms_output.put_line(i);
    17    end loop;
    18    commit;
    19  end; 
    20  alter table t_test_check modify constraint ck_id_not_null enable
    21  alter table t_test_check modify constraint ck_id_not_null disable
    22  --结论,enable/disable实际控制约束是否可。disable的情况下,约束根本不被检查
    23  
    24  --2. deferrable initially deferred/immediate
    25  
    26  truncate table t_test_check
    27  --必须删除掉,否则无法修改为deferrable,并触发ora-02447异常
    28  alter table t_test_check drop constraint  ck_name_not_null;
    29  alter table t_test_check add constraint ck_name_not_null check(name is not null)  deferrable initially deferred enable validate;
    30    
    31  declare
    32    i int;
    33  begin
    34    for i in 1..100 loop
    35      insert into t_test_check(id, name)
    36        values(i,case when i=20 then null else to_char(sysdate,'yyyy') end);
    37      dbms_output.put_line(i);
    38    end loop;
    39    commit;
    40  end;
    41  --结论,在enable的情况下,deferrable initially deferred的唯一作用就是提交的时候再验证,而
    42  --immediate是语句级别检验,但要是不通过,则都回滚整个事务。
    43  
    44  --3. validate/novalidate
    45  alter table t_test_check  modify constraint CK_ID_NOT_NULL disable;
    46  
    47  declare
    48    i int;
    49  begin
    50    for i in 1..10 loop
    51      insert into t_test_check(id, name)
    52        values(null, to_char(sysdate,'yyyy')  );
    53      dbms_output.put_line(i);
    54    end loop;
    55    commit;
    56  end;
    57  
    58  alter table t_test_check  modify constraint CK_ID_NOT_NULL enable novalidate;
    59  --证明novalidate不对过去的数据检查
    60  update t_test_check set id=null;  
    61  --证明,更新的时候会检查的
    62  

    通过试验可以获得几点:

    1. enable/disable完全决定约束是否启用,只有enalbe的情况下,其它状态才可用
    2. validate/invalide-后者只见确认新的数据和修改数据,前者确认所有的(老的现有数据)
    3. deferrable/not deferrable 仅仅用于延迟检查而已,但最终还是要检查的。如果是deferrable,则可以按照语句级别或者事务级别检查。

    在实际情况中,常常会遇到那么一些人有一些习惯:不对字段或者表做任何约束。

    好处是:节约了表设计时间,减少了变更说需要耗费的时间。

    坏处是:终归需要通过其它程序来实现业务逻辑,必须在程序中对数据检查,而且是必须每个应用中都做这种检查,所耗费的时间未必少,可能还更多。

    这种习惯,大体而言不好,虽然设计的时候节约了时间,但最终还是要付出代价的,否则为什么数据库要设计这种功能。

    --

    俗话说:磨刀不误砍材工。

    在数据库中实现了约束,那么所有应用就可以放心地使用数据,尤其是一些要求很高的生产系统。

    此外,如果担心加载数据的时候,速度会变慢,oracle也是有提供折中的方案--deferrable,enable/disable.

    通常的做法是在加载前先disable约束,加载之后再启用约束(这种操作在olap或者dw中尤其常见),如果是uk或者pk,可以使用keep index。

    例如:

    create table t_test_uk(
      sid varchar2(20) constraint  ck_test_uk_sid unique constraint ck_test_uk_notnull check(sid is not  null)
    )
    /
    begin
      for J  in 1..10  loop
        insert into t_test_uk(sid) values('CHN-'||trim(to_char(J,'00')));    
        --DBMS_OUTPUT.put_line('CHN-'||trim(to_char(J,'00')));
      end loop;
      commit;
    end;
    
    
    ALTER TABLE T_TEST_UK MODIFY CONSTRAINT ck_test_uk_sid DISABLE KEEP INDEX;
    
    SELECT * FROM T_TEST_UK WHERE SID='CHN-01';

    最后的select语句,无论是否启用约束,都可以利用上索引。
    而且重新启用索引,也是很容易的:

    ALTER TABLE T_TEST_UK MODIFY CONSTRAINT CK_TEST_UK_SID enable;

  • 相关阅读:
    算法导论:快速排序
    lintcode:打劫房屋 III
    lintcode:打劫房屋II
    lintcode:打劫房屋
    算法导论:二叉搜索树
    算法导论:整齐打印
    砝码称重问题二
    多重背包问题II
    多重背包问题
    lintcode:背包问题II
  • 原文地址:https://www.cnblogs.com/lzfhope/p/7630327.html
Copyright © 2020-2023  润新知