sql server: Unique constraint generates unique key and unique index

Question

I’ve got 2 tables:

attCatAppSet, attCatAppSet_translation

On both tables I’ve applied a unique constraint on 2 columns (that are not Primary Keys), so that column-pair values can't be duplicated.

GO
ALTER TABLE attCatAppSet
ADD CONSTRAINT UQ_category_id_setOrder 
UNIQUE(category_id, setOrder)
GO


GO
ALTER TABLE attCatAppSet_translation
ADD CONSTRAINT UQ_siteLanguage_id_attCatAppSet_id 
UNIQUE(siteLanguage_id, attCatAppSet_id)
GO

Result: looking at the object explorer I get 2 different implementations of my commands. On table attCatAppSet, there is a unique index constraint. On table attCatAppSet_translation, there is a unique index and unique key constraint.

enter image description here

Same thing shows if I call:

GO
sp_helpIndex attCatAppSet
GO
sp_helpIndex attCatAppSet_translation

enter image description here

How come I've got 2 different implementations of the query?
What is the difference between the 2 results?

At first blush, siteLanguage_id has a foreign key constraint while setOrder does not. — Rahul Vijay Dawda
– Rahul Vijay Dawda, Commented Oct 3, 2014 at 10:44
both constraints have at least a FK column, but this shouldn't be the issue. — M. Carlo Bramini
– M. Carlo Bramini, Commented Oct 3, 2014 at 10:49

Vojtěch Dohnal · Accepted Answer · 2014-10-03 11:48:44Z

3

Your constraint in the table attCatAppSet contains setOrder field, that is not a foreign key. So your foreign key in attCatAppSet is just category_id - and it is also displayed as a foreign key. But your constraint UNIQUE(category_id, setOrder) requires combined unique index of category_id and setOrder - and so this index was created and is displayed as a normal index (not as a foreign key).

Foreign keys are used to define relations between tables. It seems that you have already created the relations before creating theese constraints. (e.g. you have already defined relation between attCatAppSet_translation and siteLanguage tables etc.)

The constraint attCatAppSet_translation contains two foreign keys, so it is displayed as a key that has following meaning: attCatAppSet_translation table contains only unique combinations of siteLanguage and attCatAppSet.

Only two combined unique indexes were created. The both indexes created by SQL Server are functionally equivalent, only they are displayed with different icons. It is only about how database model is documented within SQL Server Management Studio.

Further information here and here.

UNIQUE constraints are part of the ANSI SQL definition and defining UNIQUE constraints is part of defining a database's logical design.

From a performance standpoint, UNIQUE constraints and unique indexes are effectively the same to the query optimizer and you will not see any performance benefit to using one vs the other.

edited Oct 3, 2014 at 11:48

answered Oct 3, 2014 at 11:23

Vojtěch Dohnal

8,1123 gold badges53 silver badges115 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

M. Carlo Bramini Over a year ago

Thus the "same" result can be achived with: CREATE UNIQUE INDEX [UQ_category_id_setOrder] ON [dbo].[attCatAppSet] ( [category_id] , [setOrder] ) ?

Dan Guzman Over a year ago

@Luther, a foreign key may be bound to a primary key, unique constraint, or unique index. Any of these that have the same columns as the foreign key can be used but you have no direct control over which is used if you have redundant indexes. See dbdelta.com/secrets-of-foreign-key-index-binding

M. Carlo Bramini Over a year ago

@VDohnal and Dan Guzman - SQL Server MVP, (I know it's redundant) thanks.

Collectives™ on Stack Overflow

sql server: Unique constraint generates unique key and unique index

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related