x

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, 2010 at 01:47 AM in Default

Rob Farley gravatar image

Rob Farley
5.7k 16 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, 2010 at 05:46 AM

Steinar gravatar image

Steinar
1.7k 3 4 6

+1 for "Effectively". There are some significant differences...
Jan 28, 2010 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, 2010 at 05:52 AM

bonskijr gravatar image

bonskijr
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, 2010 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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x131
x27
x22
x17

asked: Jan 28, 2010 at 01:47 AM

Seen: 1838 times

Last Updated: Jan 28, 2010 at 05:54 AM