December 14, 2004 at 8:16 am
Can someone tell me what the difference is, if any, between a unique constraint and a unique index? Seriously, I've seen every answer possible on this one. A unique index is created as a byproduct of creating a unique constraint, but what exactly IS a unique constraint ASIDE from a unique index? Anything? An idea? Is one "better" for performance than the other? How or why?
December 14, 2004 at 8:40 am
For one thing, you can choose to ignore duplicate keys when you create a unique index. This isn't possible with a unique constraint.
For another, you can cluster a unique index.
December 14, 2004 at 12:57 pm
... and adding to jxflagg as of SQL 2000 you can disable a constraint but can't disable an index.
In my opinion is an option that only at application level you will be able to tell what's right for U
HTH
* Noel
December 14, 2004 at 1:48 pm
BOL says
"Creating a unique index instead of non-unique on the same combination of columns provides additional information for the query optimizer; therefore, creating a unique index is preferred. "
December 14, 2004 at 1:50 pm
thanks gang. more good feedback than I had expected.
December 15, 2004 at 12:47 am
Hi all,
I read somewhere the same question (i think it was on ORACLE discussion groups), and the answer was something like this: a constraint is a logical term (used in data model design), while an index is a physical term (used in the relational database implementation). Currently, a logical unique constraint is enforced or implemented by creating a physical unique index, and the guy that answered the question suggested that a constraint should be used. In theory, he said, in the future, constraints may be enforced by some physical object other than index.
Regards,
Goce.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply