Sometimes you will regret it, other times you won't.
Notice that even if you believe that you should 'never use natural keys', that is NOT the same thing as 'always add a generated synthetic key to every table as the primary key'. You should NOT always add a generated key to every table, even if you use a trash ORM that really wants you to do this.
Here's a real world example if you run a script once every 5 minutes that launch sub-task you might be tempted to add a non natural auto-increment number that identify each occurence to create a link between the script and the subtasks.
However it will be way more painless to use a timestamp of the script starting point a natural key.
This way when shit happen you have a relevant and stable way to identify each occurrence. And timestamp is easily indexable and ordered.
So use timestamps with sub-second precision, which virtually every SQL database supports (even ones like SQLite that don't have built-in date/time types).
If you want a timestamp, by all means add a timestamp field. And of course add a (non primary) key on the field.
I would add a different surrogate field though for the primary key. Because I've -always- discovered edge cases which break my original model.
In this situation I can think of 2. Firstly if the process triggers twice at the same time you have a problem (which happened to me in a case where a test suite fired up thousands of instances on a machine with many cores). Secondly twice a year with daylight savings (and when the owner of the system decided to change from local time to utc).
Is there an actual natural kh out there that will never fail me? Probably. But I'm tired of looking for it.
This is how I was always taught to use them so I'm kinda confused what the author is getting at. If the thing you're using as a key can change and that doesn't define the row then you don't have a key. If you're at a conference where everyone has a badge number then that's a great natural key when scanning people into your workshop. If you're at Disney and everyone has a magicband with ids then you can use that as a natural key all over the place when to you that's a visitor.
I think this is an excellent example of one of the pitfalls that the author is getting at.
(Disclaimer: not a Discord bot programmer.)
Suppose discord_user_id is an identifier like @Spivak#2024 that uniquely identifies the user. When Discord forces all users to change to unique ID's and eliminates the disambiguating numbers (or allows users to change them), then where does that leave you?
(For the unfamiliar: something like this happened. Not sure if those are really the "discord user id" used in bot service though, but suppose that they are for the sake of the argument.)
It probably leaves you in the same place you’d be with a synthetic key of your own: unable to track users across this key change without additional Discord data.
Yes, but if you then reference that as a foreign key in some other table or system, then whatever migration you need to do will encompass those systems as well, whereas if you use a synthetic key from the start you wouldn't need to change them.
You're reading this wrong. The real ID (the Snowflake) is exposed in the API, and the UI if you turn dev tools on. That's the one I use. You don't use @username#1209 but 1247755691426542433.
Does Discord guarantee, _absolutely guarantee_, that they will never change their ids? This might be a safe practice if they explicitly claim that, and you trust them absolutely. Not otherwise.
Feels like this could have used a few more solid examples up-front. I think another good example would be PlayStation Network using the natural key of "gamer tags" as the primary key to identify players would be a good example. Since this effectively locks players into having to keep a gamertag in order to uniquely identify them in the service -- instead of having a synthetic key that carries no meaning or data other than "uniqueness"
yeah the way he described it it's like... well who would ever do that. but foreign keying to emails or usernames is much easier to "accidentally" do and is a classic source of long-term headaches.
As a great example: My steam account name is the email I was using in 2003. I have largely not used it since then. The email on the account has been updated, but the account name? Stuck.
Last I checked, Steam still has me logging in with my two decade old hotmail address as my account name. At least it's not something that shows publicly, I think.
I'm able to log out and back into Steam using my plain username rather than email. The box actually says"enter account name" and wouldn't log me in if I tried to enter my email instead. I'm not sure if there are conditions to this e.g. my account was only created in 2009 and I'm not sure if I've ever toggled anything that "flipped it over" along the way either.
On the API side I know Steam has something like 3 different forms of auto generated alphanumerical account ID and I imagine that's what everything is really keyed off of on the back end.
This is one of those cases where examples of natural keys failing are so ubiquitous that they are almost redundant.
For the group who have forged a career with natural keys, and never regretted it, more power to you. Great.
However to the rest of us, myself included, where ill-considered natural keys have caused endless opinions and suffering, my commiserations.
If I could send back one piece of advice to junior-me it would be to avoid natural primary keys. (Ideally with the corollary to avoid sequences, but that's another thread for another day.)
On the other hand, Discord used to allow arbitrary usernames and would add a suffix for when you needed to disambiguate (e.g. if you used the username JohnDoe, your "id" would be something like JohnDoe#12345) but over the past year or so forced everyone to pick a fully unique username. In this case, the decision seemed to potentially be financially motivated, given that people with subscriptions were given priority for claiming usernames, and historically there really hasn't been much reason to pay for it in the first place.
A phone company I was a customer of used my phone number as the customer Id. Which doesn't work great for people, or companies, with more than one phone number. They could also only provision one SIM per phone number, and not use a phone number tied to a SIM as a virtual number, and also the primary (customer Id) phone number had to be tied to a SIM.
I ported my phone number away from them, but what if I ever go back? Will my old data be there? Including my old address? What if my phone number gets recycled, and some-one else gets that phone number and ports it to them? So many questions.
A natural key is what I would normally call unique index. Say first, surname and date of birth in an employee table as a bad (poor design) example. As opposed to a surrogate key like personId being an auto incrementing ID which is the norm since it can be easier to use when joining tables. I wish articles like these would explain terminology up front. I found it irritating to wade into the anecdotal trivia and not know what I was reading about and I’m very familiar with how databases work!
One reason to not do that is because you could then figure out other users IDs by their hiring date. This ended up being a problem for me once with the auxiliary systems that relied on that ID, namely when it was used in links in other applications.
It increases the attack surface as an authorization vulnerability will allow an attacker to enumerate and access all records. Yes, it is security through obscurity, but a random (e.g. UUID) scheme makes it harder.
Exactly, it's not that my systems use security through obscurity, it's the other ones mine ties into.
This was years ago and you don't see it as much anymore, but think autogenerated links to shitty CRM, ticketing, and project management software where the link is the query aka - Blahsoftware.local/info/bunchofgarbage?=userid+garbage+view+sensitiveinfo.html type stuff.
Anyone that’s spent large amounts of time using vlookup in excel can totally relate. Mashing some fields together as a key works great until it doesn’t.
1. When duplication occurs and goes unnoticed because the natural key isn't being used, and then perhaps the problem is "corrected" by an administrator in a way that doesn't make sense.
For example, someone sets up an account with an street address A, then forgets they had it and sets up an account with street address B. They call and complain that they can't find an old order, or whatever, and the duplication is discovered. The administrator later clobbers one of the addresses but both are in use. A natural key (behaviorally speaking) may have presented the duplication, assuming address isn't part of the natural key. This can be satisfied by uniqueness constraints, etc.
2. When you are browsing the database, you see a bunch of synthetic keys and have to perform various joins to be able to see the relevant data. The synthetic keys make joins easier to write, but make may make ad-hoc queries more time-consuming and difficult.
A synthetic key is adding an additional dataum - so any problem that could be solved with the original data can be solved by the new data too (worst case, ignore the primary key). That is one of the best reasons to add a proper index - there is almost literally no downside. We're talking maybe a few bytes/record and arguably a trivial amount of administrative overhead.
Bearing that in mind:
1. Add a unique constraint on the natural key.
2. If the accuracy of joining by natural key is acceptable, join based on it. If that doesn't work, then a table design without a synthetic key wouldn't be fit for purpose either.
I think somewhat different than the article in some ways.
The first example is why `UPDATE CASCADE` was implemented. So it's possible to use natural keys as identity without the fear of children table. At least in most databases it works.
The drawback of enumeration is real, so if you expose this key you'll need some authentication/ authorization mecanism.
Another good thing in natural keys is that you can eliminate part of the joins. You don't have to join the father table because the key in child table is known.
I think the biggest challenge is how to map logins to people. It's very common to interpret both as the same, but they are not.
> The first example is why `UPDATE CASCADE` was implemented. So it's possible to use natural keys as identity without the fear of children table. At least in most databases it works.
This is only true in a system using a single database, not replicating data in external services, and not offering APIs.
And while it might work today when the service/website is still fairly small and self contained, the requirements might change at any time. So the title (You will [future] regret it) still applies to this solution
There another reason not mentioned — if your key is something like a UUID, it’s very easy to define the logic for joining and filtering based on that key.
If you were using some sort of string like an email address or username, you have to think about case sensitivity and trimming white space and all sorts of preprocessing and then make sure you do it consistently EVERYWHERE
That helps part of it, but there are still places for problems to pop up.
- you need to do an adhoc query to look something up so you have to type in the key in a where clause
- Or you used different sanitation methods in two different databases and you need to join things now
- you try to join the table that had the sanitized email key to a different table that just so happens to have email but it wasn’t sanitized because it was an optional field and not the key
And then you get the other stuff like one-off migrations that are done by someone unaware of the business constraints, or a contractor who completely misses the ORM method, etc.
These of course should be caught by checks and balances, and you can’t count on anyone knowing what they need to know ten years after an implementation is done
> you need to do an adhoc query to look something up so you have to type in the key in a where clause
Having a surrogate key here doesn’t help. A WHERE predicate can be rewritten as an INNER JOIN quite easily. Or you could use a subquery, or a CTE. Many options.
The other problems discussed are an engineering culture problem. Either you value correctness and consistency or you don’t.
I misunderstood your point; I thought you were saying having a surrogate key here would be better.
For the above, either ensure that all email addresses are stored in one case (and index / query on the same), or if that’s impossible for some reason, you can query with ILIKE, optionally creating a trigram index (for Postgres; this isn’t a problem in MySQL since by default it’s entirely case-insensitive) on the column. Another option is to index with a functional index (CREATE INDEX user_email_lowercase ON user (LOWER(email))), and then code the API doing these lookups to cast to lowercase. That way, it’ll be retrieved and displayed however the user entered it, but retrieved based only on the CI version.
You could also have CHECK constraints on those columns to ensure that nothing is written incorrectly, and/or a pre-write trigger that casts them to lowercase.
Or you could make the email column case-insensitive, since it’s generally accepted to be CI anyway.
It is pretty common to assume that email addresses aren't case sensitive since many email providers treat them that way. Email addresses absolutely are case sensitive so you need to preserve case when storing them and be careful about case when using them.
This makes email addresses particularly unsuited to being used as a natural key. Most people treat them as case insensitive and you need to work around that, but you can't safely just treat them as case insensitive yourself.
The way I think of it is a key, in a database, is a record key. It is intended to identify a particular logical record, not the logical thing which is referred to by the record.
An an footnote, I'll add this doesn't mean that you need to have complexities like record versioning, record history, or anything. But couched in those conceptual terms where those things are possible, is a happy and safe space to be. In this space, a database records entries, as if they were each a single paper form with boxes where you fill out, in pencil, to erase if you like or not, the particulars of the thing you're recording. This form comes pre-stamped with a number: the record key.
In this cozy little world, you can be imperfect, and mistakenly (or deliberately, as your use case requires), file multiple slips that refer to the same logical thing, and yet all have different file ("record") numbers - or keys.
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.
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.
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.
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.
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.
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.
I'm not saying it's a bad practice, the opposite actually.
> 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.
True, if you start your application from scratch. Like if I started designing a new app today, I'd just choose the UUIDv7 for the primary key.
It's not an easy thing to add into an existing application, though. I see applications leaking their internal IDs all the time, but usually it's not worth the effort to fix that, because it's a comparatively minor problem.
> I'm not saying it's a bad practice, the opposite actually
I'm sorry that I didn't make that more clear. I saw that you mentioned it as a best practice and are aware of the advantages. It's just that there are so many others that don't have the balanced view as you seem to have.
I have been involved in many discussions at my work place where "security by obscurity" is used as a way to shut down discussions. They changed their minds about sequential keys after the incident I mentioned, but it still has the power to "win" other discussions. Sure, we need to have rate limiting on ip-addresses, auth and other mechanisms, but they are not perfect and bugs happen all the time. An "unguessable" id is an additional security layer
> It's not an easy thing to add into an existing application, though
I agree, but there are ways to reduce the attack surface. You could add an extra "public id" field that can be used for lookup in addition to the existing id. In this way you can have a gradual migration where you go through each endpoint and migrate them individually without changing the foreign keys and other relations in the database (they would still use the sequential key). Maybe you end up not having time to migrate them all, but at least you can reduce the attack surface on the most sensitive endpoints.
If you have low volume endpoints you could perhaps even simply add a mapping layer where you do a simple db lookup to replace the public key with the internal without changing the existing queries. You could even cache this easily to reduce the db load. (both ids are permanently fixed and can be cached forever).
> I have been in a startup where competitors used our sequential keys to scrape a list of customers.
If your system allows customers to see each other (or worse: unauthenticated users to see customers) in this fashion in the first place then whether you're using a sequential integer v. a random UUID is the least of your problems.
The 'customers' could be free tier users - a social media type system where everyone has a public profile - intended for the public - would still be scrapable by /profile/1, profile/2, etc. Doesn't necessarily require 'authentication' for the exposing of sequential integers to have a bad outcome.
You're right. The urls were public to be shared (think of marketing material / ecommerce), so there was not a security incident.
But it did give our competitor free highly qualified leads that they could use to poach customers. This product was new to our customers, and we had spent a lot of time selling and convincing them that it was useful.
UUIDv7 may not be generated sequentially, but it is still temporally ordered and discloses a timestamp, which may be an undesirable leakage for some applications/environments. When obfuscation matters that much, use a UUIDv4 and eat the index performance hit.
Some might suggest, "encrypt your object identifiers on the way in/out", but there's a ton of pitfalls involved since for most applications they are now rolling their own crypto, and it also makes identifiers much longer.
Yes, you can go to great depths, but they each have trade-offs - in performance, increased complexity etc. and it's necessary to make a judgment for each particular app instead of applying the most overengineered solution everywhere.
Sort of, I have way more experience with clients saying that an invoice is missing (which turned out to be a rolled back transaction) and then I get to explain how transactions work, and then if the customer is smart enough they'll say something like "then why the hell is the invoice number part of that?"
Precisely. And if I have a surrogate key to identify “a car”, I get to define what makes a car unique for my purposes. Maybe that really is the VIN. Maybe today it’s the VIN, but tomorrow it’s whatever vehicle is currently being driven by its owner. Maybe it’s something else.
Some day I may need to track multiple VINs for a vehicle (maybe it’s got parts from multiple VINs and I want to track that). I can still always decompose that table and have an n-to-1 relationship between cars and VINs without migrating the rest of my data model.
> 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!
But the DBMS already maintains a row identifier (called rowid or ctid or whatever depending on the DBMS). Why do you need an explicit one?
It may be useful if you have data that originates from another source or if something outside of our system references your entity. In that case you need to keep some form of an externalRef, so it's usually easier to just use an id that you can control, for referencing both internally and externally.
In the DB we use[1] the internal row id is not stable:
The value returned by the function is not necessarily constant between queries as various operations performed on the database may result in changes to the row identifiers of a table.
So, users should refrain from using the ROWID function in ordinary situations; retrieval by primary key value should be used instead.
Be careful with those. SQLite has a rowid concept, but it's not guaranteed to be stable - running a VACUUM against a table can reassign the rowids for every row!
> If the rowid is not aliased by INTEGER PRIMARY KEY then it is not persistent and might change. In particular the VACUUM command will change rowids for tables that do not declare an INTEGER PRIMARY KEY. Therefore, applications should not normally access the rowid directly, but instead use an INTEGER PRIMARY KEY.
Because every DB can and will shift those as needed. They reference the physical location on disk for a given tuple. They are not meant for general consumption.
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.
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.
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.
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.
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.
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.
> 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.
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.
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.
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.
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.
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.
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.
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.
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.
Either your system is happy enough to route every new entity through "one DB at scale" so it can let your "one DB at scale" be in charge of an auto-incrementing long, or it isn't.
A common method is to have a small app (which can quite easily be HA) that hands out sequential chunks to each shard, interleaving and/or gapping as necessary.
> 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.
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.
> 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.
That is not what he is arguing at all. He is showing that there are very many highly plausible changes that are problematic with natural keys. And he totally correct about that. Frankly, the fact that a post arguing for natural keys makes it the top of an HN comment thread is extremely weird. The original article is correct that natural keys are bad.
Why does anything need to be a primary key anywhere in order to enforce some constraint?
At least from ORMs I know I can set for example any group of attributes unique. Other constraints can be implemented in some general method that is called when persisting in the actual database.
Even if no ORM, you can write a wrapper around your persisting procedure.
It doesn’t, you’re right. However, indexes aren’t free, so if your data is such that a natural PK (composite or otherwise) makes sense, you’ll save RAM. Also, for clustering RDBMS like MySQL, data is stored around the PK, so you can get some locality boosts depending on query patterns.
> Some modern systems also technically allow FK on alternate keys
As far as I can tell, all modern systems allow it, as it is part of the SQL standard that foreign keys can be either primary keys or unique indexes. Here's a brief quotation from a copy of ISO/IEC 9075-2:1999 (not the latest version) that I randomly found online:
> If the <referenced table and columns> specifies a <reference column list>, then the set of <column name>s contained in that <reference column list> shall be equal to the set of <column name>s contained in the <unique column list> of a unique constraint of the referenced table.
So it mentions unique constraints first. Then afterward it says:
> If the <referenced table and columns> does not specify a <reference column list>, then the table descriptor of the referenced table shall include a unique constraint that specifies PRIMARY KEY.
If I'm reading this right, it means that in the base case, where you specify the column to reference, it can be any unique constraint, where a primary key is just another possible unique constraint (as all primary keys are by definition unique). And only if you don't specify the fields to reference does it then fall back to the primary key instead of a named unique constraint.
I'm not disagreeing with you entirely - it's true that often there's an assumption in database theory that primary keys are natural and foreign keys are primary keys. But this isn't a hard requirement in practice or in theory, and it partly depends on the foreign key's purpose, why you need it in the first place. This StackOverflow answer also explains it well: https://softwareengineering.stackexchange.com/a/254566
I should add that there is also a set of database design wisdom that suggests you should never use database constraints such as foreign keys, only app/api constraints, but that's a whole different tangent.
> I should add that there is also a set of database design wisdom that suggests you should never use database constraints such as foreign keys, only app/api constraints, but that's a whole different tangent.
That’s less a DB design thought and more of a “devs with little formal training in RDBMS who only want to use it as a dumb store” thought.
Use the DB to its strengths. CHECK constraints add an infinitesimal amount of overhead for writes, and guarantee that there will never be invalid data written. A bad code change could allow, however briefly, for that to occur.
Another example is where you use a service that provides you with a stable id. It makes little sense to add a surrogate id and a fk on that surrogate id. It violates data quality and integrity just for a hypothetical situation.
Data integrity/quality matters. Adding friction to prevent accidents also matters. I don't want something accidentally and trivially updating a field that's used to reference thing externally.
Something about the nat key is about to change? Fine, we can write migrations. Even if it affects millions of rows, it's not a big deal.
I understand people have been burnt by bad design decisions involving nat keys, but they're not some devil's key everyone here dogmatically makes them out to be. You can mess up using anything.
I think there's the important point. Is your key actually natural or is it someone else's surrogate key anyway? Going back to the vehicle identification number: that's already a surrogate key. You just did not assign it yourself.
A VIN is not a surrogate key. A surrogate key must, by definition, have no semantic meaning, and not be composed of parts from multiple domains (among other requirements).
> It makes little sense to add a surrogate id and a fk on that surrogate id. It violates data quality and integrity just for a hypothetical situation.
I would still almost always use an internal artificial key on top of the external id. If you want to enforce data integrity, you can still enforce uniqueness on the external id. "Stability" of an external identifier is almost always an assumption and one that I've seen fail enough times to want that internal id by default.
> then we add the ability for users to update their email.
At this point, you should verify the new email. At least until it is verified, you must track the old email. At this point, you realize you can now introduce a synthetic key and you're fine.
Let's say you have a duplicate customer entry and the customer demands their accounts be merged. Now you can't identify the user by their key alone, since by definition, they can't be the same (yet.)
Per RFC5321, the local part (before @) _may_ be case-sensitive, but in practice, it almost never is, and relying on case sensitivity is a recipe for disaster.
My point being is if you're using email as a key you "have to" treat it as case sensitive even though for most it's not. And yes, I agree it will be a recipe for disaster.
You require three fields (or four): email at registration, a date for that entry (together these create a natural key), and current email (this one not part of the key and editable).
We're almost all the way to a Tag URI[0], so you could combine it with the user's name or username or any other identifier that fits the spec[1] (you could even use the website's own name) and you have a (definitely two thirds, probably 100%) natural key.
It's stable over time and unique, easy to mint, and has a standard behind it. The user also gets to change their contact details without any problem related to the key.
Except you're encoding PII in the ID, which makes them plainly visible to people who should not have access to user data, and hard or impossible to change. Sure, I could e.g. change my e-mail and the contact data would be updated, but you still have the old e-mail associated with my account via ID. I'm not sure this would fly under GDPR.
Erm, don't show the ID to people who don't need it.
Aside from that, it's not a violation of GDPR to keep personal information (that they consented to you having) in order to process business for that person. Using an email address as a unique identifier is not a violation, using it to spam them would be. If they're willing to give you their current email why not an old one?
> Erm, don't show the ID to people who don't need it.
How do you communicate with other people in your company about a customer without sending around PII if the customer's ID is PII?
Maybe we could create a field that uniquely identifies the customer that isn't PII. Then that could be used to uniquely identify a customer in places where we don't want to expose their PII. But then... why not just use this unique ID as the key?
Do you often send the auto-incremented int (that would be the default substitute to this) when communicating with others? Then why would you send this?
It's so strange an argument. Right now you have my username but not my email address, yet you can still query the website database and get certain data that you're allowed to see. There are so many ways to query a particular user's data, and they would all depend on what you're trying to do, needing the specific key would mean you should have access to it anyway and it could be given on per case basis anyway.
> Do you often send the auto-incremented int (that would be the default substitute to this) when communicating with others?
It's not an int, but yes, we have a unique synthetic identifier that serves as the database PK and as a means of communicating about a customer in insecure channels without exposing PII. "Customer ID ### is having an issue with such-and-such."
To turn your second part back around: why a natural key? What is the function of minting a natural key if humans are meant to use something else?
Then use a time-limited token, you can assign it to a particular role or support engineer too. You could do fancy things like making it pronounceable… there are so many options that do not involve passing around keys (while fearing you might leak an email address, which is less worrying than the key, IMO).
> Because non-natural keys are unnecessary in the presence of a natural key, and unnecessary things bring in complexity.
None of the things you've presented are actually "natural" keys, they are pieces of information that you've made assumptions about to shoehorn them into being usable as a "natural key".
> Use secure channels…
No channel is perfectly secure. As channels become more secure, they become harder to use and add complexity. The more places you store customer data, the more risk you create. The attempt to force semantic data to serve as "natural key" has now added risk and complexity to your entire communication infrastructure.
I don’t believe you understand what a natural key is, but aside from that, I’m not the one advocating passing around IDs like that isn’t a security failing. If you wouldn’t put it in a URL then you shouldn’t be passing it around anyway.
> Do you often send the auto-incremented int (that would be the default substitute to this) when communicating with others?
Frequently yes. It is extremely common to communicate about specific records using the ID for that record. The fact that this sort of behavior is extremely common is pretty clearly indicated by the question itself.
> There are so many ways to query a particular user's data, and they would all depend on what you're trying to do, needing the specific key would mean you should have access to it anyway
A responsible organization at scale with limit and log access to customer data. I should be able to determine if two people are talking about the same customer record without needing access to that record's PII.
It is much better to have an artificial key that is linked to this data. There is no upside to the natural key and many, many downsides.
But now then if you want to expose a detail page for that user the id for identifying that page has to include all this potentially personal information about them?
You don’t have to use the PK as the URL slug. Even if you want to route that way, you can have an internal ID and external ID. This is one way to use something random like a UUIDv4 for display without incurring the costs (at least, some of them) of having it as a PK.
And then if you want to list other entities to that user you will have to start mapping the external id and foreign relationships every time to external users?
And also if you are doing exception logging, for ids/primary keys there's higher odds of them being logged out, including your own logs and also external platforms.
It feels like having primary key set up like this just will complicate everything unnecessarily for the future including many edge cases that you don't foresee.
Just have the main ID not have any meaning.
It shouldn't contain information about the date, it shouldn't be auto increment, it should really be just random.
The solution I outlined is the one GitLab and PlanetScale both use internally, so it has been tested at scale and works well, for both Postgres (the former) and MySQL (the latter).
> It shouldn't contain information about the date, it shouldn't be auto increment, it should really be just random.
That’s a great way to tank performance. You want your PK to be k-sortable.
> And then if you want to list other entities to that user you will have to start mapping the external id and foreign relationships every time to external users?
If we're talking about relational database engines, that's what they do, relate things. One join statement is much the same as another.
Another benefit of having stable identies / surrogate primary keys is that any relations (FKs) will be much simpler.
Sure, like the post poster you replied to is pointing out, you _can_ use natural keys, and then also relying on dates or other parts of the data - but creating a relation for that can end up being extremely cumbersome.
- Indexes generally become larger
- relationships become harder to define and maintain
- Harder for other developers to get up to speed on a project
ON UPDATE CASCADE is not the nightmare that you are making it out to be. (My impression from the article is that this is a single SQL database being discussed.)
YANGNI for 99% of projects and databases. When you get to global sharded nosql etc. you need to use UUIDs for anything and incrementing IDs falls over too.
You’ll have to worry about performance tanking instead. If you’re using UUIDv7 then less so, but it’s still (at best) 16 bytes, which is double that of even a BIGINT.
Anyone who says UUIDs aren’t a problem hasn’t dealt with them at scale (or doesn’t know what they’re looking at, and just upsizes the hardware).
Most databases with a UUID type store them as 128-bit integers, typically the same as a BIGINT. It's not like 378562875682765 is the bit representation of a bigint either.
And if you're not using uuidv7 or some other kind of cluster-friendly id, you'd best be using a hash index, and if you're doing neither, you probably don't care about their size or performance anyway. You don't pick UUIDs blindly, but on balance, they solve a lot more problems than they cause.
Postgres’ UUID type is 16 bytes. MySQL can store them as BINARY(16) once encoded. Conversely, a BIGINT for either is 8 bytes. Not sure about SQL Server or Oracle.
> You don't pick UUIDs blindly, but on balance, they solve a lot more problems than they cause.
IME, this is precisely the problem – devs choose them blindly, because then you don’t have to think about proper modeling, you can arbitrarily create a key in your app and be nearly guaranteed of its uniqueness, etc.
128 bits is 16 bytes. BIGINT is a 64 bit int (long or long long).
So there is at least additional storage required, and probably some CPU cost as well. And even UUIDv7 isn't guaranteed to produce sequential IDs, but it's probably good enough to not seriously fragment your table storage.
It's never a single database in the real world. As soon as you integrate something or have an API to something the keys are out there. Unless you add a translation layer, but then you could just as well use surrogate keys directly.
> Using a natural PK -inside- your own database can still be a lot more pleasant to work with
Until you need to do anything like described above. The advantage of artificial keys is that they have no semantic content. Anything with semantic content carries the risk that the role that semantic content plays in your system can change and cause problems. Having a non-semantic identifier protects you from that.
This is not to say that you should never use a semantic identifier as a key. However, you should always have a non-semantic artificial key as the identifier and use the semantic identifiers only when necessary.
I said "can still be" because, yes, it's a trade-off.
I guess if you're trying to give a rule of thumb to juniors, "always have an auto-inc key in case you've misjudged whether the natural key is a good idea" is probably safest (though if you do tell them that, keep an eye out for them trying to add an auto-inc to things like a many-many join table which should've been PKed on the pair of FKs).
But every database design decision can potentially result in problems if the meaning or usage of part of the data changes, and while you should absolutely take that into account when selecting an initial design, adding complexity in case it's needed later is, itself, also a trade-off.
It used to be that "always use the natural key if there is one, because adding a surrogate key is denormalisation and should be done only when necessary" was a common rule, and that was also overly absolutist.
Basically, my rule of thumb is something like "use a natural key if you're confident that you can DBA your way through any required changes easily enough to make the advantages the rest of the time a net win overall" and I find that has better results than 'always' or 'never' would.
(I'd also note that "the role that semantic content plays" changing also applies to e.g. cardinality of relationships and when -those- change it's Interesting Times no matter what you used for PKs and FKs, so you have to have a plan for things like that anyway ... as ever, it's trade-offs all the way down)
Sure, if all data is in a single DB. But in the real world you’ve generally got some/all of:
- 1 or more data warehouses
- Other services storing said data (e.g. the user id will live in many databases in a service oriented architecture)
- External API integrators who have their own data stores totally out of your control that also have copies of parts of your data
- Job queues. It’s common to have jobs scheduled for the future on some other system (Redis, etc.) that say “do X for user with id Y in Z days”. If the “id” changes these fail
- Caches. Say I cache data by user email instead of a surrogate key, user changes their email, and another user signs up with the old email. Cache hits for the wrong user!
- etc.
Changing the primary key becomes an absolute nightmare project once data lives in multiple places like this, and in my experience it mostly does.
Having truly stable identity for your entities just solves so many future problems, and it’s SO easy to do. In almost all cases, natural PKs are really all downside, virtually zero upside, except slightly less storage.
My first job in the late 2000's was at a small university with a home-grown ERP system originally written in the 80s (Informix-4GL). Student records, employee records, financials, asset tracking - everything. It used natural compound keys.
Even worse than the verbose, repetitive, and error-prone conditions/joins was the few times when something big in the schema changed, requiring a new column be added to the compound key. We'd have to trawl through the codebase and add the new column to every query condition/join that used the compound key. It sucked.
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.
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).
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?
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.
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.
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.
> 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...
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.
>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?
> 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.
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.
> 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.
>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.
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 :-)
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.
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.
Another example that happens surprisingly often in healthcare. A registration clerk will incorrectly enter a personal health number (PHN) into the system. Then the actuall holder of that PHN shows up. If this were the PK then the system just wouldn’t handle this case and the reg clerk would have a huge mess to sort out on the spot. A surrogate key on the Person table allows this registration to be made where 2 people have the same PHN in the system. Then cleanup can be handled after the fact to track down the first person, determine their correct PHN and update the record.
Think about these different intentions, each deserving an audit trail with different attached metadata:
- brand new MRN for a newborn. The system should be able to provide info on the mother, at all points of care for the newborn.
- unknown person arrives in the emergency department unconscious. You’ll see imaging named John Doe in this case. The system should be able to retrieve info if and when identity is established.
- the machine-readable bracelet given at admission needs to be replaced. This is really two different cases: you intend to create a duplicate bracelet, or to correct an error in which all current info in the system needs to abruptly update.
One of the most surprising things I learned about the US healthcare system is how often everything is mutating or being retroactively updated (assume it applies to other countries too).
Things you'd think would be constant after Step 1 often aren't, and processes are tolerant of their being corrected / re-entered after Step 52 has already been completed.
Finding and eliminating duplicates is a very common software problem that is rarely solved in a reusable, user-friendly way that preserves history while eliminating redundant data.
In fact, in 40 years of working with computers I can't think of a single UI that I'd want to emulate.
Ultimately the key is the business-modeling that captures "duplicates" as Things That Happen.
That's the precondition for any sane UI, and sometimes it's not even obvious because the "duplication" has been transformed, reified into its own concept.
> how about a personal identification number? In Denmark we have the CPR number, and I understand that the US Social Security Number is vaguely analogous.
The US SSN is not guaranteed to be unique, the SSN assigned to a person could change, there is no guarantee that a person with an SSN assigned to them is a US citizen, and there is no guarantee that a US citizen has an SSN - they must be requested, and you don’t need one unless you do something that requires having one. There are also things called ITINs and ATINs that look like an SSN but are not, yet can be used in place of an SSN in a huge range of SSN-required situations!
The cases you listed do not mean SSNs are not unique, unless there are people who share the same SSN. You can still define a unique index for the SSN column. A column can be both nullable and unique as each null is different in SQL.
That happens all the time. First of all people steal SSN's and use them (and you are not the police, so it's not your responsibility to do anything about that). Second people make up fake SSN's because they don't want to give you their SSN.
People also make typo's, and you can end up with the same SSN.
> It’s not as uncommon as you might think. In fact, some 40 million SSNs are associated with multiple people, according to a 2010 study by ID Analytics.
I’ll add that there is a huge difference between the SSN database that the Social Security Administration maintains, and the list of SSNs that have been associated with a person. Especially because it is very common to change a single digit of your SSN when performing credit fraud - because they’ve already burned their real one. Some people will have dozens of SSNs attached to them.
IDA was very good at determining who a person is through the graphs that represent identities in our world (names, DOBs, phone numbers, addresses, SSNs, etc.)
> The most misused SSN of all time was (078-05-1120). In 1938, wallet manufacturer the E. H. Ferree company in Lockport, New York decided to promote its product by showing how a Social Security card would fit into its wallets. A sample card, used for display purposes, was inserted in each wallet. Company Vice President and Treasurer Douglas Patterson thought it would be a clever idea to use the actual SSN of his secretary, Mrs. Hilda Schrader Whitcher.
> The wallet was sold by Woolworth stores and other department stores all over the country. Even though the card was only half the size of a real card, was printed all in red, and had the word "specimen" written across the face, many purchasers of the wallet adopted the SSN as their own. In the peak year of 1943, 5,755 people were using Hilda's number. SSA acted to eliminate the problem by voiding the number and publicizing that it was incorrect to use it. (Mrs. Whitcher was given a new number.) However, the number continued to be used for many years. In all, over 40,000 people reported this as their SSN. As late as 1977, 12 people were found to still be using the SSN "issued by Woolworth."
As late as the 1970s, the first 3 digits of your SSN told what office issued your number to you. And the next 2 digits told workers at that office what filing cabinet held your application.
In Spain we have the DNI number, that a lot of people asume is unique, even database designers that use is as a natural key.
Turns out the DNI can have, and actually have, a lot of duplicates. The police has a page explaining it (https://citapreviadnipasaporte.es/dni/dni-duplicados-espana/), and how it's not a primary key in their databases, but a number entered manually from a pool of possible numbers. And number re-using is a possibility. They estimate the number of duplicates in 200,000 for a population of 50,000,000.
The point is that if you asume DNIs are unique and use them as PK your database is exposed to the bad design of the DNI database. There are some stores that use the DNI as the "unique" identifier for fidelity cards.
The user login (hopefully) is not the internal primary key for the user. It should be unique at a given point in time, obviously, but certainly there are reasons it might need to change.
And I've been bitten by this design numerous times. The most common bug: literally the birthday paradox, i.e. "these two people have been assigned the same number, and now we need to distinguish them in our database".
Also your DNI can change. Typically foreigners get a NIE (used for the same thing but a different format), and get a new DNI if they ever get Spanish nationality.
I remember having to deal with this and other identifiers like NIF and NIE while working on the academic titles homologation platform for the MCIN. I didn't understand why it wasn't used as, not necessarily PK, but an identifier logging in. Thanks for letting me know that the hellish time spent integrating Cl@ve wasn't in vain.
We have the same problem in Denmark, most people just don't realize it. At my dayjob we get at least one person every year who changes gender and consequently gets a new SSN (the final digit is supposed to signify gender). Most people don't store SSNs so they never realize, but it does happen fairly frequently.
- SSN from different ID space gets assigned to immigrants; when they become citizens they are assigned a new permanent SSN.
- SSN:s have a long and a short form; the short form which cuts off century information can be the same for someone who is 5 years old and someone who is 105 years old.
- When an unconscious patient comes in to the E.R. you don't know their SSN, so a temporary one is assigned for use in patient records. Such temporary SSN:s are not coordinated nation-wide so multiple patients may have the same SSN. In some hospitals they don't even have a local standard for ID:s. The staff just makes something up on the spot. It happens that the SSN they made up collides with a valid SSN for another person.
Also: the personnummer carries a date that often means birth date, but there are cases where it’s not, but I’ve seen a few system that just assumes it’s the same.
> SSN from different ID space gets assigned to immigrants; when they become citizens they are assigned a new permanent SSN
Having gone through that, my personummer didn’t change. Maybe that doesn’t happen anymore?
It does, but if you already have a Personnummer or get a residence permit right away so that you are eligible for one you don't get the temporary Samordningsnummer.
Immigrants and refugees will get a "replacement CPR number", which is outside the normal space/range of CPR numbers. Once registered as living in Denmark, they'll get a real CPR number.
Danish SSNs doesn't have a long or short form, you used the seventh digit to do a table lookup to see if the person is born in 18XY, 19XY or 20XY. The date of birth is always ddmmyy, there is no long form. So if the seventh digit is 9, and the 5-6 digit is between 00 and 36, then you're born in between 2000 and 2036, if the 5-6 digits are 37 to 99, then your born in the 1900. But you need the published table to figure that out.
Last point, there is backup system for unconscious patients, but it should be the same across all medical records as these are somewhat standardized.
> - SSN:s have a long and a short form; the short form which cuts off century information can be the same for someone who is 5 years old and someone who is 105 years old.
In Ireland, until the 90s, if a woman got married, she gave up her PPS number (essentially a social security number) and took her husband's, with a 'W' appended (this fact tells you a lot about pre-90s Ireland...) If she subsequently divorced or the husband died, she got a _new_ PPS number.
While this was abolished in the 90s, _some people still have these 'W' numbers_, ticking timebombs for anyone relying on them as a key.
Then I guess it could lead to duplicate records. Now, whether this is a problem or not depends on the business and how much one cares about data integrity. I work with higher education, so uniquely identifying students and keeping all their records organized is somewhat important. Granted, I don't work in the US, and here we have a unique, national number. So this is covered, except for foreign students. I was curious how this problem is solved in other countries.
The US government doesn't assume that it can uniquely identify a citizen with 100% certainty. When things get tricky, we rely on the judicial system to weigh the evidence and make a decision. Which could later get changed.
If you want to design your system to assume that you can do it, that's your problem. Literally, it is now your problem, and nobody is going to step in to help.
The population health database I work with uses generated IDs for people and relies on health systems to link records before submission (they put a lot of effort into data used for billing). But we do check for problems by looking at duplicates of SSN with date of birth. We don't consider names, because they could be transcribed differently. Even though the SSN+DOB pair should be unique, there have been cases where a widow provides her deceased husband's SSN. Likely because she used it for Social Security benefits and forgot her own long ago.
To give a slightly more technical answer, at a large insurance company I used to work for, the legal department had provided definitions on what conditions we would consider various components of PII a match. So between SSN, DoB, First Name, Last Name, and a couple others, there were potential combinations that our system would say, "yes this is the same person". Note that we didn't necessarily need exact matches on things like names, but "close enough" matches were sometimes sufficient.
You can get kind-of close with driver's license numbers. They will change when US citizens move among states, (and we do that quite frequently.)
(People who don't drive have to get state-issued IDs that have the same number. At least, they have to if they want to buy do anything that requires proof of identification.)
I used to work for one state's Department of Motor Vehicles.
Part of why REAL ID is such a charley foxtrot is because there exists no such identifier and since every state counts approximately as a sort-of-almost sovereign nation (this is basically what the 10th Amendment guarantees), there is and can be no possible nationwide citizen identifier.
There are about 3500 different counties in the US. All of which may or may not issue their own birth certificates. States are supposed to do that now.
Google Cloud projects have three attributes: user-friendly names, system numbers, and system names. System names are alphanumeric. They can be chosen by the user, derived from the friendly name if there's no collision.
But! There's some system names from the olden days that are actually all numbers - so not actually alpha-and-numeric. Thankfully we don't run into those often.
Yeah, these things are almost never as simple as they are supposed to be.
Poland has PESEL numbers since 70s. It was supposed to be unique, only apply to Polish citizens, never change, and have a checksum digit. Every Polish citizen gets one at 18 when they get their national ID document, and you can request it earlier if you want to.
Turns out there are duplicated PESEL numbers. A LOT of non-Polish citizens have them assigned (mostly Ukrainian refuges but not only). The checksums are sometimes wrong. And some people have several PESEL numbers.
If you used PESEL as database key you're fucked.
The system works perfectly, but it interfaces with external world through computer-human-paper-human-computer interface. And at some point the mistake propagates so far that it becomes the truth assumptions be damned.
Natural keys are (quite literally) essential to defining entities. Quine had a great slogan for this: "No entity without identity!" Natural keys are how you determine the identity of an object, which is to say, if you have two different referring expressions, how can you tell whether they are referring to the same object, or different objects?
Suppose you take the advice of this article, and use, say, social security numbers to identify people. (Let's ignore the fact that this only works for the U.S.) Suppose one fine day, somebody tries to enter in a record about a new person--but some typo has happened somewhere, and the new person's SSN clashes with somebody's SSN who is already in the system.
Sure, the database will notify you that something went wrong. But how do you know which social security number is correct, and which is incorrect? You will have to find a set of fields which uniquely identifies each person ANYWAYS. I.e. you'll have to differentiate them by name, birthday, place of birth, etc etc.
Now even worse!!! What if somebody attempts to add the same person TWICE to your database, but mistypes their social security number. Now your database can't even tell you that something has gone wrong. It will happily record duplicate or contradictory information about the same person--and in order to resolve the mess, again, you have to find out what uniquely identifies the people ANYWAYS.
Now, even worser than worse--what if you have taken the advice of this article, and you haven't bothered to identify a set of fields which are genuinely unique to each person. You just have a social security number and a name. How are you going to even going to correct the fact that John Smith is in your system twice, when you have ten John Smiths? How can you possibly tell which two John Smiths are the same John Smith?
Yeah, it take some time and careful thinking to properly come up with natural keys for your entities. But unless you do, you haven't actually specified your entities at all. This is one area where long years of experience really pay off. Expert data modelers spend years and decades honing their craft, observing the work of others, etc etc. Eventually they acquire the wisdom needed to be able to know what kind of information is really needed to nail down what kind of entity the database needs to know about.
> Suppose you take the advice of this article, and use, say, social security numbers to identify people.
You seen to have misunderstood the point of the article: the author is recommending NOT using the SSN (a natural key) for primary keys, and instead to use an artificial, automatically generated key, so that the SSN is decoupled from the record and can potentially be updated.
But SSN's are artificial, automatically generated keys. They are not natural keys, they cannot be natural keys for persons, because not every person has one.
A natural key is a set of attributes which an entity has even if it is not in your database.
It’s funny that indexing used to be unavailable when processing big data on Hadoop. Now that we have options to do so we now care. As tech evolves we adapt.
Another massive annoyance with natural keys - privacy. If your table's primary key contains personal information, that PII now infects every other table that holds a foreign key to that table.
Thank you for your comment. It spurred me to think about this issue more thoroughly in a way I hadn't, even though this comment may disagree -- to some extent -- with your perspective.
I have not seen clear guidelines about whether an organization's surrogate keys for persons are considered PII. (And this ambiguity has frustrated me for some time as I am unclear whether to take an aggressive or conservative view on labelling PII where I work.) When I have read the guidelines, it seems ambiguous but on balance I think it disagrees with your implied claim (that PII is not present in a table that uses a surrogate foreign key to a person/user.)
"Any information about an individual maintained by an
agency, including (1) any information that can be used to distinguish or trace an individual‘s identity, such as name, social security number, date and place of birth, mother‘s maiden name, or biometric records; and (2) any other information that is linked or linkable to an individual, such as medical,
educational, financial, and employment information."
A surrogate key associated with a person is arguably "any other information that is linked or linkable to an individual" meaning that all tables containing the surrogate key remain PII and remain "infected". It's true the surrogate key only allows linkability within the context of the data ecosystem in which it resides, but such distinctions (of "internal" to the system vs "external from the system") are not made in the language of the definition. Additionally, from a pure risk and PII disclosure impact, usually the whole database gets dumped, not just the "non-person" tables. If you have financial/medical transactions in table "A" and a personal numeric ID linking data to a person table "B", both tables contain PII, right?
From a privacy standpoint, if you can SQL JOIN the data to trace the person involved either within your system or even data reasonably obtainable outside your system, (or if an attacker can), it's PII.
Intranet IP addresses are called "linked PII" in section 3.2.2 of the above NIST guidelines for example, and NIST does define some related terms that would seem to apply to surrogate keys like:
* Distinguishable Information: Information that can be used to identify an individual.
* Linkable Information: Information about or related to an individual for which there is a possibility of logical association with other information about the individual.
* Linked Information: Information about or related to an individual that is logically associated with other information about the individual.
As a data engineer, the above interpretation means PII is in a zillion tables and labeling a table with a boolean indicator yes/no isn't that helpful. But as a policy person, NIST seems to be recommending gauging PII more at the system (not table) level and with a PII Confidentiality Impact Level of low/medium/high that takes into account the context and overall risk and that seems sensible. From a data cataloging standpoint (gauging what's "infected" to use your term), I think it's probably helpful to identify particular transactional tables or personal table as having "high" PII disclosure impact vs "low"; the presence of "infection" from a virus ("PII") is mostly irrelevant in a sufficiently large system where viruses/some PII is inevitable but what matters is the severity/impact. A zillion tables will be "low" (e.g. if most tables have audit column saying which user last changed a particular record) but certain transactional or user tables may be "high" and should be ...
Your perspective is very thorough and interesting, but I can simplify the matter a lot - yes, from my personal experience it absolutely does matter for privacy regulation whether you use natural or surrogate keys.
I've worked on systems in the biotech space where certain tables and certain columns in our dataset were considered PHI (personal health information), and others weren't. An auto-incrementing user ID does not qualify as PHI under HIPAA. Whereas a person's name or email address most certainly does (since, the very fact that a particular person had received lab testing at a particular lab, is itself considered PHI.)
Those PHI bits of data were scrubbed from the dataset that most regular employees had access to. Casually allowing all employees to have access to PHI would not have been compliant with the law.
The restrictions on what can even be logged (when there's a system error, for example) was very much controlled. "User 12345 experienced <some exception>" was perfectly fine (and greatly aided operational investigation, and customer support.)
Thanks for the reply. I will concede or defer to you in regards to PHI and HIPAA... it seems the philosophy behind HIPAA/PHI is very different than PII or GDPR. HIPPA is prescriptive. PII/GDPR are principle-based. HIPAA, it seems, has some text that it's not PHI if the risk is "very small" based on the opinion of someone with statistical expertise documents that it could be de-identified OR if the person avoids an explicit list of 18 things that it cares about (see items (A) through (R) on page 96 of
https://www.hhs.gov/sites/default/files/ocr/privacy/hipaa/ad... ).
One of the first 17 things might be a surrogate key ("account number") in one's system but if you look through the others, the rest are things like name, SSN, biometrics, IP#s, etc, which are definitely not surrogate keys.
The "OR" language makes the statistical expertise (and "principles" of privacy) irrelevant if you avoid the 18 things; that avoidance forms a "safe harbor" of sorts so you don't have to do any heavy thinking/lifting.
The 18th ("(R)") element of what is considered PHI does seem to refer to surrogate keys but in a manner which creates a clear carve-out/safe-harbor for them not being PHI. That 18th form of PHI is "Any other unique identifying
number, characteristic, or code, except as permitted by paragraph (c) of this section;"
But that paragraph (c) section seems indicate identifiers such as surrogate integer/guid keys kept within a system, as long as they are 1) not derived from an individual's information (ie like integer or UUID surrogate keys) and 2) which are maintained solely in the system are not considered as element-18-"R"-PHI:
"(c) Implementation specifications:
Re-identification. A covered entity may assign a code or other means of record identification to allow information de-identified under this section to be re-identified by the covered entity, provided that:
(1) Derivation. The code or other means of record identification is not derived from or related to information about the individual and is not otherwise capable of being
translated so as to identify the individual; and
(2) Security. The covered entity does not use or disclose the code or other means of record identification for any other purpose, and does not disclose the mechanism for re-
identification."
By my reading, a surrogate key maintained within a system is thus clearly not PHI under HIPAA. I never looked at the details of HIPAA until today since it hasn't applied much to my data and have been focused more on PII/GDPR. I appreciate you describing the context of your remarks.
The other difference between HIPAA and GDPR is compliance rules. Under GDPR it is not a problem if all your employees who have access to your database have access to PII (since, from the perspective of GDPR, the customer has given consent to share their information with your organization as a whole). Sharing with third parties outside your organization is where you get into trouble.
But under HIPAA, even your own employees need to have a specific, documented justification for accessing customer PHI. If they can do their job just as well by only accessing a non-PHI dataset, you're required by law to only allow them access to a scrubbed, non-PHI dataset.
I don't think GDPR is that different in that regard, you also have to minimize access to PII within the organization. See Article 25:
> The controller shall implement appropriate technical and organisational measures for ensuring that, by default, only personal data which are necessary for each specific purpose of the processing are processed. That obligation applies to the amount of personal data collected, the extent of their processing, the period of their storage and their accessibility. In particular, such measures shall ensure that by default personal data are not made accessible without the individual's intervention to an indefinite number of natural persons.
GDPR is indeed different in that regard. And I'm not sure how your quote says otherwise. "The employees of this company" are not an indefinite number of persons.
> SSN ... which are definitely not surrogate keys.
Surely SSN is a surrogate key? They are not naturally derived. The early ones were serial (i.e. an auto-incrementing field) and more recent ones are randomly generated (i.e. a UUID).
We do know because the database owner has openly talked about how the keys are derived. That still doesn't make it a good key for your database, but I can assure you that the world doesn't revolve around you. It is someone's surrogate key – therefore it is a surrogate key.
You seem to be insisting on a semantic interpretation that makes the term less useful. A third party's surrogate key should not be considered a surrogate key when in your system. It doesn't matter how that key was generated, only that the semantic meaning of the key is outside of your control.
Conceptually, any information created or consumed outside your organization is not valid as part of a surrogate key, so SSNs are not a surrogate key. Furthermore, any time you reveal the primary key, that key can become the thing that people come to depend upon to find that database row, which leads to the possibility that someday, someone will have an important need for some primary keys to change, even if the primary key was supposed to be a surrogate key. The longer a database lives, the more likely that surrogate keys morph into natural keys. That's what happened to SSNs.
> Conceptually, any information created or consumed outside your organization is not valid as part of a surrogate key, so SSNs are not a surrogate key.
SSNs are created within the organization. Maybe not within your organization, but nobody is talking about you. They are a surrogate key.
Agreed, and also they tend to be more guessable. Make a page available with an email address as the id and just watch the hackers use it to discover users of your service and attempt to log in as them.
Just to add on to the CPR number. As mentioned it contains a birthdate and a gender identification as well as a checksum.
Since many refugees don’t know (or can’t prove) their birthdate, they are given first of January. And enough first of Januaries are handed out that for some years there aren’t enough valid numbers, so numbers that fail the checksum are handed out too.
Here in Czech republic, everyone has an id number (rodné číslo) but as a foreigner, I have multiple id numbers. Further complicating matters, I was given a rč with the wrong gender (the gender is encoded in the number) and my nationality was at some point incorrectly listed as "Ireland". I do wonder if the Czech government thinks I am just two completely different people.
The title could probably be extended to 'You'll regret using natural keys as primary keys' and it would be right in some ways.
Personally, I've come to the conclusion that it's probably best to use both surrogate and natural keys. Surrogates (IDENTITYs, UUIDs what-have-you) as PK from a technical perspective and natural key as 'PK' from a business/data modeling perspective.
Natural keys are often the best you can do if you're trying to be partition tolerant.
With surrogate keys you end up with duplicate entities whenever a partition heals (supposing the entity appeared to both partitions while they were still separate).
A simpler and more compelling reason in my experience is that relating tables is so much easier with synthetic keys. You can decide how large your synthetic keys are based on uniqueness requirements, natural keys have their own size and format that would then be copied to make relations. And if uniqueness depends on several values to be unique then dealing with compound foreign keys isn't fun or efficient. At this point, there would have to be a very compelling reason to want to use natural keys to offset this and I've rarely seen them. A case that may come up is for a miscellaneous table to use the synthetic key from another table with another natural value that uniquely identifies a row. I wouldn't choose this route if you ever think you want to relate anything to rows in this table, they should be leaves related only to the synthetic key being used.
"Natural keys" just means that someone else can change them under your feet.
People change names, their phone numbers, passport numbers. Governments change their numbering schemes all the time. Corporations merge and split. Heck, even governments merge and split, surprisingly often, at the municipal level. All without your knowledge or consent. And you're left wondering why you suddenly have duplicate key errors in your database.
The only key that you can trust is one that you, and only you, control. That's the point of the surrogate key. Whether it's BIGINT or UUID v7 is beside the point.
567 comments
[ 2.6 ms ] story [ 331 ms ] threadEven if just for documenting issues it makes life easier. “Table whatever id 12345 is the record in question.”
I’ve just seen data / relationships change too much too often in new and interesting ways to believe in using a natural key.
Notice that even if you believe that you should 'never use natural keys', that is NOT the same thing as 'always add a generated synthetic key to every table as the primary key'. You should NOT always add a generated key to every table, even if you use a trash ORM that really wants you to do this.
However it will be way more painless to use a timestamp of the script starting point a natural key.
This way when shit happen you have a relevant and stable way to identify each occurrence. And timestamp is easily indexable and ordered.
The timestamp generated in a transaction is generally the time the transaction started, which means it is always the same within the same transaction.
That's both incorrect and entirely unwarranted.
I would add a different surrogate field though for the primary key. Because I've -always- discovered edge cases which break my original model.
In this situation I can think of 2. Firstly if the process triggers twice at the same time you have a problem (which happened to me in a case where a test suite fired up thousands of instances on a machine with many cores). Secondly twice a year with daylight savings (and when the owner of the system decided to change from local time to utc).
Is there an actual natural kh out there that will never fail me? Probably. But I'm tired of looking for it.
- Internet scanner that would use host IP as the primary key
- A table connecting two other tables using their primary keys as the composite primary key (when there can only be zero to one such connections)
(Disclaimer: not a Discord bot programmer.)
Suppose discord_user_id is an identifier like @Spivak#2024 that uniquely identifies the user. When Discord forces all users to change to unique ID's and eliminates the disambiguating numbers (or allows users to change them), then where does that leave you?
(For the unfamiliar: something like this happened. Not sure if those are really the "discord user id" used in bot service though, but suppose that they are for the sake of the argument.)
https://support.discord.com/hc/en-us/articles/206346498-Wher...
https://support.discord.com/hc/en-us/articles/206346498-Wher...
me too!
And NTL merged with Virgin in 2006, making it 18 years. Oh dear.
On the API side I know Steam has something like 3 different forms of auto generated alphanumerical account ID and I imagine that's what everything is really keyed off of on the back end.
For the group who have forged a career with natural keys, and never regretted it, more power to you. Great.
However to the rest of us, myself included, where ill-considered natural keys have caused endless opinions and suffering, my commiserations.
If I could send back one piece of advice to junior-me it would be to avoid natural primary keys. (Ideally with the corollary to avoid sequences, but that's another thread for another day.)
I ported my phone number away from them, but what if I ever go back? Will my old data be there? Including my old address? What if my phone number gets recycled, and some-one else gets that phone number and ports it to them? So many questions.
They are meant for identification, if anything it should be a benefit that they are easily guessable.
It increases the attack surface as an authorization vulnerability will allow an attacker to enumerate and access all records. Yes, it is security through obscurity, but a random (e.g. UUID) scheme makes it harder.
Thats like guessing a password 18 ASCII chars long.
Sure, what I meant was UUIDs are not supposed to be confidential information, unlike passwords. They are exposed in URLs and whatnot.
This was years ago and you don't see it as much anymore, but think autogenerated links to shitty CRM, ticketing, and project management software where the link is the query aka - Blahsoftware.local/info/bunchofgarbage?=userid+garbage+view+sensitiveinfo.html type stuff.
1. When duplication occurs and goes unnoticed because the natural key isn't being used, and then perhaps the problem is "corrected" by an administrator in a way that doesn't make sense.
For example, someone sets up an account with an street address A, then forgets they had it and sets up an account with street address B. They call and complain that they can't find an old order, or whatever, and the duplication is discovered. The administrator later clobbers one of the addresses but both are in use. A natural key (behaviorally speaking) may have presented the duplication, assuming address isn't part of the natural key. This can be satisfied by uniqueness constraints, etc.
2. When you are browsing the database, you see a bunch of synthetic keys and have to perform various joins to be able to see the relevant data. The synthetic keys make joins easier to write, but make may make ad-hoc queries more time-consuming and difficult.
Bearing that in mind:
1. Add a unique constraint on the natural key.
2. If the accuracy of joining by natural key is acceptable, join based on it. If that doesn't work, then a table design without a synthetic key wouldn't be fit for purpose either.
The first example is why `UPDATE CASCADE` was implemented. So it's possible to use natural keys as identity without the fear of children table. At least in most databases it works.
The drawback of enumeration is real, so if you expose this key you'll need some authentication/ authorization mecanism.
Another good thing in natural keys is that you can eliminate part of the joins. You don't have to join the father table because the key in child table is known.
I think the biggest challenge is how to map logins to people. It's very common to interpret both as the same, but they are not.
This is only true in a system using a single database, not replicating data in external services, and not offering APIs.
And while it might work today when the service/website is still fairly small and self contained, the requirements might change at any time. So the title (You will [future] regret it) still applies to this solution
An example is using a car's chassis number as the key for the record describing that vehicle.
If you were using some sort of string like an email address or username, you have to think about case sensitivity and trimming white space and all sorts of preprocessing and then make sure you do it consistently EVERYWHERE
- you need to do an adhoc query to look something up so you have to type in the key in a where clause
- Or you used different sanitation methods in two different databases and you need to join things now
- you try to join the table that had the sanitized email key to a different table that just so happens to have email but it wasn’t sanitized because it was an optional field and not the key
These of course should be caught by checks and balances, and you can’t count on anyone knowing what they need to know ten years after an implementation is done
Having a surrogate key here doesn’t help. A WHERE predicate can be rewritten as an INNER JOIN quite easily. Or you could use a subquery, or a CTE. Many options.
The other problems discussed are an engineering culture problem. Either you value correctness and consistency or you don’t.
`SELECT * FROM users WHERE email LIKE 'FirstName@domain.net'`
For the above, either ensure that all email addresses are stored in one case (and index / query on the same), or if that’s impossible for some reason, you can query with ILIKE, optionally creating a trigram index (for Postgres; this isn’t a problem in MySQL since by default it’s entirely case-insensitive) on the column. Another option is to index with a functional index (CREATE INDEX user_email_lowercase ON user (LOWER(email))), and then code the API doing these lookups to cast to lowercase. That way, it’ll be retrieved and displayed however the user entered it, but retrieved based only on the CI version.
Or you could make the email column case-insensitive, since it’s generally accepted to be CI anyway.
It is pretty common to assume that email addresses aren't case sensitive since many email providers treat them that way. Email addresses absolutely are case sensitive so you need to preserve case when storing them and be careful about case when using them.
This makes email addresses particularly unsuited to being used as a natural key. Most people treat them as case insensitive and you need to work around that, but you can't safely just treat them as case insensitive yourself.
An an footnote, I'll add this doesn't mean that you need to have complexities like record versioning, record history, or anything. But couched in those conceptual terms where those things are possible, is a happy and safe space to be. In this space, a database records entries, as if they were each a single paper form with boxes where you fill out, in pencil, to erase if you like or not, the particulars of the thing you're recording. This form comes pre-stamped with a number: the record key.
In this cozy little world, you can be imperfect, and mistakenly (or deliberately, as your use case requires), file multiple slips that refer to the same logical thing, and yet all have different file ("record") numbers - or keys.
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.
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.
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.
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.
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.
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
> 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.
True, if you start your application from scratch. Like if I started designing a new app today, I'd just choose the UUIDv7 for the primary key.
It's not an easy thing to add into an existing application, though. I see applications leaking their internal IDs all the time, but usually it's not worth the effort to fix that, because it's a comparatively minor problem.
I'm sorry that I didn't make that more clear. I saw that you mentioned it as a best practice and are aware of the advantages. It's just that there are so many others that don't have the balanced view as you seem to have.
I have been involved in many discussions at my work place where "security by obscurity" is used as a way to shut down discussions. They changed their minds about sequential keys after the incident I mentioned, but it still has the power to "win" other discussions. Sure, we need to have rate limiting on ip-addresses, auth and other mechanisms, but they are not perfect and bugs happen all the time. An "unguessable" id is an additional security layer
> It's not an easy thing to add into an existing application, though
I agree, but there are ways to reduce the attack surface. You could add an extra "public id" field that can be used for lookup in addition to the existing id. In this way you can have a gradual migration where you go through each endpoint and migrate them individually without changing the foreign keys and other relations in the database (they would still use the sequential key). Maybe you end up not having time to migrate them all, but at least you can reduce the attack surface on the most sensitive endpoints.
If you have low volume endpoints you could perhaps even simply add a mapping layer where you do a simple db lookup to replace the public key with the internal without changing the existing queries. You could even cache this easily to reduce the db load. (both ids are permanently fixed and can be cached forever).
If your system allows customers to see each other (or worse: unauthenticated users to see customers) in this fashion in the first place then whether you're using a sequential integer v. a random UUID is the least of your problems.
But it did give our competitor free highly qualified leads that they could use to poach customers. This product was new to our customers, and we had spent a lot of time selling and convincing them that it was useful.
Some might suggest, "encrypt your object identifiers on the way in/out", but there's a ton of pitfalls involved since for most applications they are now rolling their own crypto, and it also makes identifiers much longer.
Some day I may need to track multiple VINs for a vehicle (maybe it’s got parts from multiple VINs and I want to track that). I can still always decompose that table and have an n-to-1 relationship between cars and VINs without migrating the rest of my data model.
But the DBMS already maintains a row identifier (called rowid or ctid or whatever depending on the DBMS). Why do you need an explicit one?
The value returned by the function is not necessarily constant between queries as various operations performed on the database may result in changes to the row identifiers of a table.
So, users should refrain from using the ROWID function in ordinary situations; retrieval by primary key value should be used instead.
[1]: https://infocenter.sybase.com/help/index.jsp?topic=/com.syba...
https://sqlite.org/rowidtable.html says:
> If the rowid is not aliased by INTEGER PRIMARY KEY then it is not persistent and might change. In particular the VACUUM command will change rowids for tables that do not declare an INTEGER PRIMARY KEY. Therefore, applications should not normally access the rowid directly, but instead use an INTEGER PRIMARY KEY.
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.
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.
Corollary: your app is part of the real world.
This took me years to realize, and once I did things became much, much simpler.
Generate your own unique keys for everything; add a few more unique constraints if needed. A bit more work but never a regret.
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.
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.
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.
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.
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.
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.
This launches into the other debate about PKs: using UUIDs rather than sequential keys.
Don’t blow up your B+trees.
Either your system is happy enough to route every new entity through "one DB at scale" so it can let your "one DB at scale" be in charge of an auto-incrementing long, or it isn't.
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.
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.
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.
That is not what he is arguing at all. He is showing that there are very many highly plausible changes that are problematic with natural keys. And he totally correct about that. Frankly, the fact that a post arguing for natural keys makes it the top of an HN comment thread is extremely weird. The original article is correct that natural keys are bad.
Just in case, ORMs send DDL statements with uniqueness constraints to the DBMS, they don't do any magic here.
As far as I can tell, all modern systems allow it, as it is part of the SQL standard that foreign keys can be either primary keys or unique indexes. Here's a brief quotation from a copy of ISO/IEC 9075-2:1999 (not the latest version) that I randomly found online:
> If the <referenced table and columns> specifies a <reference column list>, then the set of <column name>s contained in that <reference column list> shall be equal to the set of <column name>s contained in the <unique column list> of a unique constraint of the referenced table.
So it mentions unique constraints first. Then afterward it says:
> If the <referenced table and columns> does not specify a <reference column list>, then the table descriptor of the referenced table shall include a unique constraint that specifies PRIMARY KEY.
If I'm reading this right, it means that in the base case, where you specify the column to reference, it can be any unique constraint, where a primary key is just another possible unique constraint (as all primary keys are by definition unique). And only if you don't specify the fields to reference does it then fall back to the primary key instead of a named unique constraint.
I'm not disagreeing with you entirely - it's true that often there's an assumption in database theory that primary keys are natural and foreign keys are primary keys. But this isn't a hard requirement in practice or in theory, and it partly depends on the foreign key's purpose, why you need it in the first place. This StackOverflow answer also explains it well: https://softwareengineering.stackexchange.com/a/254566
I should add that there is also a set of database design wisdom that suggests you should never use database constraints such as foreign keys, only app/api constraints, but that's a whole different tangent.
That’s less a DB design thought and more of a “devs with little formal training in RDBMS who only want to use it as a dumb store” thought.
Use the DB to its strengths. CHECK constraints add an infinitesimal amount of overhead for writes, and guarantee that there will never be invalid data written. A bad code change could allow, however briefly, for that to occur.
Data integrity/quality matters. Adding friction to prevent accidents also matters. I don't want something accidentally and trivially updating a field that's used to reference thing externally.
Something about the nat key is about to change? Fine, we can write migrations. Even if it affects millions of rows, it's not a big deal.
I understand people have been burnt by bad design decisions involving nat keys, but they're not some devil's key everyone here dogmatically makes them out to be. You can mess up using anything.
I think there's the important point. Is your key actually natural or is it someone else's surrogate key anyway? Going back to the vehicle identification number: that's already a surrogate key. You just did not assign it yourself.
A VIN encodes the following:
* Country of origin
* Manufacturer
* Vehicle type
* Vehicle model
* Engine type
* Transmission type
* Model year
* Manufacturing plant
* Serial number
I would still almost always use an internal artificial key on top of the external id. If you want to enforce data integrity, you can still enforce uniqueness on the external id. "Stability" of an external identifier is almost always an assumption and one that I've seen fail enough times to want that internal id by default.
At this point, you should verify the new email. At least until it is verified, you must track the old email. At this point, you realize you can now introduce a synthetic key and you're fine.
Let's say you have a duplicate customer entry and the customer demands their accounts be merged. Now you can't identify the user by their key alone, since by definition, they can't be the same (yet.)
Except for having to update all foreign references. Some of those may be external further complicating issues.
Emails are often among the worst keys because they are not terribly stable and they are reusable often enough to burn you.
The domain must always be case-insensitive.
We're almost all the way to a Tag URI[0], so you could combine it with the user's name or username or any other identifier that fits the spec[1] (you could even use the website's own name) and you have a (definitely two thirds, probably 100%) natural key.
It's stable over time and unique, easy to mint, and has a standard behind it. The user also gets to change their contact details without any problem related to the key.
[0] https://taguri.org/
[1] http://www.faqs.org/rfcs/rfc4151.html
Aside from that, it's not a violation of GDPR to keep personal information (that they consented to you having) in order to process business for that person. Using an email address as a unique identifier is not a violation, using it to spam them would be. If they're willing to give you their current email why not an old one?
How do you communicate with other people in your company about a customer without sending around PII if the customer's ID is PII?
Maybe we could create a field that uniquely identifies the customer that isn't PII. Then that could be used to uniquely identify a customer in places where we don't want to expose their PII. But then... why not just use this unique ID as the key?
It's so strange an argument. Right now you have my username but not my email address, yet you can still query the website database and get certain data that you're allowed to see. There are so many ways to query a particular user's data, and they would all depend on what you're trying to do, needing the specific key would mean you should have access to it anyway and it could be given on per case basis anyway.
It's not an int, but yes, we have a unique synthetic identifier that serves as the database PK and as a means of communicating about a customer in insecure channels without exposing PII. "Customer ID ### is having an issue with such-and-such."
To turn your second part back around: why a natural key? What is the function of minting a natural key if humans are meant to use something else?
Because non-natural keys are unnecessary in the presence of a natural key, and unnecessary things bring in complexity.
> "Customer ID ### is having an issue with such-and-such."
Then you need access to the customer's ID, but the devil here is in the detail you didn't add, the such-and-such.
> communicating about a customer in insecure channels
Use secure channels…
When it comes to PII at my company, secure channels means "encrypted email only". No Slack, no Jira, no chat in video calls.
That's just not feasible for 100% of communications.
None of the things you've presented are actually "natural" keys, they are pieces of information that you've made assumptions about to shoehorn them into being usable as a "natural key".
> Use secure channels…
No channel is perfectly secure. As channels become more secure, they become harder to use and add complexity. The more places you store customer data, the more risk you create. The attempt to force semantic data to serve as "natural key" has now added risk and complexity to your entire communication infrastructure.
Frequently yes. It is extremely common to communicate about specific records using the ID for that record. The fact that this sort of behavior is extremely common is pretty clearly indicated by the question itself.
> There are so many ways to query a particular user's data, and they would all depend on what you're trying to do, needing the specific key would mean you should have access to it anyway
A responsible organization at scale with limit and log access to customer data. I should be able to determine if two people are talking about the same customer record without needing access to that record's PII.
It is much better to have an artificial key that is linked to this data. There is no upside to the natural key and many, many downsides.
e.g. instead of mysocialmedia.com/users/2374927
you would be showing
mysocialmedia.com/users/email@example.com-2024-06-05-mysocialmedia.com
Then exposing a lot of information that you may have not wanted to expose.
And also if you are doing exception logging, for ids/primary keys there's higher odds of them being logged out, including your own logs and also external platforms.
It feels like having primary key set up like this just will complicate everything unnecessarily for the future including many edge cases that you don't foresee.
Just have the main ID not have any meaning.
It shouldn't contain information about the date, it shouldn't be auto increment, it should really be just random.
> It shouldn't contain information about the date, it shouldn't be auto increment, it should really be just random.
That’s a great way to tank performance. You want your PK to be k-sortable.
If we're talking about relational database engines, that's what they do, relate things. One join statement is much the same as another.
Sure, like the post poster you replied to is pointing out, you _can_ use natural keys, and then also relying on dates or other parts of the data - but creating a relation for that can end up being extremely cumbersome.
- Indexes generally become larger - relationships become harder to define and maintain - Harder for other developers to get up to speed on a project
Welcome to the Mr. Cooper mortgage provider website.
Your logon is your email and you can't change it.
If you used your cable provider email you're stuck with them for the life of your 30 year mortgage.
I've been very slooowly degoogling myself, and that includes changing all logins that have a gmail address to a different non google email.
I'd say only like 1/3 of the sites I made logins for have the option of changing the email.
Even if it's initially single, it's bad to assume that it will be so forever and that you are not going to use third party providers in the future.
How well does ON UPDATE CASCADE work if there's millions of existing relations to that entity?
And incrementing IDs are also problematic yes, since they hide business information data within them.
And I do think that I need it for much more than 1% of projects and DBs.
Anyone who says UUIDs aren’t a problem hasn’t dealt with them at scale (or doesn’t know what they’re looking at, and just upsizes the hardware).
> You don't pick UUIDs blindly, but on balance, they solve a lot more problems than they cause.
IME, this is precisely the problem – devs choose them blindly, because then you don’t have to think about proper modeling, you can arbitrarily create a key in your app and be nearly guaranteed of its uniqueness, etc.
So there is at least additional storage required, and probably some CPU cost as well. And even UUIDv7 isn't guaranteed to produce sequential IDs, but it's probably good enough to not seriously fragment your table storage.
But yes, UUIDs are usually stored as integers.
This is a myth. Planetscale [0] uses integers. They are assuredly at scale.
As for auto-incrementing, there are plenty of solutions for distributed systems.
[0]: https://github.com/planetscale/discussion/discussions/366
This is true for an internal PK that's an auto-inc id as well as for natural keys, though.
Using a natural PK -inside- your own database can still be a lot more pleasant to work with, even if you don't let it escape.
Until you need to do anything like described above. The advantage of artificial keys is that they have no semantic content. Anything with semantic content carries the risk that the role that semantic content plays in your system can change and cause problems. Having a non-semantic identifier protects you from that.
This is not to say that you should never use a semantic identifier as a key. However, you should always have a non-semantic artificial key as the identifier and use the semantic identifiers only when necessary.
I guess if you're trying to give a rule of thumb to juniors, "always have an auto-inc key in case you've misjudged whether the natural key is a good idea" is probably safest (though if you do tell them that, keep an eye out for them trying to add an auto-inc to things like a many-many join table which should've been PKed on the pair of FKs).
But every database design decision can potentially result in problems if the meaning or usage of part of the data changes, and while you should absolutely take that into account when selecting an initial design, adding complexity in case it's needed later is, itself, also a trade-off.
It used to be that "always use the natural key if there is one, because adding a surrogate key is denormalisation and should be done only when necessary" was a common rule, and that was also overly absolutist.
Basically, my rule of thumb is something like "use a natural key if you're confident that you can DBA your way through any required changes easily enough to make the advantages the rest of the time a net win overall" and I find that has better results than 'always' or 'never' would.
(I'd also note that "the role that semantic content plays" changing also applies to e.g. cardinality of relationships and when -those- change it's Interesting Times no matter what you used for PKs and FKs, so you have to have a plan for things like that anyway ... as ever, it's trade-offs all the way down)
- 1 or more data warehouses
- Other services storing said data (e.g. the user id will live in many databases in a service oriented architecture)
- External API integrators who have their own data stores totally out of your control that also have copies of parts of your data
- Job queues. It’s common to have jobs scheduled for the future on some other system (Redis, etc.) that say “do X for user with id Y in Z days”. If the “id” changes these fail
- Caches. Say I cache data by user email instead of a surrogate key, user changes their email, and another user signs up with the old email. Cache hits for the wrong user!
- etc.
Changing the primary key becomes an absolute nightmare project once data lives in multiple places like this, and in my experience it mostly does.
Having truly stable identity for your entities just solves so many future problems, and it’s SO easy to do. In almost all cases, natural PKs are really all downside, virtually zero upside, except slightly less storage.
Even worse than the verbose, repetitive, and error-prone conditions/joins was the few times when something big in the schema changed, requiring a new column be added to the compound key. We'd have to trawl through the codebase and add the new column to every query condition/join that used the compound key. It sucked.
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.
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).
Yes and no. It is the Axe of Theseus ;)
https://en.m.wikipedia.org/wiki/Ship_of_Theseus
> 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?
https://www.youtube.com/watch?v=BUl6PooveJE
Typically for the purposes of ownership. So it's really part of a a hierarchical identity scheme.
> You think your surrogate key will save you? It will not.
It definitely will, say my 20+ years of experience.
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.
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.
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.
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.
Yes, and normalize it. 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
> 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.
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?
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.
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.
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.
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.
Performance is another major argument for synthetic keys, as they are can be made sequential, which is rarely the case for natural keys.
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 :-)
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.
Think about these different intentions, each deserving an audit trail with different attached metadata:
- brand new MRN for a newborn. The system should be able to provide info on the mother, at all points of care for the newborn.
- unknown person arrives in the emergency department unconscious. You’ll see imaging named John Doe in this case. The system should be able to retrieve info if and when identity is established.
- the machine-readable bracelet given at admission needs to be replaced. This is really two different cases: you intend to create a duplicate bracelet, or to correct an error in which all current info in the system needs to abruptly update.
Things you'd think would be constant after Step 1 often aren't, and processes are tolerant of their being corrected / re-entered after Step 52 has already been completed.
That's the precondition for any sane UI, and sometimes it's not even obvious because the "duplication" has been transformed, reified into its own concept.
The US SSN is not guaranteed to be unique, the SSN assigned to a person could change, there is no guarantee that a person with an SSN assigned to them is a US citizen, and there is no guarantee that a US citizen has an SSN - they must be requested, and you don’t need one unless you do something that requires having one. There are also things called ITINs and ATINs that look like an SSN but are not, yet can be used in place of an SSN in a huge range of SSN-required situations!
(Please don’t use the SSN as a database key!)
The cases you listed do not mean SSNs are not unique, unless there are people who share the same SSN. You can still define a unique index for the SSN column. A column can be both nullable and unique as each null is different in SQL.
That happens all the time. First of all people steal SSN's and use them (and you are not the police, so it's not your responsibility to do anything about that). Second people make up fake SSN's because they don't want to give you their SSN.
People also make typo's, and you can end up with the same SSN.
An SSN is not unique in the real world.
Note that in less than 100 years, more than half of all possible SSNs have already been used…
https://www.pcworld.com/article/424392/a-tale-of-two-women-s...
I’ll add that there is a huge difference between the SSN database that the Social Security Administration maintains, and the list of SSNs that have been associated with a person. Especially because it is very common to change a single digit of your SSN when performing credit fraud - because they’ve already burned their real one. Some people will have dozens of SSNs attached to them.
IDA was very good at determining who a person is through the graphs that represent identities in our world (names, DOBs, phone numbers, addresses, SSNs, etc.)
> The wallet was sold by Woolworth stores and other department stores all over the country. Even though the card was only half the size of a real card, was printed all in red, and had the word "specimen" written across the face, many purchasers of the wallet adopted the SSN as their own. In the peak year of 1943, 5,755 people were using Hilda's number. SSA acted to eliminate the problem by voiding the number and publicizing that it was incorrect to use it. (Mrs. Whitcher was given a new number.) However, the number continued to be used for many years. In all, over 40,000 people reported this as their SSN. As late as 1977, 12 people were found to still be using the SSN "issued by Woolworth."
https://www.ssa.gov/history/ssn/misused.html
As late as the 1970s, the first 3 digits of your SSN told what office issued your number to you. And the next 2 digits told workers at that office what filing cabinet held your application.
https://www.ssa.gov/policy/docs/ssb/v45n11/v45n11p29.pdf
Turns out the DNI can have, and actually have, a lot of duplicates. The police has a page explaining it (https://citapreviadnipasaporte.es/dni/dni-duplicados-espana/), and how it's not a primary key in their databases, but a number entered manually from a pool of possible numbers. And number re-using is a possibility. They estimate the number of duplicates in 200,000 for a population of 50,000,000.
The point is that if you asume DNIs are unique and use them as PK your database is exposed to the bad design of the DNI database. There are some stores that use the DNI as the "unique" identifier for fidelity cards.
- SSN from different ID space gets assigned to immigrants; when they become citizens they are assigned a new permanent SSN.
- SSN:s have a long and a short form; the short form which cuts off century information can be the same for someone who is 5 years old and someone who is 105 years old.
- When an unconscious patient comes in to the E.R. you don't know their SSN, so a temporary one is assigned for use in patient records. Such temporary SSN:s are not coordinated nation-wide so multiple patients may have the same SSN. In some hospitals they don't even have a local standard for ID:s. The staff just makes something up on the spot. It happens that the SSN they made up collides with a valid SSN for another person.
> SSN from different ID space gets assigned to immigrants; when they become citizens they are assigned a new permanent SSN
Having gone through that, my personummer didn’t change. Maybe that doesn’t happen anymore?
Danish SSNs doesn't have a long or short form, you used the seventh digit to do a table lookup to see if the person is born in 18XY, 19XY or 20XY. The date of birth is always ddmmyy, there is no long form. So if the seventh digit is 9, and the 5-6 digit is between 00 and 36, then you're born in between 2000 and 2036, if the 5-6 digits are 37 to 99, then your born in the 1900. But you need the published table to figure that out.
Last point, there is backup system for unconscious patients, but it should be the same across all medical records as these are somewhat standardized.
We don't do that. instead, we shove that extra bit of information into the digit following the last two digits through a table: https://da.wikipedia.org/wiki/CPR-nummer#Under_eller_over_10...
While this was abolished in the 90s, _some people still have these 'W' numbers_, ticking timebombs for anyone relying on them as a key.
a slightly more serious answer: you largely don’t. The US doesn’t have a national ID, proof of birth is not even close to standardized, etc.
If you want to design your system to assume that you can do it, that's your problem. Literally, it is now your problem, and nobody is going to step in to help.
That's why everybody use SSN.
(People who don't drive have to get state-issued IDs that have the same number. At least, they have to if they want to buy do anything that requires proof of identification.)
I used to work for one state's Department of Motor Vehicles.
Part of why REAL ID is such a charley foxtrot is because there exists no such identifier and since every state counts approximately as a sort-of-almost sovereign nation (this is basically what the 10th Amendment guarantees), there is and can be no possible nationwide citizen identifier.
There are about 3500 different counties in the US. All of which may or may not issue their own birth certificates. States are supposed to do that now.
Google Cloud projects have three attributes: user-friendly names, system numbers, and system names. System names are alphanumeric. They can be chosen by the user, derived from the friendly name if there's no collision.
But! There's some system names from the olden days that are actually all numbers - so not actually alpha-and-numeric. Thankfully we don't run into those often.
Poland has PESEL numbers since 70s. It was supposed to be unique, only apply to Polish citizens, never change, and have a checksum digit. Every Polish citizen gets one at 18 when they get their national ID document, and you can request it earlier if you want to.
Turns out there are duplicated PESEL numbers. A LOT of non-Polish citizens have them assigned (mostly Ukrainian refuges but not only). The checksums are sometimes wrong. And some people have several PESEL numbers.
If you used PESEL as database key you're fucked.
The system works perfectly, but it interfaces with external world through computer-human-paper-human-computer interface. And at some point the mistake propagates so far that it becomes the truth assumptions be damned.
Suppose you take the advice of this article, and use, say, social security numbers to identify people. (Let's ignore the fact that this only works for the U.S.) Suppose one fine day, somebody tries to enter in a record about a new person--but some typo has happened somewhere, and the new person's SSN clashes with somebody's SSN who is already in the system.
Sure, the database will notify you that something went wrong. But how do you know which social security number is correct, and which is incorrect? You will have to find a set of fields which uniquely identifies each person ANYWAYS. I.e. you'll have to differentiate them by name, birthday, place of birth, etc etc.
Now even worse!!! What if somebody attempts to add the same person TWICE to your database, but mistypes their social security number. Now your database can't even tell you that something has gone wrong. It will happily record duplicate or contradictory information about the same person--and in order to resolve the mess, again, you have to find out what uniquely identifies the people ANYWAYS.
Now, even worser than worse--what if you have taken the advice of this article, and you haven't bothered to identify a set of fields which are genuinely unique to each person. You just have a social security number and a name. How are you going to even going to correct the fact that John Smith is in your system twice, when you have ten John Smiths? How can you possibly tell which two John Smiths are the same John Smith?
Yeah, it take some time and careful thinking to properly come up with natural keys for your entities. But unless you do, you haven't actually specified your entities at all. This is one area where long years of experience really pay off. Expert data modelers spend years and decades honing their craft, observing the work of others, etc etc. Eventually they acquire the wisdom needed to be able to know what kind of information is really needed to nail down what kind of entity the database needs to know about.
You seen to have misunderstood the point of the article: the author is recommending NOT using the SSN (a natural key) for primary keys, and instead to use an artificial, automatically generated key, so that the SSN is decoupled from the record and can potentially be updated.
A natural key is a set of attributes which an entity has even if it is not in your database.
I have not seen clear guidelines about whether an organization's surrogate keys for persons are considered PII. (And this ambiguity has frustrated me for some time as I am unclear whether to take an aggressive or conservative view on labelling PII where I work.) When I have read the guidelines, it seems ambiguous but on balance I think it disagrees with your implied claim (that PII is not present in a table that uses a surrogate foreign key to a person/user.)
The definition of PII per NIST https://nvlpubs.nist.gov/nistpubs/legacy/sp/nistspecialpubli... is:
"Any information about an individual maintained by an agency, including (1) any information that can be used to distinguish or trace an individual‘s identity, such as name, social security number, date and place of birth, mother‘s maiden name, or biometric records; and (2) any other information that is linked or linkable to an individual, such as medical, educational, financial, and employment information."
A surrogate key associated with a person is arguably "any other information that is linked or linkable to an individual" meaning that all tables containing the surrogate key remain PII and remain "infected". It's true the surrogate key only allows linkability within the context of the data ecosystem in which it resides, but such distinctions (of "internal" to the system vs "external from the system") are not made in the language of the definition. Additionally, from a pure risk and PII disclosure impact, usually the whole database gets dumped, not just the "non-person" tables. If you have financial/medical transactions in table "A" and a personal numeric ID linking data to a person table "B", both tables contain PII, right?
From a privacy standpoint, if you can SQL JOIN the data to trace the person involved either within your system or even data reasonably obtainable outside your system, (or if an attacker can), it's PII.
Intranet IP addresses are called "linked PII" in section 3.2.2 of the above NIST guidelines for example, and NIST does define some related terms that would seem to apply to surrogate keys like: * Distinguishable Information: Information that can be used to identify an individual. * Linkable Information: Information about or related to an individual for which there is a possibility of logical association with other information about the individual. * Linked Information: Information about or related to an individual that is logically associated with other information about the individual.
As a data engineer, the above interpretation means PII is in a zillion tables and labeling a table with a boolean indicator yes/no isn't that helpful. But as a policy person, NIST seems to be recommending gauging PII more at the system (not table) level and with a PII Confidentiality Impact Level of low/medium/high that takes into account the context and overall risk and that seems sensible. From a data cataloging standpoint (gauging what's "infected" to use your term), I think it's probably helpful to identify particular transactional tables or personal table as having "high" PII disclosure impact vs "low"; the presence of "infection" from a virus ("PII") is mostly irrelevant in a sufficiently large system where viruses/some PII is inevitable but what matters is the severity/impact. A zillion tables will be "low" (e.g. if most tables have audit column saying which user last changed a particular record) but certain transactional or user tables may be "high" and should be ...
I've worked on systems in the biotech space where certain tables and certain columns in our dataset were considered PHI (personal health information), and others weren't. An auto-incrementing user ID does not qualify as PHI under HIPAA. Whereas a person's name or email address most certainly does (since, the very fact that a particular person had received lab testing at a particular lab, is itself considered PHI.)
Those PHI bits of data were scrubbed from the dataset that most regular employees had access to. Casually allowing all employees to have access to PHI would not have been compliant with the law.
The restrictions on what can even be logged (when there's a system error, for example) was very much controlled. "User 12345 experienced <some exception>" was perfectly fine (and greatly aided operational investigation, and customer support.)
One of the first 17 things might be a surrogate key ("account number") in one's system but if you look through the others, the rest are things like name, SSN, biometrics, IP#s, etc, which are definitely not surrogate keys.
The "OR" language makes the statistical expertise (and "principles" of privacy) irrelevant if you avoid the 18 things; that avoidance forms a "safe harbor" of sorts so you don't have to do any heavy thinking/lifting.
The 18th ("(R)") element of what is considered PHI does seem to refer to surrogate keys but in a manner which creates a clear carve-out/safe-harbor for them not being PHI. That 18th form of PHI is "Any other unique identifying number, characteristic, or code, except as permitted by paragraph (c) of this section;"
But that paragraph (c) section seems indicate identifiers such as surrogate integer/guid keys kept within a system, as long as they are 1) not derived from an individual's information (ie like integer or UUID surrogate keys) and 2) which are maintained solely in the system are not considered as element-18-"R"-PHI:
"(c) Implementation specifications: Re-identification. A covered entity may assign a code or other means of record identification to allow information de-identified under this section to be re-identified by the covered entity, provided that: (1) Derivation. The code or other means of record identification is not derived from or related to information about the individual and is not otherwise capable of being translated so as to identify the individual; and (2) Security. The covered entity does not use or disclose the code or other means of record identification for any other purpose, and does not disclose the mechanism for re- identification."
By my reading, a surrogate key maintained within a system is thus clearly not PHI under HIPAA. I never looked at the details of HIPAA until today since it hasn't applied much to my data and have been focused more on PII/GDPR. I appreciate you describing the context of your remarks.
But under HIPAA, even your own employees need to have a specific, documented justification for accessing customer PHI. If they can do their job just as well by only accessing a non-PHI dataset, you're required by law to only allow them access to a scrubbed, non-PHI dataset.
> The controller shall implement appropriate technical and organisational measures for ensuring that, by default, only personal data which are necessary for each specific purpose of the processing are processed. That obligation applies to the amount of personal data collected, the extent of their processing, the period of their storage and their accessibility. In particular, such measures shall ensure that by default personal data are not made accessible without the individual's intervention to an indefinite number of natural persons.
Surely SSN is a surrogate key? They are not naturally derived. The early ones were serial (i.e. an auto-incrementing field) and more recent ones are randomly generated (i.e. a UUID).
If you use data as a key, then that is a natural key, if you invent a value to use as an identifier, that is an artificial or surrogate key.
If an API provides you an ID for a record, that is data. If you use it as a key, that is also a natural key in your system.
It may not be your surrogate key, but it is someone's!
SSNs are created within the organization. Maybe not within your organization, but nobody is talking about you. They are a surrogate key.
Since many refugees don’t know (or can’t prove) their birthdate, they are given first of January. And enough first of Januaries are handed out that for some years there aren’t enough valid numbers, so numbers that fail the checksum are handed out too.
With surrogate keys you end up with duplicate entities whenever a partition heals (supposing the entity appeared to both partitions while they were still separate).
People change names, their phone numbers, passport numbers. Governments change their numbering schemes all the time. Corporations merge and split. Heck, even governments merge and split, surprisingly often, at the municipal level. All without your knowledge or consent. And you're left wondering why you suddenly have duplicate key errors in your database.
The only key that you can trust is one that you, and only you, control. That's the point of the surrogate key. Whether it's BIGINT or UUID v7 is beside the point.