9 ms·

You think your surrogate key will save you? It will not. The world has an external reality that needs to be reflected in your database. If the unique identifie

You think your surrogate key will save you? It will not. The world has an external reality that needs to be reflected in your database. If the unique identifier for your object — VIN, CUSIP, whatever — if it changes, the world will henceforth refer to it by both. You will need to track both. Adding a synthetic key only means you have to track all three. Plus you have to generate a meaningless number, which is actually a choke point in your data throughput. The natural key forces you to think about what makes the row unique. What identifies it. Sometimes, it makes you go back to the SME and ask them what they mean. Sometimes it makes you reconsider time: it’s unique now, but does it change over time, and does the database need to capture that? In short, what are the boundaries of the Closed World Assumption? You need to know that too, to answer any "not exists" question. To use our professor’s car’s example, we actually do not know the database design. It could well be that the original identifier remained the primary key, and the "new id" is entered as an alias. The ID is unique in the Car table, identifying the vehicle, and is not in the CarAlias table, where the aliases are unique. Oh, you say, but what if the bad old ID gets reused? Good question. Better question: how will the surrogate key protect you? It will not. The reused ID will be used to query the system. Without some distinguishing feature, perhaps date, it will serve up duplicates. The problem has to be handled, and the surrogate key is no defense. Model your data on the real world. Do not depend on spherical horses.

kayodelycaon 2y ago
Of course they won’t save you from external data. The whole point is for your system to have a way to identify rows internally so you can deal with external systems getting wonky without corrupting your own data. All of your concerns are easily solved by a unique index. Using external keys as a forcing function to prevent people from representing data wrong is not great.
crabmusket 2y ago
The surrogate key uniquely identifies a row in your database, which is an entity just as real and significant as the car or the employee or what-have-you. Don't confuse the two! I agree with you that having a surrogate key isn't going to save you from the reasons why natural keys can be difficult. The complexity has to go somewhere. But not having a unique identifier for each row is going to make things extra difficult.
- hobs 2y ago
  The main thing is that the synthetic key should never leave the database and never be displayed in the app - if you want to have another key that represents the human oriented key do it, but it should be another field, an indexed field even, but one that has a lot less monotonic sequential properties that are inherent to synthetic database identifiers. You want to change that human key? Sure. You want to to complain that the keys are not sequential? Sure. You want to actually make them weird strings that are harmful to my brain? Why not? You want to update primary keys in the database? No. Absolutely not.
  - The_Colonel 2y ago
    I assume you hint at the security aspect of monotonic keys? I've found this issue a bit overblown. It's basically security by obscurity, which is a nice bonus, but not something your security model can be based on. I mean, it is a good practice to expose some kind of non-sequential key (e.g. UUIDv7), but it doesn't seem to me like a dealbreaker.
    - crabmusket 2y ago
      I love it when my competitors use sequential integer IDs.
    - throwaway8486k 2y ago
      Security by obscurity makes it more difficult for bad actors as an additional layer that they need to break. I don't see a reason why that's a bad thing and doesn't take a lot of effort to implement in this case. I have been in a startup where competitors used our sequential keys to scrape a list of customers. Lesson learned the hard way with actual business consequences! Sequential keys also leak information (German tank problem) Your competitors can estimate number of customers, your growth rate and other stats that you often don't want them to know. https://en.m.wikipedia.org/wiki/German_tank_problem https://en.m.wikipedia.org/wiki/German_tank_problem
      - The_Colonel 2y ago
polemic 2y ago
In the wise words of Patsy, "it's only a model". The real world is resistent to clean abstractions and abstractions are distressingly subject to change. What made your row unique today is quite likely to become non-unique in the days/months/years to come. Always use surrogate keys. Your future self will thank you.
- hehdhdjehehegwv 2y ago
  And always kids, write code for one person, and one person only: your future self.
  - M95D 2y ago
    Yeah, fsck your co-workers and your replacement when you quit.
    - hehdhdjehehegwv 2y ago
      If you’re good to your future self you’re also good to them.
      - M95D 2y ago
        Invalid argument. Very rarely your future self will work in that company or on the same project. Even if someone plans to keep his job for a long time, writing for his's future self usually means making himself non-replaceable by writing unreadable code.
tadfisher 2y ago
In that case, using this natural datum as an ID is a contradiction, because we are now removing the uniqueness constraint. It's fine to model the real world, but the real world also includes your data, and you may want to identify unique _records_ as it is not a universal truth that data can be corrected on entry.
huygens6363 2y ago
There is a model of a thing and there is a row that stores a representation of that model of a thing. They both are things. Ignoring the last one might be tempting, but it’s not practical. Interestingly your own way of thought is applied, but now a level deeper again. How do you model a row? What makes it unique? A surrogate ID is the only sensible unique identifier for such a thing as there is no “natural key” that would make sense for instances of something so general as “Row”. What you were saying amounts to “don’t model the thing holding the model”, but experience shows the thing holding the model is itself an (often unwilling) active part of systems. Someone here gave the example of wrongly entered PK’s by administrative personnel handing live customers. That’s IMO a good example of why you need an extra layer on top of your Actual Model(c). I can think of more.
- crabmusket 2y ago
  > They both are things. Corollary: your app is part of the real world.
  - TeMPOraL 2y ago
    Furthermore: bugs in your app are part of the real world, so woe to those who used keys from your app as natural/external keys in their app.
