Re: Unique Indexes VS. Constraints

Thierry Poels wrote:
>
> hmmmz, yes and no ....
>
> a unique constraint IS based on a unique index. If you create a unique
> constraint, a unique index is also created. This will fail if duplicate
> keys are found.
>
> regards,
> Thierry Poels
> HP-UX SysAdmin & Oracle DBA.
>
> DanHW wrote in message <20001002202454.10152.00000037_at_ng-bj1.aol.com>...
> >>I'm curious, why would a database design use unqiue indexes versus
> >>constraints to enforce integrity.
> >>
> >>Situation: I have to determine how a large oracle db is put together.
> >>This database has some constraints but many more unqiue indexes as I
> >>determined using Oracle Enterprise Manager and ERWIN. I understand this
> >>DB's origin may have been from a flat DB2 database.
> >>
> >>Thanks
> >>Scott
> >>
> >
> >Unique indexes and constraints do different things.
> >A constraint restricts the value of a column to a [specified list of
 values] or
> >[a function of that column] or [a function of other columns in that same
 row].
> >For example, a constraint will limit a column to be 0 or 1; or be uppercase
> >only, or between 2 other values in that row. The most important aspect is
 that
> >it can be compared only with columns in the row beign updated.
> >
> >A unique index prevents the value [or the combination of values if a
 multipart
> >key] from being duplicated in ANY OTHER ROW in the table.
> >
> >In addition, a unique index can be used during a query to get a quicker
> >response; a constraint does nothing at query.
> >
> >Final big difference - if a unique key is defined, then you are guaranteed
 that
> >there are no duplicates. A constraint is only applied at insert/update
 time; it
> >is not applied to the table retroactively....if I create a unique index, if
 a
> >violation occurs, I get an error and no index is created. I can create a
> >constraint on a table; unless I insert or update each row [so the
 constraint is
> >evaluated for each row], a row can have values that violate the constraint.
> >
> >Hope these explain the difference
> >
> >Dan Hekimian-Williams

although a unique constraint can be implemented with a non-unique index...

HTH

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk

We are born naked, wet and hungry...then things get worse