SQLite中CHECK约束与触发器失效求助：限制员工薪资不超部门经理

为什么你的CHECK约束失效 #

多数SQL数据库（如MySQL、SQLite）的CHECK约束不支持包含跨表子查询，所以你写的带NOT EXISTS的约束会直接报错。只有部分数据库（如PostgreSQL）允许通过自定义函数间接实现这类跨表检查。

为什么你的触发器未生效 #

你的触发器存在两个关键问题：

• 缺少FOR EACH ROW：默认是语句级触发器，只会在执行INSERT语句时触发一次，不会针对每条新插入的记录单独检查
• 未引用新插入的记录：查询的是整个EMPLOYEE表的历史数据，而非当前要插入的NEW记录，导致无法检测新数据是否违规

针对不同数据库的正确实现 #

1. SQLite（适配你用的RAISE语法）

CREATE TRIGGER SALARY_VIOLATION BEFORE INSERT ON EMPLOYEE
FOR EACH ROW
BEGIN
    -- 检查新员工薪资是否超过其部门经理的薪资
    SELECT RAISE(FAIL, "employee salary cannot be more than the manager salary")
    FROM DEPARTMENT D
    JOIN EMPLOYEE M ON D.mgr_ssn = M.ssn
    WHERE NEW.Dno = D.Dnumber AND NEW.Salary > M.Salary;
END;

• FOR EACH ROW：确保每条插入记录都触发检查
• NEW：代表即将插入的新记录，用NEW.Dno关联部门，NEW.Salary对比经理薪资

2. MySQL

MySQL不支持RAISE，需要用SIGNAL抛出错误：

DELIMITER //
CREATE TRIGGER SALARY_VIOLATION BEFORE INSERT ON EMPLOYEE
FOR EACH ROW
BEGIN
    DECLARE manager_salary DECIMAL(10,2);
    
    -- 获取新员工所在部门经理的薪资
    SELECT M.Salary INTO manager_salary
    FROM DEPARTMENT D
    JOIN EMPLOYEE M ON D.mgr_ssn = M.ssn
    WHERE D.Dnumber = NEW.Dno;
    
    -- 对比薪资，违规则抛出错误
    IF NEW.Salary > manager_salary THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'employee salary cannot be more than the manager salary';
    END IF;
END //
DELIMITER ;

3. PostgreSQL（支持带函数的CHECK约束）

如果用PostgreSQL，可以通过自定义函数实现CHECK约束：

-- 先创建检查函数
CREATE OR REPLACE FUNCTION check_salary_vs_manager(p_dno INT, p_salary DECIMAL)
RETURNS BOOLEAN AS $$
BEGIN
    RETURN NOT EXISTS (
        SELECT 1
        FROM DEPARTMENT D
        JOIN EMPLOYEE M ON D.mgr_ssn = M.ssn
        WHERE D.Dnumber = p_dno AND p_salary > M.Salary
    );
END;
$$ LANGUAGE plpgsql;

-- 添加CHECK约束
ALTER TABLE EMPLOYEE
ADD CONSTRAINT SALARY_CONSTRAINT CHECK (check_salary_vs_manager(Dno, Salary));

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