表中已存重复数据的情况,如何增加唯一性约束?

本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

转载声明:转载请注明出处,本技术博客是本人原创文章

本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

原文链接:blog.ouyangsihai.cn >> 表中已存重复数据的情况,如何增加唯一性约束?

这周某系统上线,有一个需求就是,为一张表修改唯一性约束,原因就是之前发现,由于唯一性约束设置不当,导致业务处理出现异常。

举例来说,如下测试表,原先唯一性约束是a和b俩字段,但发现实际业务中,a和b的组合是可能重复的,加上c字段才会是唯一,

SQL create table test(                                                        2  id number,   3  a  varchar2(10),   4  b  varchar2(10),   5  c  varchar2(10)); Table created.

SQL insert into test values(1, 'a', 'a', 'a'); 1 row created.

SQL insert into test values(2, 'b', 'b', 'b'); 1 row created.

SQL commit; Commit complete.

SQL select * from test;     ID A          B      C ---------- ---------- ---------- ----------      1 a          a      a      2 b          b      b

基于以上数据,新建唯一性约束,可以看出,对于唯一性约束,Oracle会自动创建一个,普通的唯一索引,索引名称默认采用约束名。

SQL alter table test add constraint unq_test_01 unique(a, b, c); Table altered.

SQL select table_name, constraint_name, constraint_type from user_constraints where table_name='TEST'; TABLE_NAME CONSTRAINT_NAME    CONSTRAINT_TYPE ---------- ------------------------------ ------------------------------ TEST                UNQ_TEST_01              U

SQL select table_name, index_name, index_type, uniqueness from user_indexes where table_name = 'TEST'; TABLE_NAME INDEX_NAME              INDEX_TYPE      UNIQUENESS ---------- ------------------------------ -------------------- ---------- TEST               UNQ_TEST_01             NORMAL            UNIQUE

Because the database enforces a unique constraint by implicitly creating or reusing an index on the key columns, the term unique key is sometimes incorrectly used as a synonym for unique key constraint or unique index.

确实插入(a, b, c)相同的数据,就会报唯一性约束错误,

SQL insert into test values(3, 'a', 'a', 'a'); insert into test values(3, 'a', 'a', 'a') * ERROR at line 1: ORA-00001: unique constraint (BISAL.UNQ_TEST_01) violated

这就完了么?

表中已存重复数据的情况,如何增加唯一性约束?

需要注意一点,上述创建过程的前提,是表中已存在数据,没有违反唯一性约束的,如果表中已存在数据,已经有重复数据,该如何处理?

我们删除刚才创建的约束,插入重复记录,此时表中存在(a, b, c)相同的记录,

SQL alter table test drop constraint unq_test_01; Table altered.

SQL insert into test values(3, 'a', 'a', 'a'); 1 row created.

SQL commit; Commit complete.

SQL select * from test;     ID A          B      C ---------- ---------- ---------- ----------      1 a          a      a      2 b          b      b      3 a          a      a

我们再用上面的方法,创建唯一性约束,可以看出,报了错误,提示信息很明确,由于存在重复的键值,因此无法生效唯一性约束,

SQL alter table test add constraint unq_test_01 unique(a, b, c); alter table test add constraint unq_test_01 unique(a, b, c)                                 * ERROR at line 1: ORA-02299: cannot validate (BISAL.UNQ_TEST_01) - duplicate keys found

是否可以直接创建一个,唯一性索引?

SQL create unique index idx_test_01 on test(a, b, c); create unique index idx_test_01 on test(a, b, c)                                    * ERROR at line 1: ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

这种情况下,最简单的方法,就是删除重复的记录,这样就可以按照正常流程,创建唯一性约束。但往往这些重复数据,有实际的业务意义,因此不能删除,所以就需要其他方法workaround一下。

其实,Oracle官方手册,就给了我们选择,我们是可以控制,约束生效的状态,

As part of constraint definition, you can specify how and when Oracle Database should enforce the constraint, thereby determining the constraint state.

The database enables you to specify whether a constraint applies to existing data or future data. If a constraint is enabled, then the database checks new data as it is entered or updated. Data that does not conform to the constraint cannot enter the database. If a constraint is disabled, then the table can contain rows that violate the constraint.

You can set constraints to validate (`VALIDATE`) or not validate (`NOVALIDATE`) existing data. If `VALIDATE` is specified, then existing data must conform to the constraint. You can set constraints to validate (`VALIDATE`) or not validate (`NOVALIDATE`) existing data. If `VALIDATE` is specified, then existing data must conform to the constraint.

The behavior of VALIDATE and NOVALIDATE always depends on whether the constraint is enabled or disabled.

简言之,

如果约束设置enabled,则会检查新插入或更新的数据是否符合约束条件。

如果约束设置disabled,则表中可以包含,违反约束的记录。

如果约束设置validate,则表中存在的数据,必须符合约束。

如果约束设置novalidate,则表中存在的数据,不必符合约束。

validate和novalidate的行为,依赖于是否设置了enabled/disabled。

相应地可以设置组合,如下所示,

表中已存重复数据的情况,如何增加唯一性约束?

针对上面的需求,我们采用enable和novalidate的组合,是不是就可以解决问题了?

我们直接创建唯一性约束,报的相同错误,原因就是虽然此时,不检查存在数据,是否符合约束,但由于需要自动创建,唯一性索引,却发现存在重复的值,因此报错。

