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

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

From: Wayne Snyder (wayne.nospam.snyder_at_mariner-usa.com)
Date: 11/04/04 

Date: Thu, 4 Nov 2004 07:59:14 -0500

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

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
    ... Second, you made mention of PK and GUID, which I am assuming you implied ... EmployeeID and no Unique Constraint is ever defined on the Candidate Key ... GUID PK is also defined as the Clustered Index, if there is one at all. ... > a unique index will not enforce uniqueness. ...
    (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)