SQLite插入Plan表时校验日期是否在关联Project表日期区间

问题原因 #

你遇到的报错是多个问题共同导致的：

• 语法笔误：endDate字段类型写错，你写的是DARE，正确应为DATE
• 核心逻辑错误：表级CHECK约束不支持跨表引用其他表的字段，你直接在Plan表的CHECK规则里写Project.startDate、Project.endDate，数据库无法从当前Plan表的上下文找到另一个表的字段，因此抛出no such column错误
• 额外的逻辑问题（非报错直接原因）：你给projectID加了UNIQUE约束，意味着同一个项目只能关联一条计划记录，如果你的业务允许一个项目对应多个计划，需要去掉这个UNIQUE配置

解决方案 #

要实现跨表的日期范围校验，不能用CHECK约束，推荐用触发器实现，校验逻辑在数据插入/更新Plan表时自动触发，比应用层校验更稳妥，不会遗漏直接操作数据库的场景。

第一步：修正Plan表建表语句

%%sql
DROP TABLE IF EXISTS Plan;
CREATE TABLE Plan (
    pID varchar(255) NOT NULL UNIQUE,
    projectID varchar(255) NOT NULL,
    name varchar(255) NOT NULL DEFAULT ' ',
    startDate DATE NOT NULL DEFAULT '2000-12-31',
    endDate DATE NOT NULL DEFAULT '2000-12-31',
    -- 保留自身起止日期的校验，跨表校验放到触发器
    CHECK (JulianDay(startDate) <= JulianDay(endDate)),
    PRIMARY KEY (pID, projectID),
    FOREIGN KEY (projectID) REFERENCES Project(projectID)
);

第二步：新增触发器实现跨表日期校验（以SQLite为例，其他数据库语法差异极小）

-- 插入数据时的校验触发器
CREATE TRIGGER check_plan_date_before_insert
BEFORE INSERT ON Plan
FOR EACH ROW
BEGIN
    SELECT RAISE(ABORT, '计划起止日期不能超出关联项目的起止日期范围')
    WHERE EXISTS (
        SELECT 1 FROM Project 
        WHERE Project.projectID = NEW.projectID 
        AND (NEW.startDate < Project.startDate OR NEW.endDate > Project.endDate)
    );
END;

-- 更新数据时的校验触发器
CREATE TRIGGER check_plan_date_before_update
BEFORE UPDATE OF startDate, endDate, projectID ON Plan
FOR EACH ROW
BEGIN
    SELECT RAISE(ABORT, '计划起止日期不能超出关联项目的起止日期范围')
    WHERE EXISTS (
        SELECT 1 FROM Project 
        WHERE Project.projectID = NEW.projectID 
        AND (NEW.startDate < Project.startDate OR NEW.endDate > Project.endDate)
    );
END;

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