阿里云-云小站(无限量代金券发放中)
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

Oracle约束状态和设计习惯

201次阅读
没有评论

共计 2900 个字符,预计需要花费 8 分钟才能阅读完成。

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

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

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

以下实验在 12.2.0.1 中进行。

drop table t_test_check purge;
 SELECT * FROM USER_CONSTRAINTS  where table_name=’T_TEST_CHECK’;
 select * from T_TEST_CHECK
 –1. enable
 create table t_test_check(
  id int  constraint  ck_id_not_null check(id is not null)  disable novalidate,
  name varchar2(30)  constraint ck_name_not_null check(name is not null) 
 )
 
 declare
  i int;
 begin
  for i in 1..100 loop
    insert into t_test_check(id, name)
      values(null, to_char(sysdate,’yyyy’)  );
    dbms_output.put_line(i);
  end loop;
  commit;
 end;
 alter table t_test_check modify constraint ck_id_not_null enable
 alter table t_test_check modify constraint ck_id_not_null disable
 – 结论,enable/disable 实际控制约束是否可。disable 的情况下,约束根本不被检查
 
 –2. deferrable initially deferred/immediate
 
 truncate table t_test_check
 – 必须删除掉,否则无法修改为 deferrable, 并触发 ora-02447 异常
 alter table t_test_check drop constraint  ck_name_not_null;
 alter table t_test_check add constraint ck_name_not_null check(name is not null)  deferrable initially deferred enable validate;
 
 declare
  i int;
 begin
  for i in 1..100 loop
    insert into t_test_check(id, name)
      values(i,case when i=20 then null else to_char(sysdate,’yyyy’) end);
    dbms_output.put_line(i);
  end loop;
  commit;
 end;
 – 结论,在 enable 的情况下,deferrable initially deferred 的唯一作用就是提交的时候再验证,而
 –immediate 是语句级别检验,但要是不通过,则都回滚整个事务。
 
 –3. validate/novalidate
 alter table t_test_check  modify constraint CK_ID_NOT_NULL disable;
 
 declare
  i int;
 begin
  for i in 1..10 loop
    insert into t_test_check(id, name)
      values(null, to_char(sysdate,’yyyy’)  );
    dbms_output.put_line(i);
  end loop;
  commit;
 end;
 
 alter table t_test_check  modify constraint CK_ID_NOT_NULL enable novalidate;
 – 证明 novalidate 不对过去的数据检查
 update t_test_check set id=null; 
 – 证明,更新的时候会检查的

通过试验可以获得几点:

  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;

更多 Oracle 相关信息见 Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

本文永久更新链接地址 :http://www.linuxidc.com/Linux/2017-10/147306.htm

正文完
星哥玩云-微信公众号
post-qrcode
 0
星锅
版权声明:本站原创文章,由 星锅 于2022-01-22发表,共计2900字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
【腾讯云】推广者专属福利,新客户无门槛领取总价值高达2860元代金券,每种代金券限量500张,先到先得。
阿里云-最新活动爆款每日限量供应
评论(没有评论)
验证码
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中