做个很简单的测试 create table tb1 ( coln1 int, coln2 varchar(8) ) go insert tb1 select 1,'Selina' union all select 2,'Hebi' union all select 3,null go 现在可以给coln2字段同时加索引ix_tb1_coln2和约束cs_tb1_coln2 之后在sysindexes可以查到新增了2行 执行select * from tb1 where coln2 is null 看执行计划会用到索引ix_tb1_coln2 删除了该索引保留约束就会用到cs_tb1_coln2 如果把约束也删除 就会做全表扫描
--BOL Creating a unique index ensures that any attempt to duplicate key values fails. If a single query is created that causes duplicate and nonduplicate key values to be added, SQL Server rejects all rows, including the nonduplicate key values. For example, if a single insert statement retrieves 20 rows from table A and inserts them into table B, and 10 of those rows contain duplicate key values, by default all 20 rows are rejected. However, the IGNORE_DUP_KEY clause can be specified when creating the index that causes only the duplicate key values to be rejected; the nonduplicate key values are added. In the previous example, only the 10 duplicate key values would be rejected; the other 10 nonduplicate key values would be inserted into table B.
And as I tested, constraints cannot reach this since this option is not available, that is to say, 20 rows would be totally refused. In my idea, they are naturally the same without considering this option.
UNIQUE 條件約束與 索引約束都會強制唯一性
您可以在一個資料表內定義多個UNIQUE 條件約束,但一個資料表內只能定義一個 索引約束 條件約束。
允許 Null 值的資料行。
UNIQUE 條件約束可定義於允許 Null 值的資料行,而索引約束條件約束只能定義於不允許 Null 值的資料行。
MS的产品重在易用,有些东西帮你做完了,所以有时候不知道为什么.这一段看Oracle里面的实现就会比较清楚.唯一约束就是说不能有重复值.但是数据库怎么保证没有重复值呢?当然是查找一遍咯.怎么查找快呢?当然是索引咯.所以MS帮你(还是帮它自己?)建了一个索引.
允許 Null 值的資料行。
UNIQUE 條件約束可定義於允許 Null 值的資料行,而索引約束條件約束只能定義於不允許 Null 值的資料行。
-------------------------
不是吧,应该在一个表内可以建多个“unique 索引约束”
这两条都是充许一条记录值为 null 的
约束与主键差不多,都是不允许字段值重复,只是约束字段可以为null,主键一定要not null
唯一索引表示行的索引键值都不同,但字段值可以相同.
谢谢你的回复,但是我问的不是约束与主键的区别,我问的是约束与索引的区别.就是我画红圈的地方有何区别.
与主键的区别我知道,主键不能为null,可是约束和索引都是可以有一条记录的字段值可以为null的
现总结如下:
1.唯一索引在不设置忽略重复键时,与唯一约束是一样的.
2.当选择忽略重复键项,插入记录时,如键值相同,插入语句会被忽略,不会返回错误信息,@@error为0,程序继续执行下面语句.
不选忽略重复键项时,将与唯一约束相同,如键值相同,插入语句会被终止,@@error>0.
create table tb1 (
coln1 int,
coln2 varchar(8)
)
go
insert tb1
select 1,'Selina' union all
select 2,'Hebi' union all
select 3,null
go
现在可以给coln2字段同时加索引ix_tb1_coln2和约束cs_tb1_coln2 之后在sysindexes可以查到新增了2行
执行select * from tb1 where coln2 is null 看执行计划会用到索引ix_tb1_coln2 删除了该索引保留约束就会用到cs_tb1_coln2 如果把约束也删除 就会做全表扫描
Creating a unique index ensures that any attempt to duplicate key values fails. If a single query is created that causes duplicate and nonduplicate key values to be added, SQL Server rejects all rows, including the nonduplicate key values. For example, if a single insert statement retrieves 20 rows from table A and inserts them into table B, and 10 of those rows contain duplicate key values, by default all 20 rows are rejected. However, the IGNORE_DUP_KEY clause can be specified when creating the index that causes only the duplicate key values to be rejected; the nonduplicate key values are added. In the previous example, only the 10 duplicate key values would be rejected; the other 10 nonduplicate key values would be inserted into table B.