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.)
- 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.
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)
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