Error "Violation of PRIMARY KEY constraint. Cannot insert duplicate key"

After completing a Selection, and even though you might have previewed it to validate your results, when you run, you might get the following error:

Violation of PRIMARY KEY constraint. Cannot insert duplicate key.

This error can occur when you are using either Append or Update Data Action. It happens when the records you are trying to store inside your Target Data Extension contain the same primary key value more than once.

Read the following table to understand, based on the Data Action you are using, what is the cause, and how you can solve it:

	APPEND	UPDATE
CAUSES
The generated records contain at least one record with a Primary Key value that already exists inside your Target Data Extension.
The generated records contain at least two records with the same primary key value.
SOLUTIONS
Use Update Data Action (might not always work)
Use Not In Results filter to exclude all existing primary keys (might not always work)
Use Prio Deduplication (most  recommended)

How to test this?

If you try to run Preview or you use the Overwrite Data Action, it will work because SFMC automatically removes the duplicates of Primary Key and keeps only one of them.

In order to test this, you can follow these steps:

1. Duplicate the Selection you are working on - then start working on the copied Selection

2. Create an In Filter and  include only the Primary Keys indicated on the error message 

3. Create an identical Target Data Extension without any Primary Key

4. Use the Sort & Limit feature to order by the Primary Key field

5.  Now you can run Preview on the Selection and have a view on the duplicates

Note:

While running Preview, DESelect Segment stores the results in an empty temporary Data Extension, instead of the actual Target Data Extension you selected. This is the reason why in some cases you can only see this error when running your Selection, but not when you are previewing it.

Related articles:

- Can I add or update data instead of overwriting an existing Target Data Extension?

- How can I deduplicate the results of my selection?