Applies to Pega Platform versions 8.5.1 through 8.6.2

Prerequisite

For additional information, see also the more recently published document, Update fails with ORA-01451, ORA-02429, and ORA-02443 [SDR-301].

Symptoms

The issue described in this document can occur in all update scenarios for Pega Platform™, Pega 8.5.1 through 8.6.2. 

DDL generated for an update to Pega 8.5.1 and later releases throws an error during installation.

The following ALTER statement causes the error:

ALTER TABLE PC_ASSIGN_WORKBASKET DROP CONSTRAINT GETNEXTWORKINWORKGROUP

/

The DROP CONSTRAINT statement causes this error:

ERROR at line 1: ORA-02443: Cannot drop constraint - nonexistent constraint

The error caused by the DROP CONSTRAINT statement indicates that the constraint does not exist. Upon investigation, the user discovered that GETNEXTWORKINWORKGROUP is a functional index and not a constraint. Therefore, the user tried using the DROP INDEX statement but received the following error:

ERROR at line 1:

ORA-02429: cannot drop index used for enforcement of unique/primary key

ORA-06512: at line 5

Errors

A series of errors occur as shown in the following examples:

ALTER TABLE PC_ASSIGN_WORKBASKET DROP CONSTRAINT GETNEXTWORKINWORKGROUP

/

ERROR at line 1: ORA-02443: Cannot drop constraint - nonexistent constraint

ERROR at line 1:

ORA-02429: cannot drop index used for enforcement of unique/primary key

ORA-06512: at line 5

Explanation

Under certain circumstances the Oracle database enforces the primary key constraint for a table by using an index that is not the primary key index. This is an optimization that Oracle makes to reduce the number of unnecessary indices. However, this optimization confuses the Pega update scripts and causes the update to fail with the following error:

ALTER TABLE PC_ASSIGN_WORKBASKET DROP CONSTRAINT GETNEXTWORKINWORKGROUP

ERROR at line 1: ORA-02443: Cannot drop constraint - nonexistent constraint

When this happens, Oracle enforces the Primary Key (PK) constraint using the

GETNEXTWORKINWORKGROUP index, which is different from the index specified by the Pega Platform when it is shipped.

Solution

Complete the following steps for your Oracle database before you attempt to run the installation for a Pega Platform update:

1. Drop the primary key constraint on the table.
2. Drop the index that was used to optimize.
3. Add the primary key constraint back.
4. Add the index back.

Related Content

Pega Documentation

Troubleshooting failed database connection after update or patch (Oracle database)

Troubleshooting failed system startup after update or patch (Oracle database)

Optimizing database properties

Optimizing a schema

Pega Support Document

Update fails with ORA-01451, ORA-02429, and ORA-02443 [SDR-301]