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

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

October 15, 2019
Oracle DBA
oracle dba oracle database management database management system database oracle performance tuning oracle sql learn sql learn personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

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;

oracle dba oracle database management system database management ORACLE ALTER TABLE ORACLE ALTER TABLE in ORACLE ENABLE CONSTRAINT ENABLE CONSTRAINT in ORACLE ENABLE CONSTRAINT ORACLE sql database learn sql learn learn retirement financial advisor retirement financial

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)
oracle dba oracle database management system database management ORACLE ALTER TABLE ORACLE ALTER TABLE in ORACLE ENABLE CONSTRAINT ENABLE CONSTRAINT in ORACLE ENABLE CONSTRAINT ORACLE sql database learn sql learn learn retirement financial advisor retirement financial

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';

oracle dba oracle database management system database management ORACLE ALTER TABLE ORACLE ALTER TABLE in ORACLE ENABLE CONSTRAINT ENABLE CONSTRAINT in ORACLE ENABLE CONSTRAINT ORACLE sql database learn sql learn learn retirement financial advisor retirement financial

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 ;

oracle dba oracle database management system database management ORACLE ALTER TABLE ORACLE ALTER TABLE in ORACLE ENABLE CONSTRAINT ENABLE CONSTRAINT in ORACLE ENABLE CONSTRAINT ORACLE sql database learn sql learn learn retirement financial advisor retirement financial

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;

oracle dba oracle database management system database management ORACLE ALTER TABLE ORACLE ALTER TABLE in ORACLE ENABLE CONSTRAINT ENABLE CONSTRAINT in ORACLE ENABLE CONSTRAINT ORACLE sql database learn sql learn learn retirement financial advisor retirement financial

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

Leave a Reply

Your email address will not be published. Required fields are marked *

*