sql - 在三元关系中实现参照完整性

Question

题
员工被组织成团队。每个团队可以有多个员工，每个员工可以属于多个团队。这种多对多关系由 team_membership 表示。 table 。
每个项目分配给一个团队。项目分割为任务，每个任务分配给一名员工。
是否可以保证任务的员工是相应项目团队的成员，而不添加触发器或冗余列？
示例表

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 
  ); 

我已经尝试过的
使用触发器在数据更改时检查有效性。这将需要为 employee 编写类似的触发程序。和 team_membership表。

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();

添加 team_id列到任务表，并使用复合外键强制约束。 project.team_id和 task.team_id是多余的。

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

添加冗余 team_id至 task并将新列添加到引用 project 的外键中.这需要 project(team_id, project_id) 上的冗余唯一约束。 .
然后从 task 创建一个外键约束至 team_membership .那么任务只能分配给一个团队成员。