I have a multi-tenant app in PostgreSQL that has a repeated tenant_id column in each table to help isolate the tenants from each other.
My question is, is there any sort of way that I can use database constraint to make sure the tenant_ids are consistent with their parents.
For example, imagine a table blog_posts
that has many comments
. I would like to ensure that for any comment, the tenant_id is the same as the tenant_id for the given blog_post_id.
Can this be done in a simple and maintainable way?
FOREIGN KEY (tenant_id, blog_post_id) REFERENCES blog_posts(tenant_id, blog_post_id)