SQL Server完整性约束实战：主键外键与级联操作全解析

简介面向数据库课程学习者的实验四文档聚焦利用 T-SQL 完成完整性约束的创建、删除与验证。内容以 pay、dept、person 三张表的操作逐步展开涵盖主键与唯一约束的增删、引用完整性测试、级联引用设置并附四道思考题和课外任务其中涵盖外键级联更新失败时的处理思路。文中给出具体报错信息与验证结果如更新主表或从表时与外键/引用约束冲突的完整提示能帮助读者理解约束冲突出现的原因与处理思路。资源共 1 份 docx 文档压缩包大小 711KB打开即可查看实验过程与完整解答已有344人学习下载适合正在练习数据库完整性实验、需要对照操作细节与排错方法的高校学生。文档将各步骤的 SQL 语句、执行结果及失败原因逐一列出可作为实验报告撰写和课后复习的参考。1. 数据库实验四把完整性约束亲手撞一遍比背十遍定义都管用很多初学数据库的人在课上听主键、外键、级联时都觉得自己懂了等真正在 SQL Server 里敲一条 UPDATE看到UPDATE 语句与 REFERENCE 约束冲突整条语句被拒才明白约束是数据库的硬规则不是纸面上的概念。这份实验四资源围绕 T-SQL 操作完整性约束展开联合主键的创建与删除、唯一约束的创建与删除、主表与从表引用完整性的双向测试、级联引用的设置与验证还附带 4 道思考题和 3 级课外挑战。适合正在修数据库课、需要提交实验报告的学生也适合想亲手验证约束行为的后端开发者。照着它把 pay、dept、person、sc、course 这几张表操作一遍主表从表之间的坑基本就全摸清了。2. 创建与删除主键、唯一约束ALTER TABLE 的完整操作链2.1 联合主键为什么存在No、Year、Month 三列的逻辑依据实验课内任务的第一步是把表 pay 的 No、Year、Month 三列联合定义为主键。先别急着抄语句想清楚为什么是这三列。pay 是一张类似工资或支付流水的基础表同一名员工在一年里可能被记录多次同一个月份里也可能有多名员工的记录所以单独拿 No、Year、Month 中任何一列做主键都会撞唯一性。只有把三者组合起来才能确定一条流水记录的身份——某员工某年某月只能有一条记录。这就是联合主键复合主键的典型应用场景。主键背后是实体完整性约束值必须非空且唯一。SQL Server 中一张表只能有一个主键但主键可以由最多 16 列、总字节数不超过 900 字节的列组合而成。平时建表时把主键直接写进 CREATE TABLE 当然可以但实验要求的是利用 T-SQL 语句创建、删除主键也就是对一张已存在的表做结构变更这就要走 ALTER TABLE 路线。另外一点容易被忽略ADD CONSTRAINT PRIMARY KEY 会在列上自动创建唯一索引默认是聚集索引所以加主键不仅仅是加约束还会影响这张表的物理存储顺序对大表执行时最好选在业务低峰期。2.2 创建与删除联合主键的 T-SQL 写法实验指定的操作链是先给 pay 表加上名为 PK_pay 的主键约束测试完之后再删掉-- 创建联合主键把 No、Year、Month 三列绑定成一张表的唯一身份 ALTER TABLE pay ADD CONSTRAINT PK_pay PRIMARY KEY (No, Year, Month);ADD CONSTRAINT 是 SQL Server 添加命名约束的统一入口PK_pay 是约束名遵循PK_表名的命名习惯。PRIMARY KEY 后面跟列清单三列顺序会影响索引键的顺序查询时如果 WHERE 条件总是先带 No那 No 放最左边通常更高效。删除主键则简单得多只需要约束名不需要再写一遍列清单-- 删除主键约束DROP 只需要约束名 ALTER TABLE pay DROP CONSTRAINT PK_pay;注意 DROP CONSTRAINT 只删约束不删列pay 表结构没有任何变化只是三列失去了联合主键这层保护。销毁容易重建也容易但约束名必须记得准确所以养成先查系统视图、再执行 DDL的习惯很重要-- 查看 pay 表现有的主键约束 SELECT name, type_desc FROM sys.key_constraints WHERE parent_object_id OBJECT_ID(pay) AND type PK;这段查询返回的 name 就是 DROP 时要用的确切约束名。为什么不给约束留空让系统自动命名实验里就有现成的教训person 表上的外键名是系统生成的 FK__person__DeptNo__2B0A656Dpay 表上的外键名则是手写的 fk_no。排错时看到 fk_no 一眼就知道是哪张表的哪条链路看到 FK__person__DeptNo__2B0A656D 虽然也能猜个大概但后缀是哈希值全靠对照表结构才能确认。所以凡是手写 T-SQL 的场合我都会带上明确前缀的约束名PK_、UK_、FK_、CK_ 四类前缀各管一摊后期维护省事很多。2.3 唯一约束创建与删除以及和主键的边界实验第二步要求删除 dept 表部门名称列上的唯一约束。唯一约束和主键非常像都保证列值唯一但边界必须分清一张表只能有一个主键却可以有多个唯一约束主键列不允许 NULL唯一约束列在 SQL Server 中允许出现一个 NULL。这个差异常被拿来当考点。创建与删除唯一约束的写法如下-- 创建唯一约束部门名称不允许重复 ALTER TABLE dept ADD CONSTRAINT UK_dept_name UNIQUE (DepartmentName); -- 删除唯一约束 ALTER TABLE dept DROP CONSTRAINT UK_dept_name;逻辑很直白先给 DepartmentName 列加上约束名 UK_dept_name 的唯一约束验证行为后再删除。删除之后 dept 表重新允许出现同名部门数据完整性退回到无人看守状态。两者对比可以整理成一张表对比项主键约束唯一约束每表数量只能 1 个可多个NULL 值不允许允许 1 个默认索引聚集索引非聚集索引语义实体完整性业务唯一性删除唯一约束同样面临约束名记不准的问题补救方式还是查系统视图把 type 换成 UQ 就是唯一约束-- 查找 dept 表上的唯一约束名 SELECT name FROM sys.key_constraints WHERE parent_object_id OBJECT_ID(dept) AND type UQ;2.4 加主键失败先查现有数据再动表结构思考题第 3 问就是这个场景修改表增加主键约束时提示某语句与某列冲突主要原因是什么。最常见的就是重复值——比如想把姓名设为主键但表里已经有两个同名员工。我一般会先用分组查询把元凶揪出来再决定是否清理数据-- 查 pay 表中违反联合主键唯一性的重复组合 SELECT No, Year, Month, COUNT(*) AS cnt FROM pay GROUP BY No, Year, Month HAVING COUNT(*) 1;GROUP BY 把三列组合相同的行聚成一组HAVING 过滤出计数大于 1 的组合查出来的每一行都是挡在创建主键路上的重复数据。如果结果为空再看另一个原因——列里有 NULL因为主键列不允许 NULL-- 检查三列中是否存在 NULL SELECT COUNT(*) AS null_cnt FROM pay WHERE No IS NULL OR Year IS NULL OR Month IS NULL;两个方向都查干净之后ALTER TABLE 一般就能顺利通过。这条排查路径同样适用于后面加外键约束失败的场景只是把查重换成查参照。3. 引用完整性测试主表与从表之间哪些操作会翻车3.1 先画出表间参照关系实验三、四、五的核心是强制表间引用完整性。动手之前先把实验涉及的表和外键关系画清楚dept部门表是主表主键是部门代号 DeptNoperson员工表通过 DeptNo 引用 dept所以 person 是从表。person 自己的主键是工号 Nopay 表通过 No 引用 person所以 pay 是从表person 此时又扮演主表角色。课外任务里还有一对关系sc选课表通过 cno 引用 course课程表的 cno而 course 表的 cpno 又引用自己的 cno构成自引用。引用完整性的规则是从表外键所在表中的外键列取值要么为 NULL要么必须存在于主表被引用的列中。违反这条规则的操作会被 SQL Server 直接拒绝报错信息里带着约束名约束名的可读性直接影响排错速度。实验文档里 pay 表的外键名是 fk_noperson 表的外键名是 FK__person__DeptNo__2B0A656D前者显然是手工命名的后者是系统自动生成的——一个实验两种命名风格说明命名规范和排错速度直接挂钩。3.2 更新主表dept 从 00101 改到 00108为什么被 REFERENCE 约束拦下实验第三步的操作是把 dept 的部门代号 00101 改成 00108-- 尝试更新主表 dept 的部门代号 UPDATE dept SET DeptNo 00108 WHERE DeptNo 00101;执行后报错UPDATE 语句与 REFERENCE 约束 FK__person__DeptNo__2B0A656D 冲突。外键约束默认行为是 NO ACTIONdept 的 DeptNo 正被 person 表引用直接改主表的话person 里所有指向 00101 的员工记录会变成悬空引用所以 SQL Server 宁可拒绝这条 UPDATE。报错里的约束名 FK__person__DeptNo__2B0A656D 是系统自动生成的格式是 FK__表名__列名__哈希后缀从中能直接读出这是 person 表上针对 DeptNo 列的外键。实验验证结果也正如描述未完成主表 dept 更新操作因为一旦修改主表 DeptNo从表 person 失去参照参照完整性被破坏。如果想知道某张表到底挂了哪些外键用系统视图查比对着报错猜名字靠谱-- 查看 person 表上的外键及其引用目标 SELECT fk.name AS fk_name, OBJECT_NAME(fk.parent_object_id) AS child_table, COL_NAME(fc.parent_object_id, fc.parent_column_id) AS child_col, OBJECT_NAME(fk.referenced_object_id) AS parent_table, COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS parent_col FROM sys.foreign_keys fk JOIN sys.foreign_key_columns fc ON fk.object_id fc.constraint_object_id WHERE fk.parent_object_id OBJECT_ID(person);这条 JOIN 查询把外键名称、从表列、主表列一次列全。后面无论遇到什么与某约束冲突都能快速定位是哪条链路上的问题。3.3 更新从表pay 从 000002 改到 000020为什么 FOREIGN KEY 约束也不同意实验第四步把枪口对准从表-- 尝试更新从表 pay 的工号 UPDATE pay SET No 000020 WHERE No 000002;这次报错是 FOREIGN KEY 约束 fk_no 冲突。原因和上一步正好对称pay 的 No 是外键引用 person 的 No 主键000020 这个工号在 person 主表里不存在从表想自己写一个没有出处的值参照完整性同样被破坏。这里值得强调一个容易混淆的点外键检查不是单向的。很多人以为外键只管从表插入实际上主表的删除、更新、插入同样受约束。主表改键值从表会悬空从表改键值主表没有对应项——两个方向都会被拒绝。实验把这两个失败案例背靠背放在一起就是在演示这个对称性。3.4 插入与删除主表、从表各自的允许与拒绝除了更新实验还要求测插入和删除。这四类操作在外键约束下的表现可以归成一张行为表操作场景结果插入主表dept 加新部门允许不影响已有引用删除主表删除被 person 引用的部门 00101拒绝从表失去参照插入从表person 插入 DeptNo 不存在的员工拒绝无源引用删除从表删除 pay 中无子引用的流水允许删除从表删除被 pay 引用的 person 工号拒绝pay 失去参照对应两条典型语句-- 从表插入指向不存在部门的记录应被外键拒绝 INSERT INTO person (No, DeptNo) VALUES (999999, 00999); -- 删除被从表引用的主表记录应被外键拒绝 DELETE FROM dept WHERE DeptNo 00101;提示插入主表通常不会失败因为新主键不涉及旧引用删除主表则要看有没有人引用它。判断的通用方法是先问一句这条记录被谁连着再决定能不能动。3.5 思考题拆解四类破坏完整性的操作怎么归类思考题第 1 问列了四个典型场景本质上是对增删改动作各举一个失败样例在选课表插入一个课程表里不存在的课程号破坏参照完整性课程表插入与现有主键相同的课程号破坏实体完整性删除课程但选课记录仍引用它破坏参照完整性修改性别为约束以外的值破坏用户定义完整性。四类里两类是外键问题一类是主键问题一类是列级 CHECK 约束问题对应实验里先后测过的主键、外键、唯一约束三类约束。思考题第 2 问更偏设计取舍当操作被约束拒绝时应该删除约束还是给出合理提示正确答案是给提示。约束一旦删除不符合逻辑的数据再也无法被自动拦截只能靠事后人工查代价远高于一条 UPDATE 被拦下后重新组织业务操作。生产环境里删约束属于高危险动作做完必须立刻补回等价的校验逻辑。4. 级联引用设置与避坑排查ON UPDATE CASCADE 怎么配、失败看哪里4.1 SQL Server 里改外键默认行为必须先 DROP 再 ADD实验第五步是设置并测试级联引用。SQL Server 不支持直接 ALTER 一个已存在的外键去追加 ON UPDATE CASCADE 选项正确姿势是先删除旧外键再用带级联选项的语句重建-- 第 1 步确认要删的外键名 SELECT name FROM sys.foreign_keys WHERE parent_object_id OBJECT_ID(person); -- 第 2 步删除旧外键 ALTER TABLE person DROP CONSTRAINT FK__person__DeptNo__38996AB5; -- 第 3 步重建为级联更新 ALTER TABLE person ADD CONSTRAINT FK_person_dept FOREIGN KEY (DeptNo) REFERENCES dept(DeptNo) ON UPDATE CASCADE;ON DELETE 子句没写默认就是 NO ACTION删除行为继续保持严格只放开更新这一条链路。重建之后验证级联是否生效-- 修改主表部门代号 UPDATE dept SET DeptNo 00108 WHERE DeptNo 00101; -- 检查从表是否跟着改 SELECT No, DeptNo FROM person WHERE DeptNo 00108;第二次执行同样的 UPDATE 不再报错SELECT 能看到 person 中原本指向 00101 的行已自动变成 00108。这就是级联引用的价值主表键值变化从表外键由数据库自动同步不用写业务代码维护一致性。注意ON UPDATE CASCADE 是外键约束的属性不是表的属性。把外键 DROP 再 ADD 相当于重建约束重建之前要确认没有其它对象依赖旧约束名。这里还有一个前提值得写进笔记外键引用的主表列必须有唯一索引主键或唯一约束否则外键根本建不起来。级联更新传播的路径是主表唯一键到从表外键所以 dept.DeptNo 必须保持唯一这在建表之初就决定了。级联触发时SQL Server 会在同一个事务里完成主表和从表的修改中间状态对外不可见这也是它比手工多语句更新更安全的原因。4.2 课外任务暴露的限制自引用外键加级联SQL Server 直接拒绝课外任务要求把 sc.cno 改成引用 course.cno 的级联外键同时把 course.cpno 也改成引用 course.cno 的级联外键。前一个操作通常能成功sc 的外键重建后course 主表更新 cnosc 选课记录自动跟随。真正翻车的是第二个操作——给 course 加自引用级联。原因在 SQL Server 对外键级联的一条硬限制不允许产生循环或多条级联路径错误 1785。course 的 cpno 引用自身的 cno一旦加上 ON UPDATE CASCADE更新 course.cno 时既要级联更新 course.cpno又要通过 sc 的外键级联更新 sc.cno形成两条级联路径同时指向同一张引用链数据库无法确定传播次序直接拒绝创建约束。这就是实验里记下级联更新失败的根源不是级联功能有玄学而是表结构里存在自引用或多路径触发了 SQL Server 的保守策略。遇到这种情况常见做法是放弃级联改用显式事务脚本或触发器同步数据这也是实验思考题第 3 问要讨论的落点。4.3 避坑清单约束操作最常见的五个翻车点坑 1加主键时报重复键。现象是 ALTER TABLE ADD CONSTRAINT PK 语句被拒提示重复。原因是表里已有数据存在重复组合。解决先跑 GROUP BY ... HAVING COUNT(*) 1 查重清掉重复行再创建约束。坑 2UPDATE 主表被系统命名约束拦截。现象是报错里出现 FK__person__DeptNo__2B0A656D 这种名字。原因是从表有引用且外键默认 NO ACTION。解决先处理从表数据或者把外键改成 ON UPDATE CASCADE二选一不要试图绕过。坑 3加了 ON UPDATE CASCADE 从表却纹丝不动。现象是 UPDATE 主表成功但从表值没变。原因是旧外键没删干净重建的约束名和旧约束不一样实际生效的还是旧的 NO ACTION 约束。解决先查 sys.foreign_keys 确认要删的约束名DROP 之后再 ADD。坑 4给自引用或存在多条级联路径的外键加 ON UPDATE CASCADE报 1785。现象是创建约束失败提示可能引起循环或多条级联路径。原因是 SQL Server 禁止循环级联。解决放弃级联用手工事务或 INSTEAD OF 触发器替代别硬刚。坑 5DROP CONSTRAINT 报找不到约束名。现象是明明建过却删不掉。原因是约束可能是系统自动命名的和你记忆里的名字对不上比如 person 的外键叫 FK__person__DeptNo__38996AB5 而不是你起的 FK_person_dept。解决删之前先查 sys.key_constraints 和 sys.foreign_keys用查出来的确切名字。5. 最后一步级联不可用时的手工一致性方案与复查习惯课外任务的结论把问题抛了回来外键不能实现级联更新时用什么方法保证数据正确性答案核心一句话——让任何时刻的外键值都能在主表找到出处。具体到操作上是三条纪律更新的外键值必须先存在于主表对应字段插入的外键值同样必须在主表存在删除主表记录前先清理或迁移从表中引用它的数据。操作前置条件后果更新主表键值先同步从表引用从表悬空更新从表外键新值在主表存在无源引用删除主表记录先清理从表引用从表悬空无级联时的手工更新我一般用临时值法分三步走把一次换键拆成不违反外键的连续操作BEGIN TRAN; -- 1. 先把从表引用改到临时值 UPDATE person SET DeptNo TMP WHERE DeptNo 00101; -- 2. 再改主表键值 UPDATE dept SET DeptNo 00108 WHERE DeptNo 00101; -- 3. 最后把从表指回新值 UPDATE person SET DeptNo 00108 WHERE DeptNo TMP; COMMIT;三步都在同一个事务里任一步失败整体回滚不会留下半截状态。临时值只要不跟现有主键冲突就行常见做法是加个统一前缀。每次操作完我还会用一条 LEFT JOIN 查孤儿数据验证引用完整性没有被悄悄破坏-- 找出 person 中找不到部门的外键孤儿 SELECT p.No, p.DeptNo FROM person p LEFT JOIN dept d ON p.DeptNo d.DeptNo WHERE d.DeptNo IS NULL;返回空结果说明参照关系完好有行返回说明某一步漏了同步得回去补数据。这份实验四资源把课内任务、思考题、课外挑战串成了一条完整链路照着从主键到外键再到级联走一遍再对照上面的避坑清单约束相关的手感基本就建立了需要动手复现的直接拿这份文档对着敲就行。从那以后我每次动表结构的约束之前都强制自己走一遍先查现有数据、再查系统视图、最后执行 DDL的三步流程先 GROUP BY 确认没有重复再查 sys.foreign_keys 确认要动的约束名最后才动手测完立刻用 JOIN 复查数据。这套流程帮我躲过了好几次同组人翻车的场景也希望帮到你。本文还有配套的精品资源点击获取