rim drop ora- upd else isa pan keep sda
oracle约束状态有几个项目,会让人迷惑,分别是:
所以,通过简单的实验,来确认它们之间的区别。
以下实验在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;
- --证明,更新的时候会检查的
通过试验可以获得几点:
在实际情况中,常常会遇到那么一些人有一些习惯:不对字段或者表做任何约束。
好处是:节约了表设计时间,减少了变更说需要耗费的时间。
坏处是:终归需要通过其它程序来实现业务逻辑,必须在程序中对数据检查,而且是必须每个应用中都做这种检查,所耗费的时间未必少,可能还更多。
这种习惯,大体而言不好,虽然设计的时候节约了时间,但最终还是要付出代价的,否则为什么数据库要设计这种功能。
--
俗话说:磨刀不误砍材工。
在数据库中实现了约束,那么所有应用就可以放心地使用数据,尤其是一些要求很高的生产系统。
此外,如果担心加载数据的时候,速度会变慢,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约束约束状态和设计习惯
来源: http://www.bubuko.com/infodetail-2338292.html