RE: Unique Index or Constraint??

From: Khedr, Waleed <Waleed.Khedr_at_FMR.COM>
Date: Fri, 01 Jun 2001 10:42:50 -0700
Message-ID: <F001.00317E8F.20010601083119@fatcity.com>

Now you can enforce unique constraint using non-unique index.

Regards,

Waleed

-----Original Message-----
Sent: Friday, June 01, 2001 10:46 AM
To: Multiple recipients of list ORACLE-L

Vishak,

Unique constraints by default leads to creation of a unique index by Oracle. This index is used for imposing uniqueness. If you have a unique index in mind then constraint is the way to go.

An explicit constraint allows you to use Oracle mechanisms to trap exceptions in another table while ENABLEing a previously disabled constraint or trap errors in your code.
Optimizer can make use of constraint declarations for query rewrite.
If you are 8i you can define a unique constraint in DISABLEd VALIDATE mode and have Oracle skip the automatic creation of Index. If you are a datawarehouse environment you can still have the optimizer make use of the constraint for query rewite even if the index is not there.
Constraint declaration gives you lots of flexibility on when to kick in the check. For OLTP environment you can defer constraint checks depending on your transaction's needs (in 8i).

There is gobs of information in the SQL ref manual on syntax and the options I have mentioned above.

HTH
- Sundeep

PS: I used to work for Wipro in 1987. I am wondering if the likes of Ashok Soota and Manoj Chug are still part of the company. You can send me email at my personal email address.
Thanks

Vishak <vishak.holla_at_wipro.com> wrote:
> Hello Gurus,
>
> I would like to know whether creating an
> unique-index on a column or a
> unique constraint on a column(during creation of the
> table) would be fine.
>
> Which option would be better?
>
> TIA
> Regards
> Vishak
>
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Vishak
> INET: vishak.holla_at_wipro.com
>
> 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).

Sundeep Maini
Consultant
Currently on Assignement at Marshfield Clinic WI mainis_at_mfldclin.edu

Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: sundeep maini
  INET: sundeep_maini_at_yahoo.com

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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Khedr, Waleed
  INET: Waleed.Khedr_at_FMR.COM

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).

Received on Fri Jun 01 2001 - 12:42:50 CDT