Why in dba_constarints index_name is null for some primary constraints

Oracle 9iR2

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

When you create a primary constraint automatically 1 row will be inserted into DBA_CONSTRAINTS and another will be into DBA_INDEXES. And in DBA_CONSTRAINTS index_owner and index_name columns will be populated with index which is pointed to primary key.

When u try to disable that constraint, then index_owner and index_name will become null in dba_constraints and row will be deleted from dba_indexes.

When u enable back the constraint, then again index_owner and index_name will become populated in dba_constraints and row will be inserted into dba_indexes.

NOTE:-when u disable and enable it back your user defined index name will be lost and it will be replaced with primary key name.(u can check this in below example).

SQL> CREATE TABLE TEST_INDEX(NUM NUMBER,NUM1  NUMBER);

Table created.

SQL> CREATE UNIQUE INDEX TEST_INDEX_IDX ON TEST_INDEX(NUM);

Index created.

SQL> SELECT index_name,table_name FROM DBA_INDEXES WHERE TABLE_NAME='TEST_INDEX';

INDEX_NAME                     TABLE_NAME

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

TEST_INDEX_IDX                 TEST_INDEX

SQL> ALTER TABLE TEST_INDEX ADD CONSTRAINT TEST_INDEX_PK PRIMARY KEY (NUM);

Table altered.

SQL> select table_name,constraint_name,index_name from dba_constraints where TABLE_NAME='TEST_INDEX';

TABLE_NAME                     CONSTRAINT_NAME                INDEX_NAME

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

TEST_INDEX                     TEST_INDEX_PK                  TEST_INDEX_IDX

SQL> ALTER TABLE TEST_INDEX DISABLE CONSTRAINT TEST_INDEX_PK;

Table altered.

SQL> SELECT index_name,table_name FROM DBA_INDEXES WHERE TABLE_NAME='TEST_INDEX';

no rows selected

SQL> select table_name,constraint_name,index_name from dba_constraints where TABLE_NAME='TEST_INDEX';

TABLE_NAME                     CONSTRAINT_NAME                INDEX_NAME

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

TEST_INDEX                     TEST_INDEX_PK

SQL> ALTER TABLE TEST_INDEX ENABLE CONSTRAINT TEST_INDEX_PK;

Table altered.

SQL> SELECT index_name,table_name FROM DBA_INDEXES WHERE TABLE_NAME='TEST_INDEX';

INDEX_NAME                     TABLE_NAME

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

TEST_INDEX_PK                  TEST_INDEX

SQL> select table_name,constraint_name,index_name from dba_constraints where TABLE_NAME='TEST_INDEX';

TABLE_NAME                     CONSTRAINT_NAME                INDEX_NAME

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

TEST_INDEX                     TEST_INDEX_PK                  TEST_INDEX_PK

SQL>

Oracle 11g

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

When u try to disable that constraint, then index_owner and index_name will become null in dba_constraints and row will not be deleted from dba_indexes.

To resolve this: neither u have to drop the index manually nor disable constraint with cascade option.

SQL> ALTER TABLE TEST_INDEX DISABLE CONSTRAINT TEST_INDEX_PK cascade;