jtriangle 2y ago
>Model your data on the real world. Do not depend on spherical horses. This took me years to realize, and once I did things became much, much simpler.
CrimsonRain 2y ago
Sorry. This is a very bad advice. I just had to fight tooth and nail to make my lead turn around from this disastrous decision. Using a lot of external IDs as our own row primary keys and then they get propagated to all other tables as foreign keys and what not. One day the foreign key chances or God forbid, the formatting changes in external systems, now we need to fix our whole database and all codes instead of a small isolated place. Generate your own unique keys for everything; add a few more unique constraints if needed. A bit more work but never a regret.
- 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.
devjab 2y ago
In terms of the Danish CPR that is mentioned here, the way we actually solved the challenge in the national architecture strategy was to define your social security number(s) as what we call an address on your person. I’m not sure why it was called an address, but it’s basically a UUID and the information. Maybe it’s because it was first used to store addresses? Anyway. Unless your system has not yet implemented the national strategies (which by now are approaching 24 years) then changing a Danish Social Security won’t actually matter because it’s just an added address to your person “object”. So basically you’ll now simply have two, and only which is marked as the active. Similar to how you’ve got an array of addresses in which you have lived but only one main address active. It did indeed cause a lot of issues historically because it was used as a natural key… though with it being based on dates, it was never really a good candidate for a key since you couldn’t exactly make it into a number unless you had some way to deal with all the ones beginning with 0 being shorter than the rest. Anyway… it was used as a key, and it was stupid. Anyway I both agree and disagree with you. Because we’ve successfully modelled the real world with virtual keys, but you access it by giving any number of natural keys. Like… you can find the digital “me” by giving a system my CPR number, but you can also find me by giving a system my current address. Technically you could find me by giving a name, but good luck if you’re dealing with a common name. There is a whole range of natural keys you can use to identify a “digital” person, but all of it leads from a natural key into a web of connected virtual keys. All of it is behind some serious gatekeeping security if you’re worried. Or at least it’s supposed to be.
- mrweasel 2y ago
  It's been years since I worked on systems with CPR numbers, but I seem to recall that there is also a policy in place, stating that you are not allowed to use the CPR number as a "primary key". Many companies did anyway, because they never actually bothered to read the guidelines and regulations. All the systems I've seen always had the CPR as a lookup for a UUID, which as you say is the the "address" of the actual person object.
paulddraper 2y ago
Terrible advice. Surrogate keys are keys are a layer of indirection. They don't fix all problems, but they fix some problems. Not least of which is performance. Often natural keys are character strings, whereas surrogate keys can be fixed size integers, saving index sizes on your FKs.
- mypalmike 2y ago
  Conversely, certain queries can be much faster by using natural keys when the FK is all that you need in the result rather than additional fields in the primary table. In this case, the primary table doesn't need to be queried at all. This doesn't generally overcome the benefits of synthetic keys, but it is an optimization sometimes put into practice.
  - crabmusket 2y ago
    Aren't you literally describing an index?
    - mypalmike 2y ago
      No. Maybe an example helps. You have a users table with username as natural PK. You have an access table with timestamps of users hitting a service, with FK of username. If you query the access table for a range of timestamps and just want the usernames, they're right there in the results of the access table query. If you had instead used a synthetic user_id key, the db would have to do an additional lookup in the users table via a join for each user_id to get the usernames.
      - crabmusket 2y ago
        Oh of course, that makes sense. I'm not sure how I misunderstood that!
- mrkeen 2y ago
  > surrogate keys can be fixed size integers This launches into the other debate about PKs: using UUIDs rather than sequential keys.
yashap 2y ago
This is bad advice. Say I have a user table, and the email is unique and required, and we don’t let users update their email, and we don’t have user deletion. If I’m going natural PK, I make email the primary key. But … then we add the ability for users to update their email. But it should still be the same user! This is trivial if we have a surrogate primary key, a nightmare if we made email the natural primary key. Or building on that example, maybe at first we always require an email from our users. But later we also allow phone auth, and you just need an email OR a phone number. And later we add user name auth, SSO, etc. Again, all good with surrogate primary keys, a nightmare with natural primary keys. There are countless examples like this. You brought up cars, same thing with licence plates, for example. Or even Social Security Numbers/Social Insurance Numbers - in Canada SINs are generally permanent, but temporary residents can have their SIN change if they later become permanent residents, but they’re still the same person. You want your entities to have stable identity, even if things you at one time thought gave them identity change. Surrogate primary keys do that, natural primary keys do not. Don’t use natural primary keys, use surrogate primary keys with unique constraints/indexes. I challenge you to come up with a single plausible example where you’re screwing yourself by choosing surrogate PK + unique constraints/indexes. Meanwhile there are endless examples where you’re screwing yourself by choosing natural PK.
- maweki 2y ago
  > I challenge you to come up with a single plausible example So I come from academia, but generally if you use a natural key as PK in a foreign key constraint it may be possible to express additional consistency criteria as CHECK-constraints in the referencing table. So this is a bad example, but say you have Name and Birthdate as your PK, and you have a second table where you have certain special offers sold to your customers and there is this special offer just for Virgos and Pisces, you could enforce that the birth date matches this special offer. Some modern systems also technically allow FK on alternate keys, so you could still do it that way, but database theory often ignores that. But second, while I agree that surrogate keys are often a good idea, I find your argument, that you must design for every conceivable change, not convincing.
  - 2y ago
