Microsoft SQL Server forum - What's the difference? Unque Constraint and unique index, etc.?

Author

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

Q. John Chen

2005-05-27, 7:23 am

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

David Portas

2005-05-27, 7:23 am

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
--

Erland Sommarskog

2005-05-27, 7:23 am

David Portas (REMOVE_BEFORE_REPLY
ING_dportas@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, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

David Portas

2005-05-27, 7:23 am

Good point Erland. I had forgotten about DROP_EXISTING.

--
David Portas
SQL Server MVP
--

Gert-Jan Strik

2005-05-27, 8:23 pm

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

Sponsored Links