cannot drop index

drop index时出现如下错误：
SQL> drop index oos_index;

drop index oos_index

*

ERROR at line 1:

ORA-02429: cannot drop index used for enforcement of unique/primary key

我们知道当创建Primary key和unique约束时，如果在该key上不存在索引，则Oracle会自动创建对应的unique索引，而当你要删除该索引时，必须先Disable或Drop该约束。看下面的例子：

SQL>CREATE TABLE employees
2 (
3 empno NUMBER(6) PRIMARY KEY,
4 name VARCHAR2(30),
5 dept_no NUMBER(2)
6 );

Table created.

SQL> select index_name,owner,table_NAME from all_indexes where owner=’SFA’ AND table_name=’EMPLOYEES’;

INDEX_NAME OWNER TABLE_NAME
———————- ——————- —————–
SYS_C007594 SFA EMPLOYEES

SQL> SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,INDEX_NAME FROM ALL_CONSTRAINTS WHERE TABLE_NAME=’EMPLOYEES’;

CONSTRAINT_NAME C TABLE_NAME INDEX_NAME
—————————— — ————————- ——————
SYS_C007594 P EMPLOYEES SYS_C007594

SQL> DROP INDEX SYS_C007594;
DROP INDEX SYS_C007594
*
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key

SQL> ALTER TABLE employees MODIFY PRIMARY KEY DISABLE; ()

Table altered.

SQL> select index_name,owner,table_NAME from all_indexes where owner=’SFA’ AND table_name=’EMPLOYEES’;

no rows selected

SQL> SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,INDEX_NAME FROM ALL_CONSTRAINTS WHERE TABLE_NAME=’EMPLOYEES’;

CONSTRAINT_NAME C TABLE_NAME INDEX_NAME
—————————— — ————————- ——————
SYS_C007594 P EMPLOYEES SYS_C007594

SQL> ALTER TABLE employees
2 MODIFY PRIMARY KEY ENABLE;

Table altered.

SQL> select index_name,owner,table_NAME from all_indexes where owner=’SFA’ AND table_name=’EMPLOYEES’;

INDEX_NAME OWNER TABLE_NAME
——————- —————– ————-
SYS_C007594 SFA EMPLOYEES

从上面可以看出，如果创建了Primary Key约束，则Oracle会自动帮你创建相应的unique索引。当把Primary Key约束Disable时会自动删除对应的Unique索引，而重新将该约束Enable时，Oracle会重建
Unique索引。特别要注意：当Disable PK或Unique约束时，Oracle只会删除对应的Unique索引。可参考Oracle文档中的解释：

If you enable a unique or primary key constraint, and if no index exists on the key, Oracle creates a unique index. This index is dropped if the constraint is subsequently disabled, and Oracle rebuilds the index every time the constraint is enabled.

To avoid rebuilding the index and eliminate redundant indexes, create new primary key and unique constraints initially disabled. Then create (or use existing) nonunique indexes to enforce the constraint. Oracle does not drop a nonunique index when the constraint is disabled, so subsequent ENABLE operations are facilitated.

在Oracle 9i中，用于支持Primary Key和Unique Key约束的索引可独立于约束本身，实现方法是在CREATE TABLE或ALTER TABLE时指定USING INDEX子句，例子如下：

SQL> CREATE TABLE employees
2 (empno NUMBER(6),
3 name VARCHAR2(30),
4 dept_no NUMBER(2),
5 CONSTRAINT emp_pk PRIMARY KEY(empno)
6 USING INDEX
7 (CREATE INDEX emp_pk_idx ON employees(empno) TABLESPACE indx)
8 );

Table created.

SQL> select index_name,owner,table_NAME from all_indexes where owner=’SFA’ AND table_name=’EMPLOYEES’;

INDEX_NAME OWNER TABLE_NAME
————————— ————- ———————-
EMP_PK_IDX SFA EMPLOYEES

SQL> SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,INDEX_NAME FROM ALL_CONSTRAINTS WHERE TABLE_NAME=’EMPLOYEES’;

CONSTRAINT_NAME C TABLE_NAME INDEX_NAME
—————————— — ——————– —————
EMP_PK P EMPLOYEES EMP_PK_IDX

这样做的好处是：

1。可将索引存储在指定的表空间中，从而与表分离

2。通过创建一个非唯一索引，让PK或Unique Key使用，可避免在Enable或Disable PK或Unique Key时重建索引，同时可以消除多余的索引。

在删除约束时可选择保留索引：
1。ALTER TABLE employees DROP PRIMARY KEY KEEP INDEX;
2。ALTER TABLE employees DROP CONSTRAINT emp_pk;–对应的索引必须为非唯一索引

对于第二条语句，必须是对应的索引为非唯一索引，否则会连索引一并删除。