SQL 如何校验数据完整性？

CHECK约束用于字段级业务规则校验，如年龄≥0、邮箱含@；外键保障引用完整性，需注意ON delete行为与索引要求；UNIQUE+NOT NULL可替代弱主键；应用层与数据库约束须并存，不可互相替代。

用 CHECK 约束定义字段级业务规则

CHECK 约束是最直接的数据完整性控制手段，它在插入或更新时强制校验表达式是否为 TRUE。比如确保年龄不为负数、邮箱必须含 @ 符号，这类逻辑适合用 CHECK 实现。

注意 postgresql 和 SQL Server 支持函数（如 position、len）参与 CHECK 表达式，但 mysql 5.7 及更早版本只允许确定性函数，UUID() 或 NOW() 会报错；MySQL 8.0+ 已放宽限制，但仍需避免子查询和用户变量。

• 写法示例：ALTER TABLE users ADD CONSTRAINT chk_age CHECK (age >= 0 AND age
• 复合条件建议拆成多个 CHECK，便于定位失败原因（单个 CHECK 失败只报“check constraint violated”，不指明哪部分错）
• NULL 值默认通过 CHECK（因为 NULL > 0 结果是 UNKNOWN，不是 FALSE），若要禁止 NULL，得额外加 NOT NULL

外键约束（FOREIGN KEY）防止孤立记录

外键是保障引用完整性的核心机制，它确保子表中某列的值必须存在于父表主键/唯一键中。一旦忽略，就会出现“订单有 customer_id=999，但 customers 表里根本没有这条记录”的情况。

常见陷阱是建表时没指定 ON DELETE 行为：默认是 restrict，删父记录时若子表有关联数据就报错 Error 1451 (HY000): Cannot delete or update a parent row；想级联删除得显式写 ON DELETE CAScadE，但务必确认业务是否允许——误删客户导致所有订单消失，就是典型副作用。

• 添加外键前，先确保父表被引用列有索引（否则 MySQL 会静默创建，PostgreSQL 则直接报错 there is no unique constraint matching given keys）
• SQL Server 中临时禁用外键用 ALTER table orders NOCHECK CONSTRAINT fk_customer_id，但之后必须手动验证数据一致性，不能依赖它长期绕过检查
• 跨 schema 引用需写全名，如 REFERENCES sales.customers(id)，否则可能意外绑定到当前 schema 下同名表

用 UNIQUE + NOT NULL 替代弱主键

有些表没有自然主键（比如日志表、中间表），但又需要防止重复行或空值干扰统计，这时 UNIQUE 配合 NOT NULL 是比自增 ID 更贴近语义的选择。

例如订单明细表中，(order_id, product_id) 联合唯一能防止同一订单重复添加同一商品；若只对 product_id 加 UNIQUE，则无法阻止不同订单里出现相同商品——关键看业务上“重复”是如何定义的。

• UNIQUE 索引允许一个 NULL（标准 SQL 行为），MySQL InnoDB 把 NULL 当作独立值处理，而 PostgreSQL 认为所有 NULL 彼此相等，所以后者在 UNIQUE (col) 下只能存一个 NULL
• 联合唯一约束的列顺序影响查询效率：如果常按 WHERE order_id = ? AND product_id = ? 查询，就把 order_id 放前面
• 某些 ORM（如 Django）生成迁移时默认给外键加 db_constraint=True，但若数据库已存在脏数据，迁移会失败，得先清理再执行

应用层校验和数据库约束必须同时存在

只靠数据库约束不够安全——网络中断、事务未提交、批量导入跳过约束（如 MySQL 的 LOAD DATA INFILE 默认忽略 CHECK）、或者应用直连数据库绕过 ORM 层，都可能让坏数据漏进来。

反过来，只做应用层校验也不可靠：多个服务共用同一库、dba 手动修复数据、甚至同事写了个脚本清库存，这些场景下数据库自己得守住底线。

• 应用层用正则校验邮箱格式（如 /^.+@.+..+$/），数据库用 CHECK 确保非空且含 @（email IS NOT NULL AND email LIKE '%@%'），两者覆盖范围不同，不能互相替代
• 高并发下应用层的“先查后插”（check-then-act）必然产生竞态，必须靠数据库的 INSERT ... ON CONFLICT DO NOTHING（PostgreSQL）或 INSERT IGNORE（MySQL）兜底
• 定期用 select count(*) FROM table WHERE NOT (condition) 检查约束是否被历史数据绕过，尤其是上线新 CHECK 后没做 backfill 的字段

约束不是设完就一劳永逸的事。真正难的是理解每条约束背后的业务含义，以及当它被触发时，系统该拒绝、修正，还是告警——这往往取决于上下文，而不是 SQL 语法本身。