0

I need some help with finding an answer to this very common error. There have been many questions raised for "Violation of PRIMARY KEY constraint" and I have tested the recommended solutions. One of the most common solutions was to use a PARTITION BY clause in the query.

Context: The query is used to gather send performance data for sms from a source Data extension and from _SMSMessageTracking. The target data extension has two Primary keys - UserId and MobileMessageTrackingID with the Data Action: Update.

Earlier the query did not have any failure/duplicate proof and we started observing this error intermittently on all Business Units.

We have fixed the error for all Business Units using the PARTITION BY clause in the query as below--

ROW_NUMBER() OVER (PARTITION BY a.[UserId],b.[MobileMessageTrackingID] 
ORDER BY b.[CreateDateTime] ASC) AS [RN]
  FROM [SourceDE] a
  JOIN [_SMSMessageTracking] b

However, after a week of this fix in all Business Units, I observed this error randomly again for an automation in one of the BU's.

I have seen other suggestions for this error fix as - removing the update action to Overwrite, removing PK, etc., but these suggestions will not work for the account requirements. So, need some more suggestions that can help resolve this. My fixed query looks similar to this (Violation of PRIMARY KEY constraint. Cannot insert duplicate key)

I could not paste the entire Query here but would be pleased to gain any insight into this issue. Thank You!

1
  • Please update your question to include details about the relationship between SourceDE and _SMSMessageTracking Dec 19, 2023 at 14:29

1 Answer 1

0

You'll need to include more than UserId and MobileMessageTrackingID in your primary key as _SMSMessageTracking includes entire conversations related to sends.

Which part of this 1:M interaction with the recipient do you wish to associate with a single SourceDE record?

It'd be simple enough to experiment with the results without a target Data Extension using Query Studio.

You might try something like this (assuming SourceDE.PhoneNumber relates to _SMSMessageTracking.Phone):

select 
  a.userId
, b.Mobile
, b.MobileMessageTrackingId
, b.ConversationId
, row_number() over (partition by a.userid, b.MobileMessageTrackingID, b.ConversationId order by b.CreateDateTime desc) as rn
from SourceDE as a
inner join _SMSMessageTracking as b on b.mobile = a.phoneNumber

Once you have these results, look for the rows that have rn greater than 1.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .