Drop Constraint

If you drop a unique or primary key constraint the index that supports it may be dropped at the same time – but this doesn’t always happen. Someone asked me recently if it was possible to tell whether or not an index would be dropped without having to find out the hard way by dropping the constraint. The answer is yes – after all, Oracle has to make a decision somehow, so if we can find out how it makes the decision we can predict the decision.

So here’s my best theory so far – along with the observations that led to it. First, run a trace while dropping a primary key constraint and see if this gives you any clues; on an instance running 10gR2 I noticed the following statement appearing in the trace file immediately after the delete from cdef$ (constraint definitions).

select
        o.owner#, i.property, o.name
from
        obj$ o,
        ind$ i
where
        i.obj# = o.obj#
and     o.obj#=:1
;

Given this clue, the obvious next step is to look in sql.bsq (or dcore.bsq if you’re running 11g) to see what it says about column ind$.property, and we find the following comment:

/* The index was created by a constraint : 0x1000 */

This looks promising, so let’s try a few experiments to see what happens in a few cases of creating indexes and constraints – here’s a starting sample:

create table t1 (n1 number);
alter table t1 add constraint t1_pk primary key (n1);

create table t1 (n1 number);
create unique index t1_pk on t1(n1);
alter table t1 add constraint t1_pk primary key (n1);

In the first case (which generated the index automatically) ind$.property held the value 4097; in the second case, where I created the index explicitly, it held the value 1. (For the less mathematically inclined, 0×1000 = decimal 4096). So it would appear that if we want to know if an index would be dropped as the constraint was dropped, we need only check to see if bitand(ind$.property,4096) = 4096. If the predicate evaluates to true the index will be dropped. (But see comment 3 and the reply below)

The exact SQL for the check is left as an exercise to the reader – you will probably want to join ind$ to obj$ to user$.

Footnote: this particular bit of the property column doesn’t seem to be exposed in any of the data dictionary views; but if anyone knows of any other way to see it then I’d be interested to hear about it, especially if you don’t have to have access to the sys schema to see it.