The Impact of Changing the Primary Key
But life again crashes in on us. A superstar moves to another team and, to his chagrin, discovers that a good-glove, no-hit utility infielder has his favorite number. Agents are quickly brought in, and the poor hanger-on decides to take one for his team, and relinquish his number.
What impact does this have on our design? Well, obviously we have to update the utility infielder's primary key. But is that all?
Here's where the impact becomes serious.
The Player table will have an index built on the PK (some products do this automatically), and your RDMBS will have to change that index at the same time by doing a delete and an insert. Free? Not if you're watching the hourglass and have other, more productive things to do.
Let's repeat our question: Is that all?
Again, sadly, the answer is no. Remember that we implement relationships by carrying the PK of the parent table as foreign data (FK) in the child. And we almost always build indexes on the FK column. So, not only do we have to find the PlayerHistory rows associated with the utility infielder and update the value of the FK column, but our RDMBS must also change the FK index.
Okay, is that all?
Yes, with respect to FKs, but how many times does this have to be done simply to make this one uniform number change? Remember that we use the PK to implement all relationships that the table is in. A three-table system is not realistic, except maybe for the Minnesota Twins; in your real-life design, the master tables are in many relationships. The impact of a change is multiplied by this number.
We're starting to have concerns about the hourglass icon being permanently etched into the screen...
Let's assume that we have a flat screen so the icon-burn is not a problem, and we really don't have anything productive to do for a while. The question now becomes: For how long are we willing to wait? Are there any other performance impacts?
Again, the answer is yes. To understand this problem, we must look at the structure of an index.