Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Unique Index and Unique Constraint

Posted on 2007-04-06
2
Medium Priority
?
708 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1000 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 1000 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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

670 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