What is the difference between Unique Index and Unique Constraint


There is no difference between a unique constraint and a unique index other than the fact that the unique constraint is listed as a constraint in the database. A unique constraint cannot be disabled.The status of a constraint does not allow the unique constraint any additional functionality over a unique index. Even though the syntax for both are 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 are physical structure that maintain uniqueness over some combination of columns across all rows of a table.

Let us start by building and example:
USE AdventureWorks
go
CREATE TABLE dbo.Test
( ID int NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Test] ADD CONSTRAINT [CK_Test] UNIQUE
(
[ID] ASC
) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [IDX_Test_ID] ON [dbo].[Test]
(
[ID] ASC
)
GO
exec sp_helpconstraint Test
exec sp_helpindex Test

Snapshot of the output can been seen below.

Unique Index

Advertisements

Tagged: , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: