如何添加约束校验match表的比赛日期处于对应tournament赛事的起止日期范围内

原写法问题说明 #

你尝试的约束逻辑无法生效是因为存在几个核心问题：

• 绝大多数关系型数据库的CHECK约束不支持跨表引用其他表的数据，写法中用子查询关联tournament表本身就不符合CHECK约束的语法规则
• 存在多处语法错误：表名拼写错误（tournament写成tournement）、字段名混用（自定义的start_d、end_d、inizio、fine都没有定义，子查询里用分号分隔逻辑也是错误的）
• 子查询没有做行限制，会出现返回多行的逻辑错误

正确实现方案 #

方案1：使用触发器（全数据库通用，最推荐） #

触发器是最稳定的跨表校验实现方式，在INSERT/UPDATE match表时触发校验逻辑，匹配对应赛事的日期范围，不符合就抛出异常终止操作。
以下是MySQL环境的示例代码：

DELIMITER //
CREATE TRIGGER trg_match_date_check
BEFORE INSERT ON `match`
FOR EACH ROW
BEGIN
    DECLARE tour_start DATE;
    DECLARE tour_end DATE;
    -- 查询对应赛事的起止日期
    SELECT start_date, end_date INTO tour_start, tour_end
    FROM tournament WHERE tid = NEW.tid;
    -- 校验日期范围
    IF NEW.date NOT BETWEEN tour_start AND tour_end THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = '比赛日期不在对应赛事的起止日期范围内';
    END IF;
END //
DELIMITER ;

-- 如果需要UPDATE操作时也触发校验，额外创建UPDATE触发器
DELIMITER //
CREATE TRIGGER trg_match_date_update_check
BEFORE UPDATE ON `match`
FOR EACH ROW
BEGIN
    DECLARE tour_start DATE;
    DECLARE tour_end DATE;
    SELECT start_date, end_date INTO tour_start, tour_end
    FROM tournament WHERE tid = NEW.tid;
    IF NEW.date NOT BETWEEN tour_start AND tour_end THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = '比赛日期不在对应赛事的起止日期范围内';
    END IF;
END //
DELIMITER ;

注意match是SQL关键字，所以用反引号包裹避免语法错误。

方案2：PostgreSQL专属实现：自定义函数+CHECK约束 #

如果你使用PostgreSQL，可以通过自定义稳定函数包装查询逻辑，再绑定到CHECK约束上：

-- 自定义校验函数
CREATE OR REPLACE FUNCTION check_match_date(match_tid INT, match_date DATE)
RETURNS BOOLEAN AS $$
DECLARE
    tour_start DATE;
    tour_end DATE;
BEGIN
    SELECT start_date, end_date INTO tour_start, tour_end
    FROM tournament WHERE tid = match_tid;
    RETURN match_date BETWEEN tour_start AND tour_end;
END;
$$ LANGUAGE plpgsql STABLE;

-- 绑定CHECK约束
ALTER TABLE "match" ADD CONSTRAINT chk_match_date
CHECK (check_match_date(tid, date));

该方案存在限制：如果后续修改了tournament表的start_date/end_date，已经存在的match记录不会自动重新校验，需要手动触发更新。

方案3：应用层+数据库双重校验 #

除了数据库层的校验，建议在业务代码层也做对应逻辑的校验，提前拦截非法请求，减少数据库的无效请求。

内容的提问来源于stack exchange，提问作者Yuri Alò