SQL query Error Violation of PRIMARY KEY

Question

I run this SQL Query Activity

SELECT
a.Id,
a.Active__c,
a.Age__c,
a.Email,
a.FirstName,
a.Salutation,
a.PreferredLanguage__c,
a.HasOptedOutOfEmail,


FROM ent.Contact_Salesforce_43 a 
LEFT JOIN  ent.Channel__c_Salesforce_11 b ON a.Id=b.Contact__c 
LEFT JOIN  ent.Member__c_Salesforce_1 c ON a.Id=c.MainContact__c 
LEFT JOIN  ent.Brand__c_Salesforce_12 d ON a.Id=d.PrimaryContact__c

Target DE: Main_DE, Id is the Primary Key and Id relates to Subscribers on Subscriber Key.

in the Automation Studio and continue to have this error:

Query failed during execution. Error: Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object 'Main_DE'. The duplicate key value is (xxxxxxxxxxxxxxx).

Violation of PRIMARY KEY constraint. Cannot insert duplicate key.

saying that there is a duplicate key value for one record only even though:

• I cleared all the data from the target Date Extension,
• There are several other records in the Select DE,
• See only one contact with this ID in the All Contacts list,
• Have exactly the same activity running in another Business Unit correctly.

The SQL Query itself was validated correctly. I tried a range of solutions posted online but the error continues to be there. Is it possible that this is some technical problem on the Marketing Cloud end?

Answer 1

Likely the issue is coming from your left joins. If there are multiple possible joins across those tables, then it can create duplicate records since these will be recognized as different records. This article gives a good background on how/why.

To get around this, it might be a good idea to utilize ROW_NUMBER() to number the partitions you set where you can then grab just the records with a number of 1 (to remove duplicates).

Something like:

SELECT
x.Id,
x.Active__c,
x.Age__c,
x.Email,
x.FirstName,
x.Salutation,
x.PreferredLanguage__c,
x.HasOptedOutOfEmail
FROM (
    SELECT
    a.Id,
    a.Active__c,
    a.Age__c,
    a.Email,
    a.FirstName,
    a.Salutation,
    a.PreferredLanguage__c,
    a.HasOptedOutOfEmail,
    ROW_NUMBER() OVER(PARTITION BY a.ID ORDER BY a.ID) as rn
    FROM ent.Contact_Salesforce_43 a 
    LEFT JOIN  ent.Channel__c_Salesforce_11 b ON a.Id=b.Contact__c 
    LEFT JOIN  ent.Member__c_Salesforce_1 c ON a.Id=c.MainContact__c 
    LEFT JOIN  ent.Brand__c_Salesforce_12 d ON a.Id=d.PrimaryContact__c
) x
WHERE x.rn = 1

There are other solutions, including GROUP BY, Reconfiguring relationships/foreign keys, reconfiguring data structure of your DE, etc. I usually recommend ROW_NUMBER() due to simplicity.

One caveat with it is that if any of the duplicates have different data in same fields, then you could be potentially missing some relational data.