索引和唯一索引以及唯一约束之间的微妙关系以及oracle和sql server 机制上的区别...-CSDN博客

问题：
我们有一个表上有一个索引，本来应该是唯一的，当时没有建立它为唯一，现在已经出现重复记录，
我现在不想动这些记录，如何保证以后插入的数据唯一。
解决：
ORACLE 中这个问题可以解决，SQL SERVER 中无法解决。

ORACLE 中的解决办法如下：
加个唯一约束,然后使用enable novalidate
实例如下：
SYS@oracle10g>create table test_unique(id int);
表已创建。
SYS@oracle10g>create index i_test_unique on test_unique(id);
索引已创建。
SYS@oracle10g>insert into test_unique values(1);
已创建 1 行。
SYS@oracle10g>/
已创建 1 行。
SYS@oracle10g>c /1/2/
1* insert into test_unique values(2)
SYS@oracle10g>/
已创建 1 行。
SYS@oracle10g>commit;
提交完成。
SYS@oracle10g>select * from test_unique;
        ID
----------
         1
         1
         2
SYS@oracle10g>alter table test_unique add constraint k unique(id) enable novalidate;
表已更改。
SYS@oracle10g>insert into test_unique values(2);
insert into test_unique values(2)
*
第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (SYS.K)

原因解释及剖析：
初步理解：必须先有索引，才能建这样的约束，不然不让你建

追究原因：其实也并不是要先建立索引，才能建这样的约束。

而是因为创建唯一约束，或主键约束时，
ORACLE会自动创建一唯一索引，
创建唯一索引时ORACLE会自动检测是否存在重复的键值（即是否违反唯一约束）。
但如果同一列上已经建立了一个索引了,那么oracle将不再自动创建一唯一索引,
因为同一列上只能建立一个索引(在同一分区的前提下),所以也就不会进行唯一性检查。
而语句 "alter table test_unique add constraint k unique(id) enable novalidate;"
后的 enable novalidate 的作用只是为了建立约束时,不对已有的数据进行唯一性检查。

换言之，做这样的改动，
一要避免建立唯一约束本身时，对表中数据的唯一性的检查；（通过加enable novalidate子句）
二要避免自动建立唯一索引；（通过先建立非唯一索引，再建立唯一约束的方法）；

SQL SERVER 相同步骤进行试验，结果如下：
--创建表
CREATE TABLE TEST1(ID INT)
--创建索引
CREATE INDEX IDX_TEST1 ON TEST1(ID)
GO
--插入一些数据
INSERT INTO TEST1 VALUES(1)
INSERT INTO TEST1 VALUES(1)
INSERT INTO TEST1 VALUES(2)
GO
SELECT * FROM TEST1
--仿照oracle中的方法进行操作
ALTER TABLE TEST1 WITH NOCHECK ADD CONSTRAINT CON_TEST1_ID UNIQUE(ID)
WITH NOCHECK/CHECK 方法虽然资料上显示，可以不对已经存在的数据进行唯一性检查，但事实却是要进行检查的
错误代码如下：
--消息 1505，级别 16，状态 1，第 1 行
CREATE UNIQUE INDEX 语句终止，因为发现对象名称 'dbo.TEST1' 和索引名称 'CON_TEST1_ID' 有重复的键。重复的键值为 (1)。
消息 1750，级别 16，状态 0，第 1 行
无法创建约束。请参阅前面的错误消息
GO