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?

| improve this question | | | | |
  • 3
    Use a multi-column foreign key, e.g. FOREIGN KEY (tenant_id, blog_post_id) REFERENCES blog_posts(tenant_id, blog_post_id) – Craig Ringer Jun 5 '15 at 5:47
  • Nice! That's even easier than I thought. – cdmckay Jun 5 '15 at 18:58

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;
| improve this answer | | | | |
  • If each blog has a unique identifier and each blog is tied to a tenant, then any comment tied to a blog need only reference the blog identifier. There is no need to propagate the tenant id to the comment -- it adds no information not already known. – TommCatt Jun 7 '15 at 5:23
  • 1
    @TommCatt: I get what you're saying, but what happens is if you have a deep hierarchy, then the joins become pretty gnarly just to find out if the tenant owns a certain entity. – cdmckay Jun 8 '15 at 1:38
  • That is true, but are you really going to be looking at comments without reference to a blog? Or a blog without reference to a tenant? Actually, yes, you could be mining for certain key words looking for terrorists, or blackmailers or stalkers. But such uses will be rare and will probably be instituted initially with no regard to tenants. But any hit would have to be traced back to the blog and tenant anyway. Really, is there any situation where you are not already performing the joins? – TommCatt Jun 9 '15 at 15:52
  • @TommCatt: This was a contrived example. The actual hierarchy is much more complicated, so yes, there are many instances where I don't want to perform the joins. Especially in the case of a SPA where I'm refreshing things incrementally but need to know if the user can access them. – cdmckay Jun 9 '15 at 19:43
  • No rule is absolute. In fact, the term "Best Practices" should be called "Best Practices In Most But Not All Situations," but that's too many keystrokes. If you have a good reason for doing something, or taking a shortcut, then by all means do so. – TommCatt Jun 11 '15 at 2:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.