Can you share more about this? Wouldn't you run into the same problems if you used a surrogate pk? Without the nat/external pk and fk, you run the risk of having validity issues.
Conversely, if the ID changes, isn't the friction what you want?
I feel like optimising for unlikely edge cases instead of integrity because of a single incident is too reactionary.
Writing a query or script to update a string, even for millions of rows, isn't that big of a deal?
Obviously there _are_ cases where nat pks/fks are bad, but not all of them.
Instead of migrating one column, now you have that and every table with a foreign key to the original. Requiring transactions and possibly locking etc.
I’d used an external int id that was syncing down into our system. There were then a bunch of other tables referencing it as a foreign key.
Overnight it turned into a sting. Clearly I was going to have some reconciliation work to do in any case and it may be that I maintained the old id as the primary key at that point (can’t recall).
It was just a good lesson in mitigating the blast radius - especially wrt things that are out of your control.
As an analogy, imagine that you’re dealing with dates in the same system (we were), you wouldn’t let their date format bleed into your other tables.
You draw a line at the boundary between the systems. In a way, natural keys break that rule.
Conversely, if the ID changes, isn't the friction what you want?
I feel like optimising for unlikely edge cases instead of integrity because of a single incident is too reactionary.
Writing a query or script to update a string, even for millions of rows, isn't that big of a deal?
Obviously there _are_ cases where nat pks/fks are bad, but not all of them.