Re: Question abount Unique Keys

Hi,

I think it's a different approach.
If you create a constraint, you actually tell Oracle that you want to set up some kind of data integrity. A unique key constraint in your case. Since Oracle has to do something in order to achieve this, it goes ahead and creates a Unique Index.
You can refer to this constraint in order to built referential integrity (foreign keys). Nevertheless, you can just build the unique index, let's say to speed up some queries and making sure to only retrieve one row per condition on the unique index without havin g to use distinct (AFAIK, distinct forces Oracle to do a full tablescan first, and then get rid of the redundant entries in the resultset to return).

Basically, just a different approach ....

-- 
Regards,
Stefan Jahnke
@:D2 Vodafone

Jared Still schrieb:


> 

> Without diving into the docs, the differences I can

> think of are that a unique index is just that, an index

> that will not allow duplicate values on the key, as long

> as none of the columns are nullable.

> 

> A unique constraint is a different beastie.  It can be referred

> to by a foreign key, and just as a PK requires an index to

> help enforce it effeciently, so does a unique constraint.

> 

> Jared

> 

> On Tuesday 10 April 2001 20:53, Helmut Daiminger wrote:

> > Hi!

> >

> > I have a question about unique keys:

> >

> > When I issue the statement

> > CREATE UNIQUE INDEX UK_TBDOCUMENT ON TBDOCUMENT(DOCUMENTID)

> > Oracle creates a unique index (as in user_indexes) but no unique constraint

> > (as in user_constraints)

> >

> > When I issue that statement in a create table statement

> > create table....

> >     CONSTRAINT UK_TBDOCUMENT

> >         UNIQUE (DOCUMENTID)

> > Oracle creates a unique constraint plus an index.

> >

> > Why is Oracle doing this? Could somebody please shed some light on this?

> >

> > This is 8.1.6 on Win2k.

> >

> > Thanks,

> > Helmut

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stefan Jahnke
  INET: stefan.jahnke_at_d2vodafone.de

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).