4 ms·

Yeesh. I once made the mistake of using an external ID as a primary key. What a day it was when they were changed on me.

by aidos 2y ago

spoiler 2y ago
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.
- mixmastamyk 2y ago
  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.
- aidos 2y ago
  Little bit hazy because it was 15 years ago. 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.
naasking 2y ago
> I once made the mistake of using an external ID as a primary key. What a day it was when they were changed on me. I've kept with this advice for the most part, but I'm tempted in some cases to use the external id when there's some guarantee of stability and universality. Like 2 and 3 digit ISO country codes.
- Piskvorrr 2y ago
  Not that I'd get about 5 different ISO country code changes (with some flipflopping) just by sitting in this very same spot for a couple of decades. "Stability" in country codes, bah humbug.
  - naasking 2y ago
    Your geographical location's sorting into a country might not be stable, but I'm referring to the ISO codes that name the country, which should be relatively stable.
    - Piskvorrr 2y ago
      ISO 3166-2:XK - self-assigned, in use, but I wouldn't go as far as calling it "stable." I mean, it's been in an explicitly declared unstable state ("about to be assigned an official code") for over a decade.

Yeesh. I once made the mistake of using an external ID as a primary key. What a day it was when they were changed on me.

by aidos 2y ago

Yeesh. I once made the mistake of using an external ID as a primary key. What a day it was when they were changed on me.

spoiler 2y ago

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.

mixmastamyk 2y ago
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.
aidos 2y ago
Little bit hazy because it was 15 years ago. 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.

naasking 2y ago

> I once made the mistake of using an external ID as a primary key. What a day it was when they were changed on me. I've kept with this advice for the most part, but I'm tempted in some cases to use the external id when there's some guarantee of stability and universality. Like 2 and 3 digit ISO country codes.

Piskvorrr 2y ago
Not that I'd get about 5 different ISO country code changes (with some flipflopping) just by sitting in this very same spot for a couple of decades. "Stability" in country codes, bah humbug.
- naasking 2y ago
  Your geographical location's sorting into a country might not be stable, but I'm referring to the ISO codes that name the country, which should be relatively stable.
  - Piskvorrr 2y ago
    ISO 3166-2:XK - self-assigned, in use, but I wouldn't go as far as calling it "stable." I mean, it's been in an explicitly declared unstable state ("about to be assigned an official code") for over a decade.