Re: Unique Indexes VS. Constraints

>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 [Quoted] [Quoted] [a function of that column] or [a function of other columns in that same row]. [Quoted] For example, a constraint will limit a column to be 0 or 1; or be uppercase [Quoted] only, or between 2 other values in that row. The most important aspect is that [Quoted] it can be compared only with columns in the row beign updated.

[Quoted] A unique index prevents the value [or the combination of values if a multipart [Quoted] key] from being duplicated in ANY OTHER ROW in the table.

[Quoted] 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 [Quoted] [Quoted] there are no duplicates. A constraint is only applied at insert/update time; it [Quoted] [Quoted] is not applied to the table retroactively....if I create a unique index, if a [Quoted] violation occurs, I get an error and no index is created. I can create a [Quoted] constraint on a table; unless I insert or update each row [so the constraint is [Quoted] evaluated for each row], a row can have values that violate the constraint.

[Quoted] Hope these explain the difference

Dan Hekimian-Williams Received on Tue Oct 03 2000 - 02:24:54 CEST