Re: Unique Indexes VS. Constraints

[Quoted] 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
Received on Tue Oct 03 2000 - 14:55:37 CEST