RE: Difference between creating a Unique Constraint and a Unique Index

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: AnthonyThomas (AnthonyThomas_at_discussions.microsoft.com)
Date: 11/04/04 

Date: Thu, 4 Nov 2004 05:34:01 -0800

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

Relevant Pages

  • Re: orace sql query
    ... "Oracle enforces all PRIMARY KEY constraints using indexes. ... believe otherwise but were that not true you could not enforce ... For example, a PRIMARY KEY constraint ... Puget Sound Oracle Users Groupwww.psoug.org ...
    (comp.databases.oracle.server)
  • Re: orace sql query
    ... "Oracle enforces all PRIMARY KEY constraints using indexes. ... believe otherwise but were that not true you could not enforce ... For example, a PRIMARY KEY constraint ...
    (comp.databases.oracle.server)
  • Re: Difference between creating a Unique Constraint and a Unique Index
    ... background when you specify a PK or Unique constraint. ... So use PK and Uniques to enforce design requirements...Indexes are ... the indexes created to enforce data integrity. ... Wayne Snyder, MCDBA, SQL Server MVP ...
    (microsoft.public.sqlserver.server)
  • Re: orace sql query
    ... believe otherwise but were that not true you could not enforce ... For example, a PRIMARY KEY constraint ... Puget Sound Oracle Users Groupwww.psoug.org ... Create a generic table without a primary key: ...
    (comp.databases.oracle.server)
  • Thank you
    ... But using Enforce ... >does not satisfy the constraints set on your dataset. ... >which constraint is not being satisfied. ... >> I created a dataset by dragging a (SQL Server) stored ...
    (microsoft.public.dotnet.framework.adonet)