3 ms·
Learned that the hard way recently, when a client changed ERP system and suddenly order numbers went from being 6 digit integers to 20 chars. I had made their
by magicalhippo 5y ago
Learned that the hard way recently, when a client changed ERP system and suddenly order numbers went from being 6 digit integers to 20 chars.
I had made their order number the primary key for their integration tables, as I was much more inexperienced at the time and had read so much about using natural keys.
Learned my lesson since, and this project just confirmed it again: natural keys are rarely a good idea.
Wasn't the worst issue ever to convert given that an ALTER TABLE fixes it, but had to drop all the FK's first, just quite tedious going over the code checking where it made assumptions about it being an integer.
- Akronymus 5y agoI personally almost always use surrogate keys, because of having been burned by natural ones once too many times as well. Altough, sometimes I end up having a unique constraint on a natural column as well, and then the temptation is there to make that the key. But to keep it consistent I pretty much always end up using a surrogate key anyways. (I think I used natural keys like 2 times in a few years) Unrelated: The blogs mascot is cute.
- hashimotonomora 5y agoIn my experience it’s best to work with a surrogate PK and generate a bijective customer-facing natural key.
- Akronymus 5y agoI work mainly with CRUD, and usually people want to search for specific customers, names or similar. While not necessarily bijective on its own (multiple customers, same name, for example), it narrows it down enough for the person to find the record. (Or filtering on multiple fields) So, I don't really see a need to generate a natural key.
- Cthulhu_ 5y agoI'm glad to have gotten this confirmed again; in school you learn to use natural keys, because e.g. someone's social security number or e-mail address is unique, right? I'm currently rebuilding a system where a user-provided 'name' property is used as a primary key (no foreign keys though). I can't change its mechanics much - the name will be used as an ID in a different application as well - but I can add an artificial ID for internal use. Who knows, maybe long-term I can support changing the name property.
- samus 5y agoNatural keys are perfectly fine. You just have to realize that you don't have full control over their sematics. But you absolutely require that to be able to construct the foreign key links in your datbase schema. Therefore, by all means feel free to introduce and save any natural keys that the application requires. But do not rely on them for foreign keys.
- tuatoru 5y ago> in school you learn to use natural keys, because e.g. someone's social security number or e-mail address is unique, right? Not sure if this is ironic, but a couple of subtleties about uniqueness aren't always conveyed in school. (Not very subtle subtleties given a few minutes' thought, but still...) It's not enough to have single-point-in-time uniqueness, i.e all entities of the type under discussion have unique values of the candiate key now, which is what a uniqueness constraint guarantees. It is also necessary that the value of the candidate key for each entity also does not change for the expected life of the database (or 250 years, whichever is longer--yes, the life of the database, not merely of the entity concerned), so no using people's names, for instance; and as the population of entities changes, values that disappear cannot later reappear (like SSNs of dead people being re-used for a new person). If you don't control key allocation, it's only rarely possible to have ironclad, law-of-physics guarantees about these aspects of keyness.