Solved

Unique Index and Unique Constraint

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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 shrink a transaction log file down to a reasonable size.

810 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