SPBS 2y ago
> You think your surrogate key will save you? It will not. The world has an external reality that needs to be reflected in your database. Yes, it will. It is precisely because of a messy external reality that you need an unchanging internal ID that is unaffected by changes to the external ID. If the software designer in the article had followed your advice, changing the chassis number would have likely resulted in broken car ownership records. Whoever is reading this in the future, please don't follow the parent comment's advice. Use surrogate/synthetic keys for your primary key.
aswerty 2y ago
Surrogate keys do mirror reality though. As I once read in a Terry Pratchett book; if you replace the handle of an axe and then replace the head, is it still the same axe? For me, the answer is yes - since we imbue the axe with an identity outside of it's integral parts. That is what a surrogate key is. An identity. Which is an abstract concept that exists in the real world. And to pile on. The top comment is bad advice! Surrogate keys provide sanity - god save you if you have to work in a database solely using natural keys.
- vintermann 2y ago
  Yes. Ultimately, "are these the same" and "are these different" are philosophical questions. Or to be more precise, teleological questions. Because those questions have no meaning except with an "for our purposes here" added. And it's up to us to decide what we care about, if anything. If we care about what other people want with the data though, or suspect that our own wants might not be set in stone, then we should also care to model identity independently in the system (that is, use surrogate keys).
- fuzztester 2y ago
  >As I once read in a Terry Pratchett book; if you replace the handle of an axe and then replace the head, is it still the same axe? Yes and no. It is the Axe of Theseus ;) https://en.m.wikipedia.org/wiki/Ship_of_Theseus https://en.m.wikipedia.org/wiki/Ship_of_Theseus
  - aswerty 2y ago
    Thanks - I think somewhere in the back of my mind I was also aware of the Ship. But Pratchett is too good. The quote: > This, milord, is my family's axe. We have owned it for almost nine hundred years, see. Of course, sometimes it needed a new blade. And sometimes it has required a new handle, new designs on the metalwork, a little refreshing of the ornamentation . . . but is this not the... axe of my family?
  - mgkimsal 2y ago
    Or Trigger's Broom: https://www.youtube.com/watch?v=BUl6PooveJE https://www.youtube.com/watch?v=BUl6PooveJE
groestl 2y ago
Adding to the list of comments damning this post to ensure none of my future colleagues follow this advice. > You think your surrogate key will save you? It will not. It definitely will, say my 20+ years of experience.
globular-toast 2y ago
How do you deal with the Ship of Theseus/Trigger's broom? There's literally nothing that defines said object apart from its history.
- zer00eyz 2y ago
  Your database starts to look like git... Think about a jet engine. Let's say you figure out that a part is defective and will cause a failure. You want to identify every plane that has one of those parts in it. What if you find that some of them had a bad oil pump that shortened the life of some bearings. You want to know every engine that had one of those pumps, so you can replace other parts. I dont know if they do this with jets but there are quite a few places that take thee concept much further than this.
  - smaudet 2y ago
    > Your database starts to look like git... No, your table archives start to look like git. Which is not a bad thing, version control on data in a database is very difficult, if not impractical to realize, I would take any history that I can get... "Synthetic" keys are just that, keys for tables that only have meaning within the context of that data. You can of course relate your records to as many other tables as you wish, but you identify records via those keys, nothing more.
RedShift1 2y ago
Every time I used a natural key I have to come to regret it.
th3byrdm4n 2y ago
When I integrate systems, I use that system's natural key (love it when it's a unique ID, but in the systems I work in - it almost never is). That said, I use that natural key as the "link" to my internally managed, normalized database. There's nothing that says I cannot add unique identifiers that would replicate the natural key. In fact, that's good design.
hyperman1 2y ago
In my experience, this won't end well. Some examples: Belgium has the RNR/INSZ identifying each person. But it can change for a lot of reasons: It gets reused if someone dies. It encodes birth date, sex, asylum state, so if something changes (which happens about every day), you need to adapt your unique key. Belgium also has a number identifying medical organizations. Until they ran out of numbers. Then someone decided to change the checksum algorithm, so the same number with a different checksum meant a different organizations. And of course they encode things in the number and reuse them, so the number isn't stable. An internal COBOL system had a number as unique key, and this being COBOL, they also ran out of numbers. This being COBOL, it was more easy to put characters in the fixed width record than expand it. And this being French COBOL, that character can have accents. So everyone using the 'number' as unique key now had to change their datatype to text and add unicode normalization. Not fun. In my experience: don't use anything as an ID that you didn't generate yourself. Make it an integer or UUID. Do not put any meaning in the ID. Then add a table (internal ID, registering entity, start date, end date or null, their key as text). You 'll still sometimes have duplicates and external ID updates as the real world is messy, but at least you have a chance to fix them. The overhead of that 1 lookup is negligable on any scale.
zer00eyz 2y ago
> Model your data on the real world. Do not depend on spherical horses. Yes, and normalize it. https://en.wikipedia.org/wiki/Database_normalization https://en.wikipedia.org/wiki/Database_normalization > Adding a synthetic key only means you have to track all three. Plus you have to generate a meaningless number, which is actually a choke point in your data throughput. This is true up to a point. You can add more data to the system to continue to generate natural, composite keys. However at some point you move from a database to an event stream, or you have to track events that aren't really needed for what your doing... Denormalization then takes precedence and a generated key makes sense again. https://en.wikipedia.org/wiki/Denormalization https://en.wikipedia.org/wiki/Denormalization > how will the surrogate key protect you? It isnt about protection, it's about not collecting the natural data to identify the event that caused the issue. Its denormalization by omission in effect.
somerandomqaguy 2y ago
>The natural key forces you to think about what makes the row unique. What identifies it. Sometimes, it makes you go back to the SME and ask them what they mean. Sometimes it makes you reconsider time: it’s unique now, but does it change over time, and does the database need to capture that? In short, what are the boundaries of the Closed World Assumption? You need to know that too, to answer any "not exists" question. Not really because your natural ID has to also account for the problem of garbage data and account for the SME's not actually being experts. And I can give a real world example of this happening; the Canadian Long Gun registry. For anyone that doesn't know, prior to the early mid 90's or so Canadian gun laws only required registration of pistols. I might be incorrectly remembering but IIRC the records were not handled at a national level either but I could be wrong. Around that time new laws were introduced that among other things required registration. Most guns by then had serial numbers so all you had to do was tie a gun to a serial number with some characteristics and voila, you've got a natural identifier, right? That's what the experts say. Well as it turns out, reality isn't quite so kind. A lot of firearms makers such Cooey from around the 1940's to the 1970's produced guns without a serial number. In other cases the serial number was present from the factory but were damaged, or the part that had thee number had been replaced without replacing the number or had been replaced with a wrong number. In rare cases the serial number from the factory was wrong because of a mistake when the worker manually stamped in the number. So already the idea to uniquely identify using some sort of simple classifier was already flawed. They attempted to solve the issue of guns without serial numbers but those stickers were cheaply made and readily fell off, and owners that were already peeved about the program to not bother with trying to paperwork correct. Which segways to the next problem. There was an extremely high rate of errors in registration forms being submitted. The most famous example I'm aware of was someone registering a Black and Decker soldering gun as a firearm, something he had done in protest. As humorous as it was, it the revelation that a soldering gun had been classified as a firearm unveiled another fundamental problem. The error rate was so high, and the pressure to show progress so great, that the someone in leadership (I can't remember if it was the government or RCMP) directed the data entry clerks to just plug the data in with no validation as is. Didn't matter if the data was wrong, or made no sense, or contradicted other entries already in the database. The intent being to just get all the data in as is so that they could fix it later. So all that wrong information? Got pushed straight into the database. Like I said, this was real world mess that occurred from 1995 until 2012 when a new government dropped the requirement for non restricted firearms to be registered with little fanfare and only squeaking protests. It's not to say that you shouldn't think about a 'natural key' persay. But the problem assuming that there is a 'natural key' requires that you or your subject matter expert is actually an expert that can identify a good enough model for that to exist. But what happens when your SME is just plain wrong and it in turn introduces fundamental flaws in your model? Or outside influences forces garbage data in? How is a database designed to model only the real world supposed to cope with that?
ratherbefuddled 2y ago
> how will the surrogate key protect you? It will not. Yes it will. Your changes will be confined to only the table(s) where the natural key is present, not spread across every table where there's a foreign key. Of course you will still have to deal with the reality that the natural key is now not unique, and model reality, but your implementation work in doing so is far simpler. In more years than I care to count I've regretted someone using natural keys as a primary key for a table many times, and surrogates never.
wruza 2y ago
In short, you advise us to foresee the future, explore unknown unknowns and expect high-precision true answers from the outside. Good advice, not for this universe. You can only get false negatives in this one. A synthetic key means “we think exists”. There exists a contract, a medical record, a person, in a real world, in our opinion. We record these existence identities into our model by using an always-unique key. Then there’s a date, an appointment #, a name, etc. You can refer to an entity by its identity, or search by its attributes. If you use searches in place of identity references, you get non-singletons eventually and your singleton-based logic breaks.
- datadrivenangel 2y ago
  This is why it's hard to be a DBA... Everyone thinks you're a Cassandra user the way other developers ignore your prophecies.
