关于primary key和unique index的奇怪问题 (58天)

今天一个dba交给我一个问题,让我帮忙查一下。说有个脚本运行的时候有错,让我看看是什么原因。

脚本的思路如下:

先drop PK,FK之类的constraint.

然后把表改个名

然后重新创建一个表

然后加上PK,FK和其他的constraint

根据报错,貌似是primary key创建失败导致的。

SQL> ALTER TABLE  T1
  2          ADD CONSTRAINT T1_PK
  3          PRIMARY KEY (
                BEN)
        USING INDEX (CREATE UNIQUE INDEX T1_PK
ON T1(
                BEN) PCTFREE 10
 INITRANS 2
 TABLESPACE POOL_IX
 LOGGING
 STORAGE(  INITIAL 16384 NEXT 16384 MINEXTENTS 1 MAXEXTENTS 400 PCTINCREASE 0 FREELISTS 1 ) )  4    5    6    7    8    9   10   11  ;
ALTER TABLE T1
*
ERROR at line 1:
ORA-00955: name is already used by an existing object

我查询了一下,index的情况

SQL> SELECT INDEX_NAME,index_type,TABLE_NAME FROM USER_INDEXES WHERE INDEX_NAME='T1_PK';
INDEX_NAME                     TABLE_NAME
------------------------------ ------------------------------
T1_PK                           T_756_3

以上信息说明,index T1_PK还没有删除,表T_765_3是更名后的表。

由此可以推荐drop PK的时候没有成功。

貌似找到了问题的原因。

然后查看执行的记录。

发现

alter table xxx drop primary key的操作是执行成功的。

这样我就看不懂了。

都已经drop了怎么index还没删除,我把脚本copy到本地,找了个测试环境试了下,脚本还是没有问题。

drop primary key的时候 index会自动删除。

我想了又想,创建primary key的时候会自动创建unique index.我想万一index不是unique的,被其他人手工创建的,那样是不是就不会随着primary key自动删除了。

我查了下Index的情况,结果index还是unique的。

这种情况貌似有些解释不清了,到底使我们的脚本有问题还是本来环境就有问题。

我大胆的假设了一下,假设环境本来有问题。

那会不会是先创建unique index,然后再创建primary key.找了个测试环境。测试如下

CREATE UNIQUE INDEX T1_PK ON T1 (BEN);
ALTER TABLE T1
            ADD CONSTRAINT T1_PK
           PRIMARY KEY (
                BEN);

然后尝试drop PK

ALTER TABLE T1 DROP PRIMARY KEY;

然后查询index,发现index没有删除。

所以可以基本推论,可能是以上的情况导致的。

然后得到一些信息,之前这些表有一些问题,是手工修复的。很可能是以上的步骤导致的。

我提供了修复的脚本,这个问题就基本告一段落了。但是我还有个疑问,有没有地方去查 primary key和unique index之间的关联,如果unique index创建在先,然后创建PK,有没有地方去标示这种情况,要不删除PK时时怎么自动删除unique index的?

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2014-04-30

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏逸鹏说道

SQL Server 重新组织生成索引

概述 无论何时对基础数据执行插入、更新或删除操作,SQL Server 数据库引擎都会自动维护索引。随着时间的推移,这些修改可能会导致索引中的信息分散在数据库...

3608
来自专栏ytkah

dedecms清空所有文章怎么操作?sql语句如何写?

  小C新建了一个站,确切的说是复制,出于seo考虑,决定清空所有文章,那么dedecms清空所有文章怎么操作?sql语句如何写呢?特别提醒:修改之前一定要先做...

2959
来自专栏PHP在线

Mysql存储引擎中InnoDB与Myisam的区别

1. 事务处理innodb 支持事务功能,myisam 不支持。 Myisam 的执行速度更快,性能更好。 2. select ,update ,insert...

3095
来自专栏Java进阶架构师

「mysql优化专题」90%程序员都会忽略的增删改优化(2)

通常情况下,当访问某张表的时候,读取者首先必须获取该表的锁,如果有写入操作到达,那么写入者一直等待读取者完成操作(查询开始之后就不能中断,因此允许读取者完成操作...

722
来自专栏Spark学习技巧

SparkSql 中外连接查询中的谓词下推规则

SparkSql SparkSql是架构在spark计算框架之上的分布式Sql引擎,使用DataFrame和DataSet承载结构化和半结构化数据来实现数据复杂...

2649
来自专栏Laoqi's Linux运维专列

Mysql 通过全量备份和binlog恢复整体数据

1K7
来自专栏机器学习算法与Python学习

SQL Server常用命令(平时不用别忘了)

SQL Server 2008 在Microsoft的数据平台上发布,可以组织管理任何数据。可以将结构化、半结构化和非结构化文档的数据直接存储到数据库中。可以对...

2987
来自专栏杨建荣的学习笔记

MySQL和Oracle的添加字段的处理差别 (r10笔记第73天)

昨天在微信群中有个朋友也是无意中问了一下,说数据库中的表字段想保持一种相对规范的顺序,怎么办?要知道Oracle中这个操作就比较纠结了,因为是按照追加的方式来处...

3536
来自专栏琯琯博客

MySQL 查询优化

21111
来自专栏杨建荣的学习笔记

MySQL Online DDL(二)(r11笔记第88天)

对于Online DDL,之前简单分析了一些场景MySQL中的Online DDL(第一篇)(r11笔记第3天),其实有一个很关键的点没提到,那就是online...

3619

扫码关注云+社区