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；