The difference between a unique index and a unique constraint is mostly about the rule you want to express. My original short answer said they enforce uniqueness in much the same way; that remains true. The choice still matters when you need a filter or covering columns.
Question: What is the difference between a unique index and a unique constraint?
Answer: SQL Server creates a supporting unique index for a UNIQUE constraint. A standalone unique index can enforce the same unfiltered key rule. I use a constraint when uniqueness is part of the business model, because it states the intent clearly. I choose a standalone index when I need a feature such as a WHERE filter or INCLUDE columns.
For example, two optional email fields may both be NULL, while any supplied email must be unique. Run the separate demonstrations in a fresh query window without existing temp tables of these names. A filtered unique index can express that rule:
CREATE TABLE #CustomerEmailDemo
(
CustomerId int NOT NULL PRIMARY KEY,
Email nvarchar(200) NULL,
DisplayName nvarchar(100) NOT NULL
);
CREATE UNIQUE INDEX IX_CustomerEmailDemo_Email
ON #CustomerEmailDemo (Email)
INCLUDE (DisplayName)
WHERE Email IS NOT NULL;A plain single-column UNIQUE constraint on a nullable SQL Server column permits one NULL, not several. The filtered index excludes NULL rows from its uniqueness check while keeping each supplied email unique. The included display name is extra leaf-level data for a query; it does not become part of the unique key. If email is required, declare NOT NULL and the plain constraint may be the clearer choice.
The unfiltered business-rule form can be written like this in a separate demonstration table:
CREATE TABLE #CustomerConstraintDemo
(
CustomerId int NOT NULL PRIMARY KEY,
Email nvarchar(200) NULL,
CONSTRAINT UQ_CustomerConstraintDemo_Email UNIQUE (Email)
);Removal syntax also reflects the distinction. Drop a constraint with ALTER TABLE ... DROP CONSTRAINT; drop a standalone index with DROP INDEX ... ON. The catalog flag sys.indexes.is_unique_constraint tells you whether a unique index supports a constraint. A suitable unfiltered unique index can serve as a referenced candidate key; a filtered index does not guarantee uniqueness for every row and should not be treated as a general foreign-key target.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
7 Comments. Leave new
A unique index may include a filter predicate to allow things like multiple null values. Can a unique constraint do the same?
A unique index may include a filter predicate to allow things like multiple null values, but can a unique constraint do the same?
I don’t know about what @mrekdal wrote of above but, even though they both prevent duplicates, there are some very real difference between the two behind the scenes. Of course, the first answer someone gives should be as you posted but a sharpie will know the hidden differences.
Please see the following code and comments. I made the code safe to run even though it conditionally drops tables to make reruns easier.
Another difference is important when it comes to constraints. They MUST be uniquely named in the database where an index can carry the same name as indexes on other tables. That’s incredibly important to concurrency when using Local Temp Tables.
–===== Since we’re conditionally dropping tables for this test,
— do this test in a nice, safe place that everyone has.
USE tempdb
;
–===== Make sure that neither of the test tables already exist.
IF OBJECT_ID(‘tempdb.dbo.TestTable1′,’U’) IS NOT NULL
DROP TABLE tempdb.dbo.TestTable1
;
IF OBJECT_ID(‘tempdb.dbo.TestTable2′,’U’) IS NOT NULL
DROP TABLE tempdb.dbo.TestTable2
;
GO
–===== Create two nearly identical test tables
CREATE TABLE dbo.TestTable1
(SomeColumn VARCHAR(50))
;
CREATE TABLE dbo.TestTable2
(SomeColumn VARCHAR(50))
;
GO
–===== Add a named unique constraint to the first table
ALTER TABLE dbo.TestTable1
ADD CONSTRAINT TestTable1_SomeColumn_Unique UNIQUE NONCLUSTERED
(
SomeColumn
) ON [PRIMARY]
;
GO
–===== Add a named unique index to the second table
CREATE UNIQUE NONCLUSTERED INDEX TestTable2_SomeColumn_Unique
ON dbo.TestTable2
(
SomeColumn
) ON [PRIMARY]
;
GO
–===== Show that the first table has both a unique constraint and a unique index.
— This also give a warning in the Messages window that can be ignored.
EXEC sp_help ‘dbo.TestTable1’
;
–===== Show that the second table only has a unique index
— This also gives a warning in the Messages window that can be ignored.
EXEC sp_help ‘dbo.TestTable2’
;
GO
–===== Show that if you drop the constraint, it also drops the index.
ALTER TABLE dbo.TestTable1
DROP CONSTRAINT TestTable1_SomeColumn_Unique
;
EXEC sp_help ‘dbo.TestTable1’
;
GO
–===== Recreate the constraint, which also recreates the index.
ALTER TABLE dbo.TestTable1
ADD CONSTRAINT TestTable1_SomeColumn_Unique UNIQUE NONCLUSTERED
(
SomeColumn
) ON [PRIMARY]
;
EXEC sp_help ‘dbo.TestTable1’
;
Go
–===== Now show that you cannot drop the unique index from TestTable1…
— (this produces an error)
DROP INDEX TestTable1_SomeColumn_Unique ON dbo.TestTable1
;
GO
–===== But you can drop the unique index from TestTable2
DROP INDEX TestTable2_SomeColumn_Unique ON dbo.TestTable2
;
You can’t have filtered constraints, and you can’t include columns on constraints. That said, in general I don’t like unique indexes apart from for one the reasons above. Uniqueness is a business constraint and should be part of the table design, not represented in an index that’s external to the table.
Thank you Sir,
That is very fair point.
Unique Index will ALLOW multiple NULL Values.
CREATE TABLE dbo.People (
ID int, Salary INT
)
CREATE UNIQUE INDEX IX_T ON [People] ([ID]) WHERE [ID] IS NOT NULL
insert into dbo.People
values(NULL, 10), (NULL, 15)
— Here Second Null will Allow
Select * from People
Thanks @Yogesh