Unique Index and Unique Constraint

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
LVL 21
pinaldaveAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dqmqCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.