​[20231115]建立enable novalidate約束2.txt

[20231115]建立enable novalidate約束2.txt

--//昨天最佳化生產系統發現有1個表沒有任何索引，而且還很大。當我想當然建立主鍵索引時遇到了問題，主鍵重複，無法建立主鍵索引。

> select JLXH,jgid,count(*) from YB_GJYB_ZYDJXX group by JLXH,jgid having count(*)>=2;
      JLXH       JGID   COUNT(*)
---------- ---------- ----------
     68897          1          2
--//有2條記錄重複.我仔細比較這兩條記錄發現內容完全一樣。
--//也就是我不能建立唯 一索引，只能建立普通索引。但是我想避免以後不再出現這樣的情況。
--//好像可以透過建立enable novalidate約束，避免以後再出來類似問題，在測試環境測試看看，並做一個記錄。

1.環境：
SCOTT@book> @ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SCOTT@book> create table deptx as select * from dept;
Table created.

SCOTT@book> insert into deptx select * from dept where deptno=40;
1 row created.

SCOTT@book> commit ;
Commit complete.
--//分析略。

2.測試：

SCOTT@book> create unique index pk_deptx on deptx(deptno);
create unique index pk_deptx on deptx(deptno)
                                *
ERROR at line 1:
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
--//有重複值,無法建立unique索引.

SCOTT@book> create index pk_deptx on deptx(deptno);
Index created.

SCOTT@book> alter table scott.deptx add constraint pk_deptx primary key (deptno) enable novalidate;
Table altered.

SCOTT@book> insert into deptx select * from dept where deptno =10;
insert into deptx select * from dept where deptno =10
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.PK_DEPTX) violated
--//這樣以後不可能插入重複值.

SCOTT@book> update deptx set deptno =10 where deptno=40 and rownum=1;
update deptx set deptno =10 where deptno=40 and rownum=1
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.PK_DEPTX) violated

SCOTT@book> update deptx set deptno=50 where deptno=40 and rownum=1;
1 row updated.

SCOTT@book> commit ;
Commit complete.

SCOTT@book> select * from deptx;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        50 OPERATIONS     BOSTON
        40 OPERATIONS     BOSTON

--//一些細節可以參考以前寫的blog:
--//[20120824]oracle中的 CONSTRAINT 屬性ENABLE DISABLE VALIDATE NOVALIDATE.txt ==> http://blog.itpub.net/267265/viewspace-741814/

是否要求滿足約束   Validate                     Novalidate
------------------------------------------------------------------------------------
                   已有記錄     新增/修改記錄   已有記錄    新增/修改記錄
Enable             Yes　    　  Yes             No　         Yes
Disable            Yes　        No              No           No

--//Validate確保已有資料符合約束；
--//Novalidate不必考慮已有資料是否符合約束。
--//除非Novalidate被指定，Enable預設Validate；
--//除非Validate被指定，Disable預設Novalidate；

--//Validate和Novalidate對Enable和Disable沒有任何預設暗示。
--//Enable Validate與Enable相同，檢查已有記錄和新增記錄，確保都符合約束；
--//Enable Novalidate 允許已有記錄不必滿足約束條件，但新增/修改的記錄必須滿足；
--//Disable Validate禁用約束，刪除約束上的索引，不允許修改任何被約束的記錄；
--//Disable Novalidate與Disable相同，禁用約束，刪除約束上的索引，且允許修改被約束的記錄。

--//如果設定約束是Disable Validate這個特性很有意思，可以實現只讀表的功能。