简介数据库实验四配套文档面向高校数据库课程学习者与实验课备查需求聚焦完整性约束与 T-SQL 操作训练。内容围绕主键与唯一约束的创建删除、表间引用完整性验证、主从表增删改影响、级联引用设置与测试展开并附思考题分析可用于实验报告整理、课堂复盘和考前查漏。资源包共 1 个 docx 文件约 711KB体量轻便适合直接查阅与打印。已有 345 人学习说明其在数据库基础实验场景中具有一定参考价值。文档把失败提示、验证结果与原因解释放在一起读者可据此理解外键冲突、参照完整性被破坏的典型情形掌握主从表操作与级联更新的判断方法并对照自己的实验步骤排查问题提升实验报告的规范性与数据分析能力适合作为数据库约束章节的配套练习与排错参考。1. 一份被当成“抄一遍就行”的实验文档卡住人的问题通常在报错信息数据库实验四这份材料主体是围绕 T-SQL 完整性约束的一组课内任务给 pay 表的三列建联合主键、删掉 dept 表部门名称上的唯一约束、改主表 dept 的部门代号看从表 person 会不会被拦、改从表 pay 的工号看外键认不认、再把级联打开重跑一遍。很多人第一遍照着步骤敲完就交等真正做课程设计、或者接手一张有历史数据的表时才发现约束报错信息比约束语法难读得多。这份文档值得下载的地方不在那几条 ALTER TABLE而在于它把“失败”也一并记了下来——约束名、冲突对象、验证结论都在相当于把约束报错背后的关键过程展开给你看。它适合刚上手 T-SQL、被“与 REFERENCE 约束冲突”卡住、或者要给数据库课设补完整性章节的人下面的顺序基本按实验四的任务线走但每一步都补上了为什么这么写、失败时该看哪里。2. 联合主键与唯一约束三列做主键的取舍和两条删除红线2.1 为什么主键落在 No、Year、Month 三列上而不是加个自增 IDpay 表记录的是某个人在某年某月的工资或缴费数据业务上天然的唯一键就是“人 年 月”。加一个自增 ID 当然能让每一行都有唯一标识但它只保证“行不重复”保证不了“业务不重复”同一个人同一月份插两条记录ID 不一样照样能进表月底对账就出双份。联合主键的作用是把这条业务规则交给存储引擎无论入口是手工敲 SQL、脚本导入还是程序写入都绕不过去。建表时直接声明是最省事的写法-- 建表即声明主键约束名自己起后面按名删除才不会抓瞎 CREATE TABLE pay ( No CHAR(6) NOT NULL, -- 工号长度和类型要与 person.No 完全一致 [Year] SMALLINT NOT NULL, -- 年份Year 是 T-SQL 内置函数名必须加方括号 [Month] TINYINT NOT NULL, -- 月份 1-12 Amount DECIMAL(10,2) NULL, CONSTRAINT PK_pay PRIMARY KEY (No, [Year], [Month]) );这段 DDL 里有两个容易被忽略的点。第一是列顺序主键列的顺序决定聚集索引键的顺序(No, Year, Month) 适合“先按工号定位、再按年月切片”的查询反过来写成 (Year, Month, No)按工号查就变成扫描。第二是类型对齐No 用 CHAR(6)从表 person 的对应列也必须是 CHAR(6)类型或长度不一致时后面建外键要么直接失败要么产生隐式转换让索引失效。这是建库阶段最容易留下的隐患改起来代价很大。表已经存在时用 ALTER 补-- 表已存在时补加联合主键 ALTER TABLE pay ADD CONSTRAINT PK_pay PRIMARY KEY (No, [Year], [Month]);和建表时声明不同ALTER 加主键会先校验现有数据表里只要有一行重复或者任一主键列为 NULL整条语句直接失败而且不能用 WITH NOCHECK 跳过——主键约束不支持在未校验数据的情况下建立。这一点和外键不一样很多人第一次遇到会误以为是权限问题。删除主键的语法很简短-- 按约束名删除注意不是按列名 ALTER TABLE pay DROP CONSTRAINT PK_pay;真正需要留意的是两条边界。其一如果这张表的主键正被其他表的外键引用DROP 会被拒绝必须先处理引用方其二主键默认是聚集索引删掉主键等于把聚集索引一起拆了表上的非聚集索引会全部重建大表上这不是一条可以随手执行的语句。2.2 加约束之前先跑两条脏数据检查ALTER 加主键失败十次里有八次是数据本身不满足。与其盯着报错猜不如提前查。重复组合这样查-- 找出违反“人年月唯一”的重复组合 SELECT No, [Year], [Month], COUNT(*) AS cnt FROM pay GROUP BY No, [Year], [Month] HAVING COUNT(*) 1;返回结果里 cnt 大于 1 的组合就是障碍。处理方式取决于业务如果确认是重复录入按业务时间保留最新一条、删掉旧的如果是两个不同来源的数据撞在一起就得先合并再建约束硬删会丢信息。NULL 单独查一次-- 主键列不允许 NULLALTER 之前先把 NULL 行找出来 SELECT SUM(CASE WHEN No IS NULL THEN 1 ELSE 0 END) AS null_no, SUM(CASE WHEN [Year] IS NULL THEN 1 ELSE 0 END) AS null_year, SUM(CASE WHEN [Month] IS NULL THEN 1 ELSE 0 END) AS null_month FROM pay;三个计数只要有一个不为 0加主键就会失败。NULL 的处理没有通用答案工资记录缺月份通常说明这条数据本身就是残缺的补全或者作废比强行给个默认值更合理。我一般会把这类行先导到一张临时表留档再决定删还是补出了问题还能回溯——这算是被脏数据坑过之后养成的习惯。2.3 唯一约束和主键的差别以及删除时的一处强制限制dept 表上部门名称的唯一约束创建和删除各一条语句-- 部门名称不允许重名 ALTER TABLE dept ADD CONSTRAINT UK_dept_DName UNIQUE (DName); -- 按约束名删除 ALTER TABLE dept DROP CONSTRAINT UK_dept_DName;主键和唯一约束经常被当成一回事实际有几个硬性差异对比项主键约束唯一约束NULL 允许情况完全不允许允许但单列时只允许一个 NULL每表数量最多一个可以有多个默认索引类型聚集可显式改非聚集非聚集能否被外键引用能能表里“单列唯一约束只允许一个 NULL”这一条最反直觉。SQL Server 在唯一索引里把 NULL 当作相等值处理所以第二行 NULL 插进来会直接冲突。不少材料笼统地写“唯一约束允许 NULL”照着理解去建表插第二条空值时就会遇到问题。如果业务上确实需要多行 NULL 并存正确做法是建带 WHERE 条件的筛选唯一索引而不是唯一约束。删除唯一约束还有一条强制限制如果这一列已经被其他表的外键引用DROP 同样会被拒绝。动手前先查一下依赖-- 查有哪些外键指向 dept 表 SELECT fk.name AS fk_name, OBJECT_NAME(fk.parent_object_id) AS child_table FROM sys.foreign_keys AS fk WHERE fk.referenced_object_id OBJECT_ID(dept);有结果就先删外键、再删唯一约束顺序反了只会多一次失败。至于“被约束拒绝时该不该把约束删掉”这个问题我的判断很明确约束是数据一致性的最后一层保障。应用层校验会被批量导入脚本绕过接口校验会被临时修数绕过只有数据库约束是所有入口都绕不过的。短期删掉约束确实省事代价是几个月后花一整天去查一批谁也不知道怎么产生的脏数据。3. 主表改号被拦截参照完整性在语句级就会生效3.1 建表顺序与外键的书写位置参照完整性要跑起来得先有主表再有从表外键定义在从表上指向主表的主键或唯一键。实验里 dept 是主表person 是从表person.DeptNo 引用 dept.DeptNo-- 主表部门 CREATE TABLE dept ( DeptNo CHAR(5) NOT NULL, DName NVARCHAR(30) NOT NULL, CONSTRAINT PK_dept PRIMARY KEY (DeptNo), CONSTRAINT UK_dept_DName UNIQUE (DName) ); -- 从表人员DeptNo 引用 dept CREATE TABLE person ( No CHAR(6) NOT NULL, PName NVARCHAR(20) NOT NULL, DeptNo CHAR(5) NOT NULL, CONSTRAINT PK_person PRIMARY KEY (No), CONSTRAINT FK_person_dept FOREIGN KEY (DeptNo) REFERENCES dept(DeptNo) );顺序颠倒过来先建 person 就会因为找不到 dept 而失败。person.DeptNo 声明为 NOT NULL 也是有意的外键列允许 NULL但 NULL 表示“尚未分配部门”和“分配了一个不存在的部门”是两回事业务上如果要求每个人都有归属就该在列定义上堵死。外键约束只保证“非 NULL 的值必须在主表中存在”管不了 NULL这一点经常被误解。3.2 复现 dept 改号被拦截的完整过程实验里的操作是把部门代号 00101 改成 00108-- 试图修改主表主键值 UPDATE dept SET DeptNo 00108 WHERE DeptNo 00101;只要 person 表里还有 DeptNo 00101 的人这条语句就会报错提示 UPDATE 语句与 REFERENCE 约束冲突冲突发生在 person 表的 DeptNo 列。原因不复杂改完之后那些人的部门代号在主表里再也找不到对应行参照关系断了。这里有个细节值得单独拎出来说——SQL Server 的外键检查是语句级的不是事务提交时才检查。一条 UPDATE 影响多行只要其中一行触发冲突整条语句全部回滚前面已经改成功的行也一并撤销。有人看到报错后去查数据发现“明明只错了一行怎么别的也没改”就是没意识到这一点。验证数据确实没动-- 确认主表数据未被修改 SELECT DeptNo, DName FROM dept WHERE DeptNo IN (00101, 00108);结果里只会看到 00101。想提前判断某次改号会不会被拦截先查引用方有没有数据-- 改号之前先数一下有多少人挂在这个部门下 SELECT COUNT(*) AS ref_rows FROM person WHERE DeptNo 00101;返回 0 说明可以放心改返回大于 0就得先决定这些人转到哪个部门再按“先改从表、后改主表”的顺序操作。这个检查看起来多余但它把所有“改到一半失败”的情况都提前暴露了。3.3 报错信息里的约束名由系统自动生成实验记录里的报错长这样UPDATE 语句与 REFERENCE 约束FK__person__DeptNo__2B0A656D冲突。这个约束名并不正常它是系统自动生成的——建表时如果只写了 FOREIGN KEY 而没写 CONSTRAINT 名字SQL Server 就会拼一个名字出来格式大致是 FK__ 加表名加列名再加一段十六进制后缀。那段后缀和对象 ID 相关换一台机器、重建一次库名字就变了。麻烦在于脚本里如果写了DROP CONSTRAINT FK__person__DeptNo__2B0A656D换到另一套环境执行必然失败而且报错是“找不到对象”排查方向很容易被带偏。规避办法只有一个——所有约束显式命名-- 显式命名跨环境脚本才可靠 ALTER TABLE person ADD CONSTRAINT FK_person_dept FOREIGN KEY (DeptNo) REFERENCES dept(DeptNo);命名我一般用 FK_从表_主表_列 的格式主键用 PK_表名唯一约束用 UK_表名_列名。好处不只是可读出问题时看报错里的约束名能立刻定位到哪张从表、哪一列不用回去翻建表脚本。实验里那两条带十六进制后缀的报错本质上就是“当初命名偷懒”留下的成本。3.4 被拦截后要不要删约束思考题里问得很直接操作被约束拒绝是删约束还是按提示调整数据。这个问题在实际项目里天天遇到。删约束看起来最快代价是把判断权交给了每一个写入方——今天这个脚本忘了校验明天那个接口漏了分支脏数据攒上几个月再想加回约束就得先清洗历史数据工作量是当初的几十倍。正确的处理路径是先读报错确认是哪条约束、哪张从表、哪一列冲突再查冲突数据是什么业务含义最后决定是调整数据、还是调整业务流程。如果确实存在“合法地需要暂时不满足约束”的场景比如批量迁移中间态用事务包起来、在事务内临时禁用约束、迁移完立刻检查并重新启用比直接删约束安全得多。约束不是障碍它是提前把问题暴露在写入那一刻而不是留到对账那天。4. 从表侧与级联引用ON UPDATE CASCADE 加在哪、为什么没生效4.1 从表插入父键不存在就进不去从表侧的第一类操作是插入。往 pay 表插一条工号在主表里不存在的记录-- person 表中不存在工号 999999这条插入会被外键拒绝 INSERT INTO pay (No, [Year], [Month], Amount) VALUES (999999, 2024, 5, 8800.00);报错指向 FOREIGN KEY 约束冲突冲突表是 person。这个拦截是对的一笔工资记录挂在一个不存在的人身上属于孤儿数据后续任何按人汇总的查询都会漏掉它或者产生空值。如果确实需要先落数据后补人员正确做法是把外键列设为可空、先写 NULL等人员建好再回填而不是删掉外键。NULL 在这里代表“尚未关联”语义是清楚的。4.2 从表更新外键列改工号等于换人实验里第二条从表操作是把 pay 里的工号 000002 改成 000020-- 尝试把一条工资记录改挂到另一个工号上 UPDATE pay SET No 000020 WHERE No 000002;报错是 FOREIGN KEY 约束 fk_no 冲突因为 person 表里没有 000020 这个人。从业务角度看这个操作本身也值得推敲——工资记录改工号等于把这笔钱挪到另一个人账上即使 000020 存在也应该走“作废原记录 新增正确记录”的流程而不是直接 UPDATE 主键值。数据库拦住了它等于替业务挡了一次误操作。这里能看出主表侧和从表侧失败原因的差异主表改号失败是因为从表还有人挂着改了就断链从表改号失败是因为改出来的新值在主表里找不到。方向相反本质都是参照关系被破坏。排查时先想清楚自己动的是哪一侧再去查对应的那一侧有没有匹配数据能省不少时间。4.3 级联更新加在哪一侧怎么加级联不是表的属性而是外键约束的属性。已经建好的外键不会因为你“后来想起来要级联”就自动带上必须先删再加-- 第一步删掉不带级联的旧外键 ALTER TABLE person DROP CONSTRAINT FK_person_dept; -- 第二步重建时显式声明级联行为 ALTER TABLE person ADD CONSTRAINT FK_person_dept FOREIGN KEY (DeptNo) REFERENCES dept(DeptNo) ON UPDATE CASCADE ON DELETE CASCADE;加完之后再执行同样的改号语句dept 的 00101 变成 00108person 里原本挂在 00101 下的人会自动跟着变成 00108语句成功返回。实验记录里“设置并测试级联引用”这一步的验证结论就是这么来的。两个子句要分开理解。ON UPDATE CASCADE 管的是主表主键值被修改时从表外键列同步更新ON DELETE CASCADE 管的是主表行被删除时从表里引用它的行一起删除。实验只测了更新但删除的连锁反应更危险——删一个部门底下所有人的记录一起消失而且不会有任何提示。生产库里我基本不用 ON DELETE CASCADE宁可让删除操作失败逼着人先去处理从表数据。还有一个执行顺序问题如果 person 表上同时有多个外键指向不同主表逐个删改时要注意别在中途留下半套约束。稳妥做法是把整个改动包在事务里BEGIN TRAN; ALTER TABLE person DROP CONSTRAINT FK_person_dept; ALTER TABLE person ADD CONSTRAINT FK_person_dept FOREIGN KEY (DeptNo) REFERENCES dept(DeptNo) ON UPDATE CASCADE; -- 确认无误再提交中途出错整体回滚 COMMIT TRAN;事务里的 DDL 在 SQL Server 中是支持的这一点比某些数据库方便。中途任何一条失败ROLLBACK 之后表结构回到原样不会出现“旧约束删了、新约束没建上”的中间状态。4.4 级联的边界多重路径与自引用表级联不是想加就能加。最常见的一条限制是多重级联路径如果 A 表的外键指向 B、B 的外键指向 C同时 A 又直接指向 C 且都带级联SQL Server 会拒绝创建理由是可能出现循环或多重级联路径。这不是 bug是防止一次删除沿着两条路径传播、行为难以预测。自引用表更典型。课程表 course 的 Cpno 引用自身的 Cno表示先修课关系这种结构上加级联更新很容易触发同样的提示。实验课外任务里记录的“级联更新失败”很可能就属于这一类而不是“外键根本不支持级联更新”——SQL Server 是支持 ON UPDATE CASCADE 的失败通常是三个原因之一建约束时压根没写 CASCADE 子句、存在多重路径被拒绝、或者自引用数据本身成环导致传播无法收敛。遇到这种情况替代方案是用触发器在应用层补齐-- 课程号变更后把选修表 sc 里的旧课程号同步为新值 CREATE TRIGGER trg_course_cno_sync ON course AFTER UPDATE AS BEGIN SET NOCOUNT ON; IF UPDATE(Cno) BEGIN UPDATE sc SET Cno i.Cno FROM sc INNER JOIN deleted d ON sc.Cno d.Cno INNER JOIN inserted i ON i.Cno d.Cno; END END;这段触发器的逻辑是从 deleted 伪表拿到被修改的旧课程号从 inserted 拿到新课程号把 sc 里引用旧号的行改成新号。它只适用于单行更新——一次 UPDATE 改多行课程号时deleted 和 inserted 之间没有可靠的对应关系得改成逐行处理或者用 MERGE 的 OUTPUT 子句拿映射。这是触发器的通用局限写之前要确认业务上是否存在批量改号的可能。5. 避坑与排查五条踩坑记录从方括号到多重级联路径5.1 报错“Incorrect syntax near Year”现象建表或加主键时语句在 Year 或 Month 附近报语法错误看起来列名没问题。原因Year、Month 都是 T-SQL 的内置函数名直接当列名写解析器会按函数处理。解决用方括号包起来写成 [Year]、[Month]更彻底的做法是建表阶段就避开这类保留字改成 PayYear、PayMonth 之类。已经建好的表改名成本高方括号是最小改动方案但要在所有引用处统一加漏一处就再报一次。5.2 加主键失败提示与某列冲突现象ALTER TABLE ADD PRIMARY KEY 报错提示违反主键约束或与现有数据冲突。原因表里存在重复组合或者主键列上有 NULL。主键要求唯一且非空两条都不满足时报错信息不会告诉你具体是哪几行。解决先跑 2.2 节那两条检查 SQL把重复组合和 NULL 行找出来。重复数据按业务规则合并或删除NULL 行补全或作废。不要试图用 WITH NOCHECK 绕过——主键约束不支持这个选项绕不过去。5.3 删除唯一约束失败提示被引用现象DROP CONSTRAINT 唯一约束时报错说对象被其他对象依赖。原因这一列已经被某张从表的外键引用删除唯一约束会让外键失去参照目标。解决先查 sys.foreign_keys 找出引用方按“删外键 → 删唯一约束 → 重建外键”的顺序操作。如果只是想换一个唯一键可以在同一个事务里完成删除和重建避免中间状态被其他会话看到。5.4 加了 ON UPDATE CASCADE改主表还是被拦现象确认语句里写了 CASCADE重新执行主表改号依然报外键冲突。原因CASCADE 是外键约束的属性已存在的约束不会自动升级。很多人是在原约束上“补写”了一句带 CASCADE 的 ALTER实际执行的还是旧约束或者语句压根没生效。解决先删旧约束再带 CASCADE 重建两步都不能省。改完用系统视图确认状态-- 确认外键的级联行为是否真的写进去了 SELECT name, update_referential_action_desc AS on_update, delete_referential_action_desc AS on_delete FROM sys.foreign_keys WHERE name FK_person_dept;on_update 显示 CASCADE 才算生效显示 NO_ACTION 说明还是旧约束。5.5 自引用表加级联提示可能出现循环或多重级联路径现象给 course.Cpno 加 ON UPDATE CASCADE 时被拒绝提示可能引起循环或多重级联路径。原因自引用表上的级联传播路径难以收敛SQL Server 在创建阶段就拒绝避免运行期出现不可预测的连锁更新。解决放弃在这个外键上用级联改成 AFTER 触发器同步见 4.4 节或者把改号操作收进一个存储过程用显式事务按“插新行 → 改引用 → 删旧行”的顺序处理。两种方案各有代价触发器对批量更新不友好存储过程要求所有写入方都走同一个入口。选哪个取决于这张表的写入渠道有多少。6. 用系统视图做一次完整性自检把约束状态查清楚实验做完很多人就停在“语句能跑通”这一步。真正接手一个库先做的应该是把约束状态摸一遍而不是等出了问题再逐条查。下面这段查询能一次性拉出所有外键及其级联行为-- 全库外键清单从表、主表、级联行为、是否被禁用 SELECT fk.name AS fk_name, OBJECT_NAME(fk.parent_object_id) AS child_table, OBJECT_NAME(fk.referenced_object_id) AS parent_table, fk.update_referential_action_desc AS on_update, fk.delete_referential_action_desc AS on_delete, fk.is_disabled AS disabled FROM sys.foreign_keys AS fk ORDER BY child_table, fk_name;重点看两列on_update 和 on_delete。只要出现 NO_ACTION就说明这条外键没有级联改主表主键值会被拦。disabled 为 1 更要注意说明约束被人为禁用过此时数据可能已经不一致需要先修复数据再重新启用。第二段查孤立行——外键存在不代表历史数据干净尤其是在约束被禁用过、或者用 NOCHECK 加过约束的库上-- 查 pay 表里在主表 person 中找不到对应工号的孤儿记录 SELECT p.No, p.[Year], p.[Month] FROM pay AS p LEFT JOIN person AS pe ON pe.No p.No WHERE pe.No IS NULL;返回结果不为空说明参照完整性已经被破坏过。这时候直接重建外键会失败得先把孤儿行处理掉要么补建人员记录要么把工资记录归档后删除。最后一段是重建外键时的标准动作带数据校验-- 先信任约束再重建WITH CHECK 会对现有数据做一次全量校验 ALTER TABLE person WITH CHECK ADD CONSTRAINT FK_person_dept FOREIGN KEY (DeptNo) REFERENCES dept(DeptNo) ON UPDATE CASCADE;WITH CHECK 和 WITH NOCHECK 的区别就在这里前者校验存量数据不通过就失败后者跳过校验约束建上了但不可信。我见过为了赶紧上线用 NOCHECK 建的约束两年后没人记得它没校验过查数据问题时被误导了很久。从那以后我每次接手一个带外键的库第一件事就是把 sys.foreign_keys 拉一遍把 NO_ACTION、disabled、以及没显式命名的约束全部列出来再决定哪些要补、哪些要改。这个习惯花不了十分钟但能避免后面几个小时的排查。希望帮到你。本文还有配套的精品资源点击获取