Oracle唯一性约束和唯一性索引的关系

唯一性约束通过唯一性索引来实现？我觉得这说法不对。

对于唯一性约束，索引是必须存在的，唯一性约束本质上是通过索引来保证的，但不一定是唯一性索引。

唯一性约束允许有NULL值，唯一性约束的列可允许有多个NULL值。唯一性约束通过BTREE索引实现，而BTREE索引是不会包含NULL值，但使用NULL值过滤时不会走索引。

在没有索引的情况下，创建唯一性约束会自动创建一个唯一性索引，但Oracle官方建议将唯一性约束和索引分开，这该怎么理解呢？原来在创建唯一性约束时默认创建的唯一性索引会随着唯一性约束DISABLE或DROP而删除，我们以三个实验来证明这个结论。

一、创建唯一性约束时默认创建唯一性索引

通过创建唯一性约束时默认创建唯一性索引，之后DISABLE唯一性约束，观察唯一性索引是否还存在。

CREATE TABLE TEST (ID NUMBER, NAME VARCHAR2(30)); ALTER TABLE TEST ADD CONSTRAINT CON_TEST_ID_UQ UNIQUE(ID);

查看索引

SELECT Index_Name, Uniqueness, Index_Type   FROM User_Indexes  WHERE Table_Name = 'TEST';

可以看到，默认创建了一个同名的唯一性索引

查看唯一性约束

SELECT Owner, Constraint_Name, Table_Name, Generated, Index_Name   FROM User_Constraints  WHERE Table_Name = 'TEST';

唯一性约束CON_TEST_ID_UQ绑定了唯一性索引CON_TEST_ID_UQ

我们DISABLE这个唯一性约束。

ALTER TABLE TEST DISABLE CONSTRAINT CON_TEST_ID_UQ;

再次查看约束

再次查看索引，发现CON_TEST_ID_UQ索引已经不见了

二、创建唯一性索引后创建唯一性约束

先创建唯一性索引，然后创建唯一性约束，之后DISABLE唯一性约束，观察唯一性索引是否还存在。

CREATE TABLE TEST1 (ID NUMBER, NAME VARCHAR2(30)); CREATE UNIQUE INDEX IDX_TEST1_ID_UQ ON TEST1(ID); ALTER TABLE TEST1 ADD CONSTRAINT CON_TEST1_ID_UQ UNIQUE(ID);

查看唯一性索引

SELECT Index_Name, Uniqueness, Index_Type   FROM User_Indexes  WHERE Table_Name = 'TEST1';

查看唯一性约束

SELECT Owner, Constraint_Name, Table_Name, Generated, Index_Name   FROM User_Constraints  WHERE Table_Name = 'TEST1';

可以看到，唯一性约束CON_TEST1_ID_UQ与唯一性索引IDX_TEST1_ID_UQ绑定起来了。

我们DISABLE这个唯一性约束CON_TEST1_ID_UQ，再次查看唯一性约束和索引的情况，发现唯一性索引仍存在。

 

三、创建普通索引后创建唯一性约束

先创建普通索引，然后创建唯一性约束，观察唯一性约束是否能成功建立。之后DISABLE唯一性约束，观察索引是否还存在。

CREATE TABLE TEST2 (ID NUMBER, NAME VARCHAR2(30)); CREATE INDEX IDX_TEST2_ID ON TEST2(ID); ALTER TABLE TEST2 ADD CONSTRAINT CON_TEST2_ID_UQ UNIQUE(ID);

查看索引

SELECT Index_Name, Uniqueness, Index_Type   FROM User_Indexes  WHERE Table_Name = 'TEST2';

查看唯一性约束

SELECT Owner, Constraint_Name, Table_Name, Generated, Index_Name   FROM User_Constraints  WHERE Table_Name = 'TEST2';

发现唯一性约束CON_TEST2_ID_UQ也建立成功了，并且和普通索引IDX_TEST2_ID绑定了。

我们DISABLE唯一性约束CON_TEST2_ID_UQ，查看唯一性约束和索引的情况，发现普通索引IDX_TEST2_ID也仍存在。

四、结论

由以上实验我们可以得出以下几个结论。

1. 创建唯一性约束时默认创建的唯一性索引会随着唯一性约束DISABLE或DROP消失。
2. 创建唯一性约束并不一定会创建索引，也可以使用现有的索引；
3. 唯一性约束并不一定需要唯一性索引；

在生产环境中，如果使用默认的唯一性索引，当唯一性约束需要DISABLE时，索引失效将会造成很大的问题，所以建议将唯一性约束与索引分开。