4 ms·

> 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

by maweki 2y ago

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

cjfd 2y ago
"for every conceivable change". 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.
zelphirkalt 2y ago
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.
- sgarland 2y ago
  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.
- a57721 2y ago
  > At least from ORMs I know I can set for example any group of attributes unique Just in case, ORMs send DDL statements with uniqueness constraints to the DBMS, they don't do any magic here.
lstamour 2y ago
> 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 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.
- sgarland 2y ago
  > 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.
spoiler 2y ago
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.
- maweki 2y ago
  > a service that provides you with a stable id 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.
  - sgarland 2y ago
    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). A VIN encodes the following: * Country of origin * Manufacturer * Vehicle type * Vehicle model * Engine type * Transmission type * Model year * Manufacturing plant * Serial number
- shkkmo 2y ago
  > 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.

by maweki 2y ago

cjfd 2y ago

"for every conceivable change". 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.

zelphirkalt 2y ago

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.

sgarland 2y ago
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.
a57721 2y ago
> At least from ORMs I know I can set for example any group of attributes unique Just in case, ORMs send DDL statements with uniqueness constraints to the DBMS, they don't do any magic here.

lstamour 2y ago

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

sgarland 2y ago
> 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.

spoiler 2y ago

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.

maweki 2y ago
> a service that provides you with a stable id 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.
- sgarland 2y ago
  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). A VIN encodes the following: * Country of origin * Manufacturer * Vehicle type * Vehicle model * Engine type * Transmission type * Model year * Manufacturing plant * Serial number
shkkmo 2y ago
> 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.