SQLServer 唯一键约束和唯一索引有什么区别？

时间：2021-04-02 00:38:47

以前也想了解到底有什么区别，但是搁着又忘记了，因为我们很少用唯一键约束。直到几天前同事给我个脚本来约束某个字段的唯一性，用的是唯一键约束，这问题又萦绕脑中了。看似有区别，又没发现什么大的区别！实际上也没多大区别，还是测试看看吧。


USE [DemoDB]
GO

CREATE TABLE [dbo].[TableUniqueKey](
	id int not null,
	name varchar(20) null
)
GO
CREATE TABLE [dbo].[TableUniqueIndex](
	id int not null,
	name varchar(20) null
)
GO

INSERT INTO [dbo].[TableUniqueKey]
SELECT 1,'KK' UNION ALL
SELECT 2,NULL UNION ALL
SELECT 3,NULL 
GO
INSERT INTO [dbo].[TableUniqueIndex]
SELECT 1,'KK' UNION ALL
SELECT 2,NULL UNION ALL
SELECT 3,NULL 
GO

以两个表分表创建唯一键和唯一索引，字段 name 中都有重复值 null。



现在创建唯一键约束 和 创建唯一索引 ，错误！

/*创建唯一键约束 和 创建唯一索引*/

--	创建唯一键约束
ALTER TABLE [dbo].[TableUniqueKey] ADD CONSTRAINT [IX_TableUniqueKey_name] UNIQUE ([name] ASC)--默认非聚集索引
GO
ALTER 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 = 3
DELETE 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')
SQLServer 唯一键约束和唯一索引有什么区别？

创建唯一键约束,同时创建同名的唯一非聚集索引, 同时创建同名统计信息; 唯一键约束靠唯一索引来约束。


若对唯一键生成的索引直接删除，错误！

--对索引删除,错误!
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

SQLServer 唯一键约束和唯一索引有什么区别？

创建唯一索引, 同时创建同名统计信息

--对索引删除!
DROP INDEX [IX_UniqueIndex_name] ON [dbo].[TableUniqueIndex] 
GO




现在重新创建：

ALTER 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

对比相关信息： 

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_constraint
FROM sys.indexes WHERE object_name(object_id) IN ('TableUniqueKey','TableUniqueIndex')

SQLServer 唯一键约束和唯一索引有什么区别？


唯一键约束 [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'
SQLServer 唯一键约束和唯一索引有什么区别？

对比索引描述中，唯一键 比 唯一 索引多了 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] DISABLE
ALTER 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 = 3
DELETE FROM [dbo].[TableUniqueIndex] WHERE ID = 3

/*对索引的更改*/
--重建索引
ALTER INDEX [IX_TableUniqueKey_name] ON [dbo].[TableUniqueKey] REBUILD
ALTER INDEX [IX_UniqueIndex_name] ON [dbo].[TableUniqueIndex] REBUILD


--更改部分索引参数
ALTER INDEX [IX_TableUniqueKey_name] ON [dbo].[TableUniqueKey] SET ( ALLOW_ROW_LOCKS = ON )
GO
ALTER INDEX [IX_UniqueIndex_name] ON [dbo].[TableUniqueIndex] SET ( ALLOW_ROW_LOCKS = ON )
GO

两种索引其实还是可以更改一下参数的。使用窗口打开查看，唯一键约束的索引有些是不能更改的。

SQLServer 唯一键约束和唯一索引有什么区别？

SQLServer 唯一键约束和唯一索引有什么区别？

SQLServer 唯一键约束和唯一索引有什么区别？


唯一键约束的索引不能像正常的索引使用太多的索引参数，因为唯一键约束与其索引同在。而单独创建的唯一索引可以设置更多的参数,如 PAD_INDEX, FILLFACTOR, IGNORE_DUP_KEY, DROP_EXISTING, STATISTICS_NORECOMPUTE, and SORT_IN_TEMPDB 。



总的来说，其实唯一键键约束和唯一索引功能是一样的： "唯一性" + "索引"

唯一键键约束 只是作为一种独特的约束(如主键约束,唯一键约束,check约束,外键约束 的一种)，以约束的形式管理.但是同时又自动创建了唯一非聚集索引，也就有了索引的性能和部分功能.实际上唯一键约束是用唯一索引来约束的。


唯一索引 就是一种索引，它对某字段进行唯一性检查，同时可以设置各种参数，非常灵活。


那么我们在创建列的唯一性时,到底使用哪一种较好呢?（个人理解）

唯一键约束在表中是必定存在的约束的,唯一键约束的索引存在于一个分区中，并且不会像索引那样可以更改。因为索引可以随时改动（当然也不会经常改动），索引个人建议还是用唯一索引更灵活。管理约束还得管理索引，而管理索引，一个就好了。但是对于一些高可用性，也要注意索引是否在其他地方也存在。


更多参考：Unique Constraints and Unique Indexes


标签：

相关文章