Oracle 数据库表中已有重复数据添加唯一键(唯一约束)

时间:2021-12-06 09:43:49

Oracle 数据库表中已有重复数据添加唯一键(唯一约束)

问题描述

以 demo 举例,模拟真实场景。

表 TEST_TABLE 有如下字段和数据:id 是主键,code 没有设置键和索引

ID CODE
1 code1
2 code2
3 code2
4 code2
5 code3

通过以上表中数据可以看出 code 是有重复数据的,此时如果我们直接添加唯一键,会报错。

通过 PL/SQL 可视化操作,或者通过 SQL 语句添加(ENABLE NOVALIDATE 的作用是约束新增数据但不会验证已有数据):ALTER TABLE TEST_TABLE ADD CONSTRAINT UK_TEST_TABLE_CODE UNIQUE(CODE) ENABLE NOVALIDATE;

即使用到了 ENABLE NOVALIDATE 也报错,如下:

ORA-02299: cannot validate(TESTUSER.TEST_TABLE.UK_TEST_TABLE_CODE)-duplicate keys found

--Create/Recreate primary, unique and foreign key constraints alter table TEST_TABLE and constraint UK_TEST_TABLE_CODE unique(CODE) novalidate

解决方案

  1. 先添加普通索引
    • CREATE INDEX IDX_TEST_TABLE_CODE ON TEST_TABLE(CODE);
  2. 再添加唯一键
    • ALTER TABLE TEST_TABLE ADD CONSTRAINT UK_TEST_TABLE_CODE UNIQUE(CODE) ENABLE NOVALIDATE;

如果你对上面提到的基本概念不太清楚,建议继续往下看,或者查找资料去学习,然后多在测试数据库中模拟“犯罪现场”,然后进行还原问题、找问题、分析问题和解决问题。

基本概念

键、索引、约束的区别

一般,我们看到术语“索引”和“键”交换使用,但实际上这两个是不同的。索引是存储在数据库中的一个物理结构,键纯粹是一个逻辑概念。键代表创建来实施业务规则的完整性约束。索引和键的混淆通常是由于数据库使用索引来实施完整性约束。

  1. 主键索引和主键有什么关系?

主键索引是创建主键的时候系统自动创建的索引,主键要求不重复,不为空,但是他如何判断有效率呢?当然是建索引了,老是全表遍历还不疯掉。

所以建立主键会自动的建立主键索引。

  1. 主键和唯一键的区别在于唯一键可以为空,主键不可以

  2. 建立唯一约束和唯一索引又什么区别?

同理,建立唯一约束的时候,也会自动的创建唯一索引。建立唯一索引可以说是唯一约束的一种手段。

基本上,实现起来是没有什么区别的。如果实在理解不了,就当一样好了。

  1. 聚簇索引和非聚簇索引有何区别?

这个上边已经讲和很详细了,还附有两篇文章,这里就不说了。

  1. 约束和主键有什么区别?

约束一般有主键约束,外键约束,唯一约束等。

分别为primary key,foreign key,unique 其中主键约束只是约束的一种。

其实他们是不同概念的东西。

参考资料