Best way to add multi column constraint with PSQL

Question

I have an availability table :

CREATE TABLE availability (
    id SERIAL PRIMARY KEY,
    user_id int REFERENCES users(id) NOT NULL,
    address_id int REFERENCES addresses(id) ON DELETE CASCADE,
    day_of_week int NOT NULL,
    start_mn int,
    end_mn int,
    EXCLUDE using gist (user_id WITH =, day_of_week WITH =, (array[start_mn, end_mn]) WITH &&)
)

In my addresses table, I also have a user_id field that references users(id).

What would be the best way to make sure that the related address inserted has the same user_id as the one in the availability table ?

I tried looking for trigger or function without success and I'd like to know if there is an idiomatic way of dealing with this.

Answer 1

You'd create a UNIQUE constraint on (id, user_id) in addresses.

Then create a composite foreign key:

CREATE TABLE availability (
   ...,
   FOREIGN KEY (address_id, user_id) REFERENCES addresses (id, user_id)
);