Oracle: Does enabling a PK rebuild its index? Scenario:


Table MY_TABLE has primary key constraint PK_MY_TABLE
Table MY_TABLE has also an index called IDX_PK_MY_TABLE which is enforces the uniqueness for constraint PK_MY_TABLE.


If I disable constraint PK_MY_TABLE and then enabled it back, does index IDX_PK_MY_TABLE get rebuilt?
It's easily tested.

There are two possible scenarios. 

Scenario 1:

Scenario 1 is creation of a PK using an existing index:

SQL&gt; create table MY_TABLE
  2  (
  3  pk number
  4  );

Table created.

SQL&gt;


Add the index to it:

SQL&gt; create unique index IDX_PK_MY_TABLE on MY_TABLE (pk);

Index created.

SQL&gt;


Add the constraint: 

SQL&gt; alter table MY_TABLE add constraint PK_MY_TABLE primary key (pk) USING INDE
X  IDX_PK_MY_TABLE;

Table altered.

SQL&gt; select constraint_name from user_constraints;

CONSTRAINT_NAME
------------------------------
PK_MY_TABLE

SQL&gt;
SQL&gt;
SQL&gt; select index_name
  2  from user_indexes;

INDEX_NAME
------------------------------
IDX_PK_MY_TABLE


Insert some data:

SQL&gt; insert into MY_TABLE values(1);

1 row created.

SQL&gt; insert into MY_TABLE values(2);

1 row created.

SQL&gt;


Test the constraint: 

SQL&gt; insert into MY_TABLE values(2);
insert into MY_TABLE values(2)
*
ERROR at line 1:
ORA-00001: unique constraint (PHIL.PK_MY_TABLE) violated

SQL&gt;


Disable the constraint:

SQL&gt;
SQL&gt; alter table MY_TABLE disable constraint PK_MY_TABLE;

Table altered.

SQL&gt; 


Try the INSERT again:

SQL&gt; insert into MY_TABLE values(2);
insert into MY_TABLE values(2)
*
ERROR at line 1:
ORA-00001: unique constraint (PHIL.IDX_PK_MY_TABLE) violated


It succeeds because the unique index hasn't been disabled/dropped when the constraint was disabled. Note that the original error was     ORA-00001: unique constraint (PHIL.PK_MY_TABLE) violated, whereas the index constraint PHIL.IDX_PK_MY_TABLE is violated once the PK constraint is disabled.

Scenario 2:

Scenario 2 is creation of the PK upon table creation:

SQL&gt; create table MY_TABLE
  2  (
  3  pk number,
  4  constraint PK_MY_TABLE PRIMARY KEY (pk)
  5  );

Table created.

SQL&gt; select index_name from user_indexes;

INDEX_NAME
------------------------------
PK_MY_TABLE

SQL&gt; select constraint_name from user_constraints;

CONSTRAINT_NAME
------------------------------
PK_MY_TABLE

SQL&gt; 


Disable the constraint:

SQL&gt; alter table MY_TABLE disable constraint PK_MY_TABLE;

Table altered.

SQL&gt; select constraint_name, status from user_constraints;

CONSTRAINT_NAME                STATUS
------------------------------ --------
PK_MY_TABLE                    DISABLED

SQL&gt;


The index has gone!:

SQL&gt;  select index_name from user_indexes;

no rows selected

SQL&gt;


Re-enabling it recreates the index:

SQL&gt; alter table MY_TABLE enable constraint PK_MY_TABLE;

Table altered.

SQL&gt; select index_name from user_indexes;

INDEX_NAME
------------------------------
PK_MY_TABLE

SQL&gt;


If you want to keep the index:

SQL&gt; alter table MY_TABLE disable constraint PK_MY_TABLE  keep index;

Table altered.

SQL&gt; select index_name from user_indexes;

INDEX_NAME
------------------------------
PK_MY_TABLE

SQL&gt;