RE: relationship between unique index and constraint

My understanding is that Oracle uses a unique index in order to enforce any unique constraint or primary key. If you create a unique constraint you'll get a unique index. If you create a unique index you won't get a unique constraint labelled as such in the constraints view, but it's not so surprising that you'd get the same error message upon attempted violation.

I just checked the Oracle (8.1.7) Concepts manual and in fact they recommend that you create the constraint rather than directly creating the index. This is what we do and conceptually it is somewhat cleaner.

> -----Original Message-----
> From: Ehresmann, David [mailto:David.Ehresmann_at_ps.net]
> Sent: Friday, April 18, 2003 1:27 PM
> To: Multiple recipients of list ORACLE-L
> Subject: relationship between unique index and constraint
>
>
> List,
>
> If I create a unique index does Oracle create the unique constraint?
>
> Example:
>
> sql>create unique index name
> on dept(dname);
>
> index created.
>
> sql>select constraint_name from user_constraints;
>
> no rows selected.
>
> sql>insert into dept(deptno, dname, loc)
> values(99,'SALES', 'DALLAS');
>
> ORA-00001: unique constraint (djehres.dname) violated.
>
> sql>select constraint_name from user_constraints;
>
> no rows selected.
>
>
> Question: If Oracle does not create a unique constraint (
> does not show up
> in user_constraints) when you create a unique index how do
> you end up with
> a UNIQUE CONSTRAINT VIOLATED error?
>

LEGAL NOTICE:
Unless expressly stated otherwise, this message is confidential and may be privileged. It is intended for the addressee(s) only. Access to this e-mail by anyone else is unauthorized. If you are not an addressee, any disclosure or copying of the contents or any action taken (or not taken) in reliance on it is unauthorized and may be unlawful. If you are not an addressee, please inform the sender immediately.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Sarnowski, Chris
  INET: csarnows_at_CuraGen.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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).