作为一门数据库课程的讲师每次讲到第5章数据库完整性我都能看到不少同学的眼神从听懂了逐渐变成好像懂了但做题就懵。这一章在数据库系统概论里属于那种考试一定考平时不重视一做题全错的典型章节。很多同学能把三大完整性背得滚瓜烂熟但一遇到该用级联还是置空CHECK约束到底能check什么触发器写完之后怎么不生效这类实战问题照样卡壳。今天我把这一章最常见的例题类型、解题思路和翻车现场全部梳理一遍用的是学生选课这个经典场景。建议先自己动手在数据库里把每道题跑一遍再对照下面的解析这样印象会深得多。1. 数据库完整性到底在解决什么问题1.1 完整性的三座大山先不急着看例题我们把概念拎清楚。完整性约束本质上是一道数据守门员数据库不是什么都收的垃圾箱不符合规则的数据就应该在入口被拦下来。实体完整性主键不能重复且不能为空。通俗讲每一行记录都得有一个独一无二的身份证号。你的学号、订单号、身份证号凡是当了主键就别想为空也别想出现第二个一样的。参照完整性外键要么为空要么必须对应被参照表里真实存在的主键。通俗讲你选课表里写了一个学号2021001那学生表里就必须能找到一个学号是2021001的人否则这条选课记录就是查无此人。用户定义完整性针对具体业务的自定义规则。比如性别只能是男和女、年龄不能为负、成绩在0到100之间。这条最灵活也最容易被忽略。这三座大山对应到SQL语句就是PRIMARY KEY、FOREIGN KEY、CHECK/NOT NULL/UNIQUE这些约束。第5章所有的例题本质上都是在问你怎么把这些规则写进表结构里以及违反了规则之后数据库会怎么办。1.2 为什么例题比概念重要概念背下来只是第一步例题才是真正检验你是否理解违约处理这件事的试金石。比如实体完整性约束违反时数据库会拒绝插入或修改但参照完整性违反时除了拒绝你还可以选择级联删除、级联修改、置空值这些额外操作。这些操作策略选不对业务逻辑就会出问题比如你删了一个课程是想把选课记录一起删掉还是把选课记录里的课程号置空这些细节在例题里体现得最明显。所以别嫌例题麻烦每一道例题背后都对应着一个实际的业务决策。2. 三组必背顶层例题定义与违约处理这一章的例题可以分为三大类单纯让你写约束定义语句的、让你分析违约后数据库会怎么反应的、以及让你综合设计带约束的完整建表语句。下面我们逐个来看。2.1 实体完整性例题主键到底怎么设才规范先看一道最基础的题目为学生表定义实体完整性约束。CREATE TABLE Student( Sno CHAR(9) PRIMARY KEY, Sname CHAR(20) NOT NULL, Ssex CHAR(2), Sage SMALLINT, Sdept CHAR(20) );这题的得分点很简单PRIMARY KEY在主键属性上声明Sno是主码。数据库会自动为Sno建立唯一索引保证不会出现两个相同的学号同时Sno被隐式设置为NOT NULL。如果主键是多个属性组合比如选课表SC主键是(Sno, Cno)那么正确的写法有两种-- 写法一列级定义只适用于单一属性主键多属性必须用表级 CREATE TABLE SC( Sno CHAR(9), Cno CHAR(4), Grade SMALLINT, PRIMARY KEY(Sno, Cno) );这里我要特别强调一个很多同学会栽的坑多属性主键绝对不能写成PRIMARY KEY(Sno)再加PRIMARY KEY(Cno)一个表只能有一个主键想表达两个字段共同唯一只能用表级约束一次声明。我在改作业时几乎每届都能看到有人犯这个错误。再来看违约处理。实体完整性的违约处理很简单就一个词拒绝执行。插入一个重复学号拒绝把已有学号改成另一个已存在的学号拒绝主键列插入NULL也拒绝。有人问如果我把一个非主键列的NULL用空字符串代替行不行比如学号插入。注意CHAR(9)里插入空字符串会在内部变成9个空格仍然被当作一个具体值插入不会报错。但如果字段本身没有NOT NULL约束NULL和空字符串是两回事这一点在完整性约束里经常作为判断题出现。2.2 参照完整性例题外键级联策略怎么选参照完整性是第5章的绝对核心也是例题出得最多的地方。看这道经典题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) );这条语句实现了SC表对Student表和Course表的参照。此时SC表中的Sno和Cno就是外键它们的取值必须真实存在于被参照表的主键中。但题目如果只考到这里那还算简单。真正拉开差距的是后面这句当被参照表Student中的某一行被删除或主键值被修改时SC表应该怎么处理教材里给了三种策略NO ACTION默认拒绝当删除或修改Student中被SC引用的记录时直接拒绝操作。CASCADE级联删除Student中的记录时SC中对应的选课记录一并删除修改主键时SC的外键值跟着变。SET NULL置空删除或修改时SC中对应外键值置为NULL前提是该外键列允许取NULL。所以一个完整的参照完整性定义例题应该是这样的CREATE TABLE SC( Sno CHAR(9), Cno CHAR(4), Grade SMALLINT, PRIMARY KEY(Sno, Cno), FOREIGN KEY(Sno) REFERENCES Student(Sno) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY(Cno) REFERENCES Course(Cno) ON DELETE NO ACTION ON UPDATE CASCADE );那这题应该怎么答才拿满分你需要写清楚选择策略的理由。选CASCADE通常是因为选课记录是学生记录的附属品学生退学了选课记录没意义一起删掉合理。选NO ACTION则是因为有些核心数据不允许被连带修改需要业务层先校验。我给一个记忆口诀删父看子有子拒绝级联同生共死置空留个空位子表外键放行。这句你自己琢磨几遍三种策略的区别就清楚了。2.3 用户定义完整性例题NOT NULL、UNIQUE与CHECK相对前两类用户定义完整性在语法上更灵活也更贴近真实业务。看例题CREATE TABLE Student( Sno CHAR(9) PRIMARY KEY, Sname CHAR(20) NOT NULL, Ssex CHAR(2) CHECK(Ssex IN (男, 女)), Sage SMALLINT CHECK(Sage 0 AND Sage 150), Sdept CHAR(20) UNIQUE );几个要点NOT NULL不解释学号、姓名这类属性不能为空。注意PRIMARY KEY已经隐含NOT NULL不用重复写。CHECK(Ssex IN (男,女))性别只能用这两个值。有人问能不能用数字0和1当然可以业务设计的事约束只是锁死规则。CHECK(Sage 0 AND Sage 150)年龄不可能为负数也不可能超过150逻辑上根本没给垃圾数据留门。UNIQUE所在系不能重复。这个约束不是非用不可但可以说明用户定义完整性可以覆盖唯一性需求。这里有一个常考判断题UNIQUE和PRIMARY KEY有什么区别两者都要求取值唯一但UNIQUE允许一个NULL值不同数据库对多个NULL是否允许不同标准SQL里一般允许一个或多个取决于具体实现而PRIMARY KEY不允许NULL并且一个表只能有一个主键但可以有多个UNIQUE字段。用户定义完整性的违约处理同样是拒绝插入或修改但由于它是自定义的你可以在约束无法满足复杂需求时改用触发器或断言这也正好引出后面的内容。3. 约束命名、修改与删除被忽视的隐藏考点不少教材把CONSTRAINT命名的内容放在小字部分结果考试一考就一大片人不会。其实这个知识点很简单但需要动手写过才会有印象。3.1 用CONSTRAINT关键字给约束起名看下面的例题CREATE TABLE Student( Sno CHAR(9), Sname CHAR(20), Ssex CHAR(2), Sage SMALLINT, Sdept CHAR(20), CONSTRAINT PK_Student PRIMARY KEY(Sno), CONSTRAINT CK_Ssex CHECK(Ssex IN (男, 女)), CONSTRAINT CK_Sage CHECK(Sage BETWEEN 0 AND 150) );这里每个约束都有了名字PK_Student、CK_Ssex、CK_Sage。为什么要起名因为后续你想修改或删除某个约束没有名字就根本找不到它。就像你办业务报我要删掉我的那个账号管理员一脸茫然但你报出身份证号就清楚多了。3.2 修改约束并保证数据兼容ALTER TABLE语句可以添加或删除约束。例题经常这样出在SC表中增加一个成绩在0到100之间的约束。ALTER TABLE SC ADD CONSTRAINT CK_Grade CHECK(Grade BETWEEN 0 AND 100);这时数据库会先去检查SC表里已有的Grade数据。如果已经有同学的成绩是105这条约束添加会直接失败因为数据库不会让新增约束破坏现有数据的合法性。这个问题我在后面翻车现场里还会再提。删除约束的典型语句是ALTER TABLE Student DROP CONSTRAINT CK_Ssex;删除约束的难度在于你怎么确定约束的名字。如果是建表时随意写的而且当时没起名数据库通常会按默认规则自动生成一个名字比如在SQL Server里可能是CK__Student__Ssex__123456这种具体要查系统表。考试里不会考查系统表的细节但你要知道有这回事。3.3 修改约束的底层逻辑有些同学会问MySQL里怎么修改约束直接说结论MySQL 8.0以
