简介数据库实验四配套文档是一份面向高校数据库课程学习者的实验报告完整覆盖SQL Server中T-SQL语句操作数据库完整性的核心实验内容。文档以docx格式呈现围绕主键与唯一约束的创建删除、参照完整性测试、级联引用设置等典型课内任务展开并结合思考题与课外练习梳理常见报错原因与排错思路。资源共1个docx文件压缩包整体约711KB内容结构清晰适合正在完成数据库实验作业或需要复习完整性约束相关知识点的学生参考。已有343人学习浏览读者可通过具体实验案例快速理解主键、外键及级联更新在真实环境中的运行机制掌握如何借助T-SQL命令逐步完成约束配置及异常处理。1. 数据库实验四用 T-SQL 把完整性约束真正“玩明白”很多同学做数据库实验四基本是照着指导书敲一遍ALTER TABLE看到“命令已成功完成”就算过关。但实际上主键、唯一约束、外键引用完整性、级联更新这些才是以后写任何业务系统都要面对的硬骨头——尤其是“外键能不能级联更新”这个问题不少人直到面试被问倒才回头补课。本篇就拿这个实验资源做一次完整拆解包含每一段 T-SQL 的写法、执行结果的含义、系统视图怎么查以及我在实际工程里踩过的几个坑。无论你是刚接触 SQL Server 的实验新手还是要做课设的从业者这份拆解都能让你从“照着敲”变成“真的懂”。2. 主键与唯一约束T-SQL 的创建、删除与本质区别2.1 先把 pay 表的联合主键建起来实验要求是把表pay的No、Year、Month三列联合定义为主键T-SQL 语句如下USE 实验数据库; GO ALTER TABLE pay ADD CONSTRAINT PK_pay PRIMARY KEY (No, Year, Month); GO这段代码做的事是给pay表增加一个名为PK_pay的主键约束约束作用在No、Year、Month三个列上。联合主键的含义是三列的组合值必须唯一且任意一列都不能为 NULL。参数说明PK_pay是约束名后续删除、修改约束都要引用这个名字括号内列的顺序可以调整但要与后续引用时保持一致如果表里已经存在重复的组合数据这条语句会直接报错删除主键约束则用ALTER TABLE pay DROP CONSTRAINT PK_pay; GO删除主键的操作在实验里常常用来“重置”表结构但实际开发中务必谨慎——如果其他表的外键引用了这个主键删除会直接失败或者需要先处理外键关系。2.2 唯一约束删除 dept 表上的部门名称唯一约束唯一约束与主键的区别在于唯一约束允许 NULL 值且一个表可以有多个唯一约束。实验要求删除dept表部门名称列上的唯一约束假设该约束名为UK_dept_dname语句如下ALTER TABLE dept DROP CONSTRAINT UK_dept_dname; GO如果不知道约束叫什么名字可以先用系统视图查询SELECT tc.CONSTRAINT_NAME, tc.CONSTRAINT_TYPE, kc.COLUMN_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kc ON tc.CONSTRAINT_NAME kc.CONSTRAINT_NAME WHERE tc.TABLE_NAME dept AND tc.CONSTRAINT_TYPE UNIQUE; GO这段查询利用INFORMATION_SCHEMA视图把dept表上所有唯一约束的约束名和关联列查出来。实际使用中我一般会在删除前先跑一次这个查询确认约束名避免“对象名无效”之类的报错。2.3 创建与删除主键、唯一约束时的常见报错梳理现象一添加主键时提示“因为表中已经存在重复值”。原因表中现有数据不满足主键的唯一性要求比如No、Year、Month三列组合有重复。解决先查重删掉重复数据再添加约束。SELECT No, Year, Month, COUNT(*) FROM pay GROUP BY No, Year, Month HAVING COUNT(*) 1;现象二删除唯一约束时提示“找不到约束”。原因约束名写错了或者在当前库下根本不存在这个约束。解决用上面的INFORMATION_SCHEMA查询确认实际约束名注意表名前不要加库名前缀。现象三添加主键时提示“列不允许 NULL”。原因主键列中已有 NULL 值存在或者列的数据类型不支持索引。解决先把 NULL 值替换为有效值再添加约束。教训主键和唯一约束看似简单但一旦碰上存量数据就变成数据清洗问题。做实验时是空表还好工作中迁移老系统数据时这一关至少要多花半天。3. 引用完整性与外键约束主表、从表操作的成败边界3.1 更新主表 dept验证引用完整性是否生效实验要求把dept表中部门代号00101改为00108观察是否因引用完整性而失败。这里的前提是person表中有外键FK__person__DeptNo__2B0A656D引用了dept.DeptNo且存在00101的关联记录。UPDATE dept SET DeptNo 00108 WHERE DeptNo 00101; GO执行时会在层级消息中看到类似下面的错误消息 547级别 16状态 0 UPDATE 语句与 REFERENCE 约束FK__person__DeptNo__2B0A656D冲突。原因是修改主表主键值时从表person中已有记录指向00101如果主表键值变了从表记录就失去了参照对象破坏引用完整性。SQL Server 默认的NO ACTION行为会直接拒绝这条更新。注意外键约束名FK__person__DeptNo__2B0A656D是系统自动生成的不同环境可能不同。要在执行前确认约束名可以用以下查询SELECT fk.name AS FK_Name, tp.name AS Parent_Table, ref.name AS Referenced_Table FROM sys.foreign_keys fk JOIN sys.tables tp ON fk.parent_object_id tp.object_id JOIN sys.tables ref ON fk.referenced_object_id ref.object_id WHERE ref.name dept;3.2 更新从表 pay验证外键约束如何拦截“凭空修改”实验接着要求把pay表中的工号000002改为000020验证是否因外键约束而失败。UPDATE pay SET No 000020 WHERE No 000002;如果pay.No是引用person.No的外键列而person表中没有000020这个工号那么这个更新会触发外键冲突报错信息指向外键约束fk_no。这个实验的实质是从表的外键列的值必须来源于主表被引用列中已存在的值。从表不能自行“发明”一个主表中不存在的引用。生产环境里这种情况常见于“先改了子表数据、还没改主表”的并发场景最后导致一堆脏数据。3.3 主表和从表操作对完整性破坏的完整清单结合实验思考题我把主表、从表增删改操作的可能影响整理成了一张表操作对象操作类型破坏完整性的情形违反的约束类型主表INSERT插入与现有主键重复的行实体完整性主键唯一主表DELETE删除被从表引用的行参照完整性外键引用主表UPDATE修改被引用主键值从表未级联更新参照完整性外键引用从表INSERT插入外键值在主表中不存在参照完整性外键引用从表UPDATE修改外键值为主表中不存在的值参照完整性外键引用从表DELETE删除数据本身不破坏完整性但可能影响统计或审计无除非级联删除这些情形在实验里验证了前四条最后一条“从表 DELETE”通常不会破坏引用完整性因为主表并不依赖从表存在。实际业务中如果主表删数据要考虑级联删除那就是另一个层次的设计问题了。4. 级联引用ON UPDATE CASCADE 的成功与“看起来成功”4.1 实验任务完整复现级联更新如何生效实验最后一项任务要求在 dept 表上设置级联更新重新把00101改为00108这次必须成功而且person表里的对应记录也要自动改过来。首先如果之前person表上的外键已经是ON UPDATE CASCADE直接执行UPDATE dept SET DeptNo 00108 WHERE DeptNo 00101;执行后person表中所有DeptNo 00101的记录也会自动变为00108。验证级联是否生效SELECT DeptNo, COUNT(*) AS cnt FROM person WHERE DeptNo IN (00101, 00108) GROUP BY DeptNo;查询结果应显示00101的行数为 000108的行数等于原00101的行数。这是验证级联更新是否生效的最直接方法。如果外键还没设置级联需要先删掉旧外键再重建ALTER TABLE person DROP CONSTRAINT FK__person__DeptNo__2B0A656D; GO ALTER TABLE person ADD CONSTRAINT FK__person__DeptNo__2B0A656D FOREIGN KEY (DeptNo) REFERENCES dept(DeptNo) ON UPDATE CASCADE; GO注意外键名可以保留原名也可以换新名这里用原名便于对照。级联更新只是其中之一级联删除用ON DELETE CASCADE但生产环境要谨慎一个误删会瞬间清空整个关联数据链。4.2 用系统视图确认外键到底有没有级联属性很多时候你以为外键设置了级联其实并没有——因为图形界面里勾错了选项或者 ALTER 语句执行失败被忽略。我在工程里常用这段脚本一次性查看所有外键的级联属性SELECT fk.name AS FK_Name, tp.name AS Parent_Table, ref.name AS Referenced_Table, fk.update_referential_action_desc AS ON_UPDATE, fk.delete_referential_action_desc AS ON_DELETE FROM sys.foreign_keys fk JOIN sys.tables tp ON fk.parent_object_id tp.object_id JOIN sys.tables ref ON fk.referenced_object_id ref.object_id ORDER BY tp.name;update_referential_action_desc字段会显示CASCADE或NO_ACTIONdelete_referential_action_desc同理。这个查询比在 SSMS 里逐个点开外键属性要快得多也适合在实验报告中截图展示“我确实设置了级联”。4.3 级联更新的边界为什么有时候“没级联”反而更好级联更新听起来很方便但在真实项目里并非银弹。我做过一个薪资系统员工表部门号被工资表、考勤表、绩效表同时引用如果一律ON UPDATE CASCADE一次错误的部门编号修改会把几十万行历史数据全部改写想后悔都没药吃。后来改成NO ACTION配合一个存储过程去显式更新关联表虽然麻烦但每一步都有日志可查。所以实验里“设置并测试级联引用”是让学生理解机制但真正上线前一定要评估这个级联是帮你还是害你外键别一味贪图“自动”。5. 课外任务深挖外键不能级联更新用什么方法保证数据正确性5.1 为什么 SQL Server 的外键对“多级联更新”支持有限课外任务里有两个“★★★”问题修改表sc的外键定义让cno级联更新修改表course的外键定义让cpno级联更新。很多同学做的时候发现第一层级联更新成功了但第二层course.cpno引用course.cno却失败或者明明设置了 CASCADE更新后从表数据并没有跟着变。原因在于SQL Server 对自引用外键表内列引用自身另一列的级联更新支持受限而且级联链不能形成环。course.cpno引用course.cno属于自引用如果存在自引用级联更新SQL Server 会直接报错。另外当级联路径过长时触发器或约束的冲突检测也可能像黑匣子一样只告诉你“语句终止”不明说哪一步出了问题。5.2 手工保证数据正确性的三层方案既然系统级联靠不住就得靠代码层面保数据。我在类似场景下一般分三层处理第一层事务 显式 UPDATE 顺序BEGIN TRANSACTION; -- 更新主表引用列 UPDATE course SET cno 0809023601 WHERE cno 0809023501; -- 更新从表 sc 的引用列 UPDATE sc SET cno 0809023601 WHERE cno 0809023501; -- 检查是否全部更新成功 IF ROWCOUNT 0 COMMIT TRANSACTION; ELSE ROLLBACK TRANSACTION;注意这两条 UPDATE 必须在同一个事务里执行任何一步失败都要整体回滚否则会出现从表引用悬空的情况。第二层临时表 映射关系批量更新如果更新的是一批编号比如把08090235010809023510整体改为08090236010809023610最简单的做法是建一张映射表CREATE TABLE #temp_cno_map ( old_cno CHAR(10) PRIMARY KEY, new_cno CHAR(10) ); INSERT INTO #temp_cno_map VALUES (0809023501, 0809023601), (0809023502, 0809023602); UPDATE sc SET cno m.new_cno FROM sc JOIN #temp_cno_map m ON sc.cno m.old_cno; UPDATE course SET cno m.new_cno FROM course JOIN #temp_cno_map m ON course.cno m.old_cno;用映射表的好处是同一个映射可以复用到多个表而且改动哪些值一目了然。把映射表换成正式表还能留存审计记录。第三层禁止 UPDATE 主键只做逻辑删除 新插入最保守、也最推荐生产环境用的方案是业务主键一旦创建永远不修改。要“改”编号就停用旧记录插入一条新编号的新记录再通过外键把从表记录转到新主键上UPDATE dept SET ValidFlag 0 -- 逻辑删除不用 DELETE WHERE DeptNo 00101; INSERT INTO dept (DeptNo, DeptName, ValidFlag) VALUES (00108, 原部门名, 1); UPDATE person SET DeptNo 00108 WHERE DeptNo 00101;这样操作有三层保护旧外键值仍在主表历史记录里存在不会破坏完整性每一次变更都有时间戳和操作人字段可追踪即使程序出错也能通过 ValidFlag 快速恢复。5.3 实验思考题的四条回答和我的实际工程理解实验思考题问的是“主表和从表的增删改操作哪些会破坏完整性”标准答案和我在 3.3 节的表格基本一致。但我想额外补充三点第一插入重复数据破坏的是实体完整性也就是主键或唯一约束第二删除被引用的主表数据、修改被引用的主表主键破坏的是参照完整性第三修改数据时如果写入不符合列定义的值比如性别列写了个“未知”破坏的是用户定义完整性。三类完整性对应三类约束实验的核心就是让你分别触发它们亲眼看到约束阻止了什么。关于“该删除约束还是给操作提示”我的态度很明确生产环境绝不为了一次方便删约束。系统提示约束冲突说明业务逻辑和数据之间存在矛盾该做的是修正数据或修正操作流程不是拆掉约束。约束是最后一道闸门拆了闸门脏数据就像洪水一样涌进来。当修改表增加主键或外键约束时出现“某语句与某列冲突”本质原因都一样存量数据不满足新约束。加主键冲突是因为有重复或 NULL加外键冲突是因为从表有孤儿数据主表中找不到对应值。正确的姿势是先查数据、清洗数据、再上约束而不是删掉约束了事。6. 实验自检用一批临时脚本验证你的环境到底配没配好实验做没做对不能只看“命令成功”要自己造数据再验一遍。我每次给学生检查实验时都强制跑一遍下面这套脚本能快速定位问题在数据、约束还是语句本身。第一步确认关键表存在且结构符合预期SELECT t.name AS Table_Name, c.name AS Column_Name, typ.name AS Data_Type FROM sys.tables t JOIN sys.columns c ON t.object_id c.object_id JOIN sys.types typ ON c.user_type_id typ.user_type_id WHERE t.name IN (dept, person, pay, sc, course) ORDER BY t.name, c.column_id;如果表缺失或列名不一致后面的实验语句全部会失败。先解决结构问题再谈约束。第二步检查所有约束是否和实验要求一致SELECT tc.CONSTRAINT_NAME, tc.CONSTRAINT_TYPE, kc.TABLE_NAME, kc.COLUMN_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kc ON tc.CONSTRAINT_NAME kc.CONSTRAINT_NAME WHERE tc.TABLE_NAME IN (dept, person, pay, sc, course) ORDER BY tc.CONSTRAINT_TYPE, kc.TABLE_NAME;查询结果里PRIMARY KEY类型应出现在pay表上FOREIGN KEY应出现在四个有引用关系的表上UNIQUE约束在删除后应不再出现。第三步验证级联更新的数据结果-- 更新前先看旧值分布 SELECT DeptNo, COUNT(*) FROM person GROUP BY DeptNo; -- 执行更新 UPDATE dept SET DeptNo 00108 WHERE DeptNo 00101; -- 更新后确认从表联动 SELECT DeptNo, COUNT(*) FROM person GROUP BY DeptNo;更新前00101有 N 行更新后00101变为 0 行00108增加 N 行级联更新才算真正成功。第四步验证外键的“防呆”能力-- 预期失败向从表插入主表不存在的引用值 INSERT INTO person (No, DeptNo) VALUES (999999, 99999);这条 INSERT 应该被外键约束拦截报错信息会指明哪一个外键约束冲突。如果插入成功了说明外键约束根本没生效或者你操作的是错误的环境。从那以后我每做一次数据库实验或接手一个旧系统都会强制走一遍这套系统视图检查流程再配合几组“故意失败”的插入和更新来验证约束真的在工作。数据完整性这事看着是数据库的基本功能但真正能禁得起验证的系统少之又少。希望这篇拆解能帮你在实验里一次通过也能在工作里少踩几个约束的坑。本文还有配套的精品资源点击获取