Marketing Cloud: ERROR: Violation of PRIMARY KEY constraint. Cannot insert duplicate key

Question

I created this query and now that I put it in an automation, but I keep getting the error:

ERROR: Violation of PRIMARY KEY constraint. Cannot insert duplicate key.

MY SQL:

SELECT DISTINCT  J.JourneyID,
                 J.VersionID,
                 J.JourneyName,
                 J.VersionNumber,
                 J.LastPublishedDate,
                 J.ModifiedDate,
                 J.CreatedDate, 
                 J.JourneyStatus, 
                 JA.ActivityID,
                 JA.ActivityName,
                 JA.ActivityExternalKey,
                 JA.JourneyActivityObjectID,
                 JA.ActivityType
FROM _Journey J
INNER JOIN _JourneyActivity JA ON JA.VersionID = J.VersionID
WHERE CreatedDate BETWEEN '2022-01-01' AND DATEADD(day, -1, GETDATE())

Answer 1

Because a journey may have multiple journey versions and multiple journey activities, when you set the JourneyID field as a Primary Key field (force the value in this field to be unique), this may conflict with your SQL result.

My suggestion would be:

1. Remove the primary key field on the JourneyID field
2. Use the query type "Overwrite" and then try again