如何在SQL Server自关联表中添加员工employee_year小于经理的约束？

SQL Server的CHECK约束确实不允许在条件中包含子查询，所以需要换其他方式实现这个需求，下面是两种常用的可行方案：

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

先创建一个标量函数用于根据经理编号获取其入职年份，再在CHECK约束中调用该函数完成判断：

1. 创建标量函数 #

CREATE FUNCTION dbo.GetManagerEmployeeYear(@ManagerId INT)
RETURNS INT
AS
BEGIN
    -- 处理顶级员工（无上级，经理编号为NULL）的情况
    IF @ManagerId IS NULL
        RETURN 9999 -- 返回一个足够大的年份，确保顶级员工的约束校验通过
    
    DECLARE @ManagerYear INT
    SELECT @ManagerYear = employee_year 
    FROM employee 
    WHERE employee_number = @ManagerId
    
    RETURN @ManagerYear
END

2. 添加CHECK约束 #

ALTER TABLE employee 
ADD CONSTRAINT CK_EmployeeYearLessThanManager
CHECK (employee_year <= dbo.GetManagerEmployeeYear(manager_employee_number))

注意：这种方式虽能实现需求，但标量函数在CHECK约束中可能引发性能问题——表数据量较大时，每次插入、更新操作都会触发函数查询。此外，如果经理的入职年份后续被修改，已存在的员工数据不会自动重新校验，需要手动处理。

方案二：AFTER触发器 #

用触发器在数据插入、更新时校验规则，不符合则回滚事务并抛出错误：

创建AFTER触发器 #

CREATE TRIGGER TR_Employee_CheckManagerYear
ON employee
AFTER INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    
    -- 检查是否存在不符合规则的数据
    IF EXISTS (
        SELECT 1
        FROM inserted i
        LEFT JOIN employee m ON i.manager_employee_number = m.employee_number
        WHERE i.employee_year > ISNULL(m.employee_year, 9999)
    )
    BEGIN
        RAISERROR('员工入职年份不能大于其经理的入职年份', 16, 1)
        ROLLBACK TRANSACTION
    END
END

说明：该触发器会在数据插入或更新完成后进行检查，若存在员工入职年份大于经理的情况，就回滚事务并抛出错误。对于顶级员工（manager_employee_number为NULL），用ISNULL将经理年份设为9999，确保这类员工的约束自动通过。

触发器方式的性能优于标量函数方案，且当经理的入职年份被修改时，还可扩展触发器逻辑，检查下属员工的年份是否仍符合规则。

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