SQL Server 2016如何添加跨表约束限制列值不超另一表列值

嘿，这个问题我之前也踩过坑！SQL Server的CHECK约束默认只能引用当前表的列，直接在CHECK里写dbo.Site.TotalOil肯定会报错，因为它没法关联到具体的Site记录。下面给你两种可行的解决方案：

方案一：标量函数 + CHECK约束 #

首先创建一个标量函数，用来根据Reserves表的关联字段（假设是SiteID，你需要换成实际的关联键）获取对应Site的TotalOil值：

CREATE FUNCTION dbo.GetSiteTotalOil(@SiteID INT)
RETURNS DECIMAL(18,2)
AS
BEGIN
    DECLARE @TotalOil DECIMAL(18,2)
    SELECT @TotalOil = TotalOil
    FROM dbo.Site
    WHERE SiteID = @SiteID
    RETURN @TotalOil
END

然后创建Reserves表（或者修改现有表）时，添加CHECK约束：

-- 创建表时添加约束
CREATE TABLE dbo.Reserves (
    ReserveID INT PRIMARY KEY IDENTITY(1,1),
    SiteID INT FOREIGN KEY REFERENCES dbo.Site(SiteID),
    TotalOil DECIMAL(18,2) NOT NULL,
    CONSTRAINT CK_Reserves_TotalOil_NotExceedSite
    CHECK (TotalOil <= dbo.GetSiteTotalOil(SiteID))
)

-- 如果是修改现有表
ALTER TABLE dbo.Reserves
ADD CONSTRAINT CK_Reserves_TotalOil_NotExceedSite
CHECK (TotalOil <= dbo.GetSiteTotalOil(SiteID))

⚠️ 注意：标量函数在大表场景下可能会有性能问题，因为每次插入/更新都会调用函数查询Site表，如果数据量很大，建议优先考虑下面的触发器方案。

方案二：使用触发器 #

触发器可以在插入或更新Reserves记录时，直接关联Site表进行校验，性能通常比标量函数更好：

CREATE TRIGGER TR_Reserves_CheckTotalOil
ON dbo.Reserves
AFTER INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    -- 检查是否有记录的TotalOil超过对应Site的TotalOil
    IF EXISTS (
        SELECT 1
        FROM inserted i
        JOIN dbo.Site s ON i.SiteID = s.SiteID
        WHERE i.TotalOil > s.TotalOil
    )
    BEGIN
        RAISERROR('Reserves.TotalOil cannot exceed Site.TotalOil for the associated Site.', 16, 1)
        ROLLBACK TRANSACTION;
    END
END

这个触发器会在每次插入或更新后，检查所有被修改的记录，如果有不符合条件的，就抛出错误并回滚操作。

额外提示 #

不管用哪种方案，都必须确保Reserves表和Site表之间有明确的关联键（比如SiteID），这样才能准确找到对应的Site记录进行校验。另外，如果Site表的TotalOil值后续被修改，你可能还需要考虑是否要同步校验Reserves表的现有记录——如果需要的话，可以在Site表也添加一个触发器，当TotalOil被修改时检查关联的Reserves记录。

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