469,648 Members | 1,158 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,648 developers. It's quick & easy.

What's the difference? Unque Constraint and unique index, etc.?

All,

What's the difference between a unique contraint and unique?
sementically, if you want a column contain unique values, it is a
contraint. And an index is for searching/sort. The questions are:

1. Does a unique constraint interally use unique index?

2. If Yes to #1, I DO NOT need to create an index for search/sort
purpose, right?

3. If Yes to #2, What's better?

4. Also for Primary Key column, it is actually a special unique
contraint. Not need to create index on PK column for searching/sorting,
correct?

5. Also for FK contraint, no need to create an index for
searching/sorting?
Thanks

John

Jul 23 '05 #1
4 2716
1. Yes

2. Correct

3. It's often said that uniqueness is "better" enforced through constraints
rather than indexes - if only because people expect this to find uniqueness
as a property of the logical model (constraints) rather than the physical
implementation (indexes). For the same reason it's also possible that some
ER modeling tools may recognize unique constraints but not unique indexes.

Indexes do have one potential advantage. You can declare the IGNORE_DUP_KEY
option on an index but not on a constraint. That's not much advantage in my
view because there are few, if any, situations in which I think the
IGNORE_DUP_KEY option is a good idea. In SQL Server 2005 constraints have
the IGNORE_DUP_KEY option too!

4. Correct

5. Wrong. Indexes aren't automatically created on foreign keys and a foreign
key is usually a good candidate for creating an index.

--
David Portas
SQL Server MVP
--
Jul 23 '05 #2
David Portas (RE****************************@acm.org) writes:
Indexes do have one potential advantage. You can declare the
IGNORE_DUP_KEY option on an index but not on a constraint. That's not
much advantage in my view because there are few, if any, situations in
which I think the IGNORE_DUP_KEY option is a good idea. In SQL Server
2005 constraints have the IGNORE_DUP_KEY option too!


I agree that IGNORE_DUP_KEY is not very useful.

However, there is another index option which is not available for
constraints which is more useful and that is DROP_EXISTING. If you for
some reason want to change a clustered index, dropping it and then
creating the new definition of the index, SQL Server has to rebuild
the non-clustered indexes twice. (Because the NC indexes uses the
clustered index key as the row locator.) DROP_EXISTING makes it possible
to do the change in one step. (I don't have the SQL 2005 docs handy, so
I can't say whether this is available for constraints in SQL 2005.)

My personal strategy is to use a constraint if it reflects some logical
property of the table, and index if it just happens to be unique. (The
typical reason that an index "happens" to be unique is when the primary
key is included as the last column or similar.)

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #3
Good point Erland. I had forgotten about DROP_EXISTING.

--
David Portas
SQL Server MVP
--

Jul 23 '05 #4
Just to add to the previous responses: the DROP_EXISTING feature is
indeed very useful. But there is no reason to shy away from using UNIQUE
constraints (or Primary Key constraints), because CREATE INDEX ... WITH
DROP_EXISTING will also work on indexes that enforce uniqueness of these
constraints.

Gert-Jan
Jul 23 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

26 posts views Thread by Agoston Bejo | last post: by
2 posts views Thread by Mansoor Azam | last post: by
10 posts views Thread by Laurence | last post: by
15 posts views Thread by Frank Swarbrick | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.