简介：面向数据库课程学习者与实验课备课教师的实验四配套文档，聚焦主键、唯一约束、引用完整性与级联引用等完整性约束的T-SQL实践，可用于课内任务对照、实验报告整理与约束报错复盘。包内仅含1个docx文件，压缩包约711KB，篇幅集中，便于按实验任务顺序查阅与打印。该文档已有345人学习，内容覆盖联合主键的创建与删除、dept表部门名称唯一约束调整、主表更新时参照完整性冲突、从表外键更新失败，以及级联更新设置与测试等环节。文中保留了约束冲突提示与验证结论，并附思考题分析，读者可据此理解参照完整性被破坏的典型情形、约束拒绝操作时的处理思路，以及主从表增删改对数据一致性的影响，适合用作实验过程记录与复习索引。
1. 约束的判定时刻：一条 UPDATE 被拒绝时，数据库到底在算什么
把UPDATE dept SET DeptNo='00108' WHERE DeptNo='00101'丢进查询窗口，回车，看到它被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 改成 00108，person 表里还有一批人的 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。这个习惯帮我省掉了好几次“以为设了级联”的返工，也让每次数据结构变更都能在事后说得清。希望帮到你。
本文还有配套的精品资源，点击获取
