Re: Row in USER_INDEXES table not deleted when constraint is dropped

On Fri, 06 Oct 2006 17:08:33 -0700, DA Morgan <damorgan_at_psoug.org> wrote:

>capnwhit_at_yahoo.com wrote:
>> If I create a unique constraint (using "alter table x add constraint
>> ..."), then Oracle will create a row in the USER_CONSTRAINTS table and
>> another row in the USER_INDEXES table. If I drop the constraint (using
>> "alter table x drop constraint ...") then Oracle deletes both rows most
>> of the time... but sometimes the row in the USER_INDEXES table is not
>> dropped. Can anybody tell me what may be happening? Running 10g,
>> 10.1.0.2.0.
>
>Can you create a demo we can try?
>
>My guess is because the index existed before the constraint was created
>but I'd like to try to duplicate what you are seeing.

Agree, certainly for a PK. I was covering the same issue with one of our SDEs today: if you create a PK constraint with the USING INDEX clause, but the index does not exist then Oracle will create he appropriate index. In this case, if you drop the constraint, the index goes as well. However, if the index already exists, it wil be used for enforcement of the primary key, but will not be droppped if the constraint is dropped.

My guess is there is a link between DBA_DEPENDCIES, DBA_INDEXES and DBA_CONSTRAINTS, but I won't be able to check till next week. I'll followup if I find something substantive, unless someone beats me to it. Received on Fri Oct 06 2006 - 23:50:19 CDT