Validating foreign key through tables in postgres

Question

I'm trying to validate a foreign key constraint on my table through another table, but I'm not sure how to go about it, or even the correct nomenclature to google.

I have a User table:

create table User ( id uuid primary key );

Each user can have multiple stores:

create table Store(
   id uuid primary key,
   user_id uuid foreign key store_user_id_fk references User (id)
);

and each user+store can have products

create table Product(
  id uuid primary key,
  user_id uuid foreign key product_user_id_fk references User (id), 
  store_id uuid foreign key product_sotre_id_fk references Store (id),
)

My question is: how can I write a constraint on Product such that any (user_id,store_id) combination also must have a valid entry in the Store table? The case I'm trying to prevent is an entry being added to Product where the store does not also belong to the user.

Is there some way of adding:

CHECK ( store_id == Store.id and user_id == Store.user_id )

To the product table?

Answer 1

Unless I'm misunderstanding your question, I'm pretty sure it would just be:

create table Product(
  id uuid primary key,
  user_id uuid foreign key product_user_id_fk references User (id), 
  store_id uuid foreign key product_sotre_id_fk references Store (id),
  FOREIGN KEY (user_id, store_id) REFERENCES store(user_id, id)
);

This would indicate that the primary key of store should be (id, user_id) not just id or, at at minimum, it should have a UNIQUE constraint on (id, user_id).