WH2349 ORA SQLCD -1: HOW TO TROUBLESHOOT A UNIQUE CONSTRAINT VIOLATION

Resolving The Problem

A unique constraint violation occurs when an UPDATE or INSERT statement attempts to insert a record with a key that already exists in the table. Take a look at the package that is throwing the error.

There are a few things that can cause this, including:

1) If the document(shipper, ASN, work order, etc.) number is defined outside of Yantra, the document may have been entered into the system already. If so, then simply go on to the next one. If not, you may have an existing record with the same number because of a failure to purge the old record.

2) One part of the key involves uses a sequence. A function is attempting to add this sequence, but the value already exists in the table.



HOW TO TROUBLESHOOT

1) Find the table that this violation is coming from.

You can find this out by looking at the error message in the DCS_Alert.log file. If a database procedure is referenced (PK61212242.PR61212242, for example), then look at the body of the procedure to find which table(s) might be the culprit.


2) Find out if there are any purges associated with the table.

a) If there is a purge associated, refer to the documentation to ensure that it is set up correctly.

b) If the purge is set up, but the record is not being removed, make sure the record meets purging criteria. You should have a document entitled "Purge codes with where clauses.pdf". This document will show you the where clause that is used by the purge, and should help you figure out why it is not purging.


3) If there is no purge, or the purge is set up correctly, look at the keys of the table to see which field is most likely the problem.

For example, if the key is WHSE, SEQ_NO, you know WHSE is not going to change, so the problem is with SEQ_NO.


4) Does the problem field contain a value that is defined outside of Yantra?

Yes) Check the pre-existing record. Due to timing or network issues, it may be possible that the record is being processed twice. If so, have the user back out of the function and go on to the next item.

No) If the value is defined by Yantra, then it usually follows a sequence.

Sequences are implemented in two ways in Yantra:

1) Warehouse Parameters
2) Oracle Sequences.


Please refer to solution 15066 to see how Warehouse Parameters are used.

Please refer to solution 16351 to see how Oracle Sequences are used.