Re: Unique index vs. unique constraint

From: Laurenz Albe <invite_at_spam.to.invalid>
Date: 02 May 2007 08:54:23 GMT
Message-ID: <1178096062.45950@proxy.dienste.wien.at>

Neron <bl.nero_at_gmail.com> wrote:
> I was trying to find out about differences between these two and I've
> come across information that Oracle deprecated creating unique index
> in favor of unique constraints. My problem is that there seems to be
> no way to define an UNIQUE constraint basing on function-based index
> (such as UPPER(some_column)) and the only way is to define an unique
> index. Does this deprecation mean that in future there will be no way
> to have a case-insensitive uniqueness of a column without using
> triggers and helper columns, or am I missing something?
>
> I'm using Oracle 9.2

As far as I know, the recommendation to use a unique constraint rather than a unique index is only because then you will see the constraint in the table definition.

Apart from that, a unique index and a unique constraint are the same.

You can safely use a unique index when a unique constraint cannot be defined for syntactical reasons.

See
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/clauses002.htm#i1034458

Yours,
Laurenz Albe Received on Wed May 02 2007 - 03:54:23 CDT