mysql - 一列作为多个复合 FK 约束的一部分是否有效且良好的设计决策？

Question

在两个复合 FK 约束的表部分中有一列可以吗？

假设我们有多个业务分支机构，并且我们有两个不同的实体，每个实体都属于一个特定的分支机构。

现在我们想要关联这两个实体(多对多)，但只有当它们属于同一分支时才允许关联。

所以我们会有三个表:

• 实体A
• 实体B
• 实体

在我看来，我有几个选择:

1) 对所有 PK 使用代理键

• EntityA(entitya_id (pk), branch_id (fk), 其他列)
• EntityB(entityb_id (pk), branch_id (fk), 其他列)
• 实体(entities_id (pk), entitya_id (fk), entityb_id (fk))

这个问题是我需要在触发器中使用 SELECT 语句来进行验证。我宁愿不那样做。此外，SELECT 语句变得更加复杂，因为它通常需要 JOIN 来访问某些信息。

2)使用自然复合键

• 实体A(PK:entitya_id，branch_id)
• 实体B(PK:entityb_id，branch_id)
• 实体(PK entitya_id、entitya_branch_id、entityb_id、entityb_branch_id)

这也需要在触发器中进行验证，但至少我只需要对 NEW 值进行简单的 IF 比较。另一方面，键/列的数量快速增长，即使在简单的情况下，表也可能具有巨大的复合键和大量冗余数据。

3) 也使用自然组合键，但只有一个列引用两个表

这是我的第一选择，但我认为不允许在多个表中有一个列引用键。虽然您不能仅使用一列来做到这一点，但显然允许该列成为多个复合约束的一部分，这已被证明 here

• 实体A(PK:entitya_id，branch_id)
• 实体B(PK:entityb_id，branch_id)
• 实体(PK:entitya_id、entityb_id、branch_id)

FK 定义如下:

CONSTRAINT `entities_entitya`
    FOREIGN KEY (`entitya_id`, `branch_id`)
    REFERENCES `entitya` (`entitya_id`, `branch_id`)
    ON DELETE CASCADE
    ON UPDATE RESTRICT,
CONSTRAINT `entities_entityb`
    FOREIGN KEY (`entityb_id`, `branch_id`)
    REFERENCES `entityb` (`entityb_id`, `branch_id`)
    ON DELETE CASCADE
    ON UPDATE RESTRICT

所以 branch_id 变成了某种共享 FK

所以重复我的问题:

• 这是合法的做法吗？
• 它是否违反了某些规范？
• 这会不会以某种方式在我脸上爆炸？

Answer 1

您的解决方案 #3 没有任何问题，我认为这是满足您的验证要求的聪明解决方案。它准确地模拟了现实世界的需求——两个实体必须属于同一个分支。

我看不出它会如何导致任何问题，除非假设需求会在以后发生变化。例如，您可能会得到一个稍微不同的要求，即两个分支可以不同，只要它们以某种方式相互关联即可。

在那种情况下，您需要两个不同的列，但是您还需要在两个不同的外键约束中使用每个分支列:

CREATE TABLE Entities (
  entitya_id        INT,
  entitya_branch_id INT,
  entityb_id        INT,
  entityb_branch_id INT,
  FOREIGN KEY (entitya_id, entitya_branch_id) 
    REFERENCES entitya(entitya_id, branch_id),
  FOREIGN KEY (entityb_id, entityb_branch_id) 
    REFERENCES entitya(entityb_id, branch_id),
  FOREIGN KEY (entitya_branch_id, entityb_branch_id) 
    REFERENCES EntityAffiliations(entitya_branch_id, entityb_branch_id)
)