Posted by David Portas on 05/27/05 08:48

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

• Next in forum: Re: Proportional fill
• Prev in forum: Re: Need some help with design of tables/views
• Thread view: Re: What's the difference? Unque Constraint and unique index, etc.?

[Reply to this message]