Re: relationship between unique index and constraint

From: Arup Nanda <orarup_at_hotmail.com>
Date: Fri, 18 Apr 2003 11:36:42 -0800
Message-ID: <F001.00585091.20030418113642@fatcity.com>

David,

Here is the lowdown on the issue:

  a.. When you create a unique CONSTRAINT, Oracle creates a unique INDEX to enforce it.   b.. When you create a unique INDEX, a constraint is NOT automatically created, but the rdbms engine reports the same message as if the constraint name was violated.

So what happens when you create a unique INDEX first and then add a unique CONSTRAINT of the same name later?

SQL> create unique index in_trans_01 on trans (col1);

Index created.

SQL> select index_name from user_indexes where table_name = 'TRANS';

INDEX_NAME

---

IN_TRANS_01 SQL> alter table trans add constraint uk_trans_01 unique (col1);

Table altered.

SQL> select index_name from user_indexes where table_name = 'TRANS';

INDEX_NAME

---

IN_TRANS_01 Surprise,surprise! We added a new constraint, but how come there is not a unique index to enforce it? Quite simple. Oracle simply uses the existing unique index IN_TRANS_01 to enforce the constraint  

SQL> select constraint_name from user_constraints where table_name = 'TRANS';

CONSTRAINT_NAME

---

UK_TRANS_01 Now let's drop the constraint:

SQL> alter table trans drop constraint uk_trans_01;

Table altered.

SQL> select index_name from user_indexes where table_name = 'TRANS';

no rows selected

Well, what happened to the index IN_TRANS_01? We didn't drop that. But since Oracle used that index to enforce the unique constraint, it was dropped to un-enforce the constraint. This is expected feature; although it come as a surprise.

In Oracle 9i, though, you have a new clause KEEP INDEX that will keep the index while dropping the constraint.

HTH. Arup Nanda
www.proligence.com

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Friday, April 18, 2003 1:26 PM

> 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?
>
> thanks,
>
> David Ehresmann
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Ehresmann, David
> INET: David.Ehresmann_at_ps.net
>
> 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).
>
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Arup Nanda
  INET: orarup_at_hotmail.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).

Received on Fri Apr 18 2003 - 14:36:42 CDT