Violation of PRIMARY KEY constraint 'FXXXX_PK'. Cannot insert duplicate key in object 'XXXXX'.

[former_member309357]

Hello experts,

    We have a PROXY-JDBC (3rd party's SQL database ) scenario in SAP PI 7.31. When proxy is got triggered from SAP. For some messages getting the error :

    Violation of PRIMARY KEY constraint 'FXXXX_PK'. Cannot insert duplicate key in object 'XXXXX'.

    The primary key maintained in table is MESSAGE GUID. In PI this field is mapped to currentDate (ddMMyyyyHHmmssSSS).

    This error is getting only for some messages. Rest of all are processing successfully. This issue is happening in production and its a bit urgent.

    Experts, kindly give your inputs on this. Thanks a lot in advance.

Thanks

Ravi Kiran.

[iaki_vila · Accepted solution]

.Hi Ravi,

Try to use the sentence UPDATE_INSERT instead of Insert.

If you are already using the UPDATE_INSERT sentence

Check SAP Note 831162, the question 19. Using UPDATE_INSERT with MS SQL Server:java.sql.SQLException Can not insert duplicate key.

Regards.

[ambrish_mishra]

Hi Ravi,

I suspect this is happening because 2 or more messages are going with same value ddMMyyyyHHmmssSSS since you would (I assume) be populating this field in mapping and multiple statements would be getting created in mapping.

If the records in question are to be treated as separate records,then you will have to change the logic and add counter (using index function for example) to populate this field else UPDATE_INSERT would  work.

Hope it helps!

Ambrish