Re:Auto increment is a terrible idea
by dragonwriter on Thursday November 30, 2017 @10:00AM
(#15816192)
> A relational database is a graph where nodes are called entities and edges relations.
No, it's not. That's a common rationalization of the term by people who have zero knowledge of relational DB theory, but a relation is answer of entities, not a relationship between them; do, in this graph interpretation, “relation” isn't the right name for an edge, as it names a set of structurally similar nodes. An edge is a “(foreign key) relationship”.
> The role of a primary key is to provide a stable, indexable reference to an entity. You have two ways to construct your primary key: either as a semantic key, or as a technical key.
Why is this piece inventing new language when there are existing, widely recognized terms? This is the classic ”natural key” vs. “surrogate key” distinction.
> Also, serial is a 32 bit integer.
There are good reasons to avoid autoincrement, but that's not one, it’s just a reason to use bigserial instead of serial.
> If you don't have mutable state, then you don't need a technical primary key, and you can use a semantic key.
Even if you have mutable state, you can use a natural (“semantic”) key instead of surrogate (“technical”) key so long as the identifying attributes are immutable (which, if they are genuinely identifying, they logically must be.)
No, it's not. That's a common rationalization of the term by people who have zero knowledge of relational DB theory, but a relation is answer of entities, not a relationship between them; do, in this graph interpretation, “relation” isn't the right name for an edge, as it names a set of structurally similar nodes. An edge is a “(foreign key) relationship”.
> The role of a primary key is to provide a stable, indexable reference to an entity. You have two ways to construct your primary key: either as a semantic key, or as a technical key.
Why is this piece inventing new language when there are existing, widely recognized terms? This is the classic ”natural key” vs. “surrogate key” distinction.
> Also, serial is a 32 bit integer.
There are good reasons to avoid autoincrement, but that's not one, it’s just a reason to use bigserial instead of serial.
> If you don't have mutable state, then you don't need a technical primary key, and you can use a semantic key.
Even if you have mutable state, you can use a natural (“semantic”) key instead of surrogate (“technical”) key so long as the identifying attributes are immutable (which, if they are genuinely identifying, they logically must be.)