Validating integrity of relational data in postgres

Question

Suppose I have four tables: USER, ORGANIZATION, TEAM, and TEAM_MEMBER.

Table ORGANIZATION
ID   Name
1    Foo
2    Bar

Table USER
ID     Name    OrgID
1      John    1

Table TEAM
ID    Name     OrgID
1     Blue     1
2     Red      2

Table TEAM_MEMBER
ID    UserID    TeamID
1     1          1

A USER and TEAM both belong to an organization directly via a FK. And a USER belongs to one or more teams via an entry in TEAM_MEMBER, which contains a FK to both a USER and a TEAM.

In the example above, I would like to prevent the case where we try to create a row in TEAM_MEMBER for user John and team Red. John belongs to org Foo, and Red is a team in org Bar, so it would nonsensical to have a user assigned to a team in the wrong org.

How can I get the DB to prevent such a scenario? Is there a CHECK constraint or a trigger that can fail if TEAM_MEMBER->USER->OrgID and TEAM_MEMBER->TEAM->OrgID don't match?

Mike Organek · Accepted Answer · 2020-07-21 19:37:55Z

1

You can create a function and add it as a check constraint on team_member:

create function is_same_org(userid int, teamid int) returns boolean as $$
  select u.orgid = t.orgid 
    from "user" u 
   cross join team t 
   where u.id = userid and t.id = teamid;
$$
language sql;

alter table team_member add constraint team_member_check check (is_same_org(userid, teamid));

answered Jul 21, 2020 at 19:37

Mike Organek

12.6k3 gold badges15 silver badges28 bronze badges

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

Collectives™ on Stack Overflow

Validating integrity of relational data 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