mysql - 是否可以在子表的复合外键中使用来自引用(通过外键)祖 parent 行的字段？

Question

完整问题:

子表通过外键(引用祖父主键)链接到其祖父表中的行，是否可以在子表的复合外键中使用引用的祖父行中的字段？

我当前的数据库设计有一个主父表，Projects。 Projects 有两个子表，JobTitles 和 Tasks。 Tasks 然后有一个子表 Subtasks。 Subtasks 然后有一个子表 Assignments，这是一个将员工放在 Subtasks 上的外部参照表。我遇到的问题是将 JobTitles 分配给 Assignments； JobTitles 属于一个Project，因此任何给定Project 下的Assignments 应该只能引用<共享一个项目的strong>JobTitles。我最近问this question关于将 JobTitle 的选择限制为共享项目的人员。但是，从那时起，我发现复合外键是一种更简洁的解决方案。

简单的数据库布局:

• 项目
  • 职位
  • 任务
    • 子任务
      • 作业

我找到了如何创建复合键 here ，但为此我需要使用项目的主键作为复合外键的一部分。

表格:

• 项目
  • 项目名称(PK)
  • ProjectID(唯一索引)
• 职位
  • 职位
  • ProjectID(外键 -> Projects.ProjectID)(复合 PK:JobTitle-ProjectID)
  • JobTitleID(唯一索引)
• 任务
  • 任务名称
  • ProjectID(外键 -> Projects.ProjectID)(复合 PK:TaskName-ProjectID)
  • TaskID(唯一索引)
• 子任务
  • 子任务名称
  • TaskID(外键 -> Tasks.TaskID)(复合 PK:SubtaskName-TaskID)
  • 子任务 ID(唯一索引)
• 作业
  • 员工 ID(外键)
  • SubTaskID(外键 -> Subtasks.SubtaskID)(复合 PK:EmployeeID-SubtaskID)
  • JobTitleID(外键 -> JobTitles.JobTitleID)
  • 作业 ID(唯一索引)

为了将 JobTitle 分配给作业，我想使用作业的 ProjectID(来自其父任务)及其选定的 JobTitleID 设置复合外键。唯一的问题是我不知道如何获取两代之后的 ProjectID 以用于 key 。通过将 ProjectID 包装在每个表中的复合键中，可以将 ProjectID 向下传递到每一代，但是考虑到它们对性能造成的损失(更不用说传递值)，复合键并不是随便扔掉的东西下来似乎有点草率)。有没有什么方法可以在不通过其他表传递的情况下获取在 key 中使用的 ProjectID？

Answer 1

如您所说，您可以将 ProjectID 通过每个表向下传递。我认为这并不马虎，它使您能够创建复合主键，从而能够重用例如跨不同项目的 TaskID。一旦意识到这一点，就会发现在每个表中使用 ID 列的常见做法有点多余。可以改为使用语义上有意义的数据作为键，这通常是我的偏好(当然它在空间方面成本更高，但索引对时间的影响相对较小):

CREATE TABLE Projects (
  ProjectName VARCHAR(20) NOT NULL PRIMARY KEY
);

CREATE TABLE JobTitles (
  ProjectName VARCHAR(20) NOT NULL,
  JobTitle    VARCHAR(20) NOT NULL,
  PRIMARY KEY (ProjectName, JobTitle),
  FOREIGN KEY (ProjectName) REFERENCES Projects (ProjectName)
);

CREATE TABLE Tasks (
  ProjectName VARCHAR(20) NOT NULL,
  TaskName    VARCHAR(20) NOT NULL,
  ParentTask  VARCHAR(20),
  PRIMARY KEY (ProjectName, TaskName),
  FOREIGN KEY (ProjectName) REFERENCES Projects (ProjectName),
  FOREIGN KEY (ProjectName, ParentTask) REFERENCES Tasks (ProjectName, TaskName)
);

CREATE TABLE Assignments (
  ProjectName VARCHAR(20)  NOT NULL,
  TaskName    VARCHAR(20)  NOT NULL,
  JobTitle    VARCHAR(20)  NOT NULL,
  Email       VARCHAR(255) NOT NULL,
  PRIMARY KEY (ProjectName, TaskName, JobTitle),
  FOREIGN KEY (ProjectName) REFERENCES Projects (ProjectName),
  FOREIGN KEY (ProjectName, TaskName) REFERENCES Tasks (ProjectName, TaskName),
  FOREIGN KEY (ProjectName, JobTitle) REFERENCES JobTitles (ProjectName, JobTitle),
  FOREIGN KEY (Email) REFERENCES Employees (Email)
);

由于 MySQL 不支持更强大的约束验证，我能想到的唯一其他选择是在 上定义 BEFORE INSERT 和 BEFORE UPDATE 触发器分配 引发错误以拒绝包含无效 JobTitle 的数据。但是，您还需要在 JobTitles 上创建触发器以处理此类引用记录更改或删除的情况；然后还有可能导致链接中断的所有其他表格。丑丑丑。

因此我更喜欢上面给出的第一种方法。