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

1、删除索引主键约束引起 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该约束。看下面

2、的例子：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,C

3、ONSTRAINT_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

4、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;CONSTR

5、AINT_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从上面可以看出，如果

6、创建了 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 uni

7、que 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 existi

8、ng) 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 empl

9、oyees2 (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_N

10、AME 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时重建索引，同时可以消除多余的索引。在删除约束时可选择保留索引：1。ALTER TABLE employees DROP PRIMARY KEY KEEP INDEX;2。ALTER TABLE employees DROP CONSTRAINT emp_pk;对应的索引必须为非唯一索引对于第二条语句，必须是对应的索引为非唯一索引，否则会连索引一并删除。