Re: Which Index With This Constraint?

When you define a primary/unique key constraint, you get a unique index for free. Best to give the constraint a name and storage parameters for the index(otherwise its name is SYS_Cxxxxx and the storage parameters come from the default tablespace; not usually what you want).

In Oracle8, you can define an index and then specify a constraint to use it. This way, you can disable the constraint without losing the index.

In Oracle7.x (and 8 if you don't manually specify an index for the constraint), when you disable a constraint, the index is dropped (but the constraint remains and is viewable in dba_constraints). This is normal behavior (for Oracle, anyway).

You can download my get_index utility from my web site below to see all indexes for a given table, and whether it stems from a p/u key constraint. My rev_eng package will reverse-engineer the DDL for constraints and indexes for one or all tables in a schema (among other useful things).

--
- Dan Clamage
http://www.telerama.com/~dclamage
If you haven't crashed the Server,
you haven't been trying hard enough.
Murray Kaiser wrote in message <912728854.930654_at_f1.andara.com>...
>Konstantin Kivi <konst_at_sirena.rinet.ru> wrote:
>
>>... <user_at_nowhere.net> wrote:
>>> How do I discover which index a given constraint uses?
>
>>> I defined indexes on specific tables and columns, then defined
constraints
>>> which apply to the same columns. I think Oracle may manage to
understand
>>> and use the existing indexes.
>
>>> How can I verify that? Can't find anything in dba_constraints,
>>> dba_indexes.
>
>>> Thanks for any info!
>
>>> A. Gordon
>>> aog_at_capgroup.com
>
>
>>I don't know the answer to your question (but I think can figure it out)
>>but you may name your constraint explicitly and in this case
>>the name of the index will be the same as the constraint
>>name. You can also make use of STORAGE and USING INDEX TABLESPASE
>>to control parameters of the index.
>
>
>>
>
>>--
>>Sincerely Yours, Konstantin Kivi, Russia, konst_at_sirena.rinet.ru
>> aka <k-kivi_at_usa.net>, 2:5020/457.24_at_fidonet.org
>>
>
>>
>Konstantin is correct about the storage parameter and using index
>clauses on the add constraint clause (Very valuable).
>
>But to answer your question, Oracle is smart enough to know to use
>existing indexes to enforce constraints. You will not be able to drop
>the unique index until you disable the primary key constraint.
>
>I tried this with a simple one column table. I created a unique index
>on the table and then added an unnamed primary key constraint.
>
>Then I tried to drop the index. The error I got was
>
>drop index test_col1
>*
>ERROR at line 1:
>ORA-02429: cannot drop index used for enforcement of unique/primary
>key
>
>BUT, it gets even more interesting. I did a
>
>alter table test disable constraint sys_C0010208;
>
>Table altered.
>
>and attempted to drop the index but it was ALREADY dropped by the
>disable clause above.
>
>mmmm
>
>
>
>
Received on Fri Dec 04 1998 - 21:56:46 CST