SQLServer 唯一键约束和唯一索引有什么区别?
来源:互联网 发布:linux性能优化 编辑:程序博客网 时间:2024/05/11 02:29
以前也想了解到底有什么区别,但是搁着又忘记了,因为我们很少用唯一键约束。直到几天前同事给我个脚本来约束某个字段的唯一性,用的是唯一键约束,这问题又萦绕脑中了。看似有区别,又没发现什么大的区别!实际上也没多大区别,还是测试看看吧。
USE [DemoDB]GOCREATE TABLE [dbo].[TableUniqueKey](id int not null,name varchar(20) null)GOCREATE TABLE [dbo].[TableUniqueIndex](id int not null,name varchar(20) null)GOINSERT INTO [dbo].[TableUniqueKey]SELECT 1,'KK' UNION ALLSELECT 2,NULL UNION ALLSELECT 3,NULL GOINSERT INTO [dbo].[TableUniqueIndex]SELECT 1,'KK' UNION ALLSELECT 2,NULL UNION ALLSELECT 3,NULL GO
以两个表分表创建唯一键和唯一索引,字段 name 中都有重复值 null。
现在创建唯一键约束 和 创建唯一索引 ,错误!
/*创建唯一键约束 和 创建唯一索引*/--创建唯一键约束ALTER TABLE [dbo].[TableUniqueKey] ADD CONSTRAINT [IX_TableUniqueKey_name] UNIQUE ([name] ASC)--默认非聚集索引GOALTER TABLE [dbo].[TableUniqueKey] ADD CONSTRAINT [IX_TableUniqueKey_name] UNIQUE NONCLUSTERED([name] ASC)GO--创建唯一索引CREATE UNIQUE NONCLUSTERED INDEX [IX_UniqueIndex_name] ON [dbo].[TableUniqueIndex]([name] ASC)GO
Msg 1505, Level 16, State 1, Line 1
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.TableUniqueKey' and the index name 'IX_TableUniqueKey_name'. The duplicate key value is (<NULL>).
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
The statement has been terminated.
Msg 1505, Level 16, State 1, Line 1
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.TableUniqueIndex' and the index name 'IX_UniqueIndex_name'. The duplicate key value is (<NULL>).
The statement has been terminated.
可以看到,都提示有重复值,重复值 为 NULL 值。现在删除重复值。
DELETE FROM [dbo].[TableUniqueKey] WHERE ID = 3DELETE FROM [dbo].[TableUniqueIndex] WHERE ID = 3
创建唯一键约束:
--创建唯一键约束ALTER TABLE [dbo].[TableUniqueKey] ADD CONSTRAINT [IX_TableUniqueKey_name] UNIQUE NONCLUSTERED([name] ASC)GO
查看相关信息:
SELECT * FROM sys.check_constraints WHERE parent_object_id = OBJECT_ID('TableUniqueKey')SELECT * FROM sys.key_constraints WHERE parent_object_id = OBJECT_ID('TableUniqueKey')SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID('TableUniqueKey')
创建唯一键约束,同时创建同名的唯一非聚集索引, 同时创建同名统计信息; 唯一键约束靠唯一索引来约束。
若对唯一键生成的索引直接删除,错误!
--对索引删除,错误!DROP INDEX [IX_TableUniqueKey_name] ON [dbo].[TableUniqueKey] GO
Msg 3723, Level 16, State 5, Line 1
An explicit DROP INDEX is not allowed on index 'dbo.TableUniqueKey.IX_TableUniqueKey_name'. It is being used for UNIQUE KEY constraint enforcement.
正确删除方法,删除表约束。
--删除约束,正确!ALTER TABLE [dbo].[TableUniqueKey] DROP CONSTRAINT [IX_TableUniqueKey_name]GO
现在对另一张表创建唯一索引。
--创建唯一索引CREATE UNIQUE NONCLUSTERED INDEX [IX_UniqueIndex_name] ON [dbo].[TableUniqueIndex]([name] ASC)GO
创建唯一索引, 同时创建同名统计信息
--对索引删除!DROP INDEX [IX_UniqueIndex_name] ON [dbo].[TableUniqueIndex] GO
现在重新创建:
ALTER TABLE [dbo].[TableUniqueKey] ADD CONSTRAINT [IX_TableUniqueKey_name] UNIQUE NONCLUSTERED([name] ASC)GOCREATE UNIQUE NONCLUSTERED INDEX [IX_UniqueIndex_name] ON [dbo].[TableUniqueIndex]([name] ASC)GO
对比相关信息:
SELECT * FROM sys.check_constraints WHERE parent_object_id = OBJECT_ID('TableUniqueKey')SELECT * FROM sys.check_constraints WHERE parent_object_id = OBJECT_ID('TableUniqueIndex')SELECT * FROM sys.key_constraints WHERE parent_object_id = OBJECT_ID('TableUniqueKey')SELECT * FROM sys.key_constraints WHERE parent_object_id = OBJECT_ID('TableUniqueIndex')SELECT object_name(object_id),name,index_id,type_desc,is_unique,is_unique_constraintFROM sys.indexes WHERE object_name(object_id) IN ('TableUniqueKey','TableUniqueIndex')
唯一键约束 [TableUniqueKey] 不是 check 约束,是属于一种为 UNIQUE_CONSTRAINT 的约束。而他们的索引都有唯一性约束。
此外,还可以通过以下检查他们的区别:
EXEC sp_helpconstraint 'TableUniqueKey' EXEC sp_helpconstraint 'TableUniqueIndex' EXEC sp_helpindex 'TableUniqueKey' EXEC sp_helpindex 'TableUniqueIndex' --EXEC sp_help 'TableUniqueKey' --EXEC sp_help 'TableUniqueIndex'
对比索引描述中,唯一键 比 唯一 索引多了 unique key 。
--唯一键不出错EXEC sp_help [IX_TableUniqueKey_name]EXEC sp_helpindex [IX_TableUniqueKey_name]EXEC sp_helpconstraint [IX_TableUniqueKey_name]--唯一索引出错EXEC sp_help [IX_UniqueIndex_name]EXEC sp_helpindex [IX_UniqueIndex_name]EXEC sp_helpconstraint [IX_UniqueIndex_name]
上面可以看出,唯一不同的是: 唯一键 比 唯一索引 多了一种叫做 "unique key" 的约束
现在禁用索引:
--禁用索引/约束(均可被禁用)ALTER INDEX [IX_TableUniqueKey_name] ON [dbo].[TableUniqueKey] DISABLEALTER INDEX [IX_UniqueIndex_name] ON [dbo].[TableUniqueIndex] DISABLE
插入重复数据:
--插入重复值,正常INSERT INTO [dbo].[TableUniqueKey] SELECT 3,NULL INSERT INTO [dbo].[TableUniqueIndex]SELECT 3,NULL
数据都能正常插入,约束或索引被禁用了。对于唯一键约束,也是禁用索引吗?
函数 ObjectProperty()的参数 CnstIsDisabled 可以确认约束是否被禁用。
SELECT ObjectProperty(object_id('IX_TableUniqueKey_name'),'CnstIsDisabled')
结果为 0 ,即约束没有被禁用,也就是说禁用的是索引,唯一键约束中,唯一性是依赖于其默认创建的唯一索引来约束的!
现在重建索引:
--删除重复DELETE FROM [dbo].[TableUniqueKey] WHERE ID = 3DELETE FROM [dbo].[TableUniqueIndex] WHERE ID = 3/*对索引的更改*/--重建索引ALTER INDEX [IX_TableUniqueKey_name] ON [dbo].[TableUniqueKey] REBUILDALTER INDEX [IX_UniqueIndex_name] ON [dbo].[TableUniqueIndex] REBUILD--更改部分索引参数ALTER INDEX [IX_TableUniqueKey_name] ON [dbo].[TableUniqueKey] SET ( ALLOW_ROW_LOCKS = ON )GOALTER INDEX [IX_UniqueIndex_name] ON [dbo].[TableUniqueIndex] SET ( ALLOW_ROW_LOCKS = ON )GO
两种索引其实还是可以更改一下参数的。使用窗口打开查看,唯一键约束的索引有些是不能更改的。
唯一键约束的索引不能像正常的索引使用太多的索引参数,因为唯一键约束与其索引同在。而单独创建的唯一索引可以设置更多的参数,如 PAD_INDEX, FILLFACTOR, IGNORE_DUP_KEY, DROP_EXISTING, STATISTICS_NORECOMPUTE, and SORT_IN_TEMPDB 。
总的来说,其实唯一键键约束和唯一索引功能是一样的: "唯一性" + "索引"
唯一键键约束 只是作为一种独特的约束(如主键约束,唯一键约束,check约束,外键约束 的一种),以约束的形式管理.但是同时又自动创建了唯一非聚集索引,也就有了索引的性能和部分功能.实际上唯一键约束是用唯一索引来约束的。
唯一索引 就是一种索引,它对某字段进行唯一性检查,同时可以设置各种参数,非常灵活。
那么我们在创建列的唯一性时,到底使用哪一种较好呢?(个人理解)
唯一键约束在表中是必定存在的约束的,唯一键约束的索引存在于一个分区中,并且不会像索引那样可以更改。因为索引可以随时改动(当然也不会经常改动),索引个人建议还是用唯一索引更灵活。管理约束还得管理索引,而管理索引,一个就好了。但是对于一些高可用性,也要注意索引是否在其他地方也存在。
更多参考:Unique Constraints and Unique Indexes
- SQLServer 唯一键约束和唯一索引有什么区别?
- 主键和唯一索引有什么区别
- 唯一约束和唯一索引区别
- 创建unique时,约束和索引有何区别。唯一约束和唯一索引区别,选项"忽略重复键"作用
- 创建unique时,约束和索引有何区别。唯一约束和唯一索引区别,选项"忽略重复键"作用
- 主键索引和唯一性索引有什么区别
- 数据库--sqlserver--创建唯一约束和唯一索引方法
- Oracle主键约束、唯一键约束、唯一索引的区别
- Oracle主键约束、唯一键约束、唯一索引的区别【主键约束和唯一键约束均会隐式创建同名的唯一索引】
- 唯一性约束和唯一性索引的区别
- 唯一性约束和唯一性索引的区别
- Oracle中唯一约束和唯一索引的区别
- 唯一性约束和唯一性索引的区别
- oracle唯一索引和唯一约束
- oracle 唯一约束 和 唯一索引
- 唯一性约束、主键约束、唯一索引的区别
- Mysql唯一索引 唯一约束
- 主键 和 唯一键(唯一约束)
- android中sharedpreference的使用
- 线性约束最优化问题的Frank-Wolfe方法
- 非物质文化遗产继承与产业化现状调研团完成问卷数据分析工作
- 【国家集训队2012】【BZOJ2671】Calc和与积
- 【WordPress插件】文章页和feed页显示版权插件
- SQLServer 唯一键约束和唯一索引有什么区别?
- 谷歌B4广域网论文笔记
- Android学习笔记(十五)
- java并发编程实战第六章(1)并发集合介绍
- Linux平台下Python的安装及IDE开发环境搭建
- 51Nod 1181-质数中的质数(质数筛法)
- 可能是最难围住的神经猫——寻找必胜路径的算法实现
- poj 1094 Sorting It All Out(拓扑排序·环·矩阵)
- MySQL-5.6.15 +vs2012 源代码安装