Support Article
Unique constraint violation on Extract to table with primary key
SA-29864
Summary
On the second run of a Business Intelligence Exchange (BIX) extract to a database table target unique constraint violation errors are observed for records, which were extracted in the first run.
Error Messages
Batch Failed: pzInskey of the record that was not inserted : ORG-APP-CLASS-WORK WO-101
2016-10-19 13:59:30,266 [ PegaRULES-Batch-4] [ STANDARD] [ RULESET:01.01.01] ( internal.access.ExtractImpl) INFO Bob@your_company - # Instances Retrieved till this batch: 60 -- 89.55% Complete - Last Id Processed in this Batch Execution: ORG-APP-CLASS-WORK WO-101
2016-10-19 13:59:30,334 [ PegaRULES-Batch-4] [ STANDARD] [ RULESET:01.01.01] ( internal.access.ExtractImpl) ERROR Bob@your_company - Severe SQL Exception: ORA-00001: unique constraint TABLE_PEGA.SYS_C005277141) violated
java.sql.BatchUpdateException: ORA-00001: unique constraint (TABLE_PEGA.SYS_C005277141) violated
Steps to Reproduce
- Create a table for BIX extracts.
- Make pzInsKey the primary key on the table with a standard primary key constraint.
- Run BIX manually two times in a row to extract records.
Root Cause
Extracts targeted at a database table perform INSERT operations, not UPDATE operations.
Resolution
Alter the extract to not include a unique constraint, or clear the target table between extract runs.
Published January 20, 2017 - Updated December 2, 2021
Have a question? Get answers now.
Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.