Oracle約束constraint的三個屬性應用

 

Oracle約束constraint是我們經常使用的一種資料庫規則物件。constraint在資料庫中的作用就是從靜態角度對資料完整性進行維護。我們經常使用的主鍵primary key和外來鍵foreign key，本質上就是約束的一種形式。

 

對Oracle的約束，我們有三個屬性可以進行設定，分別為deferrable、deferred和validated。針對不同的需求設計場景，採用不同型別的屬性，可以幫助我們實現不同的約束效果。下面我們分別來進行實驗。

 

1、 環境準備

 

首先我們還是準備資料實驗環境。

 

 

SQL> create table t (id number);

Table created

 

SQL> alter table T

  2    add constraint c_t_id1

  3    check (id>5);

 

Table altered

 

 

我們建立了資料表T，在列id上新增了約束c_t_id1。約束內容很簡單，就是要求id值保證是大於5。約束c_t_id1使用的是預設選項，資料字典中對該約束的表示如下：

 

 

SQL> select constraint_name, constraint_type ctype, SEARCH_CONDITION cond, STATUS, DEFERRABLE, DEFERRED, VALIDATED  from dba_constraints where table_name='T' and wner='SYS';

 

CONSTRAINT_NAME      CTYPE COND       STATUS   DEFERRABLE     DEFERRED  VALIDATED

-------------------- ----- ---------- -------- -------------- --------- -------------

C_T_ID1              C     id>5       ENABLED  NOT DEFERRABLE IMMEDIATE VALIDATED

 

 

注意，此時約束的三個屬性取值分別為：deferrable: not deferrable；deferred：immediate；validated：validated；

 

我們觀察一下此時資料表的插入現象：

 

 

SQL> insert into t values (1); //插入非法的資料；

insert into t values (1)

 

ORA-02290: 違反檢查約束條件 (SYS.C_T_ID1) //立刻報錯，將資料剔除！

 

SQL> insert into t values (6);

1 row inserted

 

SQL> commit;

Commit complete

 

SQL> select * from t;

 

        ID

----------

         6

 

 

結論：在預設情況下，Oracle的約束是不允許延遲（not deferrable）、立即應用和驗證的（immediate、validated）。在資料變化的時候，立即進行約束驗證。

 

2、 deferrable：約束應用可以延遲

 

deferrable預設值為not deferrable，字面含義是不可延遲。那麼我們如果設定可以延遲，效果是什麼呢？

 

 

SQL> alter table T

  2    drop constraint C_T_ID1;

 

Table altered

 

SQL> alter table T

  2    add constraint C_T_ID1

  3    check (id>5)

  4    deferrable;

 

Table altered

 

 

此時，資料字典中的情況是如下：

 

 

SQL> select constraint_name,  SEARCH_CONDITION cond, STATUS, DEFERRABLE, DEFERRED, VALIDATED  from dba_constraints where table_name='T' and wner='SYS';

 

CONSTRAINT_NAME      CTYPE  STATUS   DEFERRABLE     DEFERRED  VALIDATED

-------------------- -----  -------- -------------- --------- -------------

C_T_ID1              C      ENABLED  DEFERRABLE     IMMEDIATE VALIDATED

 

 

與預設情況相比，deferrable屬性變化為了deferrable。我們觀察一下現象：

 

 

SQL> insert into t values (3);

insert into t values (3)

 

ORA-02290: 違反檢查約束條件 (SYS.C_T_ID1)

 

 

在插入資料的時候，立即進行約束驗證。和預設情況下沒有差異。那麼怎麼處理呢？

 

//手工設定deferred屬性為deferred

SQL> set constraint c_t_id1 deferred;

Constraints set

 

SQL> insert into t values (3); //此時插入資料時候，並不進行驗證操作了。

1 row inserted

 

SQL> insert into t values (7);

1 row inserted

 

SQL> commit;

commit

 

ORA-02091: 事務處理已回退

ORA-02290: 違反檢查約束條件 (SYS.C_T_ID1) //直到進行commit的時候，才會應用約束；

 

 

那麼，如何設定回原有的屬性呢？

 

 

SQL> set constraint c_t_id1 immediate;

Constraints set

 

SQL> insert into t values (4); //又恢復插入立刻檢查約束的狀態了？

insert into t values (4)

 

ORA-02290: 違反檢查約束條件 (SYS.C_T_ID1)

 

 

結論：單獨deferrable的含義就是是否允許約束檢查延後進行。單獨設定deferrable為deferrable之後，約束檢查延後是不可以直接使用的，要配合deferred引數，如果該引數是immediate，那麼約束還是在DML的時候進行應用。如果deferred引數設定為deferred，約束就是在事務commit提交的時候應用，出現錯誤就連帶回滾rollback整個事務。

 

