MariaDB插入部门经理时如何校验mgr_start_date晚于对应雇员bdate

MariaDB 实现跨表字段校验常用以下两种方案：

方案一：触发器实现（全版本兼容，推荐生产使用） #

针对DEPARTMENT表的插入、更新操作分别创建前置触发器，触发时关联查询EMPLOYEE表的出生日期做校验，不满足条件直接抛出错误中断操作。

1. 插入操作触发器 #

DELIMITER //
CREATE TRIGGER check_mgr_date_before_insert
BEFORE INSERT ON DEPARTMENT
FOR EACH ROW
BEGIN
    -- 允许部门未设置经理的场景可打开下方注释
    -- IF NEW.mgr_ssn IS NULL THEN
    --     RETURN;
    -- END IF;
    DECLARE emp_bdate DATE;
    SELECT bdate INTO emp_bdate FROM EMPLOYEE WHERE ssn = NEW.mgr_ssn;
    IF NEW.mgr_start_date <= emp_bdate THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = '校验失败：部门经理入职日期必须晚于该经理的出生日期';
    END IF;
END //
DELIMITER ;

2. 更新操作触发器 #

DELIMITER //
CREATE TRIGGER check_mgr_date_before_update
BEFORE UPDATE ON DEPARTMENT
FOR EACH ROW
BEGIN
    -- 允许部门未设置经理的场景可打开下方注释
    -- IF NEW.mgr_ssn IS NULL THEN
    --     RETURN;
    -- END IF;
    DECLARE emp_bdate DATE;
    SELECT bdate INTO emp_bdate FROM EMPLOYEE WHERE ssn = NEW.mgr_ssn;
    IF NEW.mgr_start_date <= emp_bdate THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = '校验失败：部门经理入职日期必须晚于该经理的出生日期';
    END IF;
END //
DELIMITER ;

方案二：自定义函数+CHECK约束（MariaDB 10.2+支持） #

先封装校验逻辑为自定义函数，再为DEPARTMENT表添加CHECK约束调用该函数完成校验。

1. 创建校验函数 #

DELIMITER //
CREATE FUNCTION check_mgr_start_date_valid(p_mgr_ssn INT UNSIGNED, p_mgr_start_date DATE)
RETURNS BOOLEAN
DETERMINISTIC
BEGIN
    -- 允许部门未设置经理的场景可打开下方注释
    -- IF p_mgr_ssn IS NULL THEN
    --     RETURN TRUE;
    -- END IF;
    DECLARE emp_bdate DATE;
    SELECT bdate INTO emp_bdate FROM EMPLOYEE WHERE ssn = p_mgr_ssn;
    RETURN p_mgr_start_date > emp_bdate;
END //
DELIMITER ;

2. 添加CHECK约束 #

ALTER TABLE DEPARTMENT
ADD CONSTRAINT chk_mgr_start_date
CHECK (check_mgr_start_date_valid(mgr_ssn, mgr_start_date));

注意事项 #

• 两种方案默认都要求部门必须设置经理，若业务允许部门无经理，可打开对应代码段的注释适配
• 若业务允许修改员工出生日期，可额外为EMPLOYEE表添加UPDATE触发器，校验该员工如果是部门经理时，新出生日期不能晚于对应部门的经理入职日期

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