best practice for primary key constraints?

Question

I wonder what would be best in a postgresql database when using primary keys.

IF there is a table with two UUID's columns like user1 and user2, what would be better, to use a constraint key or just define both columns as unique without any primary key, because in postgresql you can only have one primary key in a table?

Example:

CREATE TABLE user_relation(
    sender_user_id UUID NOT NULL,
    receiver_user_id UUID NOT NULL,
    status INT NOT NULL,
    chat_id BIGINT,
    UNIQUE (chat_id),
    CONSTRAINT user_relation_pk UNIQUE(sender_user_id, receiver_user_id)

Or would it be better to just define the both 'primary keys' as unique values like that?

CREATE TABLE user_relation(
    sender_user_id UUID NOT NULL,
    receiver_user_id UUID NOT NULL,
    status INT NOT NULL,
    chat_id BIGINT,
    UNIQUE (sender_user_id, receiver_user_id, chat_id)
)

Neither. Create a synthetic/surrogate key for each table and make that the primary key. — Robert Harvey, May 26 '19 at 18:49
Your text isn't clear re exactly what column sets are UNIQUE, we only know some from your first code block's declarations. So it's not clear what your question is. PS The second constraint does not say the same thing as the first. It is weaker. It only says triplets are unique. PS As a constraint SQL PK means UNIQUE NOT NULL. Declare UNIQUE column sets that don't contain smaller such column sets; the smaller ones imply the larger. You can declare one with all NOT NULL columns as PK instead. A DBMS might use PKs for other things. Read the manual. PS This is almost certainly a faq. — philipxy, May 27 '19 at 02:03
Possible duplicate of [What's the best practice for primary keys in tables?](https://stackoverflow.com/questions/337503/whats-the-best-practice-for-primary-keys-in-tables) — philipxy, May 27 '19 at 02:06
*"...what would be better, to use a constraint key or just define both columns as unique without any primary key, because in postgresql you can only have one primary key in a table?"* You can have only one primary key in a table, but a primary key can have more than one column, exactly like a unique constraint can have more than one column. — Mike Sherrill 'Cat Recall', May 27 '19 at 14:27

score 0 · Answer 1 · answered May 27 '19 at 14:08

Your two table definitions are quite different.

UNIQUE(a, b, c)

does not mean that each of these columns will be unique, but that the combination of all three will be unique (i.e., you cannot have the same triple in the table twice).

Technically speaking, a primary key is no more than a unique constraint with added NOT NULL, so it does not matter which of them you use as a primary key.

Different from many others, I do not believe that ist is always necessary to introduce an artificial primary key. As long as you are sure that

the chosen column(s) feel(s) like a “good representation” of the data.
the value for the column(s) will never change

I see no problem with using that as a primary key.

best practice for primary key constraints?

1 Answers1