當先建立唯一約束後再建立主鍵約束的情況下，如果使用普通方法刪除主鍵約束後，唯一約束索引不會被刪除，這是Oracle 10g的一個PROBLEM。
本文透過一個實驗給大家演示一下這個問題的出現過程及處理方法。

【問題現象】
在10g環境下，在刪除主鍵約束後，在插入重複資料時候仍然報“ORA-00001: unique constraint (SEC.PK_T) violated”錯誤。
現象是主鍵約束已經刪除成功，但是唯一約束索引沒有級聯刪除。

【問題模擬】
1.建立表T
sec@ora10g> create table t (x int, y int);

Table created.

2.先建立惟一約束索引
sec@ora10g> create unique index pk_t on t (x);

Index created.

3.再建立主鍵約束
sec@ora10g> alter table t add (constraint pk_t primary key(x));

Table altered.

4.檢視約束資訊
sec@ora10g> col OWNER for a5
sec@ora10g> col CONSTRAINT_NAME for a30
sec@ora10g> col TABLE_NAME for a10
sec@ora10g> col INDEX_OWNER for a12
sec@ora10g> col INDEX_NAME for a10
sec@ora10g> select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,INDEX_OWNER,INDEX_NAME from user_constraints where table_name = 'T';

OWNER CONSTRAINT_NAME                C TABLE_NAME INDEX_OWNER INDEX_NAME
----- ------------------------------ - ---------- ------------ ----------
SEC   PK_T                           P T          SEC          PK_T

5.檢視索引資訊
sec@ora10g> select INDEX_NAME,INDEX_TYPE,GENERATED from user_indexes;

INDEX_NAME INDEX_TYPE                  G
---------- --------------------------- -
PK_T       NORMAL                      N

GENERATED欄位說明：
GENERATED    VARCHAR2(1)        Indicates whether the name of the index is system generated (Y) or not (N)

6.刪除主鍵約束
sec@ora10g> alter table t drop constraint pk_t cascade;

Table altered.

7.確認約束和索引刪除情況
sec@ora10g> select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,INDEX_OWNER,INDEX_NAME from user_constraints where table_name = 'T';

no rows selected

sec@ora10g> select INDEX_NAME,INDEX_TYPE,GENERATED from user_indexes;

INDEX_NAME INDEX_TYPE                  G
---------- --------------------------- -
PK_T       NORMAL                      N

可見，此時索引沒有被刪除。
因此，此時如果插入重複的資料，還是會報違反“約束”
sec@ora10g> insert into t values (1,1);

1 row created.

sec@ora10g> insert into t values (1,1);
insert into t values (1,1)
*
ERROR at line 1:
ORA-00001: unique constraint (SEC.PK_T) violated

【問題原因】
這個問題的原因可以參考MOS的[ID 309821.1]文章，文章中給出了具體原因。
Oracle 10G Does not Drop User Index Associated With Unique/Primary Key Constraints [ID 309821.1]

原來是Oracle在的10g版本中對內部函式"atbdui"進行了調整，導致在刪除約束的時候無法刪除使用者建立的索引。
這個現象被Oracle分類到了“PROBLEM”。

【處理方法】
在刪除約束的時候需要顯示的指定“drop index”選項來完成索引的級鏈刪除。
sec@ora10g> alter table t drop constraint pk_t cascade drop index;

Table altered.

經確認，約束和索引均被刪除後，嘗試插入兩條重複資料，成功。
sec@ora10g> select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,INDEX_OWNER,INDEX_NAME from user_constraints where table_name = 'T';

no rows selected

sec@ora10g> select INDEX_NAME,INDEX_TYPE,GENERATED from user_indexes;

no rows selected

sec@ora10g> insert into t values (1,1);

1 row created.

sec@ora10g> insert into t values (1,1);

1 row created.

【BTW】如果之前建立的是普通索引，如果使用“cascade”選項，該索引也不會被刪除掉，實驗參考如下：
sec@ora10g> drop table t purge;

Table dropped.

sec@ora10g> create table t (x int, y int);

Table created.

sec@ora10g> create index pk_t on t (x);

Index created.

sec@ora10g> alter table t add (constraint pk_t primary key(x));

Table altered.

sec@ora10g> select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,INDEX_OWNER,INDEX_NAME from user_constraints where table_name = 'T';

OWNER CONSTRAINT_NAME                C TABLE_NAME INDEX_OWNER INDEX_NAME
----- ------------------------------ - ---------- ------------ ----------
SEC   PK_T                           P T          SEC          PK_T

sec@ora10g> alter table t drop constraint pk_t cascade;

Table altered.

sec@ora10g> select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,INDEX_OWNER,INDEX_NAME from user_constraints where table_name = 'T';

no rows selected

sec@ora10g> select INDEX_NAME,INDEX_TYPE,GENERATED from user_indexes;

INDEX_NAME INDEX_TYPE                  G
---------- --------------------------- -
PK_T       NORMAL                      N

sec@ora10g> insert into t values (1,1);

1 row created.

sec@ora10g> insert into t values (1,1);

1 row created.

【小結】
實驗是答疑解惑的重要手段。當遇到一個問題後，我們應該以一種科學嚴謹的態度對待之，必有所獲。

Good luck.

secooler
10.03.01

-- The End --

【PK】Oracle 10g刪除主鍵約束後無法刪除唯一約束索引問題的模擬與分析

相關文章