Oracle Index – Does Enabling a Primary Key Rebuild Its Index?

constraintindexoracle

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?

Best Answer

It's easily tested.

There are two possible scenarios.

Scenario 1:

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

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

Table created.

SQL>

Add the index to it:

SQL> create unique index IDX_PK_MY_TABLE on MY_TABLE (pk);

Index created.

SQL>

Add the constraint:

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

Table altered.

SQL> select constraint_name from user_constraints;

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

SQL>
SQL>
SQL> select index_name
  2  from user_indexes;

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

Insert some data:

SQL> insert into MY_TABLE values(1);

1 row created.

SQL> insert into MY_TABLE values(2);

1 row created.

SQL>

Test the constraint:

SQL> 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>

Disable the constraint:

SQL>
SQL> alter table MY_TABLE disable constraint PK_MY_TABLE;

Table altered.

SQL>

Try the INSERT again:

SQL> 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> create table MY_TABLE
  2  (
  3  pk number,
  4  constraint PK_MY_TABLE PRIMARY KEY (pk)
  5  );

Table created.

SQL> select index_name from user_indexes;

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

SQL> select constraint_name from user_constraints;

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

SQL>

Disable the constraint:

SQL> alter table MY_TABLE disable constraint PK_MY_TABLE;

Table altered.

SQL> select constraint_name, status from user_constraints;

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

SQL>

The index has gone!:

SQL>  select index_name from user_indexes;

no rows selected

SQL>

Re-enabling it recreates the index:

SQL> alter table MY_TABLE enable constraint PK_MY_TABLE;

Table altered.

SQL> select index_name from user_indexes;

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

SQL>

If you want to keep the index:

SQL> alter table MY_TABLE disable constraint PK_MY_TABLE  keep index;

Table altered.

SQL> select index_name from user_indexes;

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

SQL>

Related Solutions

Function based index as a conditional unique key

I'm not completely sure that I understand what you mean by "every FK_ID should be unique for that value of OBJ_TYPE".

If you are trying to say that each FK_ID has to be unique within each OBJ_TYPE, then it sounds like you just want a composite index on (OBJ_TYPE, FK_ID).

CREATE UNIQUE INDEX idx_uniq_metadata
   ON metadata( obj_type, fk_id );

If, on the other hand, you are trying to say that if OBJ_TYPE = 1 then FK_ID must be unique but you can have duplicate FK_ID values if the OBJ_TYPE is something else, then you'd want a function-based unique index.

CREATE UNIQUE INDEX idx_uniq_metadata2
   ON metadata( (case when obj_type = 1 
                      then fk_id 
                      else null 
                   end) );

Change a partitioned global non-unique index that is the primary key to a non-partitioned unique index

You can do this with DBMS_REDEFINITION, but you'll need double the space occupied by the table.

If I read your question correctly, this should do it:

[oracle@node1 ~]$ sqlplus phil/phil

SQL*Plus: Release 11.2.0.2.0 Production on Sun Jan 6 18:47:12 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set echo on
SQL> @part
SQL>
SQL> drop table PARTITIONED_INTERIM;

Table dropped.

SQL>
SQL> drop table partitioned;

Table dropped.

SQL>
SQL> create table partitioned
  2  (
  3  pk number,
  4  blah  varchar(40),
  5  blah2 varchar(40)
  6  )
  7  partition by range (pk)
  8  (partition p1 values less than (1000),
  9   partition p2 values less than (10000),
 10   partition p3 values less than (30000),
 11   partition p4 values less than (MAXVALUE)
 12   );

Table created.

SQL>
SQL> create index pk_idx
  2  on partitioned (pk)
  3  global partition by range (pk)
  4  (partition p1 values less than (1000),
  5   partition p2 values less than (10000),
  6   partition p3 values less than (30000),
  7   partition p4 values less than (MAXVALUE)
  8   )
  9  ;

Index created.

SQL>
SQL> ALTER TABLE partitioned ADD CONSTRAINT ppk PRIMARY KEY (pk) USING INDEX pk_idx;

Table altered.

SQL>
SQL> select index_name, uniqueness , table_name, partitioned
  2  from user_indexes
  3  where table_name = 'PARTITIONED';

INDEX_NAME                     UNIQUENES TABLE_NAME                     PAR
------------------------------ --------- ------------------------------ ---
PK_IDX                         NONUNIQUE PARTITIONED                    YES

SQL>
SQL> EXEC dbms_redefinition.can_redef_table('PHIL', 'PARTITIONED');

PL/SQL procedure successfully completed.

SQL>
SQL> create table PARTITIONED_INTERIM as ( select * from PARTITIONED where 1=0 );

Table created.

SQL>
SQL> exec DBMS_REDEFINITION.start_redef_table(uname =>     'PHIL',orig_table => 'PARTITIONED',int_table => 'PARTITIONED_INTERIM');

PL/SQL procedure successfully completed.

SQL>
SQL> create unique index partitioned_interim_pk on partitioned_interim (pk) global online;

Index created.

SQL>
SQL> exec dbms_redefinition.sync_interim_table(uname => 'PHIL',orig_table => 'PARTITIONED',int_table => 'PARTITIONED_INTERIM');

PL/SQL procedure successfully completed.

SQL>
SQL> exec dbms_redefinition.finish_redef_table(uname => 'PHIL',orig_table => 'PARTITIONED',int_table => 'PARTITIONED_INTERIM');

PL/SQL procedure successfully completed.

SQL>
SQL> select index_name, uniqueness , table_name, partitioned
  2  from user_indexes
  3  where table_name = 'PARTITIONED';

INDEX_NAME                     UNIQUENES TABLE_NAME                     PAR
------------------------------ --------- ------------------------------ ---
PARTITIONED_INTERIM_PK         UNIQUE    PARTITIONED                    NO

SQL>
SQL>
SQL>

Note that you'll have to deal with other indexes/objects etc too, using DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS() and DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT().

Best Answer

Related Solutions

Function based index as a conditional unique key

Change a partitioned global non-unique index that is the primary key to a non-partitioned unique index

Related Question