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?

Not necessary. Just drop user_id column from Product. You can always get the user_id through the Store. A particular Product row relates to exactly 1 Store row. Which contains the user_id. This does require a slight change. Make store.user_id and product.store_id not null. — Belayer
– Belayer, Commented Feb 21, 2021 at 1:16

Jeremy · Accepted Answer · 2021-02-19 22:14:59Z

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).

answered Feb 19, 2021 at 22:14

Jeremy

6,78322 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Validating foreign key through tables in postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related