当插入引用其他两个表的第三个表时，如何检查两个表之间的约束？

Question

考虑这个示例架构：

Customer ( int CustomerId pk, .... )

Employee ( int EmployeeId pk,
           int CustomerId references Customer.CustomerId, .... )

WorkItem ( int WorkItemId pk,
           int CustomerId references Customer.CustomerId,
           null int EmployeeId references Employee.EmployeeId, .... )

基本上，三个表：

• 具有主键和一些附加列的客户表
• 具有主键的员工表，以及对客户表主键的外键约束引用，代表客户的员工。
• 工作项表，存储为客户完成的工作，以及有关为其执行工作的特定员工的信息。

我的问题是。 添加新工作项目时，如何在数据库级别测试员工是否确实与客户关联。

例如，如果 Scott（员工）在 Microsoft（客户）工作，Jeff（员工）在 StackOverflow（客户）工作，我如何防止某人将工作项添加到数据库中，其中客户 = Microsoft，员工 = Jeff，哪些没有意义？

我可以使用检查约束或外键来完成此操作，还是需要触发器来手动测试它？

应该提到我使用 SQL Server 2008。

更新：我应该补充一点，WorkItem.EmployeeId 可以为 null。

谢谢，埃吉尔。

Answer 1

复合列（CustomerId、EmployeeId）上的外键不起作用吗？

ALTER TABLE WorkItem
ADD CONSTRAINT FK_Customer_Employee FOREIGN KEY (CustomerId, EmployeeId)
    REFERENCES Employee (CustomerId, EmployeeId);

Answer 2

您可以通过创建一个视图“WITH SCHEMABINDING”来实现此目的，该视图跨越这些表并强制执行各个表的集体约束。

Answer 3

为什么你希望employeeId为null int WorkItem？ 也许您应该添加另一个表以避免这种特殊的奇怪现象。 据我所知，最简单的方法是在 workItem 中添加对 employeeid 的唯一约束，如果您想要的话，甚至可以在 customerId 上添加唯一的约束。

添加跨多个表的约束的更通用方法是定义一个应始终为空的视图，并添加它为空的约束。

Answer 4

你想在这里建模什么？

1. 您是一家承包机构或类似机构，并且您有一群承包商（在一段时间内）分配给客户。

2. 您实际上正在存储有关其他公司员工的信息（例如，您可能正在提供外包薪资服务）。

   您实际上正在存储有关

在情况 (1) 中，Employee 表似乎存在问题。 特别是，当 Scott 与 MS 的合同到期并且他与其他人签约时，您无法保留历史数据，因为您需要更改 CustomerId。 这也会使所有 WorkItem 无效。 相反，您应该有第四个表（例如 CustomerEmployee）来存储它。 然后 WorkItem 应该引用该表。

在情况 (2) 中，Employee 的主键实际上应该是 CustomerId、EmployeeId。 两个客户可以拥有相同的员工 ID 号。 然后 Kieron 的外键就可以工作了。

Answer 5

我最近遇到了类似的情况，考虑一下架构：
表company (id_cia PK) 表product_group (id_cia FK to company, id_group PK) 表products (id_group FK to Product_group, id_product PK, id_used_by_the_client null)

规则：数据库必须只允许一个公司的每个产品有一个id_used_by_the_client，但该字段可以为空。 示例：

插入公司 (1) = 允许

插入公司 (2) = 允许

插入产品组 (1, 1) = 允许

插入产品组 (1,2) = 允许

插入产品组 (2,3) = 允许

插入产品产品值 (1, 1, null) = 允许

插入产品值 (1, 2, null) = 允许

插入产品值 (1, 3, 1) = 允许

插入产品值 (1, 4, 1) = 不允许允许，在属于公司 1 的组 1 中已经存在 id_used_by_the_client = 1。

插入产品值 (2, 4, 1) = 不允许，在属于公司 1 的组 2 中已经存在 id_used_by_the_client = 1。

插入进入产品值 (3, 4, 1) = 允许，在属于公司 2 的组 3 中，没有 id_used_by_the_client = 1。

我决定使用触发器来控制这种完整性。

Answer 6

或者：

• 将 EmployeeID 列设置为 Employee 的主键（也可能是自动 ID），并将 EmployeeID 作为外键存储在 WorkItem 记录中，而不是将 Employee 和 Customer ID 存储在 WorkItem 中。 您可以通过 Employee 表连接到 Customer 表来检索 WorkItem 的 Customer 详细信息。

或者：

• 将 WorkItem 的 EmployeeID 和 CustomerID 列设置为 Employee 的复合外键。

我个人赞成第一种方法。