主键约束索引的奇葩现象

缺乏、安全感 2022-12-16 10:53 76阅读 0赞

在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

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

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

![format_png][]

![format_png 1][]

我们验证下，从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都会涉及，

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

**近期的热文：**

《[如何判断应用系统性能好不好？][Link 1]》

《[Oracle Cloud创建19c数据库][Oracle Cloud_19c]》

《[DATE类型的“小陷阱”][DATE]》

《[SQL工具集-格式化结果的SQL][SQL_-_SQL]》

《[聊聊数据库范式》][Link 2]

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

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

《[打造国产技术产品的必要性][Link 3]》

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

《[登录缓慢的诡异问题][Link 4]》

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

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

[format_png]: /images/20221123/b860d3fc666a48b098cb6331a9a2a30a.png
[format_png 1]: /images/20221123/1edd5c4b16434dd4b28118b56d50389d.png
[Link 1]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea815ad4ddf6d3c28b6bc547fe08a3de87e06d8c3292a6cc9bdffc7fc5f7d432263637d86231&idx=1&mid=2247491367&scene=21&sn=89f5263257059b2c7f61be001bc27b94#wechat_redirect
[Oracle Cloud_19c]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea815ad1ddf6d3c71da6d160381513bc2cb57b4d1b801bfde274c999abae90fadaa4fb27c14c&idx=1&mid=2247491362&scene=21&sn=0aeaf1f06a566c5e34ce258cfe5ef8f2#wechat_redirect
[DATE]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea815b06ddf6d21009d16b5dd491e3e62fcb22bd45c2fff61c7fb01b78e21248ed99050a0191&idx=1&mid=2247491317&scene=21&sn=ecdd579f838a26303eb3ba35ad98c87f#wechat_redirect
[SQL_-_SQL]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea815b02ddf6d214ff8098c44d2f1aabacb383ae1fb4e25a94db4ac0bfa4fa8c8e65610cec94&idx=1&mid=2247491313&scene=21&sn=8ee15fea049d85c2534c2bbfad4eaab8#wechat_redirect
[Link 2]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea815b24ddf6d232f8395bbf2ee5dcec7f71c5c63ede0630d34a21f363ba02f77559801d2b3a&idx=1&mid=2247491287&scene=21&sn=7327002ce4b56f72a84a09bd04bdafd6#wechat_redirect
[SQL_CPU]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea815b6addf6d27c0f85b9b84c237e4ca93f497fe293c31877990cef9259c6ab8450745b31be&idx=1&mid=2247491225&scene=21&sn=ac866f3d0bbba602e31aa53ca880b74f#wechat_redirect
[JVM]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea815b74ddf6d2621f3b547a49d4b6c8025612fb1ab0892928365b881ad2f76f01206ed68e45&idx=1&mid=2247491207&scene=21&sn=11ad7551928df895633f9b73f103eb0e#wechat_redirect
[Link 3]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea81588bddf6d19d016604aedd9ae6d523f5dcd74d1223d304a6c114d0e5d186b90381a3a5ba&idx=1&mid=2247490936&scene=21&sn=6d21bb9d94ad29ea7865a95b8e848b96#wechat_redirect
[Oracle]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea815939ddf6d02f97942e6dd5b5c60e60cd732d31c893f8879244f66d7d914b9f23fe7fc207&idx=1&mid=2247490762&scene=21&sn=67f5fdf567297ab86db77a57162f7a6a#wechat_redirect
[Link 4]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea815955ddf6d0434a80b00ba0e31ba96198814d630a9a255ec7b427a210ab01ad5bba685038&idx=1&mid=2247490726&scene=21&sn=f8836d3e25f6496fab88258526f048a9#wechat_redirect
[70_SRE_7]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea815e73ddf6d76569914615095c7e5efc66c9fa603d18dbad3c6370d75b718ab01fdc6e9459&idx=1&mid=2247490432&scene=21&sn=b0ffda7d19d9d36dc646f67734531684#wechat_redirect
[600]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea815fa3ddf6d6b521f9a1fccaeaf2b2ed2b547c3102b18c51397ef670985f477ee102d76fe8&idx=1&mid=2247490128&scene=21&sn=3e9b7f11a1bf04b4f302830fcdbed57d#wechat_redirect