Case Study: ORA-00001 (Unique Constraint Violated) using JDB_UpdateTable
Purpose of Document
This document explains the possible cause for error ORA-00001 in an Update statement.
Business scenario: you need to audit sales order change by recording it into F42420 (Audit Log Transaction). When you activate Workflow JDESOCHNG to notify approver to Approve/Reject changes, then the approver will need updating status to indicate order change has approved through P98805 (Generic Workflow Approval Forms).
Cause: An UPDATE or INSERT statement attempted to insert a duplicate key. For Trusted Oracle configured in DBMS MAC mode, you may see this message if a duplicate entry exists at a different level. Action: Either remove the unique restriction or do not insert the key.
Example through query tool:
Example: Table F42420 and Data stored based on Primary Key:
KCOO** (Order Co)
DOCO** (Order Number)
DCTO** (Or Ty)
LNID** (Line Number)
CORD** (Chg Ord)
UPMJ * (Date Updated)
TDAY* (Time of Day)
USER* (User ID)
RFRV (Revision Reason)
APSR (Approval Status)
APPV (Approver)
APPJ (Approval Date)
ATIM (Time Approved)
...
Row 1
00001
1234
SO
1.000
1
19-10-2012
84646
JDE
CUS
0
...
Row 2
00001
1234
SO
1.000
1
19-10-2012
84711
JDE
CUS
P
0
...
Notes:
Columns which belong to both primary key and foreign key are described as **
Data Sorted based on Foreign Key by WHERE ( ALKCOO = '00001' AND ALDOCO = 1234.000000 AND ALDCTO = 'SO' AND ALLNID = 1000.000000 AND ALCORD = 1.000000 )
KCOO (Order Co)
DOCO (Order Number)
DCTO (Or Ty)
LNID (Line Number)
CORD (Chg Ord)
UPMJ (Date Updated)
TDAY (Time of Day)
...
Row 2
00001
1234
SO
1.000
1
19-10-2012
84711
...
Row 1
00001
1234
SO
1.000
1
19-10-2012
84646
...
Error in Log:
Entering JDB_UpdateTable (Table F42420) (hRequest 081611F8) UPDATE PRODDTA.F42420 SET ALKCOO='00001',ALDOCO=1234.000000,ALDCTO='SO',ALLNID=1000.000000,ALCORD=1.000000,ALUPMJ=112293,ALTDAY=70604.000000,ALUSER='JDE', ALRFRV='CUS',ALAPSR='A',ALAPPV='AMISHK',ALAPPJ=112293, , * WHERE ( ALKCOO = '00001' AND ALDOCO = 1234.000000 AND ALDCTO = 'SO' AND ALLNID = 1000.000000 AND ALCORD = 1.000000 )
OCI0000178- Unable to execute - UPDATE PRODDTA.F42420 SET ALKCOO=:BND1,ALDOCO=:BND2,ALDCTO=:BND3,ALLNID=:BND4,ALCORD=:BND5,ALUPMJ=:BND6,ALTDAY=:BND7,ALUSER=:BND8, ALRFRV=:BND9,ALAPSR=:BND10, ALAPPV=:BND11,ALAPPJ=:BND12,* WHERE ( ALKCOO = :KEY1 AND ALDOCO = :KEY2 AND ALDCTO = :KEY3 AND ALLNID = :KEY4 AND ALCORD = :KEY5 ) OCI0000179 - Error - ORA-00001: unique constraint (PRODDTA.F42420_PK) violated JDB9900401 - Failed to execute db request JDB3600011 - Failed to perform Update for F42420 Exiting JDB_UpdateTable with Failure (Table F42420)
Notes:
The JDEDEBUG traced above is coming from the Workflow Kernel log because the Workflow kernel is calling a Business Function to update table F42420
Since the eerror is coming from the database level (ODBC, OCI) level, the same information repeats in JDE.LOG. It is important to review the JDE.LOG when the result is different from what is expected
For this example, the error is inevitable because WHERE test result two rows which meets this condition so if E1 updates as indicated, the above two rows can have the same primary key
Issue same query through query tool (Ex: Oracle SQL Developer etc.)
If error ORA-00001 is issued then
Get Data from table indicated based on WHERE clause (for this example, WHERE ( ALKCOO = '00001' AND ALDOCO = 1234.000000 AND ALDCTO = 'SO' AND ALLNID = 1000.000000 AND ALCORD = 1.000000 )
Verify whether the result of update can be unique for primary key (primary index)
For this example, issue is coming from F42420.CORD column which is copied in copying existing order. Correct example should be:
KCOO (Order Co)
DOCO (Order Number)
DCTO (Or Ty)
LNID (Line Number)
CORD (Chg Ord)
UPMJ (Date Updated)
TDAY (Time of Day)
...
Row 2
00001
1234
SO
1.000
1
19-10-2012
84711
...
Row 1
00001
1234
SO
1.000
0
19-10-2012
84646
...
From the above data image:
CORD = 0 indicated before image
CORD = 1 indicates after image
To fix this error, apply <Bug 14785958> - P98805 NOT UPDATING F42420 ORDER STATUS. For this example, this error was invoked in copying exiting sales order which in return copied CORD as it is.
Considerations:
Cause and resolution in this document is one example and case study only.
Some other possible causes of this type of message are:
Table specification between JDE EnterpriseOne and database (metadata) are different: to synchronize this generate table through OMW (Object Management Workbench) after backing up target data. This action will: drop table, drop index and create table and create index with constraint.
Constraint information in Database is faulty: do not set any constraint other than Unique Key Constraint because JD Edwards EnterpriseOne does not set constraint explicitly
Corrupted Index: (this is a common cause) generate index through OMW
Error is coming during insert: check primary key for target table and verify that unique key is unique (for example, UKID, UPMJ/TDAY and so on). Case study on this example, can be found at <Document 779434.1> E1: 43: Commit Failed Error in PY after copying Business Data from PD to PY.