主鍵約束設定Enable Novalidate時報錯

Hoegh發表於2016-06-19
最近專案中要求對一張業務表修改主鍵，前提是之前的業務資料不能受到影響。我想到了針對約束的enable novalidate，也就是說允許已有記錄不必滿足約束條件，但新增/修改的記錄必須滿足。

我們先回顧一下關於啟用和禁用約束的4種組合：
啟用約束：
enable( validate)：啟用約束,建立索引,對已有及新加入的資料執行約束。
enable novalidate：啟用約束,建立索引,僅對新加入的資料強制執行約束,而不管表中的現有資料。

禁用約束：
disable( novalidate)：關閉約束,刪除索引,可以對約束列的資料進行修改等操作。
disable validate ：關閉約束,刪除索引,不能對錶進行 插入/更新/刪除等操作。

我們針對主鍵約束的enable novalidate做如下測試。

1.建立測試表

點選(此處)摺疊或開啟
  1. SQL> select * from v$version;

  3. BANNER
  4. ----------------------------------------------------------------
  5. Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
  6. PL/SQL Release 10.2.0.4.0 - Production
  7. CORE    10.2.0.4.0    Production

  9. TNS for 64-bit Windows: Version 10.2.0.4.0 - Production
  10. NLSRTL Version 10.2.0.4.0 - Production

  12. SQL> create table hoegh(x number,y number,z number,
  13.   2 constraint pk_hoegh primary key(x,y));

  15. Table created

  17. SQL>
  18. SQL> insert into hoegh values(1,1,1);

  20. 1 row inserted

  22. SQL> insert into hoegh values(1,2,1);

  24. 1 row inserted

  26. SQL> commit;

  28. Commit complete

  30. SQL> select * from hoegh;

  32.          X Y Z
  33. ---------- ---------- ----------
  34.          1 1 1
  35.          1 2 1

  37. SQL>

2.刪除原有主鍵

點選(此處)摺疊或開啟
  1. SQL> alter table hoegh drop constraint PK_HOEGH cascade drop index;

  3. Table altered

3.新增新主鍵並設定Enable Novalidate，報錯

點選(此處)摺疊或開啟
  1. SQL> alter table hoegh add constraint PK_HOEGH primary key(x,z) enable novalidate;

  3. alter table hoegh add constraint PK_HOEGH primary key(x,z) enable novalidate

  5. ORA-02437: 無法驗證 (HOEGH.PK_HOEGH) - 違反主鍵

  7. SQL>
我們看到新增主鍵約束失敗了，提示“ORA-02437: 無法驗證 (HOEGH.PK_HOEGH) - 違反主鍵”，也就是說已有的資料不滿足新的主鍵約束；雖然我們設定了Novalidate，但是和我們預想的結果不一致，資料庫仍然檢驗了已有資料。
我們知道主鍵約束=唯一約束+非空約束，那麼這個是不是和唯一約束有關係呢？接下來，我們嘗試新增一個唯一約束，同時設定Enable Novalidate。

點選(此處)摺疊或開啟

  1. SQL> alter table hoegh add constraint unq_xz unique(x,z) enable novalidate;

  3. alter table hoegh add constraint unq_xz unique(x,z) enable novalidate

  5. ORA-02299: 無法驗證 (HOEGH.UNQ_XZ) - 找到重複關鍵字

  7. SQL>
我們看到也失敗了，看來根源就在這裡。建立主鍵時系統預設還要同時建立一個唯一索引，novalidate只是使主鍵約束不對已經存在的資料進行檢驗，並不對建立唯一索引進行控制，所以導致失敗。
那麼如何解決這個問題呢？

4.建立普通索引，然後新增主鍵約束並設定Enable Novalidate，成功

我們可以在建立主鍵前先給該欄位建個普通非唯一索引，這樣建主鍵時系統就會自動利用這個普通索引了。
點選(此處)摺疊或開啟
  1. SQL>
  2. SQL> create index idx_xz on hoegh(x,z);

  4. Index created

  6. SQL> alter table hoegh add constraint PK_HOEGH primary key(x,z) enable novalidate;

  8. Table altered

  10. SQL> insert into hoegh values(1,3,1);

  12. insert into hoegh values(1,3,1)

  14. ORA-00001: 違反唯一約束條件 (HOEGH.PK_HOEGH)

  16. SQL> select * from hoegh;

  18.          X Y Z
  19. ---------- ---------- ----------
  20.          1 1 1
  21.          1 2 1

  23. SQL>
  24. SQL> insert into hoegh values(1,3,2);

  26. 1 row inserted

  28. SQL> commit;

  30. Commit complete

  32. SQL>
  33. SQL> select * from hoegh;

  35.          X Y Z
  36. ---------- ---------- ----------
  37.          1 1 1
  38.          1 2 1
  39.          1 3 2

  41. SQL>
我們看到，已有的記錄不受新約束的影響，而新增記錄必須滿足新的主鍵約束。

5.結論

從上面的測試結果看出,enable novalidate在針對主鍵約束或者唯一約束使用時需要先建立相關索引,再使用enable novalidate。


                                                                                                                                                                                          ~~~~~~~ the end~~~~~~~~~
                                                                                                                                                                                                               hoegh
                                                                                                                                                                                                           2016.06.19

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/30162081/viewspace-2120425/，如需轉載，請註明出處，否則將追究法律責任。

相關文章