0

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!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Karan Kumar
  • 2,678
  • 5
  • 29
  • 65
  • 3
    you are missing the data type for `user_id` and `matched_profile` –  Jan 15 '22 at 20:23
  • 2
    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 –  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
  • 1
    @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
  • It's explained in the link that I included in my comment. –  Jan 15 '22 at 21:02

1 Answers1

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

Wakka
  • 426
  • 1
  • 9