Oracle約束enable validate時資料檢查

Oracle約束按約束狀態又可分為disable novalidate,disable validate,enable novalidate,enable validate. 其中disable/enable代表是否啟用約束，validate/novalidate代表啟用約束時是否對錶中原有資料作檢查。

      這裡模擬生產環境中的情況，先建一張沒有約束的表，等有一定資料後建立enable novalidate約束，進而執行enable validate來規範表中所有資料，具體如下：

      1. 建立原始表及資料

          SQL> create table t1(

                2  id number,

                3  name varchar2(20));

          SQL> insert into t1 values(1,'A');

          SQL> insert into t1 values(1,'B');

          SQL> insert into t1 values(2,'C');

          SQL> insert into t1 values(3,'D');

          SQL> insert into t1 values(3,'E');

          SQL> insert into t1 values(4,'F');

      2. 在表上建立enable novalidate主鍵

          SQL> alter table t1 add constraint pk_t1 primary key (id)

                2  deferrable enable novalidate;

          此時主鍵已經生效，但源表中包含不符合約束的資料

      3. 嘗試將約束狀態更改為 enable validate，卻發現原有資料有問題

          SQL> alter table t1 enable validate constraint pk_t1;

          alter table t1 enable validate constraint pk_t1

          *

          第 1 行出現錯誤:

          ORA-02437: 無法驗證 (SCOTT.PK_T1) - 違反主鍵

      4. 找出違反約束的資料，這裡採用Exceptions表儲存異常資料

          SQL>@$ORACLE_HOME/rdbms/admin/utlexpt1.sql    ------  建立異常資料儲存表

          SQL>alter table t1 enable validate constraint pk_t1 exceptions into exceptions;                                     ------  將異常資料裝入異常表

      5. 根據異常表，找出對應源表中的異常資料

           SQL> select id,name from t1 where rowid in(

                 2  select row_id from exceptions);

                 ID NAME

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

                         1 A

                         1 B

                         3 D

                         3 E

      6. 修正異常資料後，將索引生效。