Question: What is the difference between unique index and unique constraint?
Answer:
Add Unique Constraint
ALTER TABLE dbo.<tablename> ADD CONSTRAINT
<namingconventionconstraint> UNIQUE NONCLUSTERED
(
<columnname>
) ON [PRIMARY]
Add Unique Index
CREATE UNIQUE NONCLUSTERED INDEX
<namingconventionconstraint> ON dbo.<tablename>
(
<columnname>
) ON [PRIMARY]
There is no difference between Unique Index and Unique Constraint. Even though the syntax is different the effect is the same. Unique Constraint creates Unique Index to maintain the constraint to prevent duplicate keys. Unique Index or Primary Key Index is a physical structure that maintains uniqueness over some combination of columns across all rows of a table. It is a convenient way to enforce a Unique Constraint in SQL Server.
Reference: Pinal Dave (https://blog.sqlauthority.com)
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