SQL中CHECK约束内使用not in查询的合法性及约束失效问题

子查询使用权限说明 #

绝大多数主流关系型数据库（MySQL、PostgreSQL、SQL Server等）均不支持在CHECK约束中使用跨表子查询，语法层面就限制了这类写法的有效性，是你编写的约束无法生效的核心原因。

约束失效具体原因 #

• CHECK约束属于行级约束，设计上仅支持对当前插入/更新行的自有字段值做校验，不允许读取其他表或当前表其他行的数据，避免校验结果不可控、锁范围扩大引发并发问题。
• 即使部分数据库支持你创建带子查询的CHECK约束，也仅会在创建约束时执行一次子查询做全量校验，后续数据变更时不会重新执行子查询逻辑，自然无法起到实时校验的作用。
• 你当前的交叉校验逻辑本身也存在逻辑漏洞：如果同时向两张表插入同一个ID，两个约束查询对方表时均未命中，最终会导致两条重复ID都插入成功，完全达不到全局唯一的要求。

适配你测试场景的修复方案 #

方案1：使用触发器替代CHECK约束实现跨表校验 #

这是最贴近你原有校验逻辑的替代方案，给两张表分别新增BEFORE INSERT、BEFORE UPDATE触发器，插入/修改ID前先查询另一张表是否存在重复ID，存在则抛出异常中断操作，示例代码（MySQL语法）如下：

--  student表插入前校验触发器
DELIMITER //
CREATE TRIGGER trg_student_id_check BEFORE INSERT ON student
FOR EACH ROW
BEGIN
  IF EXISTS (SELECT 1 FROM staff WHERE id = NEW.id) THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'ID已在staff表存在，全局唯一校验失败';
  END IF;
END //
DELIMITER ;

--  staff表插入前校验触发器
DELIMITER //
CREATE TRIGGER trg_staff_id_check BEFORE INSERT ON staff
FOR EACH ROW
BEGIN
  IF EXISTS (SELECT 1 FROM student WHERE id = NEW.id) THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'ID已在student表存在，全局唯一校验失败';
  END IF;
END //
DELIMITER ;

方案2：从ID生成源头避免重复 #

如果仅需要实现ID全局唯一，更稳妥的方式是在业务层统一ID生成规则，比如使用自增序列、UUID、雪花ID等方式生成全局唯一ID，不需要依赖数据库跨表校验，性能和稳定性都更高。

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