RE: relationship between unique index and constraint

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Fri, 18 Apr 2003 13:06:55 -0800
Message-ID: <F001.00585170.20030418130655@fatcity.com>

One more detail:

"But since Oracle used that index to enforce the unique constraint, it was dropped to un-enforce the constraint."

However if the enforcing index for the PK or unique constraint is not a unique index, it will not be dropped along with the PK or unique constraint.

-----Original Message-----

Here is the lowdown on the issue:

When you create a unique CONSTRAINT, Oracle creates a unique INDEX to enforce it.
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.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jacques Kilchoer
  INET: Jacques.Kilchoer_at_quest.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 - 16:06:55 CDT