主键约束索引的奇葩现象

在Oracle数据库中，我们知道创建主键约束的时候，会自动创建唯一索引，靠着唯一索引，保证数据的唯一，删除主键约束时，会自动删除对应的唯一索引。但是最近碰到了个奇怪的问题，同事说测试环境中删除一张表的主键约束，发现约束删了，但唯一索引还在，难道有什么隐藏的问题？

Oracle11.2.0.4，创建测试表，然后创建主键，自动生成同名的索引，

SQL> create table a (id number, name varchar2(30));
Table created.


SQL> alter table a add constraint pk_a primary key (id);
Table altered.


SQL> select constraint_name, constraint_type from user_constraints where table_name='A';
CONSTRAINT_NAME                C
------------------------------ -
PK_A                           P


SQL> select index_name, index_type from user_indexes where table_name='A';              
INDEX_NAME                     INDEX_TYPE
------------------------------ ---------------------------
PK_A                           NORMAL

直接删除约束，看到约束和索引确实都删除了，并未出现同事说的现象，

SQL> alter table a drop constraint pk_a;
Table altered.


SQL> select constraint_name, constraint_type from user_constraints where table_name='A';
no rows selected


SQL> select index_name, index_type from user_indexes where table_name='A';
no rows selected

但是现象在这摆着的，有果必有因，看下MOS，是不是有类似的案例出现过，果然，370633.1这篇文章介绍的和我们碰到的很像，使用ALTER TABLE删除主键约束的时候不能删除隐含的索引，但是请注意，有个前提，就是待删除主键约束的表是通过导入执行的，并不是我们自己手工创建的，而且文章 明确说了如果表是自行创建的，不是导入进来的，删除主键约束的时候会连带删除主键索引，这个问题来自于一个未公布的bug，3894962，通过导入操 作，主键索引并未依赖于主键约束，因此当删除主键约束的时候，不会自动删除对应的主键(/唯一)索引，值得一提的是，9i中并无此现象，从10.1.0之 后的版本才出现此问题，解决方案就是删除索引，一种方式是drop constraint的时候带上drop index，一次性执行，另一种就是drop index删除索引，

我们验证下，从test用户导出表A，再imp导入到用户bisal，

[oracle@app ~]$ exp test/test file=/home/oracle/test.dmp tables=a
[oracle@app ~]$ imp bisal/bisal file=/home/oracle/test.dmp fromuser=test touser=bisal

此时删主键，约束确实删了，但是索引还在，

SQL> alter table a drop constraint pk_a;
Table altered.


SQL> select constraint_name, constraint_type from user_constraints where table_name='A';
no rows selected


SQL> select index_name, index_type from user_indexes where table_name='A';
INDEX_NAME                     INDEX_TYPE
------------------------------ ---------------------------
PK_A                           NORMAL

此时两种方式，一是直接删索引，

SQL> drop index pk_a;
Index dropped.


SQL> select index_name, index_type from user_indexes where table_name='A';
no rows selected

另一种就是删除主键约束的同时删除索引，

SQL> alter table a drop constraint pk_a drop index;
Table altered.


SQL> select constraint_name, constraint_type from user_constraints where table_name='A';
no rows selected


SQL> select index_name, index_type from user_indexes where table_name='A';
no rows selected

说句题外话，以前很少用drop constraint ... drop index，他还是可以带来一定的便利性，例如某些开发组的规范，要求索引名称和约束名称具备固定的前缀，当创建主键时，可能选择先创建唯一索引，再创建主键，以便能自定义主键约束和索引的名称，

SQL> create unique index idx_pk_a on a(id);
Index created.


SQL> alter table a add constraint pk_a primary key (id);
Table altered.


SQL> select index_name, index_type from user_indexes where table_name='A';              
INDEX_NAME                     INDEX_TYPE
------------------------------ ---------------------------
IDX_PK_A                       NORMAL


SQL> select constraint_name, constraint_type from user_constraints where table_name='A';
CONSTRAINT_NAME                C
------------------------------ -
PK_A                           P

此时删除主键，显而易见不能删除索引，因为名称不同，但使用drop constraint ... drop index，就可以做到了一次执行，

SQL> alter table a drop constraint pk_a drop index;
Table altered.


SQL> select index_name, index_type from user_indexes where table_name='A';  
no rows selected


SQL> select constraint_name, constraint_type from user_constraints where table_name='A';
no rows selected

另一个骚操作，就是用drop primary key drop index，

SQL> alter table a drop primary key drop index;
Table altered.


SQL> select constraint_name, constraint_type from user_constraints where table_name='A';
no rows selected


SQL> select index_name, index_type from user_indexes where table_name='A'; 
no rows selected

此外，文章中写的是import，并未提及是imp，还是用的impdp，因此，我们再测下数据泵的执行，

[oracle@app ~]$ expdp test/test directory=dir_test dumpfile=d_test.dmp tables=a
[oracle@app ~]$ impdp bisal/bisal directory=dir_test dumpfile=d_test.dmp fromuser=test touser=bisal

可知现象相同，因此针对这个bug，imp/exp和impdp/expdp都会涉及，

SQL> alter table a drop constraint pk_a;
Table altered.


SQL> select constraint_name, constraint_type from user_constraints where table_name='A';
no rows selected


SQL> select index_name, index_type from user_indexes where table_name='A';
INDEX_NAME                     INDEX_TYPE
------------------------------ ---------------------------
PK_A                           NORMAL

从这个案例，我们能体会到，无论什么技术，一种现象的产生，肯定有他的原因，结论固然重要，更重要的是探索结论的过程，以及为了支持结论需要了解的知识，正所谓知其然，更要知其所以然，就是这回事儿。

近期的热文：

《如何判断应用系统性能好不好？》

《Oracle Cloud创建19c数据库》

《DATE类型的“小陷阱”》

《SQL工具集-格式化结果的SQL》

《聊聊数据库范式》

《如何捕获问题SQL解决过度CPU消耗的问题》

《如何查看JVM运行的堆内存情况》

《打造国产技术产品的必要性》

《Oracle删除字段的方式和风险，你都了解么？》

《登录缓慢的诡异问题》

《从70万字SRE神作提炼出的7千字精华文章》

《公众号600篇文章分类和索引》