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?