Postgres: Unable to set primary key on a column

Question

I am trying to set primary key on a column (which references a parent table), but I get an error

CREATE TABLE IF NOT EXISTS matches
(
    id serial,
    user_id PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE, 
    matched_profile REFERENCES profiles(id) ON DELETE CASCADE,  
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_on TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (user_id, matched_profile)  
);

Not sure what's wrong here, if anyone could explain. Thanks!

you are missing the data type for `user_id` and `matched_profile` — a_horse_with_no_name, Jan 15 '22 at 20:23
Btw: the use of `serial` [is discouraged](https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_serial) in favor of standard compliant `identity` columns — a_horse_with_no_name, Jan 15 '22 at 20:25
Thanks alot, can you explain what should I use instead of serial? I need a unique identifier for all the rows (maybe in future a use case might come) — Karan Kumar, Jan 15 '22 at 20:27
@KaranKumar From the link: *identity columns should be used instead*. Please check [this question](https://stackoverflow.com/questions/55300370/postgresql-serial-vs-identity). — astentx, Jan 15 '22 at 20:53

score 0 · Answer 1 · answered Jan 15 '22 at 21:02

0

Firstly you are missing data types for user_id and matched_profile.

You are also using serial when you should be using INT GENERATED ALWAYS AS IDENTITY

answered Jan 15 '22 at 21:02

Wakka

384
8

Postgres: Unable to set primary key on a column

1 Answers1