Solved

Unique Index and Unique Constraint

Posted on 2007-04-06
2
705 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 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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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…

617 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