Enforce referential integrity in a ternary relation

Question

Question

Employees are organized into teams. Each team can have multiple employees, and each employee can belong to multiple teams. This many-to-many relationship is represented by the team_membership table.

Each project is assigned to one team. Projects are subdivided into tasks, and each task is assigned to an employee.

Is it possible to guarantee that a task's employee is a member of the corresponding project's team, without adding triggers or redundant columns?

Example tables

CREATE TABLE employee 
  ( 
     employee_id   bigserial PRIMARY KEY, 
     employee_name text 
  ); 

CREATE TABLE team 
  ( 
     team_id   bigserial PRIMARY KEY, 
     team_name text 
  ); 

CREATE TABLE team_membership 
  ( 
     team_id     bigint NOT NULL REFERENCES team, 
     employee_id bigint NOT NULL REFERENCES employee, 
     PRIMARY KEY (team_id, employee_id) 
  ); 

CREATE TABLE project 
  ( 
     project_id   bigserial PRIMARY KEY, 
     team_id      bigint NOT NULL REFERENCES team, 
     project_name text 
  ); 

CREATE TABLE task 
  ( 
     task_id     bigserial PRIMARY KEY, 
     task_name   text, 
     project_id  bigint NOT NULL REFERENCES project, 
     employee_id bigint NOT NULL REFERENCES employee 
  ); 

What I have already tried

Use a trigger to check validity when data changes. This would require writing similar trigger procedures for the employee and team_membership tables.

CREATE FUNCTION check_employee_member_of_team() RETURNS trigger AS $$
  BEGIN
    IF NOT new.employee_id IN (
      SELECT employee_id FROM team_membership tm
      JOIN project pr ON tm.team_id = pr.team_id
      WHERE pr.proejct_id = new.project_id
    ) THEN
        RAISE EXCEPTION 'Employee is not a member of project';
    END IF;
    RETURN NEW;
  END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER insert_or_update_task_trigger
    BEFORE INSERT OR UPDATE ON task
    FOR EACH ROW EXECUTE PROCEDURE check_employee_member_of_team();

Add a team_id column to the task table, and enforce the constraint using composite foreign keys. project.team_id and task.team_id are redundant.

CREATE TABLE project 
  ( 
     project_id   bigserial PRIMARY KEY, 
     team_id      bigint NOT NULL REFERENCES team, 
     project_name text, 
     UNIQUE (project_id, team_id) 
  ); 

CREATE TABLE task 
  ( 
     task_id     bigserial PRIMARY KEY, 
     task_name   text, 
     project_id  bigint NOT NULL, 
     team_id     bigint NOT NULL, 
     employee_id bigint NOT NULL REFERENCES employee, 
     FOREIGN KEY (project_id, team_id) REFERENCES project (project_id, team_id), 
     FOREIGN KEY (team_id, employee_id) REFERENCES team_membership (team_id, employee_id) 
  ); 

Answer 1

Add a redundant team_id to task and add the new column to the foreign key referencing project. This requires a redundant unique constraint on project(team_id, project_id).

Then create a foreign key constraint from task to team_membership. Then the task can only be assigned to a team member.

Answer 2

The following solution, a variation on that of @LaurenzAlbe, reduces the redunduncy while maintaining all the constraints:

employee (empl_id, empl_name), primary key empl_id

team (team_id, team_name), primary key team_id

team_membership (empl_id, team_id),
  primary key (empl_id, team_id),
  foreign key empl_id references employee,
  foreign key team_id references team

project (project_num, team_id, project_name)
  primary key(project_num, team_id)
  foreign key team_id references team

task (task_num, project_num, team_id, empl_id, task_name)
  primary key(task_num, project_num, team_id)
  foreign key (project_num, team_id) references project,
  foreign key (empl_id, team_id) references team_membership

Note that project_num is a sequential number internal to each task (so different tasks can have the same set of numbers for their projects), while task_num is a sequential number internal to each project.

Finally, note that, as for the comment of @DamirSudarevic, the foreign key on team_membership is enough to ensure that empl_id and team_id reference to an existing employee and team, respectively.