Oracle-L: Re: relationship between unique index and constraint

From: Arup Nanda <orarup_at_hotmail.com>
Date: Fri, 18 Apr 2003 14:06:41 -0800
Message-ID: <F001.00585241.20030418140641@fatcity.com>

RE: relationship between unique index and constraintThat's correct. Thank you, Jacques.

Arup
----- Original Message -----

To: Multiple recipients of list ORACLE-L Sent: Friday, April 18, 2003 5:06 PM

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: 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 - 17:06:41 CDT