SQLServer 唯一鍵約束和唯一索引有什麼區別？，sqlserver索引

SQLServer 唯一鍵約束和唯一索引有什麼區別？，sqlserver索引

以前也想瞭解到底有什麼區別，但是擱著又忘記了，因為我們很少用唯一鍵約束。直到幾天前同事給我個指令碼來約束某個欄位的唯一性，用的是唯一鍵約束，這問題又縈繞腦中了。看似有區別，又沒發現什麼大的區別！實際上也沒多大區別，還是測試看看吧。

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