Multi-tenant database constraints

Question

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?

Answer 1

This is what I ended up going with:

create table blog_posts (
    id uuid not null,
    tenant_id uuid not null,
    title text not null,
    content text not null,
    primary key (id)
);

create unique index blog_posts_tenant_id_id_idx on blog_posts (tenant_id, id);

create table comments (
    id uuid not null,
    tenant_id uuid not null,
    blog_post_id uuid not null references blog_posts (id),
    content text not null,
    primary key (id)
);

alter table comments add foreign key (tenant_id, blog_post_id)
    references blog_posts (tenant_id, id)
    on update cascade;