3、 deferred：是否進行延遲應用

 

從上面的實驗中，我們可以看出deferred屬性是配合deferrable屬性使用的。當deferrable設定為deferrable之後，可以透過set constraints進行deferred屬性的設定，來確定約束應用時點。

 

本部分確定deferred定義的方式和與deferrable屬性的關係。是可以在定義約束是使用initially關鍵字來確定約束的deferred屬性取值。

 

 

SQL> alter table T

  2    add constraint C_T_ID1

  3    check (id>5)

  4    deferrable initially deferred;

 

Table altered

 

 

set constraints語句只有在約束的deferrable屬性設定為deferrable的時候才可以使用。

 

 

SQL> alter table T

  2    drop constraint C_T_ID1;

 

Table altered

 

SQL> alter table T

  2    add constraint C_T_ID1

  3    check (id>5)

  4  ;

 

Table altered

 

SQL> set constraint c_t_id1 deferred;

 

set constraint c_t_id1 deferred

 

ORA-02447: 無法延遲不可延遲的約束條件

 

 

4、 disable禁用約束和validate驗證約束

 

disable與validate的關係很緊密，相互制約影響。我們觀察下面的實驗：

 

 

SQL> alter table t disable constraint c_t_id1;

 

Table altered

 

SQL>  select constraint_name, SEARCH_CONDITION cond, STATUS, DEFERRABLE, DEFERRED, VALIDATED  from dba_constraints where table_name='T' and wner='SYS';

 

CONSTRAINT_NAME  COND       STATUS   DEFERRABLE     DEFERRED  VALIDATED

---------------- ---------- -------- -------------- --------- -------------

C_T_ID1          id>5       DISABLED NOT DEFERRABLE IMMEDIATE NOT VALIDATED

 

 

透過disable constraint語句，可以對一個約束進行禁用操作。禁用disable下的約束，validated屬性是not validate，也就不起作用的。

 

 

SQL> select * from t;

 

        ID

----------

         6

         2

         4

        86

 

 

資料表約束禁用後，資料完整性被破壞。此時，如果我們直接進行約束啟用。

 

 

SQL> alter table t enable constraint c_t_id1;

 

alter table t enable constraint c_t_id1

 

ORA-02293: 無法驗證 (SYS.C_T_ID1) - 違反檢查約束條件

 

SQL>  select constraint_name, SEARCH_CONDITION cond, STATUS, DEFERRABLE, DEFERRED, VALIDATED  from dba_constraints where table_name='T' and wner='SYS';

 

CONSTRAINT_NAME  COND       STATUS   DEFERRABLE     DEFERRED  VALIDATED

---------------- ---------- -------- -------------- --------- -------------

C_T_ID1          id>5       DISABLED NOT DEFERRABLE IMMEDIATE NOT VALIDATED

 

 

在約束被啟用的時候，會自動進行檢驗。如果資料不滿足條件，Oracle不會開啟該約束引用。

 

enable和validate可以配合使用enable novalidate子句使用。

 

 

SQL> alter table t enable novalidate constraint c_t_id1;

Table altered

 

SQL>  select constraint_name, SEARCH_CONDITION cond, STATUS, DEFERRABLE, DEFERRED, VALIDATED  from dba_constraints where table_name='T' and wner='SYS';

 

CONSTRAINT_NAME  COND       STATUS   DEFERRABLE     DEFERRED  VALIDATED

---------------- ---------- -------- -------------- --------- -------------

C_T_ID1          id>5       ENABLED  NOT DEFERRABLE IMMEDIATE NOT VALIDATED

 

 

此時，只是針對現有T中資料不進行驗證，對新增加變化的資料，同樣會進行驗證。

 

SQL> insert into t values (45);

1 row inserted

 

SQL> commit;

Commit complete

 

SQL> insert into t values (3);

insert into t values (3)

 

ORA-02290: 違反檢查約束條件 (SYS.C_T_ID1)

 

 

 

5、結論

 

預設情況下，Oracle constraint是不開啟延遲約束和原有資料保留驗證的。那麼在什麼樣的場景下，我們可以考慮使用這些特性呢？

 

ü        批次資料匯入、物化檢視重新整理的時候，事務量很大，而且存在資料規律前後顛倒的情況。此時，如果開啟著立即約束應用的開關，可能存在一些暫時性的約束不滿足的情況，從而影響到整個系統的執行。開啟約束延遲驗證，就可以幫助我們解決這個問題；

ü        歷史資料移植。歷史資料存在不規則的情況，很多時候難以滿足我們新系統的資料完整性要求。可以使用not validate的方式，對歷史資料不進行約束控制，而只針對新資料開啟管理；