Re: Unique Constraint vs Unique Index

In article <01beb003$0b1a0e40$8909eec3_at_default>,   "GDN" <gert.deneve_at_advalvas.be> wrote:
> When you take a look at the SQL-reference for Oracle 7, you see that
the
> "create unique index... " statements doesn't exist any more. You can
> consider it as a hidden feature.
> So, you cannot be sure that it will work in future version of Oracle.
>
> GDN
>
> Lyx <lynux_at_netvigator.com> wrote in article
> <7jbrrg$5fo$1_at_imsp009a.netvigator.com>...
> > I am a newbie and determining on which methods (unique constraint or
> > unique index) would be better for a Data WH design.
> > Should there be any difference between a unique constraint or purely
> > unique index applied except for enabling/disabling constraint?
> > Also, what will be the internal engine done if a constraint is
applied?
> >
> > Thanks!
> >
>

Hi. I'm responsible for this one.

In 7.x I tried to remove the concept of a unique index, so there would only be unique constraints. That was a bad idea. For one, indexes keep getting more options, so it's better to have just one syntax for them (the CREATE INDEX syntax). For another, as of 8.0 unique constraints can reuse existing unique indexes whose columns are in a different order. That means a statement that would have created an index in version x won't in version y because version y knows how to reuse more existing indexes. Relying on unique constraints to create unique indexes as a side effect is a bad idea. CREATE UNIQUE INDEX is and will be supported. The manuals, classes, and so forth have been updated.

Why have unique constraints then? Foreign keys can only refer to primary key or unique constraints. Also, deferred unique constraints can wait to the end of the txn to guarantee uniqueness. Unique constraints make it harder to drop unique indexes, which is sometimes a good thing.

• Bob Jenkins

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Mon Jun 07 1999 - 20:13:27 CDT