DrScientist 2y ago
> The natural key forces you to think about what makes the row unique. What identifies it. When designing a table - you should always be clear about what the natural key is and make sure it has uniqueness constraint. Mindlessly having a surrogate key without thinking about what the natural key is, is an anti-pattern. So totally agree here. That doesn't stop you also having a surrogate key though. Another aspect of natural versus surrogate keys is joins as the key often ends up in both tables. Using natural keys can mean in some circumstances you can avoid a join to get the information you want - as it's replicated between tables. There is also the question of whether you surface the surrogate key in the application layer or not - some of the problems of surrogate keys can be avoided by keeping them contained within the app server logic and not part of the UI. So via the UI - you'll search by car registration number, not surrogate key, but in terms of your database schema you don't join the tables on registration number - you use a surrogate key to make registration numbers easier to update.
thefz 2y ago
Been vehemently against surrogates my whole life, glad to find a kindred soul.
imtringued 2y ago
>Plus you have to generate a meaningless number, which is actually a choke point in your data throughput. No it isn't. Working with natural keys in general involves using compound primary keys since it is unlikely that any lone field is suitable as primary key. Comparing an integer is quick. Comparing three string fields in a join is not.
abujazar 2y ago
Natural keys can change, and synthetic keys never have to. That alone is reason enough to use synthetic keys. Performance is another major argument for synthetic keys, as they are can be made sequential, which is rarely the case for natural keys.
IlliOnato 2y ago
Sorry, by your logic an ISSN would be a good key for a database of scientific journals. It's exactly what ISSN is invented for! Right? Right? Been there, done that. Journals that changed their names (and identities) but not ISSN. That changed the ISSN but not the name/identity. Journal mergers which instead of obtaining a new ISSN kept one of the old ones. "Predatory journals" that "borrow" an ISSN (you may not consider them real journals, but you've got to track them anyway, even if only to keep them from being added to the "main" database). The list may go on and on. And don't even start me on using even more natural ID, the journal name, perhaps in combination with some other pieces of data, like year the publication started, country of origin, language, etc... Any scheme based on this will need to have caveats after caveats. (A fun fact: there were journals that ceased publication but later on "returned from the dead". Such resurrected journals are supposed to be new journals and to get a new ISSN. Sometimes this rule is followed...) At the end, a "meaningless number" you assign yourself is the only ID that reliably works (in combination with fields representing relationships between journals). The problem with keys that "have meaning" is that they appear to carry information about your entity. And in vast majority of cases this is correct information! So it's almost impossible to resist "extracting" this information from a key without doing actual database lookup at least mentally, and often in your software too. Hidden assumptions like this lead to bugs that are really hard to eliminate. A meaningless number on the other hand does not tempt one :-)
sgarland 2y ago
Your post brings up a critical difference I’ve noticed when working with devs (I’m a DBRE): those who actually do rigorous data modeling, and those who view it as an annoyance impeding their project. Spend time modeling your schema. Ask yourself, “does every attribute in this table directly relate to the primary key? And is every attribute reliant upon the primary key?” Those two alone will get you most of the way through normalization.
indoordin0saur 2y ago
I agree with both the original author's thoughts and yours. Surrogate keys are generally necessary but also overrated. Natural keys will always remain important. I think it's important to keep natural keys as reliable as possible whenever you control the system. Creating a new surrogate key every time the data moves creates it's own headache as you have to keep track of more and more keys referring to the same concept.

