Violation of Primary Key (SQL Server)

Whether poor design or an invalid transaction happening, we may see these error in either of these situations. For the solution, review the information in the video (ETL) Violation of PRIMARY KEY constraint 'PK_Constraint' - Different Source and Destination Keys. One pattern I've seen quite often is developers changing primary keys or sometimes swapping primary keys from source to destination in ETL contexts. While there may be some legitimate contexts to doing this, more than likely this is a poorly thought out design if we don't carefully wrap our transactions. In other words, a design that has differing primary keys will be a design that needs more work built around the logic from source to destination.

In the context of ETL, some questions and considerations in the video are addressed such as:

• What to do when primary keys in source and destinations differ
• Review the video and think about where this design would make sense along what other logic would need to be added
• A note about changing the primary key (also see T-SQL: Changing A Primary Key - What We Should Consider) along with reflection about whether this situation calls for this or wrapped design in the logic
• Considering data flows in our data pipelines and how we want to consider primary keys in ETL

An hour of thoughtful planning can be worth a full work week. Planning isn't sitting in a meeting and discussing things. Planning is considering what you will do, what the effects are along with what the limitations of what you're capable of doing. If changes need to made to the primary key, then you should plan this change and recognize the effects that it will have. Likewise, if you design a system where a source of a data pipeline will involve a different primary key than the destination of those data, we should review how that destination data source will be used. As an example, if we allow other data (inserts) from other sources of data, then we must be extremely careful as this may cause a primary key conflict.

One regular point I like to remind my audience, especially as of recent, which fits into this context: we can often solve the same problem with a variety of techniques. While we look at one or two ways to solve the problem, these aren't the only ways we can approach this problem. The most appropriate solution to a problem is one in which you can troubleshoot quickly in the future and one in which you understand. Be careful about applying solutions that solve a problem, but introduce new problems in the future. In addition, be careful about using designs or solutions that introduce problems that you are not aware of - these take much longer to correct than a design where you can quickly solve the problem.

A great re-occuring example of this is my discussion on alerting best practices. There are a variety of alerts everywhere for everything in today's development environments, but many of these alerts cause more issues than they solve (one of which is creating a distraction). Be careful about "easy" solutions which introduce bigger problems. Know your environment and where to apply the best solution. Of course, all these being written, it is helpful to learn new techniques to solve problems because they may be useful in the future.