unique constraint 和 unique index 的关系

 

今天开发人员说，明明执行了删除唯一约束，但是插入数据的时候仍然遇到唯一性冲突。经过检查，发现是和约束同名的一个唯一索引造成的。为啥删除了唯一约束之后，索引还在？这个地方其实有点讲究，关键的区别就在于，索引是自动建立的还是手动建立的。

 

自动建立

1. 默认的情况下，当我们建立一个unique constraint的时候，Oracle会自动的建立一个同名的unique index，真正的唯一性验证是通过索引完成的。
2. 当删除unique constraint的时候，自动建立的unique index也会被删除。

手动建立

1. 在字段上手工建立了一个unique index，名称无所谓。
2. 在相同字段上建立unique constraint 的时候，Oracle会使用已经存在的index来进行验证，而不会再次建立index。
3. 当删除unique constraint的时候，手工建立的unique index不会被删除。

------------------------------------------
drop table test_ak1 purge;
create table test_ak1 (x int, y int, z int);
-- 直接建立unique constraint,默认建立一个同名unique index
alter table test_ak1 add (constraint ak_1 unique (x));

-- 查看约束和索引
select c.constraint_name, c.constraint_type, c.index_name from user_constraints c where c.table_NAME = upper('test_ak1');
select i.index_name,i.uniqueness from user_indexes i where i.table_name = upper('test_ak1');

-- 删除约束的同时会删除索引
alter table test_ak1 drop constraint ak_1;

------------------------------------------
drop table test_ak2 purge;
create table test_ak2 (x int, y int, z int);

-- 手工建立unique index
create unique index ak_2 on test_ak2 (x);
-- 建立一个unique constraint，会使用相同字段上的unique index，不论名字是否一样
alter table test_ak2 add (constraint ak_2 unique (x));

-- 查看约束和索引
select c.constraint_name, c.constraint_type, c.index_name from user_constraints c where c.table_NAME = upper('test_ak2');
select i.index_name,i.uniqueness from user_indexes i where i.table_name = upper('test_ak2');

-- 直接删除索引，会报ora-02429错误
drop index ak_2;
-- 删除约束的时候，索引不受影响
alter table test_ak2 drop constraint ak_2;