Importing data with primary key duplicates

Question

We have a file that's dropped on the FTP regularly to update our master database, which has FEDId as its primary key. On rare occasions, the receiving file can contain a few records with identical primary keys (FEDIDs), because it is possible that a person had registered with 2 email addresses on 2 different occasions, but he/she has the same PrimaryKey (FedID). However, in such cases, the SQL automation that should update the master data extension fails prompting the following message: "Query failed during execution. Error: Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object." Is it possible to edit the SQL or the target data extension so that it includes both records of the same PK with 2 different email addresses (it's a simple SQL with INNER JOIN on PK / FEDId), or alternatively updates the record with 2 email addresses (one-to-many)? Is a composite PK maybe the solution to this case?

Answer 1

If it is a valid case to have one FedID along with multiple email addresses, the correct way would be adapting the data extension so you have a composite primary key (FedID and EmailAddress).

Editing the SQL statement would work if you like to filter one of the records out, however you won't get both records that way.