Violation of PRIMARY KEY Constraint - When Source And Destination Keys Differ

Check out the solution in the video (ETL) Violation of PRIMARY KEY constraint 'PK_Constraint' - Different Source and Destination Keys. While it's rare to see, from time to time, I do see a design where the source and destination keys differ. There may be situations where this is a valid design - though if the source has different keys than the destination, we may want to consider eliminating the key altogether for the source since the key will switch when it hits the destination. This doesn't mean we should, as there still may be legitimate cases where this is the correct design.

One example of an exception here may be if the source table is part of our data warehouse, but the destination table is part of a report. I've seen this type of design use different keys legitimately because the report was only reporting some of the information, not the full amount of information. In the video, I break down an example of this and intentionally generate errors.