Skip Ribbon Commands
Skip to main content
Navigate Up
Sign In

Quick Launch

Average Rating:

facebook Twitter
Email
Print Bookmark Alert me when this article is updated

Feedback

Complex Mapping Fails with "duplicate key error" when Target table has Compound / Composite Primary Key with Violation of PRIMARY KEY constraint
Problem Description
When using Azure SQL DB or MS SQL DB, a mapping fails because of the violation of a PRIMARY KEY constraint.

Logs have the following error displayed:

[...] Violation of PRIMARY KEY constraint '<PK_Name>'. Cannot insert duplicate key in object '<Object_Name>'. The duplicate key value is (<arg1>,<arg2>,...,<argN>). [...] The statement has been terminated. 

Cause
There is more than one reference to the Target table in the mapping, e.g.: Lookups (unconnected or not).

There's one or more another reference in another block on the mapping to the same table, e.g.: lookup.

This can be detected checking the WHERE clause for the target in the logs, the WHERE clause will not be updated after applying the KB above.

This is a metadata CACHING Problem.
Solution
To resolve this issue, do the following:
  1. Review the mapping and remove all blocks that reference the target table;
  2. Build the target using the composite PK in the WHERE clause;
  3. Add again the other blocks removed at steps 1.
More Information
​​
UPDATE <Target Table> SET <Field1> = ?, <Field2> = ?, <Field3> = ?, <Field4> = ?, <Field5> = ? WHERE <Unupdated Composite PK> = ?
Applies To
Product: Cloud Data Integration
Problem Type: Product Feature
User Type: Architect; Data Analyst; Data Scientist; Data Steward; Developer
Project Phase: Configure; Implement; Onboard; Optimize
Product Version:
Database:
Operating System:
Other Software:

Reference
Attachments
Last Modified Date:9/14/2020 8:54 PMID:627262
People who viewed this also viewed

Feedback

Did this KB document help you?



What can we do to improve this information (2000 or fewer characters)