数据库完整性这块上课听的时候觉得每条都好懂一到做题和上机就翻车。最近翻到当时的课程记录正好是2021年的第五章例题重新捋了一遍才发现很多当时觉得莫名其妙的报错其实都是对三类完整性约束的理解差了一层。这篇就把当时的例题和踩坑过程完整复盘一下给同样在啃数据库的读者做个参考。1. 那堂课上最容易被问懵的三个问题开讲之前老师先抛了个问题数据库完整性到底保证的是什么当时底下答案五花八门有人说是数据不丢有人说是数据不错还有人说防止别人乱改。其实都不太准确。数据库完整性保证的是数据符合约定规则——这个约定规则不只是业务上说得通而是能落到数据库层面、由数据库强制执行的硬性规定。丢了数据是故障恢复范畴数据被篡改是安全性范畴完整性约束管的是这张表里不该出现的值根本没机会出现。接着自然引出三类完整性这是第五章的主干完整性类型约束对象核心思想生活类比实体完整性单张表内的记录每一行都要有唯一身份标识且该标识不能为空每个人都有唯一的身份证号参照完整性表与表之间的对应关系子表中的引用值必须在父表中真实存在不能指向不存在的记录借书登记里的书号必须是馆里真实有的书用户定义完整性单个字段的业务规则字段取值必须符合业务语义比如年龄不能为负、性别只能取固定几类会员制度规定未成年人不能进入某些区域表面看很好区分一做题就容易混。尤其实体完整性和用户定义完整性——都是对单张表的限制凭什么学号不能重复属于实体完整性成绩不能超过100属于用户定义完整性关键区别在于实体完整性涉及的是行的唯一标识解决的是这一行到底是谁的问题用户定义完整性涉及的是字段的业务语义解决的是这个值合不合理的问题。后面做例题时判断错的大部分都栽在这上面。2. 实体完整性例题拆解主键约束与唯一约束的区别到底在哪典型例题是这样的某高校教学管理系统需要建立学生表属性包括学号、姓名、性别、年龄、所在系要求学号能够唯一标识每个学生。写出创建表的SQL语句并说明实体完整性如何实现。这题大多数人都能写出来前半句PRIMARY KEY (Sno)。但后面的问题才是关键——为什么实体完整性要求主键不能为空有人觉得主键值缺失无非是这行记录暂时没编号补上就行。这里必须想明白主键是行的身份标识空值表示未知既然无法判断这条记录是谁那这张表里的每一个人在逻辑上就不成立。所以主键 非空 唯一两者缺一不可。2.1 最容易丢分的复合主键场景题目如果换成就餐记录表一个学生一天可以点多次餐学号本身就重复了这时怎么办正确答案是用复合主键PRIMARY KEY (Sno, OrderTime)。很多同学的第一反应是那再加个自增ID当主键不就完了。这在真实项目里确实常见但考试题考的是关系模型的基本概念——复合主键是从候选键中选出来的它强调的是业务上的唯一标识能力而不是为了技术上方便。自增ID是物理层的便利手段不代表真实的业务唯一性。判断是否会丢分有个自测方法拿到一张表和它的业务描述先问自己哪些列的取值组合能保证整张表里不会有重复行这个取值组合就是候选键。所有候选键里选一个做主键其他候选键用UNIQUE约束。比如学生表里学号是候选键身份证号也是候选键选学号作主键给身份证号加UNIQUE约束。2.2 建表语句的完整写法与理解CREATE TABLE Student ( Sno CHAR(9) PRIMARY KEY, Sname CHAR(20) NOT NULL, Ssex CHAR(2), Sage SMALLINT, Sdept CHAR(20) );也可以把约束写在表级CREATE TABLE Student ( Sno CHAR(9), Sname CHAR(20) NOT NULL, Ssex CHAR(2), Sage SMALLINT, Sdept CHAR(20), CONSTRAINT PK_Student PRIMARY KEY (Sno), CONSTRAINT UQ_Student_Sno UNIQUE (Sno), CONSTRAINT NN_Student_Sname CHECK (Sname IS NOT NULL) );考试时更常考的是后一种形式因为它能考察约束命名的规范性和表级约束与列级约束的差别。列级约束只能约束当前列表级约束可以约束多列组成的复合条件。我当初做这题时忽略了一点明明已经用PRIMARY KEY了还画蛇添足再加一个UNIQUE和NOT NULL虽然逻辑上不报错但没有任何意义只是让语句显得啰嗦。判卷老师眼里这种操作说明概念没理透——主键本身就隐含了非空且唯一。2.3 实操中的意外主键重复插入报错上机实验时A同学建完表往里面插入两条学号相同的数据数据库直接报错。错误信息大概像这样ERROR 1062 (23000): Duplicate entry 2021001 for key Student.PRIMARY这里有两个信息值得注意。第一是错误码23000这是SQL标准里的完整性约束违反错误码看到它基本可以确定是约束被触发。第二是Duplicate entry ... for key直接指明重复的是哪张表、哪个键。很多同学一看报错就懵其实数据库已经把排查路径指好了——顺着它说的键去看数据。实战经验给约束起一个语义明确的名称比让数据库自动生成名字要省心得多。例如CONSTRAINT PK_Student PRIMARY KEY (Sno)报错信息里会直接出现PK_Student一眼就知道是学生表主键冲突如果不命名不同数据库会生成PRIMARY、PRIMARY KEY这类模糊名称出错时还要自己猜是哪个约束。3. 参照完整性例题拆解从选课表看外键的完整逻辑第二道经典例题在学生表基础上增加课程表Course(Cno, Cname, Cpno, Ccredit)和选课表SC(Sno, Cno, Grade)其中选课表的学号参照学生表的学号课程号参照课程表的课程号要求实现参照完整性。CREATE TABLE Course ( Cno CHAR(4) PRIMARY KEY, Cname CHAR(40) NOT NULL, Cpno CHAR(4), Ccredit SMALLINT ); CREATE TABLE SC ( Sno CHAR(9), Cno CHAR(4), Grade SMALLINT, PRIMARY KEY (Sno, Cno), FOREIGN KEY (Sno) REFERENCES Student(Sno), FOREIGN KEY (Cno) REFERENCES Course(Cno) );这题的坑在于很多人以为外键类型写对了就行忽略了被参照的列必须有主键约束或唯一约束。如果Student表的Sno不是主键执行建表语句时某些严格的数据库会直接拒绝创建外键因为不满足引用完整性前提——外键要参照的必须是候选键。为什么要这样规定如果被参照列可以重复外键值就不知道该匹配哪一行了参照就不唯一了。3.1 三类操作受限是参照完整性的核心考点参照完整性不仅仅是建外键更常考的是对三种操作的制约操作是否允许原因向SC表插入一行Sno在Student中不存在禁止子表不能引用不存在的父表记录从Student表删除一行该学号已出现在SC中视约束规则而定删除会导致SC中出现悬空引用修改Student表的Sno该学号已被SC引用视约束规则而定修改同样会导致参照悬空题目常这样设问要删除学号为2021001的学生记录但他有选课记录数据库会怎么处理标准SQL提供了几种策略这也是课本反复强调的点NO ACTION / RESTRICT直接拒绝删除这是很多数据库的默认行为CASCADE级联删除选课记录一并删除SET NULL选课表中的外键值设为NULL要求该外键列允许为空。三种策略没有绝对好坏取决于业务语义。学生退学选课记录通常没有保留价值用CASCADE合理仓库表和商品表的分类关系删除分类时商品应挪到未分类可用SET NULL用户被删除但订单要留痕做审计就得用拒绝删除或软删除方案。上机练习时我遇到过一题要求删除课程号C004同时删除选课表中所有C004的选课记录这就是CASCADE的典型用法ALTER TABLE SC ADD CONSTRAINT FK_SC_Cno FOREIGN KEY (Cno) REFERENCES Course(Cno) ON DELETE CASCADE;3.2 参照完整性上机最坑的三个细节跑上面SQL时有一批人会卡在莫名其妙的报错上归纳下来主要是三类第一是字符集不一致。Student表建表时用了默认字符集SC表建表时环境变量不同导致用了另一种字符集两表间建外键报错Illegal mix of collations。这个问题隐蔽在图形化工具里看不出来要查SHOW CREATE TABLE才能看到两表的字符集设置。解决办法是建表时显式指定统一字符集不要依赖环境默认。第二是外键列类型不一致。Student表的Sno是CHAR(9)SC表里写成VARCHAR(9)或CHAR(8)外键照样建不上或插入数据时报错。外键列和被引用列除了数据类型要兼容长度最好也完全一致避免隐式转换带来的麻烦。第三是删除父表时的顺序。想在库里删掉Student表如果SC表还引用着它直接DROP TABLE Student会失败。必须先删子表DROP TABLE SC再删父表DROP TABLE Student或者用DROP TABLE Student CASCADE强制级联删除不同数据库支持程度不同。建表时反过来先建父表再建子表。这个先后顺序在笔试里也是常客很多人背了先删子后删父却没理解原因换个问法就懵了。4. 用户定义完整性例题拆解CHECK约束的边界与陷阱第三类例题最常见的是定义一个约束使性别只能取男或女成绩在0到100之间。对应SQLCREATE TABLE SC ( Sno CHAR(9), Cno CHAR(4), Grade SMALLINT CHECK (Grade 0 AND Grade 100), PRIMARY KEY (Sno, Cno), FOREIGN KEY (Sno) REFERENCES Student(Sno), FOREIGN KEY (Cno) REFERENCES Course(Cno) ); CREATE TABLE Student ( Sno CHAR(9) PRIMARY KEY, Sname CHAR(20) NOT NULL, Ssex CHAR(2) CHECK (Ssex IN (男, 女)), Sage SMALLINT, Sdept CHAR(20) );这类约束看似简单细究起来有三个非常值得展开的地方。4.1 CHECK约束与NULL值的关系很多教材和习题会问如果一个表的年龄列设置了CHECK (Sage 0)插入一条Sage NULL的记录会怎样答案是插入成功。因为SQL中NULL表示未知对未知值做比较结果也是未知未知不等于FALSE。CHECK约束只在条件为FALSE时拒绝未知时放行。这个细节在考试里容易被特意拿来设陷阱在实际开发里也容易让新人误解我明明加了CHECK为什么NULL还能进来。如果业务上不允许空年龄必须额外加NOT NULL两者是配合关系不是替代关系。4.2 不同数据库对CHECK约束的执行差异这是上机时最容易让人困惑的地方。某同学在模拟环境里创建了一个带CHECK约束的表插入了一条明显违反约束的记录竟然成功于是坚信CHECK约束没用。真实情况是环境默认不强制CHECK约束把遵守标准SQL当作可选优化。更常见的商业数据库和开源数据库在这点上差异明显有的明确支持并强制CHECK有的在旧版本里会解析CHECK语法但建表时不真正执行只做兼容。所以生产环境里千万不要只依赖加个CHECK就万事大吉建完表后必须主动插入一条违规数据验证约束是否真的生效。这个验证习惯也是那次课上老师反复强调的——测试时主动制造违规数据是检验完整性约束是否落实的最直接手段。4.3 约束命名与后续维护的价值用户定义完整性的实现可以写在列级也可以写在表级还可以在表建好后用ALTER TABLE补加ALTER TABLE SC ADD CONSTRAINT CK_SC_Grade CHECK (Grade BETWEEN 0 AND 100);给约束起个好名字的好处在上机排错时特别明显。如果约束叫CK_SC_Grade违规插数据时错误信息里直接出现这个名字能立刻定位到是选课表的成绩范围约束被触发。如果不命名错误信息只有系统和自动生成的约束ID排查起来要先把表和约束列表翻一遍。命名规范建议用约束类型_表名_列名PK_、FK_、CK_、UQ_、NN_看着名字就能理解其含义。5. 实际验证环节把例题完整跑一遍产生的意外做课程实验时我照着例题把三张表建好然后把课本上的插入、修改、删除测试用例依次执行了一遍。整个过程记录下来的报错和修正比例题本身还有价值。这里把完整的验证链路复盘出来供参考。5.1 按顺序建表别让引用关系反过来第一次实验时我先建SC表因为它的定义已经写好了懒得找其他表语句。结果建表直接报错说引用的表不存在。数据库的约束检查发生在定义时被参照的表必须先存在。正确顺序是Student、Course然后才是SC。后来发现这也符合逻辑上的依赖关系——先有基本实体再有联系。5.2 插入数据时父表数据必须前置建好表后插入数据我按先学生、再课程、最后选课的顺序插就顺利通过。但有一个同学想测试外键故意先往SC里插一条学号不存在的记录数据库立刻拒绝错误提示明确指向外键约束。这就是参照完整性的插入制约在起作用。想测试约束是否生效不需要一次把所有数据都造出来可以只在一个会话里建表插数据触发报错后再看错误码和消息文本。5.3 删除数据时的外键连锁反应测试删除时我先删Student表里的一条记录而这条记录在SC里有选课数据。数据库默认阻止删除报错提示外键约束失败。这时有两种走法一种是业务上学生退学且选课记录需要保留审计改用软删除加一个Status字段标记退学状态不动物理数据另一种是确认选课记录不需要保留给外键加上ON DELETE CASCADE再执行删除。我当初为了验证级联效果执行了ALTER TABLE SC DROP FOREIGN KEY FK_SC_Sno; ALTER TABLE SC ADD CONSTRAINT FK_SC_Sno FOREIGN KEY (Sno) REFERENCES Student(Sno) ON DELETE CASCADE;然后删除Student表里的目标记录发现SC里相关选课记录也自动消失了。这个操作演示了CASCADE语义但也提醒了一件事——修改外键策略前必须确认业务上可接受级联后果否则大量关联数据可能被静默删除。5.4 约束不是越多越好但该有的必须要有在验证过程中我一度想给所有列都加上检查约束觉得这样数据最安全。实际上约束太多会让业务数据插入变得困难还会牺牲灵活性比如一个允许管理员临时录入不完整记录的内部功能会被严格的NOT NULL约束挡住。更合理的做法是主键约束和外键约束必须由数据库强制用户定义完整性则根据真实业务需求有选择地添加。核心业务字段加上约束日志型、分析型字段尽量不加。考试题的完整性检查基本上只考主键、外键、CHECK、DEFAULT、NOT NULL并没有要求你每个字段都约束一遍。6. 从例题到实战完整性设计的一些个人体会第五章的例题练完最大的收获是明白了数据库完整性不是一个写完就完的静态配置。约束不是写在建表语句里就高枕无忧了项目迭代中经常出现这种情况业务变了某个字段的取值范围扩大原来的CHECK约束反而成了阻碍表之间关系调整了外键策略可能需要从默认行为改成级联或置空。完整的约束生命周期应该包括上线前验证约束生效、上线后监控违规插入记录、业务变更时同步修订约束。另一个体会是不要试图用数据库约束解决所有问题。比如一个学生选的课不能超过10门这种跨行统计规则用CHECK约束实现不了需要触发器或应用层校验。同一门课一个老师只能教一个班这类更复杂的业务规则靠约束也描述不清楚。数据库完整性擅长的是字段级和引用级的硬规则跨行、跨表的复杂业务逻辑要交给应用层或专门的服务去判断。分清硬规则交给约束、软规则交给业务这条边界项目里数据质量问题会少很多。最后分享一个我在上机时养成的小习惯也推荐给所有正在学这章的人每写完一个约束不管多自信都手动执行一条违反该约束的SQL验证数据库是否真的拒绝。比如建完CHECK (Grade BETWEEN 0 AND 100)就插一条Grade 101的数据建完外键就插一条引用不存在学号的数据。这么做看似多余实际是检验约束确实生效最可靠的办法。完整性这章的内容考试考的从来不是背定义而是能不能预测数据库在各种操作下会做出什么反应——多做这种试探性违规实验这种预测能力自然就来了。
