sql - 如何在多个表中强制执行唯一性

Question

我在 MySQL 服务器中有以下表:

Companies:
- UID (unique)
- NAME
- other relevant data

Offices:
- UID (unique)
- CompanyID
- ExternalID
- other data

Employees:
- UID (unique)
- OfficeID
- ExternalID
- other data

在它们中的每一个中，UID 都是由数据库创建的唯一标识符。

有外键来确保UID上的Employee -> Office -> Company之间的链接。

Offices 和 Employees 中的 ExternalID 字段是公司(实际上是我的客户)提供给我的应用程序的 ID。客户没有(也不关心)我自己的 ID，我的应用程序从他们那里收到的所有数据都完全根据他们的 ID(即我表中的 ExternalID)进行识别。

即来自客户端的伪语言请求就像“我是 X 公司，为我的员工 Y 更新数据”。

我需要对 CompanyID 和 Employees.ExternalID 的组合强制执行唯一性，因此在我的数据库中，同一公司的员工不会有重复的 ExternalID。

我在考虑 3 种可能的解决方案:

1. 更改 Employees 的架构以包含 CompanyID，并对这两个字段创建唯一约束。

2. 执行触发器，在更新/插入 Employees 时验证唯一性。

3. 强制检查应用程序级别(即我的接收服务)。

我的替代方案 - dbadmin-in-me 认为 (3) 是最糟糕的解决方案，因为它无法在应用程序错误或其他问题的情况下保护数据库不一致，而且很可能是最慢的解决方案。

触发器解决方案可能是我想要的，但它可能会变得复杂，特别是如果需要在单个语句中执行多个插入/更新，并且我不确定性能与(1)相比。

并且 (1) 看起来是最快和最简单的方法，但有点违背我对关系模型的理解。

SO DB 专家的意见是关于每种方法的优缺点，特别是如果有可能添加额外的间接级别 - 即公司 -> 办公室 -> 部门 -> 员工，并且需要相同的唯一性保存(公司/员工)。

Answer 1

你是对的 - #1 是最好的选择。
诚然，乍一看我会质疑它(因为走捷径)，但知道确保一名员工仅与一家公司相关的业务规则 - 这是有道理的。

此外，我还有一个外键，将员工表中的公司 ID 与办公室表中的公司 ID 相关联。否则，您允许员工与没有办公室的公司有关联。除非那是可以接受的...

如果无法在数据模型中证明关系，触发器是最后的手段，并且从应用程序服务逻辑意味着逻辑是集中的 - 没有机会出现错误数据，除非有人放弃约束(这意味着你有更大的问题)。