Friday, March 9, 2012

Errors 2601 and 2627

Hi

What is the difference between Sql Server 2005 errors 2601 and 2627? How could one cause error 2627?

Thanks

2601 - Violation in unique index

2627 - Violation in unique constraint (although it is implemented using unique index)

The error messages are used to distinguish the object on which the violation happens (unique constraint or unique index).

Also, constraints are logical entities and part of the ANSI SQL standard whereas indexes are physical structures that are not part of the standard. So the ANSI SQL standard doesn't talk about how a primary key or unique constraint should be enforced by a database engine. It just happens that SQL Server enforces primary key/unique constraints using an unique index underneath the covers. And when you create logical data model you can use only constraints. Indexes are created on the tables for optimizing certain access paths or queries and not part of the logical data model.

No comments:

Post a Comment