简介面向数据库课程学习者与实验课备课教师的实验四配套文档聚焦主键、唯一约束、引用完整性与级联引用等完整性约束的T-SQL实践可用于课内任务对照、实验报告整理与约束报错复盘。包内仅含1个docx文件压缩包约711KB篇幅集中便于按实验任务顺序查阅与打印。该文档已有345人学习内容覆盖联合主键的创建与删除、dept表部门名称唯一约束调整、主表更新时参照完整性冲突、从表外键更新失败以及级联更新设置与测试等环节。文中保留了约束冲突提示与验证结论并附思考题分析读者可据此理解参照完整性被破坏的典型情形、约束拒绝操作时的处理思路以及主从表增删改对数据一致性的影响适合用作实验过程记录与复习索引。1. 约束的判定时刻一条 UPDATE 被拒绝时数据库到底在算什么把UPDATE dept SET DeptNo00108 WHERE DeptNo00101丢进查询窗口回车看到它被REFERENCE 约束 FK__person__DeptNo__2B0A656D 冲突顶回来很多人的第一反应是“数据库又在拦我”。但在数据库这条链路上这件事的本质是引擎替你做了一次你根本没法可靠完成的全量判定此刻 person 表里到底还有多少行仍然指着 00101。这个判定的结果决定了整库数据还能不能自洽。数据库完整性约束从来不是语法作业它是把业务里“本来靠人盯”的规则翻译成引擎每次写操作都必须结算的一次开销。搞懂约束的判定时机、判定方向和判定粒度比背十条ALTER TABLE有用得多——语法三年不变而数据被约束顶回来的现场每次都不一样。2. 主键与唯一约束三列联合主键如何锁住「一人一月一张工资单」pay 表最终用 No、Year、Month 三列联合做主键这个设计不是随手写的。工号加年加月正好是“某人在某年某月只有一张工资单”这条业务规则的最小完备描述。单用 No 做主键一个人这辈子就只能有一条工资记录单用 (Year, Month) 做主键全公司在某个月就只能发一份工资。只有三列组合起来才不多不少地卡住那条规则。约束设计的第一步从来不是选类型而是先把这句话写清楚。2.1 主键在存储层落成了什么一棵 B 树加两道判据关系模型教科书写“主键 唯一 非空”这句话在存储层的落地方式是一个唯一索引。在 SQL Server 里如果表上还没有聚簇索引主键默认会建一个聚簇唯一索引已经有聚簇索引的表主键退化成非聚簇唯一索引。这个细节决定了你的表物理上按什么顺序摆放。插入一行时引擎拿着 (No, Year, Month) 这组键值去 B 树里定位一次找到相同键直接拒绝并抛实体完整性冲突键里有 NULL也直接拒绝。这个检查在索引写入路径上会被访问方法层顺手完成并没有在逻辑层跑一次 if 判断。在它上面再包一层try/catch捕获冲突本质上是在捕获索引结构的排他性。-- 给 pay 表建三列联合主键 -- 列顺序即索引键序顺序定了之后想改只能先删再建 ALTER TABLE pay ADD CONSTRAINT PK_pay PRIMARY KEY (No, Year, Month); -- 删除主键约束必须指名道姓 ALTER TABLE pay DROP CONSTRAINT PK_pay;参数说明里最容易忽略的是括号内的列顺序。B 树按 (No, Year, Month) 的字典序排列因此WHERE No?和WHERE No? AND Year?都能走索引查找而WHERE Year?因为跨过了首列只能走全索引扫描。这不是性能玄学是执行计划里 Seek 与 Scan 的字面差别也是很多大表在业务查询变慢之后回头改主键列序的真实原因。联合主键的列序本质上是把最常用的过滤条件放在前缀。2.2 UNIQUE 与 PRIMARY KEY 的差别不止「能不能为空」dept 表的部门名称列上挂的是唯一约束不是主键。业务规则是“部门名不能重名”但部门刚建、名字还没定的时候允许留空。主键不允许空唯一约束允许所以这里选唯一约束。但“允许空”这三个字藏着一个反直觉的机制在唯一性判定里NULL 被当成本身相等来比较。所以一个加了单列唯一约束的字段最多只能存在一行 NULL第二行 NULL 插进去会直接撞约束。这个点上翻车的人不少现象是“我明明允许为空怎么就插不进去了”。-- 在部门名称列上建唯一约束 ALTER TABLE dept ADD CONSTRAINT UK_dept_DeptName UNIQUE (DeptName); -- 删除唯一约束 ALTER TABLE dept DROP CONSTRAINT UK_dept_DeptName;如果业务真的需要“多个部门都还没起名”唯一约束就不合适得换成带条件的唯一索引过滤索引或者干脆把这条规则挪到应用层并用状态位兜住。约束选型的第一步永远是问这条规则对所有行都成立还是只对某一类行成立。2.3 加约束那一刻的全表检查存量数据也要过一遍思考题里问“加主键约束时报某语句与某列冲突主要原因是什么”答案不是语法写错而是存量数据不符合新规则——比如想拿姓名做主键但表里已经有重名。这引出约束的一个容易忽视的成本建约束时引擎要对存量数据做一次完整校验。大表上加主键或唯一约束会带来一段不短的阻塞窗口期间写入排队。这个代价恰恰是约束的价值所在——它保证的是全表自洽而不只是“从此刻起新写入的数据合规”。-- 加约束之前先自查有没有重复的三列组合 SELECT No, Year, Month, COUNT(*) AS cnt FROM pay GROUP BY No, Year, Month HAVING COUNT(*) 1; -- 再查有没有 NULL 混在里面联合主键任一列都不许空 SELECT COUNT(*) AS null_rows FROM pay WHERE No IS NULL OR Year IS NULL OR Month IS NULL;两条查询都返回 0 行再执行 ALTER 才稳。有些人为了跳过校验会用WITH NOCHECK让约束只约束新数据。这个口子打开之后表里会长期躺着违反约束的历史行之后任何一次碰到那一行的更新都会报错排查起来非常费劲——报错信息指向的是新写的语句根因却在几年前的数据里。-- 查表上已有的键约束和唯一约束确认名字和类型 SELECT name, type_desc, is_primary_key, is_unique FROM sys.key_constraints WHERE parent_object_id OBJECT_ID(pay);这段查询在维护老库时特别有用约束名往往是图形化工具自动生成的跟你以为的名字不一样DROP的时候会直接告诉你“对象不存在”。3. 参照完整性的双向闸门为什么改主表和改从表都会被顶回来实验里做了两个 UPDATE两个都被拒绝了报错措辞还不一样改 dept 报REFERENCE 约束冲突改 pay 报FOREIGN KEY 约束冲突。这不是两个独立的 bug而是同一个机制在两个方向上各触发了一次。把这两个方向拆开外键就再也不是黑匣子了。3.1 外键检查的两个方向父键查找与反向查找外键虽然声明在从表上但它约束的是一段关系因此检查也发生在两端。从表做 INSERT 或 UPDATE 时引擎拿着新写入的外键值去主表查一次父行查不到就拒绝。这个方向叫父键查找。实验里的第 4 步正是如此把 pay 表的工号从 000002 改成 000020而 person 表里根本没有 000020 这个人父键查找落空语句被顶回来。主表做 UPDATE 或 DELETE 时引擎拿着被改动的旧键值去从表反向找一次子行只要找到就拒绝。实验里的第 3 步就是这个方向把 dept 的 00101 改成 00108person 表里还有一批人的 DeptNo 是 00101改完之后这批人失去参照对象语句被顶回来。这里有个常见误解要澄清从表更新失败原因并非“外键列不许改”。外键列当然可以改只要改完的新值在主表里存在。失败的根本原因是改完之后指向了一个不存在的父行。把它理解成“禁止修改”下次遇到“改了别的列为什么也报外键错”就彻底懵了。3.2 报错文本里的措辞差异与约束名构成SQL Server 在两侧给出的措辞不一样主表侧冲突说的是“与 REFERENCE 约束冲突”从表侧冲突说的是“与 FOREIGN KEY 约束冲突”。同一个约束两种说法正好用来区分是哪一端触发的。读日志时先看这个措辞能省掉一半定位时间。约束名FK__person__DeptNo__2B0A656D也有固定构成FK 前缀双下划线表名双下划线列名双下划线再加一段由对象标识派生出来的后缀。后缀在重建数据库之后会变。所以任何把约束名硬编码进脚本的做法换一台机器跑就会失败这是典型的血泪经验级别问题。-- 查 person 表上所有外键拿到真实约束名和级联动作 SELECT fk.name AS fk_name, OBJECT_NAME(fk.parent_object_id) AS child_table, COL_NAME(fkc.parent_object_id, fkc.parent_column_id) AS child_col, OBJECT_NAME(fk.referenced_object_id) AS parent_table, COL_NAME(fkc.referenced_object_id, fkc.referenced_column_id) AS parent_col, fk.delete_referential_action_desc AS on_delete, fk.update_referential_action_desc AS on_update FROM sys.foreign_keys AS fk JOIN sys.foreign_key_columns AS fkc ON fk.object_id fkc.constraint_object_id WHERE fk.parent_object_id OBJECT_ID(person);最后一列on_update的值是 NO_ACTION 还是 CASCADE直接决定后面第 4 章里那场级联实验的结论。很多人“明明设了级联”实际上建的是另一个约束或者建的时候漏写了 ON UPDATE 子句——查这个视图一眼就能看穿。3.3 为什么不能删约束了事应用层校验的判定窗口思考题里问“操作被约束拒绝是该删约束还是按约束提示调整操作”标准答案是后者。理由不止“删了以后没人管”还有一条更严格的机制层面的理由并发。应用层“先查再写”天生是两个语句中间有一个窗口。两个会话同时通过检查、同时写入孤儿行照样产生而且这类问题在生产上极难复现。数据库的约束检查发生在语句执行、持锁期间判定与写入是一个原子动作不存在这个窗口。这是约束不可被应用层替代的核心原因与代码风格无关。代价也要认。外键检查会在父表对应的行上加锁高并发写入链路上这会成为锁竞争点也是死锁的常见来源之一。所以确实存在把一致性校验从数据库挪出去的架构但那必须是明确写进设计文档的取舍——用一致性换吞吐并且配套离线对账任务——而不是某天被约束顶烦了顺手DROP掉。4. 级联引用ON UPDATE CASCADE 一条语句改了两张表的哪几行实验的后半段是级联引用。前面被拒绝的两次更新在配上级联之后应该能顺利通过这就是级联存在的意义它不是放宽约束而是给约束提供一套自动的、成对执行的补偿动作让父行变化时子行跟着变关系在整条语句结束时依然成立。4.1 级联不是触发器它在语句内部完成扩散-- 给 sc 表的外键补上级联更新 ALTER TABLE sc ADD CONSTRAINT fk_sc_course FOREIGN KEY (cno) REFERENCES course(cno) ON UPDATE CASCADE;关键点是级联动作由约束本身定义不需要额外建触发器。主表键值被改写时引擎在索引里定位所有引用旧值的子行逐条改写动作发生在同一条语句、同一个事务里。任何一行失败整条语句连同已改的子行一起回滚不会出现“主表改了、子表改了一半”的状态。这个特性在数据修复时很好用但在大表上要小心一条看起来只影响一行主表的 UPDATE可能扩散成几万行的子表写入事务日志量随之放大锁的持有范围也扩大到所有被触及的子行。级联写得越深这种放大越明显。做数据订正前先SELECT COUNT(*)数一下会连带影响多少行是个很便宜的习惯。4.2 自引用外键上的级联course.cpno 指向 course.cno 的难题实验里有一个绕不开的失败course 表的 cpno 参照本表的 cno想给这条外键加上级联更新语句直接被拒绝。原因在于自引用结构下级联路径可能成环——改 cno 会触发 cpno 的修改而 cpno 又是一堆 cno 的候选引用引擎无法在执行前静态确定这条链会终止在哪里于是干脆不允许你声明这个级联。常见的提示是“可能会引起循环或多重级联路径”。-- 自引用外键 级联更新会在建约束阶段被拒绝 ALTER TABLE course ADD CONSTRAINT fk_course_cpno FOREIGN KEY (cpno) REFERENCES course(cno) ON UPDATE CASCADE;这里要把话说准结论是“这种形态下引擎不允许你声明级联”并非“外键不能实现级联更新”。两者差别很大——前者是建模限制后者会让人误以为级联能力本身有缺陷。同一个限制也会出现在一个表存在多条级联路径的场景里比如一张表同时被两个带级联的外键指向引擎同样会拒绝因为它没法保证这些路径不互相干扰。4.3 没有级联时怎么保证正确性插、迁、删三段式实验正文给出的结论是更新后的外键值必须在主表存在插入时数据也必须在主表存在删除主表时要先处理从表。用机制语言重述一遍就是所有写操作都必须顺着依赖方向走。落到“改主表键值”这个具体场景正确的操作顺序是三段式BEGIN TRAN; -- 1 先立新父行让 00108 在 dept 里先存在 INSERT INTO dept (DeptNo, DeptName) VALUES (00108, 待补名称); -- 2 把从表迁到新父此时 00108 已存在父键查找通过 UPDATE person SET DeptNo 00108 WHERE DeptNo 00101; -- 3 最后拆掉旧父行此时已无子行引用 00101反向查找落空 DELETE FROM dept WHERE DeptNo 00101; COMMIT;顺序为什么一点都不能反先改子表会撞父键查找先删父行会撞反向查找只有“先建新父、再迁子行、后拆旧父”这条路径全程不违反约束。这就是那句“级联不可用时怎么保证数据正确性”的答案——把一条需要级联的语句拆成三条都不违反约束的语句并用一个显式事务把它们的原子性补回来。也有团队会临时禁用约束NOCHECK再改数据改完立刻重新启用并校验。这个做法在纯离线维护窗口里能用但在有并发写入的库上非常危险约束关闭的那段时间里任何写入都不受检查重新启用时的校验结果可能把自己吓一跳。5. 避坑与排查约束报错从文本到修复动作的五条路径约束相关的故障有个共同点报错信息每次都指向你刚执行的语句但根因往往在存量数据、在约束定义、在命名约定上。下面五条是实验和真实维护里最容易重复发生的。5.1 五条踩坑记录现象、原因、解决其一加主键时报“与 PRIMARY KEY 约束冲突”。现象是 ALTER 语句直接失败提示有重复键。原因是存量数据里存在重复的候选组合或者某一列有 NULL。解决办法是先跑分组计数查询找出重复组把重复行处理掉再建约束。切记主键是“唯一 非空”两条判据只解决了重复、没解决 NULL照样失败。其二加外键时报“ALTER TABLE 语句与 FOREIGN KEY 约束冲突”。现象是约束建不上。原因是从表里存在孤儿行——那些外键值在主表里找不到对应父行。解决办法是用左连接把孤儿行捞出来-- 找出 pay 表里指向了不存在工号的记录 SELECT p.No, p.Year, p.Month FROM pay AS p LEFT JOIN person AS m ON p.No m.No WHERE m.No IS NULL;查出来的每一行都要么修正外键值要么在主表补父行不能直接删了了事——先问清楚这些行为什么会存在往往能发现一段上游数据导入的历史问题。其三唯一约束下第二个 NULL 插不进去。现象是明明字段允许为空插入第二行空值时却报唯一性冲突。原因就是前面说的唯一性判定里 NULL 彼此相等。解决方式是换成带过滤条件的唯一索引或者把这条规则挪出数据库用应用状态位表达“尚未命名”。其四按名字 DROP 约束时报“对象不存在”。现象是脚本在开发库能跑、在测试库报错。原因是用了图形工具自动生成的约束名而那个名字里带对象标识后缀换个库就变了。解决办法是先把名字查出来再动态执行或者干脆在项目里统一约束命名规范强制人工起名。-- 先查出真实名字再删 SELECT name FROM sys.foreign_keys WHERE parent_object_id OBJECT_ID(pay); -- 确认无误后按查到的名字删除 ALTER TABLE pay DROP CONSTRAINT fk_no;其五以为设了级联实际没生效。实验里就出现了这个现象给 sc 表的外键声明了级联更新改课程号时选修记录却没有跟着变。常见原因有两个一是建约束时漏写了ON UPDATE CASCADE子句update_referential_action_desc查出来还是 NO_ACTION二是同一列上存在多个外键约束你改的那条带了级联但另一条没带检查顺序上先撞上了没带的那条整条语句照样失败。排查方式就是回到 3.2 节那段查询把这张表上所有外键连同级联动作列一遍。5.2 排查顺序先看约束定义再看反例数据最后看操作顺序拿到一个约束报错按这个顺序走基本不会绕路。第一步永远是查约束本身。报错文本里的约束名可能是自动生成的先确认它到底约束了哪张表的哪一列、指向哪张表、级联动作是什么。约束定义和你想的不一样后面所有分析都是白费。第二步是找反例数据。主键和唯一约束的反例是重复组合外键的反例是孤儿行检查约束的反例是不满足表达式的行。这三类查询都有固定写法提前准备好脚本出问题的时候直接套。第三步才是审视操作顺序。绝大多数“级联不好用”的抱怨最后都定位到执行顺序上先删了父行再改子行或者在一个事务里把两条互相依赖的语句写反了。顺序问题不会在单条语句上报错只会在第二条语句上突然冒出来所以看日志时要连着看前后几条。注意不要在排查阶段临时关闭约束。关掉之后表的自洽性就没人兜着了而重新启用约束时的校验可能在业务高峰时段触发长时间阻塞问题会从“一条语句失败”升级成“整张表被锁住”。6. 设计取舍规则写在数据库里还是写在应用代码里约束该放在哪一层本质上是三个需求的平衡判定必须在并发下成立、报错信息要让人看懂、规则变更的成本要可控。维度数据库约束应用层校验判定时机语句执行时持锁完成单独的查询语句存在窗口并发安全原子不可绕过需额外加锁或唯一索引兜底报错可读性约束名加冲突行偏底层可定制成业务语言变更成本需 DDL大表上有校验窗口改代码即可可移植性各库语法差异明显跟随应用跨库一致我的取舍原则是能让引擎判定的规则尽量交给引擎。唯一性、存在性、取值范围这三类判定条件简单、后果严重、并发下最容易出问题放数据库最划算。而像“审批流只能从待审变成通过或驳回”这类带状态语义的规则数据库表达式写起来又长又难读报错信息也没法看放应用层更合适数据库只保留一个粗粒度的检查约束做兜底。级联的选择也遵循同一套逻辑。CASCADE 适合那种“父没了子也必须跟着变”的强从属关系比如明细行之于订单头一旦关系里带有历史保留需求级联删除就会变成一场事故——它删得又快又安静误操作的后果在几秒内扩散到整条引用链上。这种场景我宁可写 NO ACTION把删除拆成显式的几步让人有意地决定哪些数据该保留。还有一个上限要提前想清楚不同数据库对级联和约束延迟的支持差异很大SQL Server 没有延迟约束Oracle 和 PostgreSQL 有 DEFERRABLE 选项。如果建模时依赖了“事务内可以短暂不一致、提交时再校验”这种能力后续迁移数据库时会直接卡死。约束选型在项目早期看是细节在迁移那天看是硬约束。从那以后我每次建外键之前都会先把孤儿行查询跑一遍再查一遍这张表上已有的外键和它们的级联动作然后才敲那条 ALTER。这个习惯帮我省掉了好几次“以为设了级联”的返工也让每次数据结构变更都能在事后说得清。希望帮到你。本文还有配套的精品资源点击获取