kayodelycaon 2y ago

Of course they won’t save you from external data. The whole point is for your system to have a way to identify rows internally so you can deal with external systems getting wonky without corrupting your own data. All of your concerns are easily solved by a unique index. Using external keys as a forcing function to prevent people from representing data wrong is not great.

crabmusket 2y ago

The surrogate key uniquely identifies a row in your database, which is an entity just as real and significant as the car or the employee or what-have-you. Don't confuse the two! I agree with you that having a surrogate key isn't going to save you from the reasons why natural keys can be difficult. The complexity has to go somewhere. But not having a unique identifier for each row is going to make things extra difficult.

hobs 2y ago
The main thing is that the synthetic key should never leave the database and never be displayed in the app - if you want to have another key that represents the human oriented key do it, but it should be another field, an indexed field even, but one that has a lot less monotonic sequential properties that are inherent to synthetic database identifiers. You want to change that human key? Sure. You want to to complain that the keys are not sequential? Sure. You want to actually make them weird strings that are harmful to my brain? Why not? You want to update primary keys in the database? No. Absolutely not.
- The_Colonel 2y ago
  I assume you hint at the security aspect of monotonic keys? I've found this issue a bit overblown. It's basically security by obscurity, which is a nice bonus, but not something your security model can be based on. I mean, it is a good practice to expose some kind of non-sequential key (e.g. UUIDv7), but it doesn't seem to me like a dealbreaker.
  - crabmusket 2y ago
    I love it when my competitors use sequential integer IDs.
  - throwaway8486k 2y ago
    Security by obscurity makes it more difficult for bad actors as an additional layer that they need to break. I don't see a reason why that's a bad thing and doesn't take a lot of effort to implement in this case. I have been in a startup where competitors used our sequential keys to scrape a list of customers. Lesson learned the hard way with actual business consequences! Sequential keys also leak information (German tank problem) Your competitors can estimate number of customers, your growth rate and other stats that you often don't want them to know. https://en.m.wikipedia.org/wiki/German_tank_problem https://en.m.wikipedia.org/wiki/German_tank_problem
    - The_Colonel 2y ago

polemic 2y ago

In the wise words of Patsy, "it's only a model". The real world is resistent to clean abstractions and abstractions are distressingly subject to change. What made your row unique today is quite likely to become non-unique in the days/months/years to come. Always use surrogate keys. Your future self will thank you.

hehdhdjehehegwv 2y ago
And always kids, write code for one person, and one person only: your future self.
- M95D 2y ago
  Yeah, fsck your co-workers and your replacement when you quit.
  - hehdhdjehehegwv 2y ago
    If you’re good to your future self you’re also good to them.
    - M95D 2y ago
      Invalid argument. Very rarely your future self will work in that company or on the same project. Even if someone plans to keep his job for a long time, writing for his's future self usually means making himself non-replaceable by writing unreadable code.

tadfisher 2y ago

In that case, using this natural datum as an ID is a contradiction, because we are now removing the uniqueness constraint. It's fine to model the real world, but the real world also includes your data, and you may want to identify unique _records_ as it is not a universal truth that data can be corrected on entry.

huygens6363 2y ago

There is a model of a thing and there is a row that stores a representation of that model of a thing. They both are things. Ignoring the last one might be tempting, but it’s not practical. Interestingly your own way of thought is applied, but now a level deeper again. How do you model a row? What makes it unique? A surrogate ID is the only sensible unique identifier for such a thing as there is no “natural key” that would make sense for instances of something so general as “Row”. What you were saying amounts to “don’t model the thing holding the model”, but experience shows the thing holding the model is itself an (often unwilling) active part of systems. Someone here gave the example of wrongly entered PK’s by administrative personnel handing live customers. That’s IMO a good example of why you need an extra layer on top of your Actual Model(c). I can think of more.

crabmusket 2y ago
> They both are things. Corollary: your app is part of the real world.
- TeMPOraL 2y ago
  Furthermore: bugs in your app are part of the real world, so woe to those who used keys from your app as natural/external keys in their app.

jtriangle 2y ago

>Model your data on the real world. Do not depend on spherical horses. This took me years to realize, and once I did things became much, much simpler.

CrimsonRain 2y ago

Sorry. This is a very bad advice. I just had to fight tooth and nail to make my lead turn around from this disastrous decision. Using a lot of external IDs as our own row primary keys and then they get propagated to all other tables as foreign keys and what not. One day the foreign key chances or God forbid, the formatting changes in external systems, now we need to fix our whole database and all codes instead of a small isolated place. Generate your own unique keys for everything; add a few more unique constraints if needed. A bit more work but never a regret.

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.

devjab 2y ago

