ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 索引和唯一索引以及唯一约束之间的微妙关系以及oracle和sql server 机制上的区别

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

原创 Linux操作系统 作者:guan_jh 时间:2009-12-10 14:13:26 0 删除 编辑


问题:
我们有一个表上有一个索引,本来应该是唯一的,当时没有建立它为唯一,现在已经出现重复记录,
我现在不想动这些记录,如何保证以后插入的数据唯一。
解决:
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

DROP TABLE TEST1


 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/20854791/viewspace-622127/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-02-18

  • 博文量
    15
  • 访问量
    29981