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)

more ▼

asked Jan 28 '10 at 01:47 AM in Default

Rob Farley gravatar image

Rob Farley
5.7k 15 18 20

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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

more ▼

answered Jan 28 '10 at 05:46 AM

Steinar gravatar image

1.7k 3 4 6

+1 for "Effectively". There are some significant differences...
Jan 28 '10 at 10:10 PM Rob Farley
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Jan 28 '10 at 05:52 AM

bonskijr gravatar image

204 5 5 7

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Jan 28 '10 at 01:16 PM

David 1 gravatar image

David 1
1.8k 1 3

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jan 28 '10 at 01:47 AM

Seen: 1616 times

Last Updated: Jan 28 '10 at 05:54 AM