In terms of the Danish CPR that is mentioned here, the way we actually solved the challenge in the national architecture strategy was to define your social security number(s) as what we call an address on your person. I’m not sure why it was called an address, but it’s basically a UUID and the information. Maybe it’s because it was first used to store addresses? Anyway. Unless your system has not yet implemented the national strategies (which by now are approaching 24 years) then changing a Danish Social Security won’t actually matter because it’s just an added address to your person “object”. So basically you’ll now simply have two, and only which is marked as the active. Similar to how you’ve got an array of addresses in which you have lived but only one main address active. It did indeed cause a lot of issues historically because it was used as a natural key… though with it being based on dates, it was never really a good candidate for a key since you couldn’t exactly make it into a number unless you had some way to deal with all the ones beginning with 0 being shorter than the rest. Anyway… it was used as a key, and it was stupid. Anyway I both agree and disagree with you. Because we’ve successfully modelled the real world with virtual keys, but you access it by giving any number of natural keys. Like… you can find the digital “me” by giving a system my CPR number, but you can also find me by giving a system my current address. Technically you could find me by giving a name, but good luck if you’re dealing with a common name. There is a whole range of natural keys you can use to identify a “digital” person, but all of it leads from a natural key into a web of connected virtual keys. All of it is behind some serious gatekeeping security if you’re worried. Or at least it’s supposed to be.

mrweasel 2y ago
It's been years since I worked on systems with CPR numbers, but I seem to recall that there is also a policy in place, stating that you are not allowed to use the CPR number as a "primary key". Many companies did anyway, because they never actually bothered to read the guidelines and regulations. All the systems I've seen always had the CPR as a lookup for a UUID, which as you say is the the "address" of the actual person object.

paulddraper 2y ago

Terrible advice. Surrogate keys are keys are a layer of indirection. They don't fix all problems, but they fix some problems. Not least of which is performance. Often natural keys are character strings, whereas surrogate keys can be fixed size integers, saving index sizes on your FKs.

mypalmike 2y ago
Conversely, certain queries can be much faster by using natural keys when the FK is all that you need in the result rather than additional fields in the primary table. In this case, the primary table doesn't need to be queried at all. This doesn't generally overcome the benefits of synthetic keys, but it is an optimization sometimes put into practice.
- crabmusket 2y ago
  Aren't you literally describing an index?
  - mypalmike 2y ago
    No. Maybe an example helps. You have a users table with username as natural PK. You have an access table with timestamps of users hitting a service, with FK of username. If you query the access table for a range of timestamps and just want the usernames, they're right there in the results of the access table query. If you had instead used a synthetic user_id key, the db would have to do an additional lookup in the users table via a join for each user_id to get the usernames.
    - crabmusket 2y ago
      Oh of course, that makes sense. I'm not sure how I misunderstood that!
mrkeen 2y ago
> surrogate keys can be fixed size integers This launches into the other debate about PKs: using UUIDs rather than sequential keys.

yashap 2y ago

This is bad advice. Say I have a user table, and the email is unique and required, and we don’t let users update their email, and we don’t have user deletion. If I’m going natural PK, I make email the primary key. But … then we add the ability for users to update their email. But it should still be the same user! This is trivial if we have a surrogate primary key, a nightmare if we made email the natural primary key. Or building on that example, maybe at first we always require an email from our users. But later we also allow phone auth, and you just need an email OR a phone number. And later we add user name auth, SSO, etc. Again, all good with surrogate primary keys, a nightmare with natural primary keys. There are countless examples like this. You brought up cars, same thing with licence plates, for example. Or even Social Security Numbers/Social Insurance Numbers - in Canada SINs are generally permanent, but temporary residents can have their SIN change if they later become permanent residents, but they’re still the same person. You want your entities to have stable identity, even if things you at one time thought gave them identity change. Surrogate primary keys do that, natural primary keys do not. Don’t use natural primary keys, use surrogate primary keys with unique constraints/indexes. I challenge you to come up with a single plausible example where you’re screwing yourself by choosing surrogate PK + unique constraints/indexes. Meanwhile there are endless examples where you’re screwing yourself by choosing natural PK.

maweki 2y ago
> I challenge you to come up with a single plausible example So I come from academia, but generally if you use a natural key as PK in a foreign key constraint it may be possible to express additional consistency criteria as CHECK-constraints in the referencing table. So this is a bad example, but say you have Name and Birthdate as your PK, and you have a second table where you have certain special offers sold to your customers and there is this special offer just for Virgos and Pisces, you could enforce that the birth date matches this special offer. Some modern systems also technically allow FK on alternate keys, so you could still do it that way, but database theory often ignores that. But second, while I agree that surrogate keys are often a good idea, I find your argument, that you must design for every conceivable change, not convincing.
- 2y ago

SPBS 2y ago

> You think your surrogate key will save you? It will not. The world has an external reality that needs to be reflected in your database. Yes, it will. It is precisely because of a messy external reality that you need an unchanging internal ID that is unaffected by changes to the external ID. If the software designer in the article had followed your advice, changing the chassis number would have likely resulted in broken car ownership records. Whoever is reading this in the future, please don't follow the parent comment's advice. Use surrogate/synthetic keys for your primary key.

aswerty 2y ago

Surrogate keys do mirror reality though. As I once read in a Terry Pratchett book; if you replace the handle of an axe and then replace the head, is it still the same axe? For me, the answer is yes - since we imbue the axe with an identity outside of it's integral parts. That is what a surrogate key is. An identity. Which is an abstract concept that exists in the real world. And to pile on. The top comment is bad advice! Surrogate keys provide sanity - god save you if you have to work in a database solely using natural keys.

