0

What purpose would a primary key column serve on a table such as post_versions here? Nothing refers to it, and there are no queries where I will ever want to select a row by post_versions.id. It'll be joined to posts in most queries.

CREATE TABLE posts (
    id       SERIAL PRIMARY KEY,
    created  TIMESTAMPTZ NOT NULL DEFAULT now(),
    user_id  INTEGER NOT NULL REFERENCES users ON DELETE RESTRICT
);

CREATE TABLE post_versions (
    id       SERIAL PRIMARY KEY, -- serves no purpose, could remove I think
    post_id  INTEGER NOT NULL REFERENCES posts ON DELETE CASCADE,
    updated  TIMESTAMPTZ NOT NULL DEFAULT now(),
    body     TEXT NOT NULL
);

Are there any problems if I just remove it?

  • If you remove the id then what's your primary key of that table? – a_horse_with_no_name Mar 4 at 14:52
  • It is a good idea to first identify the candidate keys, and then decide whether to add a surrogate key or not. I can imagine that post_id + updated may be a candidate key. If it is good enough? – Lennart Mar 4 at 22:16
0

there are no queries where I will ever want to select a row by post_versions.id

If you have a column that you know you'll never have any use for whatsoever, be it a synthetic key or any other kind of column, then sure, remove it.

The problem with a table without a primary key, is that it can potentially have duplicate rows, and, in the relational model, duplicate tuples do not exist. So if your table happens to have duplicate tuples, it would violate a fundamental rule of the relational model.

But that's not necessarily a reason to create a synthetic key that you have no use for, for the sole purpose of having a primary key. Firstly, the way your table is structured with its updated column, it's very unlikely that it will ever have duplicates. Secondly, even if it happened, the consequences of having duplicates must be evaluated relatively to what is done with the data. When for instance a table is used to store log entries to be only displayed, there is no problem having duplicates. On the other hand, if an application had to update some specific row for some reason , then the absence of any primary key would be a problem and probably reveal a design error.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.