唯一約束和唯一索引在表設(shè)計(jì)中是最常見(jiàn)的，都有唯一屬性，用于約束表字段的唯一性。但是，這兩者到底有什么區(qū)別呢？
接下來(lái)我們測(cè)試看看，創(chuàng)建兩張表分別用于測(cè)試唯一約束和唯一索引。
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
現(xiàn)在分別創(chuàng)建唯一鍵約束和創(chuàng)建唯一索引，發(fā)現(xiàn)錯(cuò)誤！
-- 創(chuàng)建唯一鍵約束ALTER TABLE [dbo].[TableUniqueKey] ADD CONSTRAINT [IX_TableUniqueKey_name] UNIQUE ([name] ASC)--默認(rèn)非聚集索引GOALTER TABLE [dbo].[TableUniqueKey] ADD CONSTRAINT [IX_TableUniqueKey_name] UNIQUE NONCLUSTERED([name] ASC)GO-- 創(chuàng)建唯一索引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.
可以看到，都提示有重復(fù)值，重復(fù)值均為 NULL 值?，F(xiàn)在刪除重復(fù)值，再創(chuàng)建唯一約束。
DELETE FROM [dbo].[TableUniqueKey] WHERE ID = 3DELETE FROM [dbo].[TableUniqueIndex] WHERE ID = 3-- 創(chuàng)建唯一鍵約束ALTER TABLE [dbo].[TableUniqueKey] ADD CONSTRAINT [IX_TableUniqueKey_name] UNIQUE NONCLUSTERED([name] ASC)
創(chuàng)建唯一鍵約束，會(huì)同時(shí)創(chuàng)建同名的唯一非聚集索引, 以及創(chuàng)建同名統(tǒng)計(jì)信息；而唯一鍵約束是靠唯一索引來(lái)約束。
若是對(duì)唯一鍵生成的索引直接刪除，則報(bào)錯(cuò)！
DROP INDEX [IX_TableUniqueKey_name] ON [dbo].[TableUniqueKey]
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]
繼續(xù)測(cè)試，現(xiàn)在對(duì)另一張表創(chuàng)建唯一索引。
CREATE UNIQUE NONCLUSTERED INDEX [IX_UniqueIndex_name] ON [dbo].[TableUniqueIndex]([name] ASC)
創(chuàng)建唯一索引, 同時(shí)創(chuàng)建同名統(tǒng)計(jì)信息。
對(duì)索引刪除后，我們?cè)僦亟ǎ瑢?duì)比再看看。
DROP INDEX [IX_UniqueIndex_name] ON [dbo].[TableUniqueIndex]GOALTER TABLE [dbo].[TableUniqueKey] ADD CONSTRAINT [IX_TableUniqueKey_name] UNIQUE NONCLUSTERED([name] ASC)GOCREATE UNIQUE NONCLUSTERED INDEX [IX_UniqueIndex_name] ON [dbo].[TableUniqueIndex]([name] ASC)GOSELECT 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 的約束。而他們的索引都有唯一性約束。
此外，還可以通過(guò)以下檢查他們的區(qū)別：
EXEC sp_helpconstraint 'TableUniqueKey' EXEC sp_helpconstraint 'TableUniqueIndex'
EXEC sp_helpindex 'TableUniqueKey' EXEC sp_helpindex 'TableUniqueIndex'
-- EXEC sp_help 'TableUniqueKey' -- EXEC sp_help 'TableUniqueIndex'
對(duì)比索引描述中，唯一鍵 比 唯一 索引多了 unique key 。
--唯一鍵不出錯(cuò)EXEC sp_help [IX_TableUniqueKey_name]EXEC sp_helpindex [IX_TableUniqueKey_name]EXEC sp_helpconstraint [IX_TableUniqueKey_name]--唯一索引出錯(cuò)EXEC sp_help [IX_UniqueIndex_name]EXEC sp_helpindex [IX_UniqueIndex_name]EXEC sp_helpconstraint [IX_UniqueIndex_name]
上面可以看出,唯一不同的是: 唯一鍵 比 唯一索引 多了一種叫做 "unique key" 的約束。
現(xiàn)在禁用索引，再插入重復(fù)數(shù)據(jù)。
--禁用索引/約束（均可被禁用）ALTER INDEX [IX_TableUniqueKey_name] ON [dbo].[TableUniqueKey] DISABLEALTER INDEX [IX_UniqueIndex_name] ON [dbo].[TableUniqueIndex] DISABLE--插入重復(fù)值，正常INSERT INTO [dbo].[TableUniqueKey] SELECT 3,NULL INSERT INTO [dbo].[TableUniqueIndex]SELECT 3,NULL
數(shù)據(jù)都能正常插入，約束或索引被禁用了。對(duì)于唯一鍵約束，也是禁用索引嗎？函數(shù) ObjectProperty() 的參數(shù) CnstIsDisabled 可以確認(rèn)約束是否被禁用。
SELECT ObjectProperty(object_id('IX_TableUniqueKey_name'),'CnstIsDisabled')
結(jié)果為 0 ，即約束沒(méi)有被禁用，也就是說(shuō)禁用的是索引，唯一鍵約束中，唯一性是依賴于其默認(rèn)創(chuàng)建的唯一索引來(lái)約束的！
現(xiàn)在重建索引：
--刪除重復(fù)DELETE FROM [dbo].[TableUniqueKey] WHERE ID = 3DELETE FROM [dbo].[TableUniqueIndex] WHERE ID = 3/*對(duì)索引的更改*/--重建索引ALTER INDEX [IX_TableUniqueKey_name] ON [dbo].[TableUniqueKey] REBUILDALTER INDEX [IX_UniqueIndex_name] ON [dbo].[TableUniqueIndex] REBUILD--更改部分索引參數(shù)ALTER INDEX [IX_TableUniqueKey_name] ON [dbo].[TableUniqueKey] SET ( ALLOW_ROW_LOCKS = ON )ALTER INDEX [IX_UniqueIndex_name] ON [dbo].[TableUniqueIndex] SET ( ALLOW_ROW_LOCKS = ON )
兩種索引其實(shí)還是可以更改一下參數(shù)的。使用窗口打開(kāi)查看，唯一鍵約束的索引有些是不能更改的。
唯一鍵約束的索引不能像正常的索引使用太多的索引參數(shù)，因?yàn)槲ㄒ绘I約束與其索引同在。而單獨(dú)創(chuàng)建的唯一索引可以設(shè)置更多的參數(shù),如 PAD_INDEX, FILLFACTOR, IGNORE_DUP_KEY, DROP_EXISTING, STATISTICS_NORECOMPUTE, and SORT_IN_TEMPDB 。
總的來(lái)說(shuō)，唯一鍵鍵約束和唯一索引功能是一樣的："唯一性" + "索引"
唯一鍵鍵約束：只是作為一種獨(dú)特的約束(如主鍵約束,唯一鍵約束,check約束,外鍵約束 的一種)，以約束的形式管理.但是同時(shí)又自動(dòng)創(chuàng)建了唯一非聚集索引，也就有了索引的性能和部分功能.實(shí)際上唯一鍵約束是用唯一索引來(lái)約束的。
唯一索引：就是一種索引，它對(duì)某字段進(jìn)行唯一性檢查，同時(shí)可以設(shè)置各種參數(shù)，非常靈活。
那么我們?cè)趧?chuàng)建列的唯一性時(shí)，到底使用哪一種較好呢?
唯一鍵約束在表中是必定存在的約束的，唯一鍵約束的索引存在于一個(gè)分區(qū)中，并且不會(huì)像索引那樣可以更改。因?yàn)樗饕梢噪S時(shí)改動(dòng)（當(dāng)然也不會(huì)經(jīng)常改動(dòng)），個(gè)人建議索引還是用唯一索引更靈活。管理約束還得管理索引，而管理索引，一個(gè)就好了。但是對(duì)于一些高可用性，也要注意索引是否在其他地方也存在。
閱讀原文：原文鏈接
該文章在 2025/1/10 11:06:27 編輯過(guò)
400 186 1886
