主键的变更导致索引无法删除

在ITPUB上看到一个有趣的帖子：http://www.itpub.net/thread-1109620-1-1.html。楼主给出了一个例子，由于主键的变更导致用户创建的索引无法被删除。

 

 

下面来重现一下这个问题：

SQL> CREATE TABLE T (ID NUMBER, NAME VARCHAR2(30));

表已创建。

SQL> ALTER TABLE T ADD PRIMARY KEY (ID);

表已更改。

SQL> CREATE INDEX IND_T_ID_NAME ON T (ID, NAME);

索引已创建。

SQL> ALTER TABLE T DISABLE PRIMARY KEY;

表已更改。

SQL> ALTER TABLE T ENABLE PRIMARY KEY;

表已更改。

SQL> DROP INDEX IND_T_ID_NAME;
DROP INDEX IND_T_ID_NAME
           *
第 1 行出现错误:
ORA-02429: 无法删除用于强制唯一/主键的索引

楼主认为这是一个bug，其实这是一个很正常的现象。下面对上面每个SQL进行说明。

第一个SQL：建表；

第二个SQL：为表T添加了一个主键，不过Oracle为了增加主键，会自动在ID列上创建一个唯一索引；

第三个SQL：手工创建了一个索引，注意这个索引包括了主键列ID。

第四个SQL：DISABLE主键，Oracle在DISABLE主键的同时，会自动删除Oracle自动添加的索引。

第五个SQL：ENABLE主键，Oracle在ENABLE主键的同时，仍然需要一个索引来支持主键，但是由于现在表中已经包含了一个索引IND_T_ID_NAME包含主键列了，所以这次Oracle没有在重新创建一个索引，而是使用了现有的索引作为主键的索引。

第六个SQL：删除索引报错。由于主键存在，没有办法删除主键对应的索引，所以这个错误信息是正常的。

其实楼主的操作可以等价于：

SQL> DROP TABLE T;

表已删除。

SQL> CREATE TABLE T (ID NUMBER, NAME VARCHAR2(30));

表已创建。

SQL> CREATE INDEX IND_T_ID_NAME ON T (ID, NAME);

索引已创建。

SQL> ALTER TABLE T ADD PRIMARY KEY (ID);

表已更改。

SQL> DROP INDEX IND_T_ID_NAME;
DROP INDEX IND_T_ID_NAME
           *
第 1 行出现错误:
ORA-02429: 无法删除用于强制唯一/主键的索引

首先想要说明的是，并不是所有碰到的错误都是bug，比如这个例子就是一种很正常的情况，只要对Oracle的实现了解比较清楚，就可以很快找到导致问题的原因。再比如前天处理的一个ORA-600错误，其实是SQL的重复数据导致的，并不是真正意义上的bug。这个ORA-600错误的处理可以参考：http://yangtingkun.itpub.net/post/468/476593和http://yangtingkun.itpub.net/post/468/476653。

另外要说一点，其实一个问题背后总会隐藏着很多的知识，以这个问题为例，想把问题搞清楚，就必须知道：在主键创建时自动创建索引；在DISABLE主键的时候会删除自动创建的索引；在创建主键时，会优先使用现有的索引；当主键存在时，不能删除用于主键的索引等等。

问题还可以再引申一步：Oracle在DISABLE主键或删除主键时，会删除自动创建的索引，而不会删除用户创建的索引。

最终问题可以引申到，如何判断一个索引是Oracle自动创建的，还是用户手工创建的，关于这个问题可以参考：http://yangtingkun.itpub.net/post/468/160390。

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/4227/viewspace-530085/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/4227/viewspace-530085/