以前也想了解到底有什么区别，但是搁着又忘记了，因为我们很少用唯一键约束。直到几天前同事给我个脚本来约束某个字段的唯一性，用的是唯一键约束，这问题又萦绕脑中了。看似有区别，又没发现什么大的区别！实际上也没多大区别，还是测试看看吧。
?
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 (
).
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 (
).
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')
?
创建唯一键约束,同时创建同名的唯一非聚集索引, 同时创建同名统计信息; 唯一键约束靠唯一索引来约束。
若对唯一键生成的索引直接删除，错误！
?
--对索引删除,错误!
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)
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