数据库完整性守住数据的质量底线一句话总结数据库完整性通过实体完整性主键、参照完整性外键和用户自定义完整性CHECK、NOT NULL、UNIQUE、DEFAULT、触发器三层防线确保数据的正确性、一致性和有效性防止脏数据、非法数据进入数据库。一、什么是数据完整性数据完整性Data Integrity指的是数据的正确性、有效性和相容性。简单来说就是数据库里的数据必须是靠谱的。1.1 一个真实的故事某电商系统因为没有设置完整性约束发生了以下惨剧用户注册时年龄填了 250 岁——系统照单全收订单表里的用户ID指向了一个不存在的用户——发货时找不到地址同一商品出现了两个相同的主键——库存管理彻底乱套某字段为 NULL但程序假设它一定有值——页面直接崩溃这些问题本可以在数据库层面就被拦截。完整性约束就是数据库的免疫系统。二、实体完整性每行数据必须有身份证2.1 规则主键PRIMARY KEY必须满足唯一性表中任意两行的主键值不能相同非空性主键值不能为 NULL2.2 SQL 实现-- 方式一列级约束CREATETABLE学生(学号CHAR(10)PRIMARYKEY,姓名VARCHAR(20)NOTNULL,年龄INT);-- 方式二表级约束适合组合主键CREATETABLE选课(学号CHAR(10),课程号CHAR(10),成绩INT,PRIMARYKEY(学号,课程号)-- 组合主键);2.3 实体完整性会自动做什么当你试图违反时DBMS 会坚决拒绝-- 错误主键重复INSERTINTO学生VALUES(2024001,张三,20);INSERTINTO学生VALUES(2024001,李四,21);-- ❌ 报错Duplicate entry-- 错误主键为 NULLINSERTINTO学生VALUES(NULL,王五,20);-- ❌ 报错PRIMARY KEY cannot be NULL三、参照完整性外键不能指向空处3.1 规则外键FOREIGN KEY要么为 NULL要么等于被引用表中某个主键值。3.2 SQL 实现CREATETABLE系(系号CHAR(2)PRIMARYKEY,系名VARCHAR(30)NOTNULL);CREATETABLE学生(学号CHAR(10)PRIMARYKEY,姓名VARCHAR(20)NOTNULL,系号CHAR(2),FOREIGNKEY(系号)REFERENCES系(系号));3.3 参照完整性会自动做什么-- 错误插入不存在系号的学生INSERTINTO学生VALUES(2024001,张三,99);-- ❌ 报错无此系号-- 错误删除被引用的系如果学生还在这个系DELETEFROM系WHERE系号01;-- ❌ 报错有外键引用不能删除3.4 级联操作让删除和更新更智能有时候当父表数据变化时我们希望子表自动跟随变化。这就是级联操作。CREATETABLE学生(学号CHAR(10)PRIMARYKEY,姓名VARCHAR(20)NOTNULL,系号CHAR(2),FOREIGNKEY(系号)REFERENCES系(系号)ONDELETECASCADE-- 删除系时自动删除该系所有学生ONUPDATECASCADE-- 修改系号时自动同步学生表中的系号);其他级联选项ON DELETE SET NULL删除父表时子表外键设为 NULLON DELETE RESTRICT/NO ACTION拒绝删除默认行为ON DELETE SET DEFAULT删除父表时子表外键设为默认值⚠️ CASCADE 是双刃剑删除父表数据会连带删除子表数据使用时务必谨慎四、用户自定义完整性业务规则的守护者4.1 NOT NULL非空约束CREATETABLE学生(学号CHAR(10)PRIMARYKEY,姓名VARCHAR(20)NOTNULL,-- 姓名不能为空手机号VARCHAR(15)-- 手机号可以为空未填写);-- 错误INSERTINTO学生(学号)VALUES(2024001);-- ❌ 姓名不能为 NULL4.2 UNIQUE唯一约束CREATETABLE学生(学号CHAR(10)PRIMARYKEY,身份证号CHAR(18)UNIQUE,-- 身份证号不能重复但不是主键姓名VARCHAR(20));主键 UNIQUE NOT NULL。一个表只能有一个主键但可以有多个 UNIQUE 约束。4.3 CHECK检查约束CREATETABLE学生(学号CHAR(10)PRIMARYKEY,姓名VARCHAR(20)NOTNULL,年龄INTCHECK(年龄0AND年龄120),-- 年龄必须在合理范围性别CHAR(2)CHECK(性别IN(男,女))-- 性别只能是男或女);-- 错误INSERTINTO学生VALUES(2024001,张三,200,男);-- ❌ 年龄超限INSERTINTO学生VALUES(2024002,李四,20,未知);-- ❌ 性别非法⚠️ MySQL 在 8.0.16 之前不支持 CHECK 约束会解析但不生效请使用 8.0.16 版本。4.4 DEFAULT默认值CREATETABLE学生(学号CHAR(10)PRIMARYKEY,姓名VARCHAR(20)NOTNULL,性别CHAR(2)DEFAULT男,-- 默认性别为男入学日期DATEDEFAULTCURRENT_DATE-- 默认当天);-- 插入时省略性别和入学日期自动使用默认值INSERTINTO学生(学号,姓名)VALUES(2024001,张三);-- 结果性别男, 入学日期今天4.5 命名约束方便后续管理CREATETABLE学生(学号CHAR(10),姓名VARCHAR(20),年龄INT,CONSTRAINTpk_学生PRIMARYKEY(学号),-- 命名主键CONSTRAINTuq_姓名UNIQUE(姓名),-- 命名唯一约束CONSTRAINTchk_年龄CHECK(年龄BETWEEN0AND120)-- 命名检查约束);-- 后续可以按名称删除约束ALTERTABLE学生DROPCONSTRAINTchk_年龄;五、断言更强大的完整性了解断言ASSERTION是更高级的完整性约束可以涉及多个表。但遗憾的是主流数据库MySQL、PostgreSQL、SQL Server对 ASSERTION 的支持非常有限。-- 标准 SQL 语法实际很少支持CREATEASSERTION 学生人数限制CHECK((SELECTCOUNT(*)FROM学生)10000);实际项目中复杂的跨表约束通常用触发器或应用层校验来实现。六、触发器动态完整性机制触发器Trigger是特殊的存储过程当特定事件INSERT/UPDATE/DELETE发生时自动执行。6.1 触发器实现级联更新-- 创建触发器当系号更新时自动同步学生表CREATETRIGGER同步系号AFTERUPDATEON系FOR EACH ROWBEGINUPDATE学生SET系号NEW.系号WHERE系号OLD.系号;END;NEW 和 OLD 分别代表更新后的新值和更新前的旧值。6.2 触发器实现审计日志CREATETRIGGER记录学生修改AFTERUPDATEON学生FOR EACH ROWBEGININSERTINTO审计日志(表名,操作,旧值,新值,操作时间)VALUES(学生,UPDATE,OLD.姓名,NEW.姓名,NOW());END;七、完整性约束总结约束类型作用对象关键字功能实体完整性主键PRIMARY KEY唯一 非空参照完整性外键FOREIGN KEY … REFERENCES维护表间引用关系非空约束任意列NOT NULL禁止 NULL唯一约束任意列UNIQUE禁止重复检查约束任意列CHECK限制取值范围默认值任意列DEFAULT自动填充默认值触发器表TRIGGER复杂动态约束八、动手练习练习 1设计带约束的表为电商系统设计以下表要求包含合理的完整性约束用户表用户ID、用户名、邮箱、注册时间商品表商品ID、商品名、价格、库存订单表订单ID、用户ID、商品ID、数量、下单时间练习 2测试约束尝试插入以下数据观察哪些会报错并解释原因INSERTINTO学生VALUES(001,张三,150,男);-- 年龄 150INSERTINTO学生VALUES(001,李四,20,男);-- 学号重复INSERTINTO学生VALUES(002,王五,20,未知);-- 性别非法INSERTINTO学生(学号,年龄)VALUES(003,20);-- 姓名 NULL练习 3级联操作测试创建系表和学生表设置外键级联删除。然后插入几条数据删除某个系观察学生表是否自动变化九、常见错误与避坑指南误区正确理解“CHECK 在 MySQL 里没用”MySQL 8.0.16 已支持 CHECK旧版本确实不生效“外键会影响性能应该不用”外键确实有小开销但数据一致性收益远大于成本。除非超大规模分库分表否则建议保留“所有校验都应该放在应用层”应用层校验数据库约束双重保险才是最安全方案“NULL 和空字符串一样”NULL 是未知空字符串是已知为空两者不同“PRIMARY KEY 和 UNIQUE 没区别”PRIMARY KEY 是 UNIQUE NOT NULL且一个表只能有一个主键十、下篇预告下一篇我们将学习数据库安全性——如何通过用户授权、角色管理和加密技术保护数据不被未授权访问和篡改。