专栏首页bisal的个人杂货铺主键约束索引的奇葩现象

主键约束索引的奇葩现象

在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

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

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • HINT无效的几个场景

    ...

    bisal
  • 开源软件许可

    因为日常工作中用到了,一些开源的产品,每个产品说明中,会有一些开源许可的介绍,各种名字,不很理解其中的含义。

    bisal
  • enq: TM - contention锁争用的解决

    这两天生产上碰见个表锁争用的问题,现象就是04:00夜维一启动,应用就开始处理缓慢,AWR看,enq: TM - contention等待事件占比超过了97%,

    bisal
  • 监控mysql索引使用效率的脚本

    从这位仁兄的博客转载来的http://blog.itpub.net/133735/

    二狗不要跑
  • 如何输出字符窜的所有组合

    例如“abc”输出a,b,c,ab,ac,bc,abc #include<stdio.h> void DFS(char str[],char ss[],int ...

    用户1624346
  • 测试Jetson NANO开发套件的防震情况,结果太震撼

    还记得我们之前发的一篇文章:实测极端温度条件下Jetson NANO开发套件的运行情况

    GPUS Lady
  • BAT 脚本技术-利用bat批处理脚本静态指定ip地址、自动获取ip地址设置

    利用 bat 设置 ip ,跟我们直接在网络和共享中心设置的 ip 地址是一样的。 静态指定 ip 地址设置:【脚本需要用管理员权限来运行。】

    小蓝枣
  • “协力抗疫,码力全开”线上黑客马拉松-汤圆小队-确诊感染者出行地点查询工具-28号

    信息来源:作为一个信息平台,信息来源准确性至关重要。因此我们选择了人民日报等联合开发的网页数据作为数据来源。

    若小啊
  • Nginx 发布 mainline version 1.13.0

    Nginx 发布了 mainline version 1.13.0(即通常说的开发版)。更新内容如下:

    Debian社区
  • SAS-一个小程序实现变量类型的自动转化~

    做为标题党的小编,一贯喜欢将标题写很大...嗯,最近写了一个小程序,虽然是一个没有任何技术含量的程序,不过还是想分享给大家。这个程序实现的功能是将SAS数据集中...

    Setup

扫码关注云+社区

领取腾讯云代金券