vintermann 2y ago
Yes. Ultimately, "are these the same" and "are these different" are philosophical questions. Or to be more precise, teleological questions. Because those questions have no meaning except with an "for our purposes here" added. And it's up to us to decide what we care about, if anything. If we care about what other people want with the data though, or suspect that our own wants might not be set in stone, then we should also care to model identity independently in the system (that is, use surrogate keys).
fuzztester 2y ago
>As I once read in a Terry Pratchett book; if you replace the handle of an axe and then replace the head, is it still the same axe? Yes and no. It is the Axe of Theseus ;) https://en.m.wikipedia.org/wiki/Ship_of_Theseus https://en.m.wikipedia.org/wiki/Ship_of_Theseus
- aswerty 2y ago
  Thanks - I think somewhere in the back of my mind I was also aware of the Ship. But Pratchett is too good. The quote: > This, milord, is my family's axe. We have owned it for almost nine hundred years, see. Of course, sometimes it needed a new blade. And sometimes it has required a new handle, new designs on the metalwork, a little refreshing of the ornamentation . . . but is this not the... axe of my family?
- mgkimsal 2y ago
  Or Trigger's Broom: https://www.youtube.com/watch?v=BUl6PooveJE https://www.youtube.com/watch?v=BUl6PooveJE

groestl 2y ago

Adding to the list of comments damning this post to ensure none of my future colleagues follow this advice. > You think your surrogate key will save you? It will not. It definitely will, say my 20+ years of experience.

globular-toast 2y ago

How do you deal with the Ship of Theseus/Trigger's broom? There's literally nothing that defines said object apart from its history.

zer00eyz 2y ago
Your database starts to look like git... Think about a jet engine. Let's say you figure out that a part is defective and will cause a failure. You want to identify every plane that has one of those parts in it. What if you find that some of them had a bad oil pump that shortened the life of some bearings. You want to know every engine that had one of those pumps, so you can replace other parts. I dont know if they do this with jets but there are quite a few places that take thee concept much further than this.
- smaudet 2y ago
  > Your database starts to look like git... No, your table archives start to look like git. Which is not a bad thing, version control on data in a database is very difficult, if not impractical to realize, I would take any history that I can get... "Synthetic" keys are just that, keys for tables that only have meaning within the context of that data. You can of course relate your records to as many other tables as you wish, but you identify records via those keys, nothing more.

RedShift1 2y ago

Every time I used a natural key I have to come to regret it.

th3byrdm4n 2y ago

When I integrate systems, I use that system's natural key (love it when it's a unique ID, but in the systems I work in - it almost never is). That said, I use that natural key as the "link" to my internally managed, normalized database. There's nothing that says I cannot add unique identifiers that would replicate the natural key. In fact, that's good design.

hyperman1 2y ago

In my experience, this won't end well. Some examples: Belgium has the RNR/INSZ identifying each person. But it can change for a lot of reasons: It gets reused if someone dies. It encodes birth date, sex, asylum state, so if something changes (which happens about every day), you need to adapt your unique key. Belgium also has a number identifying medical organizations. Until they ran out of numbers. Then someone decided to change the checksum algorithm, so the same number with a different checksum meant a different organizations. And of course they encode things in the number and reuse them, so the number isn't stable. An internal COBOL system had a number as unique key, and this being COBOL, they also ran out of numbers. This being COBOL, it was more easy to put characters in the fixed width record than expand it. And this being French COBOL, that character can have accents. So everyone using the 'number' as unique key now had to change their datatype to text and add unicode normalization. Not fun. In my experience: don't use anything as an ID that you didn't generate yourself. Make it an integer or UUID. Do not put any meaning in the ID. Then add a table (internal ID, registering entity, start date, end date or null, their key as text). You 'll still sometimes have duplicates and external ID updates as the real world is messy, but at least you have a chance to fix them. The overhead of that 1 lookup is negligable on any scale.

zer00eyz 2y ago

> Model your data on the real world. Do not depend on spherical horses. Yes, and normalize it. https://en.wikipedia.org/wiki/Database_normalization https://en.wikipedia.org/wiki/Database_normalization > Adding a synthetic key only means you have to track all three. Plus you have to generate a meaningless number, which is actually a choke point in your data throughput. This is true up to a point. You can add more data to the system to continue to generate natural, composite keys. However at some point you move from a database to an event stream, or you have to track events that aren't really needed for what your doing... Denormalization then takes precedence and a generated key makes sense again. https://en.wikipedia.org/wiki/Denormalization https://en.wikipedia.org/wiki/Denormalization > how will the surrogate key protect you? It isnt about protection, it's about not collecting the natural data to identify the event that caused the issue. Its denormalization by omission in effect.

somerandomqaguy 2y ago

>The natural key forces you to think about what makes the row unique. What identifies it. Sometimes, it makes you go back to the SME and ask them what they mean. Sometimes it makes you reconsider time: it’s unique now, but does it change over time, and does the database need to capture that? In short, what are the boundaries of the Closed World Assumption? You need to know that too, to answer any "not exists" question. Not really because your natural ID has to also account for the problem of garbage data and account for the SME's not actually being experts. And I can give a real world example of this happening; the Canadian Long Gun registry. For anyone that doesn't know, prior to the early mid 90's or so Canadian gun laws only required registration of pistols. I might be incorrectly remembering but IIRC the records were not handled at a national level either but I could be wrong. Around that time new laws were introduced that among other things required registration. Most guns by then had serial numbers so all you had to do was tie a gun to a serial number with some characteristics and voila, you've got a natural identifier, right? That's what the experts say. Well as it turns out, reality isn't quite so kind. A lot of firearms makers such Cooey from around the 1940's to the 1970's produced guns without a serial number. In other cases the serial number was present from the factory but were damaged, or the part that had thee number had been replaced without replacing the number or had been replaced with a wrong number. In rare cases the serial number from the factory was wrong because of a mistake when the worker manually stamped in the number. So already the idea to uniquely identify using some sort of simple classifier was already flawed. They attempted to solve the issue of guns without serial numbers but those stickers were cheaply made and readily fell off, and owners that were already peeved about the program to not bother with trying to paperwork correct. Which segways to the next problem. There was an extremely high rate of errors in registration forms being submitted. The most famous example I'm aware of was someone registering a Black and Decker soldering gun as a firearm, something he had done in protest. As humorous as it was, it the revelation that a soldering gun had been classified as a firearm unveiled another fundamental problem. The error rate was so high, and the pressure to show progress so great, that the someone in leadership (I can't remember if it was the government or RCMP) directed the data entry clerks to just plug the data in with no validation as is. Didn't matter if the data was wrong, or made no sense, or contradicted other entries already in the database. The intent being to just get all the data in as is so that they could fix it later. So all that wrong information? Got pushed straight into the database. Like I said, this was real world mess that occurred from 1995 until 2012 when a new government dropped the requirement for non restricted firearms to be registered with little fanfare and only squeaking protests. It's not to say that you shouldn't think about a 'natural key' persay. But the problem assuming that there is a 'natural key' requires that you or your subject matter expert is actually an expert that can identify a good enough model for that to exist. But what happens when your SME is just plain wrong and it in turn introduces fundamental flaws in your model? Or outside influences forces garbage data in? How is a database designed to model only the real world supposed to cope with that?

