★ FEATURED ARTICLE 简介面向数据库课程上机实验的《数据库实验四》docx文档聚焦T-SQL语句操作与完整性约束适合正在学习数据库原理、需要完成主键与唯一约束/参照完整性/级联引用练习的本科生或自学者。文档完整记录课内任务实现过程、思考题解答与课外实践覆盖pay表No、Year、Month联合主键与dept表部门名称唯一约束的删除、主表与从表更新失败报错分析、级联引用设置等关键内容。课外任务进一步探讨外键无法级联更新时的替代处理办法有助于理解约束背后的设计原则。资源为1个docx文件压缩包约711KB排版清晰可直接对照实验步骤操作。已有344人学习可帮助读者快速掌握SQL Server中实体完整性与参照完整性的验证方法避开外键冲突等常见报错并理解约束被拒绝时的正确处理思路。1. 数据库实验四约束冲突报错是多数人卡住的第一道坎数据库实验里最让新手崩溃的报错往往不是语法错误而是这句“UPDATE 语句与 REFERENCE 约束冲突”。明明 SQL 写对了表存在、列存在、值也存在整条语句就是被拒受影响行数还是 0。以 T-SQL 做完整性约束的实验里联合主键建不上、唯一约束删不掉、主表更新被拦、从表更新被拦、级联引用失效每一步都可能把人卡住。我按实验顺序把每个报错的触发条件和根因拆开讲主键与唯一约束怎么建、怎么删为什么更新主表会被 REFERENCE 约束拦截为什么改从表会触发 fk_no 冲突以及 ON UPDATE CASCADE 设置了为什么还是失败。适合正在写数据库实验报告、对着约束报错无从下手的读者照着做就能把每一条验证结果填进报告。2. 主键与唯一约束联合主键建不上、唯一约束删不掉的排查2.1 给 pay 表加联合主键ALTER TABLE 的正确姿势与失败场景实验第一个任务是把 pay 表的 No、Year、Month 三列联合定义为主键。pay 是工资表一个员工一个月只有一条记录而 No 只是工号单独做主键意味着一个员工全表只能出现一次显然不符合业务语义。所以要靠“组合”来约束同一个人、同一年、同一个月只能有一条工资记录。ALTER TABLE pay ADD CONSTRAINT PK_pay PRIMARY KEY (No, Year, Month);ALTER TABLE 表示修改已存在的表结构ADD CONSTRAINT 是追加约束PK_pay 是约束名PRIMARY KEY (No, Year, Month) 声明这三列组合的值在全表唯一。这里为什么不用 CREATE TABLE 里直接写 PRIMARY KEY因为 pay 表已经存在CREATE TABLE 只能管建表当时ALTER TABLE 才能回头补约束。联合主键的规则是“组合唯一”同一工号可以出现多次但工号年份月份的组合不能重复这条约束同时把三列都做成非空正好挡住“工资记录找不到归属员工”和“同一月发两次工资”这两类脏数据。如果这张表里已经有重复组合执行会直接报错提示某语句与某列冲突。别急着删约束重建先查数据SELECT No, Year, Month, COUNT(*) AS cnt FROM pay GROUP BY No, Year, Month HAVING COUNT(*) 1;GROUP BY 按三列分组HAVING 过滤出出现次数大于 1 的分组。有结果说明存在相同组合的记录要先清理或合并数据再重新加主键。删除主键则简单得多约束名对得上就行ALTER TABLE pay DROP CONSTRAINT PK_pay;DROP CONSTRAINT 后面跟的约束名必须和 ADD 时写的一致。如果约束名记不住就去 sys.key_constraints 里查2.2 节会讲这套查询。2.2 删除唯一约束约束名是黑匣子先查系统视图再动手第二个任务删除 dept 表部门名称列上的唯一约束。T-SQL 的删除语句长这样ALTER TABLE dept DROP CONSTRAINT UK_DeptName;但很多人直接执行就翻车报“找不到对象因为它不存在或者没有权限”。原因很简单UK_DeptName 是你以为的名字实际建表时如果没有显式命名SQL Server 会生成一套自动约束名格式类似 UK__dept__DepartmentName__一串随机十六进制手工根本拼不出来。常见做法是先查系统视图拿到真实约束名SELECT name, type_desc FROM sys.key_constraints WHERE parent_object_id OBJECT_ID(dept) AND type UQ;sys.key_constraints 存的是主键、唯一约束等键约束type 等于 UQ 就表示唯一约束type_desc 会显示 UNIQUE_CONSTRAINT。如果当年是用 CREATE UNIQUE INDEX 建的唯一索引那它不在这个视图里要去 sys.indexes 查 is_unique 1 的索引。查到名字后复制出来再执行 DROP。注意唯一约束底层就是一个唯一索引删除约束的同时会删掉对应索引。如果这个部门名列经常被 where 条件查询删除后查询可能会少一条可用索引路径实验里无所谓生产环境要评估一下。2.3 加约束时提示“与列冲突”根因是存量数据不满足新约束思考题第 3 题问的正是这个场景给表增加主键约束时提示出错信息某语句与某列冲突主要原因是什么一句话表中现有数据不符合新增约束的要求。举例说想把“姓名”设为主键但表里有两个同名员工姓名不唯一。主键要求非空且唯一旧数据已经把唯一性破坏了数据库不可能替你把重复数据合并唯一选择是拒绝建约束。同样的原理适用于外键给选课表的课程号加外键让它引用课程表的课程号但选课表里恰好有一个课程号在课程表里不存在外键约束一样会被拒绝——这就是思考题第 4 题的答案。我一般会先找出“不满足约束的存量数据”再决定清理还是修正最后重试建约束SELECT DeptName, COUNT(*) AS cnt FROM dept GROUP BY DeptName HAVING COUNT(*) 1;查出重复后按业务处理要么把重复行合并成一行要么给其中一行改名。数据干净了ALTER TABLE 就不会再报冲突。动手清理前记得先备份原表删错行没有后悔药这是我在别的项目里交过学费的教训。3. 引用完整性冲突主表更新被 REFERENCE 约束拦截的根因3.1 报错现场改 dept 部门代号触发 FK__person__DeptNo__2B0A656D任务三要求测试强制表间引用完整性。场景是 dept部门表作为主表person人员表作为从表person 表的 DeptNo 外键引用 dept 表的 DeptNo。要执行的操作看起来很普通UPDATE dept SET DeptNo 00108 WHERE DeptNo 00101;看起来就是把一个部门代号改掉和 person 有什么关系SQL Server 直接甩回一句错误信息UPDATE 语句与 REFERENCE 约束“FK__person__DeptNo__2B0A656D”冲突。该语句已终止。这句报错里FK__person__DeptNo__2B0A656D 是 SQL Server 自动生成的外键约束名规则是 FK__表名__列名__哈希后缀DeptNo 是 person 表里那列的名称。看到这种带双下划线和十六进制尾巴的约束名基本可以断定它不是手动命名的而是建表脚本里直接写 FOREIGN KEY REFERENCES 时由系统生成的。3.2 数据库为什么要拒绝这次更新NO ACTION 保护参照完整性关键点在 person 表里面还存着不少 DeptNo 00101 的员工。如果把主表 dept 里的 00101 改成 00108person 表里这些员工就指向一个不存在的部门变成孤儿记录——这种状态在数据库术语里叫破坏参照完整性从表失去了参照对象。SQL Server 里外键默认的引用动作是 NO ACTION意思很直白只要主表的键值正被从表引用主表就不许改、不许删。初看这个设计挺不近人情其实它是参照完整性的最后防线。如果不拦截后续任何 join 查询都会悄悄丢掉这些员工统计报表也全部失真。这个实验的验证结果报告里写“未完成主表 dept 更新操作影响从表 person”真实含义是UPDATE 被拦截主表数据没变从表也因为主表的拒绝而没受到连带影响。数据库宁可拒绝一条业务上“想改”的语句也不让数据进入不一致状态这是约束存在的意义。3.3 验证三步法查引用、触发冲突、确认未生效我一般会按三步把这条结论写进实验报告每一步都有实际输出-- 1. 先查从表中有多少行引用了旧值预判冲突 SELECT DeptNo, COUNT(*) AS ref_count FROM person WHERE DeptNo 00101 GROUP BY DeptNo; -- 2. 执行被测试的 UPDATE预期被拒绝 UPDATE dept SET DeptNo 00108 WHERE DeptNo 00101; -- 3. 确认主表数据没有被修改 SELECT DeptNo FROM dept WHERE DeptNo IN (00101, 00108);第一步的 COUNT(*) 统计引用行数只要大于 0就说明旧部门代号还在被使用第二步用于触发真实报错把消息窗口里的约束名完整记录下来第三步用 WHERE IN 同时覆盖新旧两个值确认主表里只存在原来的 00101没有出现 00108说明 UPDATE 没有部分生效。实验报告里要求填“验证结果未完成主表 dept 更新操作”就是靠这一步的查询结果撑起来的。3.4 真要改主表键值怎么处理先改从表还是重建约束实验里被拒绝就结束了但实际开发中经常真的需要改部门代号。两条路都可以走区别在业务是否允许“硬迁移”。路线一先改从表、再改主表UPDATE person SET DeptNo 00108 WHERE DeptNo 00101; UPDATE dept SET DeptNo 00108 WHERE DeptNo 00101;顺序不能反先改主表会被 REFERENCES 约束拦截先改从表之后person 里旧部门代号没人引用了主表更新才能通过。代价是如果 person 表有几万行这是一笔不小的写操作建议在低峰期执行。路线二先删外键约束、改完再补ALTER TABLE person DROP CONSTRAINT FK__person__DeptNo__2B0A656D; UPDATE dept SET DeptNo 00108 WHERE DeptNo 00101; ALTER TABLE person ADD CONSTRAINT FK_person_dept FOREIGN KEY (DeptNo) REFERENCES dept(DeptNo);这是很多人说的后悔药做法。但中间那段没有外键的时间窗里任何程序都可能写入指向不存在部门的脏数据生产环境我一般不用实验里作为对比思路记录一下没问题。恢复外键之前建议先跑一遍 2.3 节那种孤立数据查询确认没有脏数据再补约束。4. 从表更新被拒FOREIGN KEY 约束 fk_no 的触发条件与处理4.1 报错现场pay 表工号改值触发 fk_no 冲突第四个任务把矛头转向从表。person 是主表工号 No 是主键pay 是工资从表pay.No 外键引用 person.No。这次要改 pay 表的工号UPDATE pay SET No 000020 WHERE No 000002;报错UPDATE 语句与 FOREIGN KEY 约束“fk_no”冲突。该语句已终止。注意这次报错的关键字是 FOREIGN KEY而第 3 章是 REFERENCE。两个报错指向同一个机制只是撞在约束的不同侧很多人在这里被绕晕。fk_no 这个约束名是手动起的比自动生成的 FK__pay__No__哈希 好认得多说明建表脚本在定义外键时显式给了名字。实验报告里要把 fk_no 原样写进去最好再注明它定义在 pay 表、引用 person 表。4.2 根因外键列的新值必须落在主表键值集合里为什么这条 UPDATE 被拒根本原因一句话从表的外键列每个值都必须在主表的键值集合里存在。000002 在 person 表里存在但改成 000020 之后person 表里没有工号为 000020 的员工这条工资记录就凭空指向一个不存在的人参照完整性被破坏。这里有个很多人第一次接触时想不明白的例外外键列允许写入 NULL。比如 pay.No 可以是 NULL表示这条工资记录暂时没关联到具体员工。SQL 的约定是NULL 表示“未知”未知值无法判断它是否破坏参照完整性所以放行但 000020 是一个明确的值不在主表里就是不符合约束。做实验时别拿“外键列能填 NULL”去质疑“为什么不能随便填值”两者不矛盾。验证方式跟第 3 章一样执行 UPDATE、收到 fk_no 冲突、再 SELECT 确认 pay 表数据未变。这一步在实验报告里对应“修改从表的 No不是从主表 person 参照所得违背了参照完整性”翻译过来就是你改出来的新值不在主表允许的取值范围内。4.3 主表被拒与从表被拒两种报错的对照很多同学把第 3 章和第 4 章的报错搞混区分起来只看“谁被改”操作对象报错关键字典型例子含义处理方向更新主表 dept 的 DeptNoREFERENCE 约束FK__person__DeptNo__2B0A656D主表键值正被从表引用禁止直接改动先改从表或启用 ON UPDATE CASCADE更新从表 pay 的 NoFOREIGN KEY 约束fk_no从表外键列改成了主表里不存在的值先确认新值在主表存在再执行第一行是“改主表被从表拽住”第二行是“改从表够不到主表”。记住这个方向感以后看到报错能立刻判断问题出在链路哪一端。还需要区分一点REFERENCE 约束报错里出现的依旧是一个外键约束它保护的是主表被引用时不允许断链FOREIGN KEY 约束报错保护的是从表不能引用不存在的主键。两者都来自同一个外键定义只是站在不同侧报告冲突。4.4 哪些增删改会破坏完整性思考题里的四类典型操作思考题第 1 题问“哪些主表和从表的增删改操作会破坏完整性约束”这是要写进实验报告的四类情况插入类往从表插一行外键列的值在主表不存在或者往主表插入一行主键值已经存在。删除类删除主表一行而这行数据正被从表引用从表瞬间失去参照对象。修改类改主表主键值从表引用跟不上改从表外键值为主表没有的值改普通列值时违反 CHECK 等用户定义约束。举例来说选课表有一行课程号不存在的选课记录就是插入破坏参照完整性课程表删掉一门被选课引用的课就是删除破坏参照完整性性别列只允许男或女某程序把它改成其他值就是修改破坏用户定义完整性。实体完整性主要体现在主键重复上课程表里再插一条相同课程号主键约束直接拦截。这四类情况里约束拦截的本质都是操作的结果会让数据不满足某个显式声明过的规则。5. 级联引用与常见问题排查ON UPDATE CASCADE 失效的三种情况5.1 设置级联引用先删旧外键再带 ON UPDATE CASCADE 重建第五个任务开始设置级联引用让主表 dept 的部门代号从 00101 改成 00108 时从表 person 自动跟着变。默认的外键行为是 NO ACTION所以必须先把第 3 章那个外键删掉再重建为级联版本ALTER TABLE person DROP CONSTRAINT FK__person__DeptNo__2B0A656D; ALTER TABLE person ADD CONSTRAINT FK_person_dept FOREIGN KEY (DeptNo) REFERENCES dept(DeptNo) ON UPDATE CASCADE;第一句把旧约束卸掉第二句用同样的外键列、同样的引用目标但加上了 ON UPDATE CASCADE。这句的语义是主表 dept 的 DeptNo 发生更新时数据库自动把 person 表里引用旧值的行改成新值不需要应用层介入。执行完重建再跑那条之前被拒的 UPDATEUPDATE dept SET DeptNo 00108 WHERE DeptNo 00101;这次不会报错受影响行数是主表 1 行紧接着去查 person 表原本 DeptNo 00101 的员工全部变成 00108。实验报告里“完成主表更新操作影响从表”指的就是这个效果。注意重建外键后约束名变了你会在报错记录或工具里看到 FK__person__DeptNo__38996AB5 这样的新哈希名这和 2B0A656D 是同一条外键在不同时期的两个名字不要误以为是两条约束。另外ALTER TABLE ADD CONSTRAINT 时如果同名约束已存在会报“数据库中已存在名为 xxx 的对象”所以先 DROP 再 ADD 的顺序不能省。5.2 课外任务翻车点自引用外键让级联更新直接失败课外任务是修改 sc 表和 course 表的外键定义给 sc.cno 建外键引用 course.cnoON UPDATE CASCADE再给 course.cpno 建外键引用 course.cno同样级联更新。然后测试把 course 里 0809023501 改为 0809023601看选修记录和课程先修关系是否跟着级联更新。实验结果很扎心级联更新失败数据库直接拒绝。原因要从外键的“级联路径”说起。course 表的 cpno 指向前置课程号cpno 本身引用的是同一张表的 cno这叫自引用外键。SQL Server 对外键有个限制不允许创建会产生循环或多条级联路径的外键定义因为它无法确定级联更新的执行顺序也避免同一数据被多条路径按不同结果覆盖。具体到这次实验sc.cno → course.cno 是一条级联路径course.cpno → course.cno 又是同表上的一条路径两条路径都挂到 course.cno。SQL Server 在创建后者的外键定义时就会报警提示可能引起循环或多个级联路径即使创建成功执行 UPDATE 时也会把整条语句终止。课外练习里“20809023506 号课程 数据库原理 的 cpno 是否级联更新没有级联更新失败”就是这个原因。这就是课外任务想让你看到的点外键不是万能钥匙级联更新在复杂引用关系下会被数据库主动禁掉不能依赖它解决所有数据一致性问题。5.3 没有级联更新怎么保证数据正确三步事务手动补偿课外思考题第三问外键不能实现级联更新用什么方法保证数据正确答案是按“先建新、再迁移、后删旧”的顺序手动补偿整个过程包在事务里BEGIN TRANSACTION; -- 1. 在主表插入新键值先有新的参照对象 INSERT INTO course (cno, cname, cpno) SELECT 0809023601, cname, cpno FROM course WHERE cno 0809023501; -- 2. 更新从表把引用旧键值的行迁移到新键值 UPDATE sc SET cno 0809023601 WHERE cno 0809023501; -- 3. 等没有记录引用旧值了再删除主表旧键值 DELETE FROM course WHERE cno 0809023501; COMMIT TRANSACTION;三步顺序有讲究。第一步先造出新的主键值让 sc 表随时有可引用的对象第二步把从表引用整体挪到新值上此时旧值仍然存在数据始终一致第三步等没有记录引用旧值了再删旧主键行。任何一步失败执行 ROLLBACK TRANSACTION 全部撤销——这是数据库给的后悔药。如果新值和现有主键重复第一步会因主键冲突失败事务回滚后数据不变提醒你换个不冲突的编号。同理如果 sc 表里有大量行要迁移UPDATE 会锁表生产环境建议分批处理或低峰期执行。这也回应了思考题第 2 题遇到约束拒绝时不应该删约束而是按约束提示修正数据约束是保证数据正确性的底线。5.4 避坑记录五条真实的约束踩坑经验整理几个最容易重复踩的坑每条按“现象 → 原因 → 解决”的顺序记录。现象加主键或唯一约束时提示“某语句与某列冲突”。原因存量数据存在重复不满足新约束。解决先按 2.3 节的 GROUP BY HAVING 查重清理再重新建约束不要盲目删数据。现象DROP CONSTRAINT 报“对象名无效”。原因约束名是系统生成的哈希名手写拼不出来。解决先查 sys.key_constraints 或 sys.foreign_keys复制真实约束名再删。现象同一外键列在报错记录里出现 2B0A656D 和 38996AB5 两个约束名。原因外键被删除重建过哈希后缀变化是正常的不是出现了两条约束。解决以 sys.foreign_keys 里当前存在的名字为准。现象建了 ON UPDATE CASCADE 但执行 UPDATE 主表仍然失败。原因外键重建时漏了级联选项或者旧约束没删干净导致重复定义。解决确认 ADD CONSTRAINT 语句里带 ON UPDATE CASCADE并且旧约束已删除。现象课外任务中修改 course 的 cnosc 表没有级联更新。原因自引用外键与 sc 外键形成多条级联路径SQL Server 拒绝执行。解决按 5.3 节的事务手动三步补偿不要试图强行创建级联。6. 快速定位约束冲突先查依赖、再查数据、后动手被约束报错卡住时最快的定位路径不是盯着报错文本猜而是把“谁引用谁、数据是否干净”一次性查出来。我习惯用三套查询开道。第一套查全部外键关系直接在实验报告里复制约束名SELECT fk.name AS foreign_key_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;输出列表里能看到每个外键约束名、它定义在哪个表parent_table、引用哪张表referenced_table。实验里遇到的 FK__person__DeptNo__2B0A656D、fk_no 都在这里现形复制粘贴比手敲靠谱。第二套查“孤立数据”专门找从表里引用不到主表的脏值SELECT p.DeptNo FROM person p LEFT JOIN dept d ON p.DeptNo d.DeptNo WHERE d.DeptNo IS NULL;LEFT JOIN 以 person 为左表右表 dept 匹配不上的行d.DeptNo 就是 NULL这些行就是破坏参照完整性的孤儿记录。加外键约束前如果这个查询有结果说明必须先清理数据。第三套验证闭环执行完 UPDATE 后用受影响行数和一句 SELECT 确认数据状态。凡是 UPDATE 被约束拦截受影响行数都是 0再用 SELECT 查一次新旧值是否存在就能把“失败未生效”钉死在报告里。这三套查询基本覆盖了实验四里所有约束报错的定位场景。从那次实验之后我每次动约束都强制走一遍“先查依赖、再查数据、后动手”的流程做完更新必再跟一条 SELECT 验证再没被约束报错绕晕过。希望帮到你。本文还有配套的精品资源点击获取
阅读完成 · 觉得有帮助？
