Solved

Unique Index and Unique Constraint

Posted on 2007-04-06
2
695 Views
Last Modified: 2008-01-09
I was reading some of the PAQ regarding Unique Constraint and Unique Index. I found this interesting post. Though, there are lots of things and I am confused. (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_22132268.html)

Any way,
My questions are:
What is the difference between them?
1) Syntax
2) Logical Implementation
3) Physical Storage
0
Comment
Question by:pinaldave
2 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
ID: 18867100
syntax shoul be obvious...

logical implementation: both use/create an index, the unique index per se (where in addition to indexing the values, it enforced the values to be unique), while the unique constraint is a contraint that forced values to be unique, using an index behind to ensure the performance for those checks.
in short: it's the "same" thing, ie you achieve the same results with it.

physical stored: see above, they are using both a "simple" index, so no difference there.
0
 
LVL 42

Assisted Solution

by:dqmq
dqmq earned 250 total points
ID: 18867300
Unique constraint (Or unique key, in SS parlance)  tells the database to assure that some combination of columns contains unique values across all rows of the table. There are some nuances with respect to NULLs--SQL server treats NULL like a value and therefore allows one occurance of NULL per constraint column. (I believe that treatment of NULL is a departure from relational theory and the SQL standard).   Theoretically, a unique constraint could be implemented any number of ways and theoretically we should not care how.

Unique index is physical structure that maintains uniqueness over some combination of columns across all rows of a table.  Naturally, it is a convenient way to enforce a unique constraint and that is exactly how SQL Server does it.

So, if you create a unique key, SQL server automatically creates an unique index to support it.  That's true of primary key constraint and unique key constraint. The only difference is that the primary key constraint (and its unique index) do not permit NULL in any column.

But if you create a unique index, directly, SQL server does not assume you also desire a unique key constraint and, therefore, does not create one.  A unique index is one way to assure uniqueness at the physical level.

A unique index assures uniqueness at the physical level.
A unique key constraint assures uniqueness at the logical level.
SS uses unique index to enforce unique constraint. But, theoretically, that could change at any time.

Also, like a primary key, a unique key can be referenced by a foreign key constraint; a unique index cannot.








 





0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now