ratherbefuddled 2y ago

> how will the surrogate key protect you? It will not. Yes it will. Your changes will be confined to only the table(s) where the natural key is present, not spread across every table where there's a foreign key. Of course you will still have to deal with the reality that the natural key is now not unique, and model reality, but your implementation work in doing so is far simpler. In more years than I care to count I've regretted someone using natural keys as a primary key for a table many times, and surrogates never.

wruza 2y ago

In short, you advise us to foresee the future, explore unknown unknowns and expect high-precision true answers from the outside. Good advice, not for this universe. You can only get false negatives in this one. A synthetic key means “we think exists”. There exists a contract, a medical record, a person, in a real world, in our opinion. We record these existence identities into our model by using an always-unique key. Then there’s a date, an appointment #, a name, etc. You can refer to an entity by its identity, or search by its attributes. If you use searches in place of identity references, you get non-singletons eventually and your singleton-based logic breaks.

datadrivenangel 2y ago
This is why it's hard to be a DBA... Everyone thinks you're a Cassandra user the way other developers ignore your prophecies.

DrScientist 2y ago

> The natural key forces you to think about what makes the row unique. What identifies it. When designing a table - you should always be clear about what the natural key is and make sure it has uniqueness constraint. Mindlessly having a surrogate key without thinking about what the natural key is, is an anti-pattern. So totally agree here. That doesn't stop you also having a surrogate key though. Another aspect of natural versus surrogate keys is joins as the key often ends up in both tables. Using natural keys can mean in some circumstances you can avoid a join to get the information you want - as it's replicated between tables. There is also the question of whether you surface the surrogate key in the application layer or not - some of the problems of surrogate keys can be avoided by keeping them contained within the app server logic and not part of the UI. So via the UI - you'll search by car registration number, not surrogate key, but in terms of your database schema you don't join the tables on registration number - you use a surrogate key to make registration numbers easier to update.

thefz 2y ago

Been vehemently against surrogates my whole life, glad to find a kindred soul.

imtringued 2y ago

>Plus you have to generate a meaningless number, which is actually a choke point in your data throughput. No it isn't. Working with natural keys in general involves using compound primary keys since it is unlikely that any lone field is suitable as primary key. Comparing an integer is quick. Comparing three string fields in a join is not.

abujazar 2y ago

Natural keys can change, and synthetic keys never have to. That alone is reason enough to use synthetic keys. Performance is another major argument for synthetic keys, as they are can be made sequential, which is rarely the case for natural keys.

IlliOnato 2y ago

Sorry, by your logic an ISSN would be a good key for a database of scientific journals. It's exactly what ISSN is invented for! Right? Right? Been there, done that. Journals that changed their names (and identities) but not ISSN. That changed the ISSN but not the name/identity. Journal mergers which instead of obtaining a new ISSN kept one of the old ones. "Predatory journals" that "borrow" an ISSN (you may not consider them real journals, but you've got to track them anyway, even if only to keep them from being added to the "main" database). The list may go on and on. And don't even start me on using even more natural ID, the journal name, perhaps in combination with some other pieces of data, like year the publication started, country of origin, language, etc... Any scheme based on this will need to have caveats after caveats. (A fun fact: there were journals that ceased publication but later on "returned from the dead". Such resurrected journals are supposed to be new journals and to get a new ISSN. Sometimes this rule is followed...) At the end, a "meaningless number" you assign yourself is the only ID that reliably works (in combination with fields representing relationships between journals). The problem with keys that "have meaning" is that they appear to carry information about your entity. And in vast majority of cases this is correct information! So it's almost impossible to resist "extracting" this information from a key without doing actual database lookup at least mentally, and often in your software too. Hidden assumptions like this lead to bugs that are really hard to eliminate. A meaningless number on the other hand does not tempt one :-)

sgarland 2y ago

Your post brings up a critical difference I’ve noticed when working with devs (I’m a DBRE): those who actually do rigorous data modeling, and those who view it as an annoyance impeding their project. Spend time modeling your schema. Ask yourself, “does every attribute in this table directly relate to the primary key? And is every attribute reliant upon the primary key?” Those two alone will get you most of the way through normalization.

indoordin0saur 2y ago

I agree with both the original author's thoughts and yours. Surrogate keys are generally necessary but also overrated. Natural keys will always remain important. I think it's important to keep natural keys as reliable as possible whenever you control the system. Creating a new surrogate key every time the data moves creates it's own headache as you have to keep track of more and more keys referring to the same concept.