表中已存重复数据的情况，如何增加唯一性约束？
这周某系统上线，有一个需求就是，为一张表修改唯一性约束，原因就是之前发现，由于唯一性约束设置不当，导致业务处理出现异常。
举例来说，如下测试表，原先唯一性约束是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. IfVALIDATEis specified, then existing data must conform to the constraint. You can set constraints to validate (VALIDATE) or not validate (NOVALIDATE) existing data. IfVALIDATEis 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
其实很容易理解，空值是一种特殊的值，表示不确定、未知，因此空值和空值比较，结果不会是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)