Difference between creating a Unique Constraint and a Unique Index

Hi,
I've looked at the online documentation and encountered this piece of
text
"If uniqueness must be enforced to ensure data integrity, create a
UNIQUE or PRIMARY KEY constraint on the column rather than a unique
index"
I'm baffled; I understand a unique contraint, and personally do not
care how it is physically enforced (using an index to speed up the
process) as long as it is enforced. However what is the deal with
unique indexes? The piece of documentation above seems to suggest that
a unique index will not enforce uniqueness.
The reason I ask is that I am working against a legacy system where
unique indexes are used for each table. Each table, besides a PK, has
a column that stores a GUID. Personally I would use a Unique
Contraint, but they have used a unique index on this column. What
would be the advantages and disadvantages of using a unique index vs a
unique constraint.'
regards
Paul SjoerdsmaYes, a unique index enforces uniqueness. A unique index is created in the
background when you specify a PK or Unique constraint.
The difference is more semantic, but also tactical... Placing the constraint
indicates a design requirement. When you create the index directly, how can
you know IF the index was created for performance, or if the index is
intended to enforce some design constraint... The fact is that you can
not... So use PK and Uniques to enforce design requirements...Indexes are
created for 2 main reasons, to enforce integrity and speed up queries.The
indexes used to speed up queries( or other statements) may need to change
over time as business priorities change, but we would still need to maintain
the indexes created to enforce data integrity.
Indexes created as a by-product of PK or unique constraints can NOT be
dropped using DROP index, so you are protected from inadvertently dropping
an index which is used for data integrity. FOr this you must use alter table
drop constraint, alter table add constraint... Any index that goes away
using DROP index, therefore, would have been created for performance, and is
a candidate for re-assessment as needs change
Additionally FKs must be placed on columns with PKs or Unique
constraints.(from BOL). HOwever the actual code allows you to put an FK on
any column which has a unique index ( although I beleive this should NOT be
allowed)..
Hope this helps/
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Paul Sjoerdsma" <paul@.pasoftware.nl> wrote in message
news:2j8ko0pdll18amito60gcqne5g58en6otj@.4ax.com...
> Hi,
> I've looked at the online documentation and encountered this piece of
> text
> "If uniqueness must be enforced to ensure data integrity, create a
> UNIQUE or PRIMARY KEY constraint on the column rather than a unique
> index"
> I'm baffled; I understand a unique contraint, and personally do not
> care how it is physically enforced (using an index to speed up the
> process) as long as it is enforced. However what is the deal with
> unique indexes? The piece of documentation above seems to suggest that
> a unique index will not enforce uniqueness.
> The reason I ask is that I am working against a legacy system where
> unique indexes are used for each table. Each table, besides a PK, has
> a column that stores a GUID. Personally I would use a Unique
> Contraint, but they have used a unique index on this column. What
> would be the advantages and disadvantages of using a unique index vs a
> unique constraint.'
>
> regards
> Paul Sjoerdsma
>|||In addition to the other respondents reply, implicit in your question is
really two seperate questions:
What is the difference between the use of PK and Unique Constraints and
Unique Indexes? The was answered by the other respondent. Except, so you
know, there is no such thing as a PK index, only a constraint, which is also
enforced through a Unique Index.
Second, you made mention of PK and GUID, which I am assuming you implied
what is typically used some sort of INT IDENTITY. Both of these are unique
by design, one locally to the table, the other across all tables, databases,
and time. However, these are mearly mechanics. They do not garauntee
uniqueness of the data, which is the sole purpose of KEYS, Primary or
Alternate. Moreover, PKs and Unique Constraints are not limited to IDENTITY
nor GUID. For example:
EmployeeID LastName FirstName
1 Smith John
2 Jones Tom
3 Marshall Eric
4 Smith John
Although EmployeeID is unique, because it is IDENTITY and is so by
construction, for this table, the DATA is NOT unique: John Smith is repeated.
Therefore, this table is NOT even a table by relational design--it is not in
1NF (First Normal Form).
The real key for this entity is the Logical Name attribute, which is
composite upon FirstName and LastName. Since that is pretty much all of the
data, it is also the Primary Key candidate. However, if this table were
JOINed to some other not only would you have to populate both attributes to
that table, you would have to populate string types (data storage
inefficient) and would have to include both attributes in any Foreign Key or
JOIN declaration, also highly inefficient.
So, oftentimes, one creates the IDENTITY or GUID attributes to simplify
these requirements. What happens, TOO OFTEN, is the PK is defined on
EmployeeID and no Unique Constraint is ever defined on the Candidate Key
combination of First and Last Names, which is the true key of this table, but
should be and MUST be for this implementation to be in at least 1NF.
Moreover, while I am on it, I see far too often that such an IDENTITY or
GUID PK is also defined as the Clustered Index, if there is one at all. That
is a lousy choice. Not only should every table have a KEY (Primary or
Otherwise)--this is a logical or design requirement--every table should also
have a WELL CHOSEN Clustered Index or Key defined--this is a physical
recommendation.
A well chosen Clustered Index or Key will affect the construction of
statistics and other indexes on the table. It should be nearly unique, if
not a unique key. It should not change often. And, it should be considered
for RANGE type query requirements. In our example, not only do we need to
create a UNIQUE CONSTRAINT for the Name attribute, the Last Name or the Last
Name + First Name combination should be considered for the Clustered Index.
I hope I have not gone to Academic, or Theoretical--some might say, Cultish.
Nevertheless, these point go far too unnoticed by the mass of database
designers and users and needs to be said more often.
Sincerely,
Anthony Thomas
"Paul Sjoerdsma" wrote:
> Hi,
> I've looked at the online documentation and encountered this piece of
> text
> "If uniqueness must be enforced to ensure data integrity, create a
> UNIQUE or PRIMARY KEY constraint on the column rather than a unique
> index"
> I'm baffled; I understand a unique contraint, and personally do not
> care how it is physically enforced (using an index to speed up the
> process) as long as it is enforced. However what is the deal with
> unique indexes? The piece of documentation above seems to suggest that
> a unique index will not enforce uniqueness.
> The reason I ask is that I am working against a legacy system where
> unique indexes are used for each table. Each table, besides a PK, has
> a column that stores a GUID. Personally I would use a Unique
> Contraint, but they have used a unique index on this column. What
> would be the advantages and disadvantages of using a unique index vs a
> unique constraint.'
>
> regards
> Paul Sjoerdsma
>|||Anthony and Wayne,
Thanks for your answers. Although I would consider myself to be
experienced in database design (with the necessary theoretical
background) I still do not grok the full idea behind the concept of a
unique index; even after your elaborate explanations.
To me it seems to have raised it's head out of some murky swamp. There
are nicer places to visit, so a Unique Key constraint is what it will
be.
However if someone could give me a clear example where I would use a
unique index instead of a unique key I would be very happy,
thanks,
regards
Paul
On Thu, 4 Nov 2004 05:34:01 -0800, "AnthonyThomas"
<AnthonyThomas@.discussions.microsoft.com> wrote:
>In addition to the other respondents reply, implicit in your question is
>really two seperate questions:
>|||> However if someone could give me a clear example where I would use a
> unique index instead of a unique key I would be very happy,
Very good question. IMO, there aren't any (*). Some say that you should use an index directly when
the uniqueness isn't a property of the data model per se, but IMO, I have a hard time finding such
an example. If I want to enforce uniqueness, I surely want to expose this at the logical level
(constraint) as well, right?
(*) Here's an exception. There are at least one attribute you can define for a unique index which
isn't available through a unique constraint. If this index attribute is important for you, define an
index directly and not though a unique constraint. In SQL Server 2005, this will be available
through a constraint...
Be prepared that different people have different viewpoints about this. It is a bit like the age-old
discussion "natural keys vs. surrogate keys".
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul Sjoerdsma" <paul.nospam.@.pasoftware.nl> wrote in message
news:h9dmo0dce287fe5gdjoo0ro4mvgqrsggb5@.4ax.com...
> Anthony and Wayne,
> Thanks for your answers. Although I would consider myself to be
> experienced in database design (with the necessary theoretical
> background) I still do not grok the full idea behind the concept of a
> unique index; even after your elaborate explanations.
> To me it seems to have raised it's head out of some murky swamp. There
> are nicer places to visit, so a Unique Key constraint is what it will
> be.
>
> However if someone could give me a clear example where I would use a
> unique index instead of a unique key I would be very happy,
>
> thanks,
> regards
> Paul
>
> On Thu, 4 Nov 2004 05:34:01 -0800, "AnthonyThomas"
> <AnthonyThomas@.discussions.microsoft.com> wrote:
> >In addition to the other respondents reply, implicit in your question is
> >really two seperate questions:
> >
>|||I have two real life examples where I would use a unique index as
opposed to a unique constraint.
1. When you have a relation table that contains the keys of two
referenced tables. For example the table StudentCourses which lists the
courses for each student. It might be defined as:
CREATE TABLE StudentCourses
(StudentID int not null REFERENCES Students
,CourseID int not null REFERENCES Courses
,CONSTRAINT PK_StudentCourses PRIMARY KEY (StudentID, CourseID)
)
In this case there already is a unique constraint on (StudentID,
CourseID). For performance purposes I would add a unique index on
(CourseID, StudentID). This would benefit query like:
SELECT Course, COUNT(*) AS StudentCount
FROM Courses C1
INNER JOIN StudentCourses CS1
ON CS1.CourseID = C1.CourseID
GROUP BY Course
2. When a (compound) covering index is needed, and one of its columns is
by itself unique. When I have the choice between a nonunique and a
unique index, I choose a unique index. But the columns of the covering
index do not constitute a new or different "unique relation", so a
unique constraint would be inappropriate.
The rule I use for unique constraints is that you should be able to
remove all user created unique indexes, and the data model would still
be consistent and enforced.
Hope this helps,
Gert-Jan
Paul Sjoerdsma wrote:
> Anthony and Wayne,
> Thanks for your answers. Although I would consider myself to be
> experienced in database design (with the necessary theoretical
> background) I still do not grok the full idea behind the concept of a
> unique index; even after your elaborate explanations.
> To me it seems to have raised it's head out of some murky swamp. There
> are nicer places to visit, so a Unique Key constraint is what it will
> be.
> However if someone could give me a clear example where I would use a
> unique index instead of a unique key I would be very happy,
> thanks,
> regards
> Paul
> On Thu, 4 Nov 2004 05:34:01 -0800, "AnthonyThomas"
> <AnthonyThomas@.discussions.microsoft.com> wrote:
> >In addition to the other respondents reply, implicit in your question is
> >really two seperate questions:
> >