question

Rob Farley avatar image
Rob Farley asked

What is the difference between a Unique Index and a Unique Constraint?

Are the two equivalent? Which one is preferable?

(Seeder question - if you have over 2000 reputation points here, please refrain from answering this question for a week or so)

indexingseeder-questionconstraintunique
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Steinar avatar image
Steinar answered

Since I only have about 635 I can answer :-) My answer is : Effectively none.

1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+1 for "Effectively". There are some significant differences...
0 Likes 0 ·
bonskijr avatar image
bonskijr answered

They're the same, however to be more self-documenting and more intuitive I prefer Unique-Constraint mainly because (imho) UNIQUENESS really should be a constraint. Plus constraint automatically adds the unique index when created.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

David 1 avatar image
David 1 answered

In principle a constraint is a logical feature of the database whereas an index is part of the physical implementation level. In practice, SQL Server compromises physical database independence and enforces uniqueness for unique indexes just as it does for constraints. SQL Server also automatically creates an index to support each constraint. So they achieve much the same end result.

I agree with bonskijr's comment that it's generally better to use constraints rather than unique indexes without constraints. Most developers and many software tools expect uniqueness to be enforced through a constraint and will look for constraints in order to identify keys and understand the data model.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.