SQL alter table test add constraint unq_test_01 unique(a, b, c) enable novalidate; alter table test add constraint unq_test_01 unique(a, b, c) enable novalidate                                 * ERROR at line 1: ORA-02299: cannot validate (BISAL.UNQ_TEST_01) - duplicate keys found

既然表中存在重复数据,就不能创建唯一性索引,只能是普通索引,但使用enable novalidate组合,可以设置约束,换句话说,利用唯一性约束,限制数据唯一性,同时有相应的非唯一索引,达到相同效果,

SQL create index idx_test_01 on test(a, b, c); Index created.

SQL alter table test add constraint unq_test_01 unique(a, b, c) enable novalidate; Table altered.

SQL select table_name, constraint_name, constraint_type from user_constraints where table_name='TEST';

TABLE_NAME CONSTRAINT_NAME          CONSTRAINT_TYPE ---------- ------------------------------ ------------------------------ TEST       UNQ_TEST_01              U

SQL select table_name, index_name, index_type, uniqueness from user_indexes where table_name = 'TEST'; TABLE_NAME INDEX_NAME             INDEX_TYPE             UNIQUENESS ---------- ------------------------- ------------------------------ ---------- TEST       IDX_TEST_01             NORMAL                NONUNIQU

可以看出,虽然表中存在重复数据,但新增数据,需要符合唯一性约束条件,符合我们的最初需求,

SQL select * from test;     ID A          B      C ---------- ---------- ---------- ----------      1 a          a      a      2 b          b      b      3 a          a      a

SQL insert into test values(4, 'b', 'b', 'b'); insert into test values(4, 'b', 'b', 'b') * ERROR at line 1: ORA-00001: unique constraint (BISAL.UNQ_TEST_01) violated

再进一步,提一个问题,

存在唯一性约束的情况下,是否可以插入相同的空值?

看着好像简单的一个问题,是不是有些犹豫?我们测试一下,就可以知道了。

测试表现在有(a, b, c)唯一性约束,此时插入两条记录,且三个字段均为空值,分别用null和’’两种方法,插入空值数据,是可以插入的,并未违反唯一性约束,

SQL insert into test values(4, null, null, null); 1 row created.

SQL insert into test values(5, '', '', ''); 1 row created.

SQL select * from test;     ID A          B      C ---------- ---------- ---------- ----------      1 a          a      a      2 b          b      b      3 a          a      a      4      5

其实很容易理解,空值是一种特殊的值,表示不确定、未知,因此空值和空值比较,结果不会是true,唯一性约束,不认为两个空值相等,所以可以插入两个空值。

如果三字段中有一个、两个空值,可以看出,会报错误,

SQL insert into test values(4, 'a', '', 'a'); 1 row created.

SQL insert into test values(5, 'a', '', 'a'); insert into test values(5, 'a', '', 'a') * ERROR at line 1: ORA-00001: unique constraint (BISAL.UNQ_TEST_01) violated

SQL insert into test values(4, '', '', 'a'); 1 row created.

SQL insert into test values(5, '', '', 'a'); insert into test values(5, '', '', 'a') * ERROR at line 1: ORA-00001: unique constraint (BISAL.UNQ_TEST_01) violated

如果我们仔细看官方文档,就可以找出答案,

Unless a NOT NULL constraint is also defined, a null always satisfies a unique key constraint. Thus, columns with both unique key constraints and NOT NULLconstraints are typical. This combination forces the user to enter values in the unique key and eliminates the possibility that new row data conflicts with existing row data.

除非指定了非空约束,否则null值满足唯一性约束。(准确地说,唯一性约束字段均为null)

Because of the search mechanism for unique key constraints on multiple columns, you cannot have identical values in the non-null columns of a partially null composite unique key constraint.

含有部分空值的复合唯一性约束的非空列上不能有相同的值。

表中已存重复数据的情况,如何增加唯一性约束?

总结:

  1. 表中不存在重复的数据,可以直接创建唯一性约束,Oracle会自动创建唯一性索引,索引名称默认为约束名。
  1. 表中已存在重复的数据,此时若需要创建唯一性约束,可以按照“创建非唯一索引”-“创建唯一性约束”的顺序来实现。

  2. 表中有唯一性约束的限制,若所有字段均为null,则可以插入相同的空值,不违反唯一性约束,若复合唯一性约束,包含部分空值,且非空列上有相同的值,则违反唯一性约束。

如果您觉得此篇文章对您有帮助,欢迎关注微信公众号:bisal的个人杂货铺,您的支持是对我最大的鼓励!共同学习,共同进步:)

表中已存重复数据的情况,如何增加唯一性约束?

表中已存重复数据的情况,如何增加唯一性约束?      表中已存重复数据的情况,如何增加唯一性约束?      

表中已存重复数据的情况,如何增加唯一性约束?
本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

转载声明:转载请注明出处,本技术博客是本人原创文章

本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

原文链接:blog.ouyangsihai.cn >> 表中已存重复数据的情况,如何增加唯一性约束?


 上一篇
DML错误日志表 DML错误日志表
前天写了篇文章《》,提到了存在唯一约束前提下,重复数据的问题。 很感谢建荣兄,他给我补充了两点, 冲突数据也可以考虑通过errorlog的方式,可以很快定位。 对于含有null的复合索引,mysql和oracle的结果完全不同。 对
下一篇 
MySQL 5.6搭建主从复制 MySQL 5.6搭建主从复制
使用MySQL 5.6,搭建主从复制。关于5.6的安装,可以参考《》。 主库创建slave用户,设置复制权限, mysql create user ‘slave’@’1.1.1.2’ identified by ‘root’;Query