Loading

Primary Key Constraint Error returned through a SQL Query

Veröffentlichungsdatum: Oct 13, 2022
Beschreibung
When gathering data through a SQL Query and targeting a Data Extension which contains a Primary Key, certain action types (Append, Update, Overwrite) and data scenarios can lead to a Primary Key Constraint error.
Lösung

Append

Using an 'Append' action is rarely the best option if a target Data Extension contains a Primary Key. In order for this action to run successfully, you must ensure that your data will never contain duplicates. This action instructs the SQL Query to gather data based on your syntax, then add all records to the end of your data extension, without regard for what Primary Key values existed in the Data Extension prior to running. If an instance of a Primary Key value exists in your target Data Extension prior to SQL Query execution, and the SQL Query gathers an identical value for the Primary Key, then  Primary Key Constraint error will be returned upon insert. 

If duplicate Primary Key values are gathered in a single SQL Query running, and the target Data Extension does not contain a match for this Primary Key value. Then upon insert, the first record will be added, and the second would result in a Primary Key Constraint error. This is reiterated in the 'Update' section of this article.
 

Update

The 'Update' action can fit certain use cases when a Primary Key is present on the target Data extension. This action requires that a Primary Key be present on the target Data Extension so that a matching value can be searched on. If a matching value for an existing Primary Key value is found in the data set which your SQL Query has just gathered, then the SQL Query will update any other fields for the matched record. 

It is still possible to encounter a Primary Key Constraint error with an 'Update' action. This scenario would need to happen:
  • SQL Query runs to gather data based on configuration and two records gathered have a 'SubscriberKey' value of '123'. 
  • The target Data Extension has a Primary Key set on 'SubscriberKey' and prior to the SQL Query running, no matches for '123' are present.
  • The SQL Query will search for a match on 'SubscriberKey' = '123' and find no matches. The first instance of '123' will be added.
  • The SQL Query then handles the second instance of '123' and searches for a matching 'SubscribeKey' value. It finds the first instance which was just added. 
  • The SQL Query will throw a Primary Key Constraint error.
If an 'Update' action is used, it is important to ensure that unique values for your Primary Key will be gathered in each running.


Overwrite

The 'Overwrite' action does not check for a Primary Key when adding records. This action will clear the target Data Extension and then insert all records it gathered to the cleared Data Extension. 

If there are duplicates for a primary key. The query will deduplicated on the Primary Key field, and then insert the data as if it were updating. This will only leave one row for each duplicate primary key.
  • SQL Query clears Target Data Extension
  • SQL Query runs to gather data based on configuration and two records gathered have a 'SubscriberKey' value of '123'
  • The target Data Extension has a Primary Key set on 'SubscriberKey'
  • The SQL Query will insert the first instance of '123' will be added
  • The SQL Query then handles the second instance of '123' and searches for a matching 'SubscribeKey' value. It finds the first instance which was just added
  • The SQL Query will overwrite the first instance
Nummer des Knowledge-Artikels

000384160

 
Laden
Salesforce Help | Article