删除索引主键约束引起ORA-02429错误的解决方法.doc

删除索引主键约束引起ORA-02429错误的解决方法2008-05-29 15:46drop 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该约束。看下面的例子：SQLCREATE TABLE employees2 (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 EMPLOYEESSQL 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_C007594SQL 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 employees2 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 selectedSQL 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_C007594SQL ALTER TABLE employees2 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 employees2 (empno NUMBER(6),3 name VARCHAR2(30),4 dept_no NUMBER(2),5 CONSTRAINT emp_pk PRIMARY KEY(empno)6 USING INDEX7 (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 EMPLOYEESSQL 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时重建索引，同时可以消除多余的索引。在删