SQL CHECK 约束的表级声明与应用层校验优先级对比

sql check约束在insert时不能完全依赖：postgresql和sql server严格校验，mysql旧版/myisam忽略，innodb下若未启用strict_trans_tables可能静默转换值；应用层校验必须前置以提供友好提示并避免数据库错误。

SQL CHECK 约束在 INSERT 时是否真能拦住非法数据

不能完全依赖。PostgreSQL 和 SQL Server 会严格执行 CHECK 约束，但 MySQL（尤其是旧版本 + MyISAM 引擎）会忽略它；即使启用 InnoDB，STRICT_TRANS_TABLES 模式未开启时，MySQL 也可能静默截断或转换值后绕过校验。

常见错误现象：INSERT INTO users (age) VALUES (-5) 在 MySQL 上不报错，select * FROM users 却看到 age = 0 —— 这不是 CHECK 生效了，是类型隐式转换在起作用。

• 使用场景：适合做“兜底”校验，比如确保 status IN ('active', 'inactive') 或 price >= 0
• 参数差异：PostgreSQL 支持表达式（如 CHECK (end_time > start_time)），MySQL 8.0.16+ 才支持函数和子查询，且不能含不确定函数（NOW()、RAND()）
• 性能影响极小，约束只在写入时触发一次计算，不索引、不锁表

应用层校验为什么必须比数据库 CHECK 更早介入

因为数据库校验发生在事务提交前最后一刻，而应用层能提前拦截、给出友好提示、避免连接占用和日志污染。

典型问题：用户提交表单时填了负数年龄，后端直接发 INSERT，数据库抛出 Error: check constraint "users_age_check" is violated —— 这个错误对前端不可读，也无法定位是哪个字段出错。

• 使用场景：表单提交、API 入参、批量导入预检
• 推荐做法：用结构化校验库（如 Python 的 pydantic、Node.js 的 zod）定义规则，与数据库约束语义一致（如都要求 age > 0 AND age ）
• 兼容性注意：应用层可处理 NULL、空字符串、时区转换等数据库不关心的逻辑，而 CHECK 对 NULL 默认放行（三值逻辑）

当 CHECK 约束和应用层规则不一致时会发生什么

数据状态会分裂。比如应用层允许 email 为空字符串，但数据库有 CHECK (email !~ '^[[:space:]]*$')，插入时必然失败；反过来，若应用层限制 name ≤ 20 字符，而数据库只设 CHECK (Length(name) ，就可能存入超长名，后续前端渲染崩掉。

• 最容易踩的坑：开发改了应用校验逻辑（比如放宽手机号格式），但忘了同步更新数据库 CHECK，上线后部分数据“合法但异常”
• 调试建议：把数据库约束定义导出为注释，和对应模型类/DTO 放一起，例如在 UserSchema 类顶部加 # DB CHECK: age > 0 AND age
• CI 阶段可加简单比对脚本，检查 DDL 中的 CHECK 表达式是否被应用层测试覆盖

PostgreSQL 中如何让 CHECK 约束真正参与 UPDATE 冲突检测

默认不会。如果 CHECK 依赖其他行（比如“每个用户只能有一个主邮箱”），单靠表级约束做不到，必须用 EXCLUDE 约束或唯一索引配合函数。

错误认知：以为写 CHECK (SELECT count(*) FROM emails WHERE user_id = id AND is_primary) 就能防重复主邮箱 —— PostgreSQL 不允许子查询出现在 <code>CHECK 中，会直接报错 ERROR: subquery is not allowed in check constraint。

• 正确做法：用唯一索引过滤，如 CREATE UNIQUE INDEX idx_unique_primary_email ON emails (user_id) WHERE is_primary
• 或者用触发器（BEFORE INSERT OR UPDATE），但要注意性能和事务可见性
• 注意点：EXCLUDE 约束虽强大，但只支持 B-tree/GiST 索引操作符，无法表达“大于某时间”的业务逻辑

复杂点在于，约束层级越靠近数据库，越难表达动态业务规则；越靠近应用，越容易漏掉并发写入路径。两边都写、且保持同步，不是过度设计，是现实需要。