如何添加SQL约束校验同表parent列仅为空或存在且非自身task值

原有约束失效原因 #

普通CHECK约束仅支持校验当前行的字段值，无法跨行查询整张表的其他记录，你尝试的写法中parent NOT IN task实际是拿当前行的task值做比对，而非匹配全表的task列，因此无法校验父任务是否真实存在。

推荐实现方案：外键约束 + 单行CHECK约束 #

这个方案是数据库原生支持的标准实现，逻辑清晰、性能最优：

• 外键约束负责校验：parent非空时，取值必须是本表已存在的task值，且天然允许parent为null
• CHECK约束负责校验：parent不允许等于当前行自身的task值

示例SQL代码 #

-- 添加外键约束，关联本表的task主键
ALTER TABLE tasks
ADD CONSTRAINT fk_parent_ref_task FOREIGN KEY (parent) REFERENCES tasks(task);

-- 添加CHECK约束，禁止任务指向自身作为父任务
ALTER TABLE tasks
ADD CONSTRAINT ck_parent_not_self CHECK (parent <> task);

注：parent为null时，parent <> task的运算结果为unknown，CHECK约束仅会拦截运算结果为false的情况，因此不会拦截合法的初始任务（parent为null）。
可根据业务需求调整外键的删除/更新行为，比如需要父任务删除后子任务的parent自动设为空，可在外键语句末尾添加ON DELETE SET NULL。

备选方案：触发器实现 #

如果你的数据库场景有额外的自定义校验逻辑（比如限制任务层级深度等），可以通过行级触发器实现，插入/更新数据前校验parent的合法性，不过维护成本高于原生约束，非必要不推荐。

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