SQL Server中如何基于另一张表约束表字段值？

你的需求是：让CasesNeeded表的CaseID只能取自CaseDetails表中与当前行ProductID相同的记录，之前尝试用带子查询的CHECK约束失败，且希望避免自定义函数。

问题原因 #

SQL的CHECK约束不允许使用子查询，所以你之前的写法触发报错：

Subqueries are not allowed in this context. Only scalar expressions are allowed

正确方案：使用复合外键 #

利用CaseDetails表中CaseID是主键（唯一）、且每个CaseID对应唯一ProductID的特性，在CasesNeeded表中创建复合外键，同时关联CaseDetails的CaseID和ProductID字段，就能强制CaseID必须属于当前行的ProductID。

修改后的CasesNeeded表创建语句：

CREATE TABLE CasesNeeded 
(
    ProductID int,
    StartDate date,
    CaseID int,
    CasesNeeded int,
    CONSTRAINT PK_CasesNeeded PRIMARY KEY (ProductID, StartDate, CaseID),
    CONSTRAINT FK_CasesNeeded_ProductRun 
        FOREIGN KEY (ProductID, StartDate) REFERENCES ProductRun(ProductID, StartDate),
    -- 新增复合外键约束
    CONSTRAINT FK_CasesNeeded_CaseProduct
        FOREIGN KEY (CaseID, ProductID) REFERENCES CaseDetails(CaseID, ProductID)
);

原理说明 #

• CaseDetails的主键是CaseID，因此(CaseID, ProductID)组合必然唯一（每个CaseID对应唯一的ProductID）
• 复合外键要求CasesNeeded中每一行的(CaseID, ProductID)必须在CaseDetails中存在，这就保证了当前行的CaseID对应的ProductID和自身的ProductID完全一致
• 这种方式是数据库原生支持的约束机制，性能优于自定义函数，可靠性更高

内容的提问来源于stack exchange，提问作者Brady Hinman