Hacker News new | past | comments | ask | show | jobs | submit login

This isn’t a new principle, it was part of database design courses in the early 2000s at least. However from a couple of decades of bitter experience I say external keys should never be your primary keys. They’ll always change for some reason.

Yes you can create your tables with ON UPDATE CASCADE foreign keys, but are those really the only places the ID is used?

Sometimes your own data does present a natural key though, so if it’s fully within your control then it’s a decent idea to use.






Even internal keys.

For example, suppose you have an information management system where the user can define their own logical fields or attributes. Naturally those names should uniquely identify a field. That makes them an easy candidate for a natural key. But I would still use a surrogate key.

I've worked in two systems that had this feature. In one the field name was a primary key, and in the other they used a surrogate key and a separate uniqueness constraint. In both a requirement to let users rename fields was added later. In the one that used a surrogate key, this was an easy and straightforward change.

In the one that used field name as a natural key, the team decided that implementing the feature wasn't feasible. The name change operation, with its cascade of values needing to be updated for all the foreign key relationships, would have been way too big a transaction to be doing on the fly, in a live database, during high usage times of day. So instead they added a separate "display name" field and updated all the queries to coalesce that with the "official" name. Which is just ugly, and also horribly confusing. Especially in oddball cases where users did something like swapping the names of two fields.


Advice used to be that "natural keys can only be primary if they don't change" but there was always an exception to the rule after a few months.

On the other hand, I remember a CEO wanting to be #1 in the database, so even non-natural primary keys can change... haha.


> but are those really the only places the ID is used?

I'm curious, where else would they be used?


Logs, metrics, data analytics, sent to third parties as identifiers. Unless you have a system small enough that you, or a few people who can be trusted, can know every component your IDs will leak out somewhere.

what you need is to add temporality to your tables. Then your logs/dependencies will work just fine

Use both. For each table, I use an internal id that is auto generated, and an external uuid.



Guidelines | FAQ | Lists | API | Security | Legal | Apply to YC | Contact

Search: