<story><title>You&apos;ll regret using natural keys</title><url>https://blog.ploeh.dk/2024/06/03/youll-regret-using-natural-keys/</url></story><parent_chain><item><author>yashap</author><text>This is bad advice.&lt;p&gt;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.&lt;p&gt;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.&lt;p&gt;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.&lt;p&gt;There are countless examples like this. You brought up cars, same thing with licence plates, for example. Or even Social Security Numbers&amp;#x2F;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.&lt;p&gt;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&amp;#x2F;indexes.&lt;p&gt;I challenge you to come up with a single plausible example where you’re screwing yourself by choosing surrogate PK + unique constraints&amp;#x2F;indexes. Meanwhile there are endless examples where you’re screwing yourself by choosing natural PK.</text></item><item><author>jklowden</author><text>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.&lt;p&gt;The natural key forces you to think about what makes the row unique. What &lt;i&gt;identifies&lt;/i&gt; 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 &amp;quot;not exists&amp;quot; question.&lt;p&gt;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 &amp;quot;new id&amp;quot; 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.&lt;p&gt;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.&lt;p&gt;Model your data on the real world. Do not depend on spherical horses.</text></item></parent_chain><comment><author>bioneuralnet</author><text>My first job in the late 2000&amp;#x27;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.&lt;p&gt;Even worse than the verbose, repetitive, and error-prone conditions&amp;#x2F;joins was the few times when something big in the schema changed, requiring a new column be added to the compound key. We&amp;#x27;d have to trawl through the codebase and add the new column to every query condition&amp;#x2F;join that used the compound key. It sucked.</text></comment>
<story><title>You&apos;ll regret using natural keys</title><url>https://blog.ploeh.dk/2024/06/03/youll-regret-using-natural-keys/</url></story><parent_chain><item><author>yashap</author><text>This is bad advice.&lt;p&gt;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.&lt;p&gt;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.&lt;p&gt;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.&lt;p&gt;There are countless examples like this. You brought up cars, same thing with licence plates, for example. Or even Social Security Numbers&amp;#x2F;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.&lt;p&gt;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&amp;#x2F;indexes.&lt;p&gt;I challenge you to come up with a single plausible example where you’re screwing yourself by choosing surrogate PK + unique constraints&amp;#x2F;indexes. Meanwhile there are endless examples where you’re screwing yourself by choosing natural PK.</text></item><item><author>jklowden</author><text>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.&lt;p&gt;The natural key forces you to think about what makes the row unique. What &lt;i&gt;identifies&lt;/i&gt; 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 &amp;quot;not exists&amp;quot; question.&lt;p&gt;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 &amp;quot;new id&amp;quot; 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.&lt;p&gt;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.&lt;p&gt;Model your data on the real world. Do not depend on spherical horses.</text></item></parent_chain><comment><author>arnorhs</author><text>Another benefit of having stable identies &amp;#x2F; surrogate primary keys is that any relations (FKs) will be much simpler.&lt;p&gt;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.&lt;p&gt;- Indexes generally become larger
- relationships become harder to define and maintain
- Harder for other developers to get up to speed on a project</text></comment>