How To ENABLE CONSTRAINT with Error ORA-14063 In Oracle SQL

This help is based on examples so it would be easier to understand. Managing Integrity Constraints needs sometimes a temporary constraints enable and disable. In most cases to enable the constraint should not be a problem but after data import or update to tables with big amount of data causes errors like ORA-14063: Unusable index exists on unique/primary constraint key. The following chapter goes through the easiest way to maintain indexes and to be able enabling the constraints again.

The syntax of Enable Constraint is:

 ALTER TABLE <your_table> ENABLE CONSTRAINT <your_table_constraint>;

The example below based on table my_table that has primary key constraint my_table_pk. During the table maintaining we had to disable the constraint and to enable it we are using the following command:

 ALTER TABLE my_table ENABLE CONSTRAINT my_table_pk;

The output we do expect is the above one but the execution could end with the error below. (ORA-14063: Unusable index exists on unique/primary constraint key)

This error is caused by unstable indexes and the quick fix would be to rebuild all indexes belong to current table. The Oracle SQL below returns a list that can be execute directly and in your case you should only change the table name (“MY_TABLE“).

SELECT distinct 'ALTER INDEX '||t.INDEX_NAME||' REBUILD ;'
  FROM ALL_IND_COLUMNS t
 WHERE t.TABLE_NAME = 'MY_TABLE';

The second option would be to write the ALTER INDEX REBUILD statements manually and execute them. The alter index syntax is following:

ALTER INDEX <your_table_index_name> REBUILD ;

This help continues with statements taken from the earlier Oracle SQL and it has two indexes we are going to rebuild. Maybe not all indexes would not need to be rebuilt but to play safe we will do it with all of them.

ALTER INDEX MY_TABLE_PK REBUILD ;
ALTER INDEX MY_TABLE_NAME_I REBUILD ;

Now has left to try primary key constraint enabling as we did in the first statement. The output should be following.

 ALTER TABLE my_table ENABLE CONSTRAINT my_table_pk;

In case, the error raises again. There are left to try 2 options: 1st try the REBUILD statements again or for 2nd DROP index and RE-CREATE from scratch.

See Also:
Oracle Select Oracle Substr Oracle Instr Home