今天刚到办公室，同事让帮他看个问题，说是索引删除不掉，数据库版本11.2.0.4.0。我操作了下的确报错，如下：

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

这个错误很明显是这个索引被主键使用了，可是主键的名字是PK_ORD_ORD_BSC_M并不是这个索引名字，而且索引是复合索引，主键是单列主键。

SQL>   select constraint_name from user_constraints where table_name='ORD_ORD_BSC_M';

CONSTRAINT_NAME
-------------------- 
PK_ORD_ORD_BSC_M

SQL> select INDEX_NAME,column_name from user_ind_columns where table_name ='ORD_ORD_BSC_M';

INDEX_NAME           COLUMN_NAME
-------------------- --------------------
IX_ORD_ORD_BSC_M_04  BFR_ORD_ID
IX_ORD_ORD_BSC_M_15  ORD_ID
IX_ORD_ORD_BSC_M_15  CST_ID
IX_ORD_ORD_BSC_M_02  INST_ID
IX_ORD_ORD_BSC_M_02  INST_DTM

从以上信息就可以看出，IX_ORD_ORD_BSC_M_15这个索引被主键使用了，创建主键的时候发现ORD_ID字段有索引，就没有在ORD_ID字段上创建名字和主键一样的索引。通过查询，发现，主键真用的这个索引。

SQL>   select constraint_name,index_name  from user_constraints where table_name='ORD_ORD_BSC_M';

CONSTRAINT_NAME      INDEX_NAME
-------------------- --------------------
PK_ORD_ORD_BSC_M     IX_ORD_ORD_BSC_M_15

删除主键后，IX_ORD_ORD_BSC_M_15索引成功删除。

SQL> alter table ORD_ORD_BSC_M drop constraint PK_ORD_ORD_BSC_M cascade;

Table altered.

SQL> drop index IX_ORD_ORD_BSC_M_15;

Index dropped.

然后创建主键。

SQL> alter table ORD_ORD_BSC_M add constraint PK_ORD_ORD_BSC_M primary key (ORD_ID);

Table altered.

此时由于ORD_ID字段没有索引，创建主键的时候会创建一个名字和主键名字一样的索引。

QL> select INDEX_NAME,column_name from user_ind_columns where table_name ='ORD_ORD_BSC_M';

INDEX_NAME           COLUMN_NAME
-------------------- --------------------
IX_ORD_ORD_BSC_M_04  BFR_ORD_ID
IX_ORD_ORD_BSC_M_02  INST_ID
IX_ORD_ORD_BSC_M_02  INST_DTM
PK_ORD_ORD_BSC_M     ORD_ID

而且主键用到就是这个和主键名字一样的索引。

SQL>   select constraint_name,index_name  from user_constraints where table_name='ORD_ORD_BSC_M';
CONSTRAINT_NAME      INDEX_NAME

-------------------- --------------------

PK_ORD_ORD_BSC_M     PK_ORD_ORD_BSC_M

这并不是创建主键加不加using index的问题，下面做下测试。下面模拟下这个案例，先删除这个主键，创建IX_ORD_ORD_BSC_M_15索引。

SQL> alter table ORD_ORD_BSC_M drop constraint PK_ORD_ORD_BSC_M cascade;

Table altered.

SQL> create index IX_ORD_ORD_BSC_M_15 on ORD_ORD_BSC_M(ORD_ID,CST_ID);

Index created.

下面创建主键，看看主键用的是IX_ORD_ORD_BSC_M_15索引还是和主键名字相同的索引。

SQL> alter table ORD_ORD_BSC_M add constraint PK_ORD_ORD_BSC_M primary key (ORD_ID) using index;

Table altered.

SQL> select constraint_name,index_name  from user_constraints where table_name='ORD_ORD_BSC_M';

CONSTRAINT_NAME           INDEX_NAME
------------------------- --------------------
PK_ORD_ORD_BSC_M          IX_ORD_ORD_BSC_M_15

可见主键用的是IX_ORD_ORD_BSC_M_15索引，而没有创建和主键名字相同的索引，这就导致IX_ORD_ORD_BSC_M_15索引无法删除的情况。

本文固定链接: http://www.dbdream.com.cn/2015/07/ora-02429-cannot-drop-index-used-for-enforcement-of-uniqueprimary-key%e9%94%99%e8%af%af/ | 信春哥，系统稳，闭眼上线不回滚！