Oracle 删除和重建由primary 约束建立的索引.docVIP

Oracle 删除和重建由primary 约束建立的索引 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该约束。看下面的例子： 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 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 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 subsequ