Finding if an index is created with constraint, or created before constraint

Oracle database needs an index to enforce primary key constraint on a table. If there is no index existing already on primary column(s), it creates a unique index while creating constraint. If there is an index existing already (any of unique and non-unique, but not bitmap), it uses the existing index to enforce the constraint. 

Here is an example that shows 2 tables, where a table t1 uses existing unique index to enforce primary key, but for the other table t2 a unique index is created while adding constraint.

SQL> create table t1 (a number, b number);

Table created.

SQL> create unique index pk_t1 on t1(a);
 

Index created.

SQL> alter table t1 add constraint pk_t1 primary key (a); -- Uses existing index pk_t1

Table altered. 

SQL> select index_name from user_indexes where table_name='T1';

INDEX_NAME

------------------------------

PK_T1

SQL> create table t2 (a number, b number);

Table created.

SQL> alter table t2 add constraint pk_t2 primary key (a); -- Creates a new index pk_t2

Table altered.

SQL> select index_name from user_indexes where table_name='T2';

INDEX_NAME

------------------------------

PK_T2

SQL> alter table t1 disable constraint pk_t1; -- Disabling constraint will not drop index here

Table altered.

SQL> select index_name from user_indexes where table_name='T1';

INDEX_NAME

------------------------------

PK_T1

SQL> alter table t2 disable constraint pk_t2; -- Disabling constraint will drop index here

Table altered.

SQL> select index_name from user_indexes where table_name='T2';

no rows selected

SQL> alter table t1 enable constraint pk_t1;

Table altered.

SQL> select index_name from user_indexes where table_name='T1';

INDEX_NAME

------------------------------

PK_T1

SQL> alter table t2 enable constraint pk_t2; --Enabling constraint will create index again

Table altered.

SQL> select index_name from user_indexes where table_name='T2';

INDEX_NAME

------------------------------

PK_T2

None of the indexes can be dropped as they are being used to enforce constraint

SQL> drop index pk_t1;

drop index pk_t1

*

ERROR at line 1:

ORA-02429: cannot drop index used for enforcement of unique/primary key

SQL> drop index pk_t2;

drop index pk_t2

*

ERROR at line 1:

ORA-02429: cannot drop index used for enforcement of unique/primary key

If we want to control the behaviour of whether index should be dropped or not, we can use the clause "keep index" or "drop index" while disabling primary key.

alter table t1 disable constraint pk_t1 drop index;

alter table t2 disable constraint pk_t2 keep index;

But the default behaviour of whether the index has to dropped or kept, depends on if the index was created during creating/enabling of constraint or before. The question is here is, how to know if the index was created with constraint or if that was there before constraint (and used to enforce constraint). I could not find a data dictionary view available to find this information (searched in 10.2 and 11.2). So, tried tracing of "disable constraint" statement on a table, and found what dictionary tables it uses to find this information. Here is the answer I got. And execution these queries need SELECT ANY DICTIONARY privilege.
 

SQL> select i.property from sys.obj$ o, sys.ind$ i where o.name='PK_T1' and i.obj# = o.obj#;

PROPERTY

----------

     1

SQL> select i.property from sys.obj$ o, sys.ind$ i where o.name='PK_T2' and i.obj# = o.obj#;

PROPERTY

----------

4097

So, the information about the index if was created with constraint or not, is stored in ind$ table, in the column property. It uses 13th bit as flag. If that is 1, then the index is created with constraint and it will be dropped when the constraint is dropped or disabled. Below query decodes the flag and gives the required information.

 SQL> select decode(bitand(i.property,4096),

                   4096, 'Created with constraint',

                   'Created before constraint')

from sys.obj$ o, sys.ind$ i

where o.name='PK_T2' and i.obj# = o.obj#;

DECODE(BITAND(I.PROPERTY,40

---------------------------

Created with constraint

SQL> select decode(bitand(i.property,4096),

                   4096, 'Created with constraint',

                   'Created before constraint')

from sys.obj$ o, sys.ind$ i

where o.name='PK_T1' and i.obj# = o.obj#;

DECODE(BITAND(I.PROPERTY,40

---------------------------

Created before constraint

The above queries are also applicable for unique index created with unique key constraint.