简介：本资源为高校数据库原理课程配套实验文档，面向计算机专业本科生及数据库初学者，聚焦关系型数据库完整性约束的核心实践——主键、唯一约束、引用完整性与级联更新的T-SQL实现与故障验证。文档完整呈现5项课内任务的操作过程、报错截图分析（如UPDATE与FK/REFERENCE约束冲突）、失败原因解读及思考题详解，涵盖实体完整性、参照完整性破坏场景、约束添加失败根因（如数据重复、参照缺失）等典型排错逻辑。资源为单文件Word文档（.docx），共1个文件，大小711KB，内容结构清晰，含实验步骤、执行结果、验证结论与课外拓展任务（如级联更新失效分析及数据一致性保障方法）。目前已有347人学习下载，适合课堂同步实训、考前复习与自主验证数据库约束机制的学习者直接使用。

1. 这不是一份普通实验报告：它是一份能让你在 SQL Server 里亲手“掰断”外键、又用级联把它焊回去的完整性实战手册

你有没有试过改一条主表数据，结果整个 UPDATE 直接报错，连错误码都带一串英文缩写？不是语法错了，不是权限不够，而是数据库在死死拽住你——它说：“你动这个字段，底下三张表就全乱套了。”这不是玄学，是参照完整性在发声。这份《数据库实验四.docx》表面看是某高校数据库课程的课内任务文档，实则浓缩了 SQL Server 中最常踩、最难调、最易被忽略的五类完整性操作闭环：联合主键定义、唯一约束动态启停、引用完整性触发时机、外键更新失败的底层归因，以及最关键——级联更新为何“看似配置成功却纹丝不动”。它不讲理论推导，只给可粘贴执行的 T-SQL 语句、真实报错截图对应的修复路径、以及课外任务里那个反复失败的ON UPDATE CASCADE为什么总不生效。适合正在调试生产环境外键逻辑的 DBA、刚被FK__person__DeptNo__2B0A656D报错卡住的后端开发，以及准备数据库原理考试却总分不清“删主表失败”和“改主表失败”差在哪的学生。它解决的不是“怎么写”，而是“为什么这么写才不翻车”。

2. 主键与唯一约束：从联合主键创建到唯一性失效的完整链路

2.1 联合主键不是加个 PRIMARY KEY 就完事：必须显式命名并验证空值容忍度

在 SQL Server 中，联合主键（Composite Primary Key）要求所有参与列组合值唯一且非空。但很多人忽略一个关键点：只要其中任意一列允许 NULL，整个联合主键就无法生效。实验中要求将pay表的No、Year、Month三列设为联合主键，第一步必须确认这三列是否已设为NOT NULL。若未设，直接执行ADD CONSTRAINT会失败。

-- 检查三列是否允许 NULL（关键前置动作） SELECT c.name AS column_name, c.is_nullable FROM sys.columns c JOIN sys.tables t ON c.object_id = t.object_id WHERE t.name = 'pay' AND c.name IN ('No', 'Year', 'Month'); -- 若发现 is_nullable = 1，则先修改列属性（以 No 为例） ALTER TABLE pay ALTER COLUMN No VARCHAR(10) NOT NULL; -- 创建联合主键：必须显式命名约束（如 PK_pay），否则后续删除极难定位 ALTER TABLE pay ADD CONSTRAINT PK_pay PRIMARY KEY (No, Year, Month);

提示：PK_pay是自定义约束名，不是系统生成。SQL Server 在创建约束时若不指定名称，系统会生成类似PK__pay__3214EC27F9E8C2E2的随机名，后期排查或删除时几乎无法人工识别。这是血泪经验——我曾在一个遗留库中花两小时才从sys.objects里捞出某个联合主键的真实名字。

2.2 唯一约束删除不是“去掉限制”，而是解除对业务语义的强制保护

唯一约束（UNIQUE Constraint）与主键不同：它允许 NULL 值存在（且允许多个 NULL），但非 NULL 值必须全局唯一。实验要求删除dept表中“部门名称”列上的唯一约束，说明该列此前可能被误加了唯一性限制，导致插入同名部门（如“技术部（北京）”“技术部（上海）”）失败。

-- 先查出 dept 表上所有唯一约束名（不能靠猜！） SELECT dc.name AS constraint_name, c.name AS column_name FROM sys.default_constraints dc JOIN sys.columns c ON dc.parent_object_id = c.object_id AND dc.parent_column_id = c.column_id JOIN sys.tables t ON c.object_id = t.object_id WHERE t.name = 'dept' AND dc.type = 'UQ'; -- 假设查得约束名为 UK_DeptName，则执行删除 ALTER TABLE dept DROP CONSTRAINT UK_DeptName;

注意：唯一约束名通常以UK_开头，但绝不能凭经验硬写。SQL Server 不保证命名规范，必须通过系统视图查询。曾有同事在测试库删错约束，导致dept表的DeptNo（部门代号）唯一性被误删，后续插入重复代号时毫无报错，直到报表数据对不上才发现。

2.3 验证主键/唯一约束生效：用 INSERT 测试比看文档更可靠

约束是否真正起效，不能只信CREATE语句返回 “命令已成功完成”。必须用边界数据主动触发失败：

-- 测试联合主键：插入完全相同的 (No, Year, Month) 组合 INSERT INTO pay (No, Year, Month, Amount) VALUES ('000001', 2023, 12, 5000.00); INSERT INTO pay (No, Year, Month, Amount) VALUES ('000001', 2023, 12, 6000.00); -- 此行必报错：违反 PK_pay -- 测试唯一约束删除后效果：插入同名部门 INSERT INTO dept (DeptNo, DepartmentName) VALUES ('00102', '人力资源部'); INSERT INTO dept (DeptNo, DepartmentName) VALUES ('00103', '人力资源部'); -- 若 UK_DeptName 已删，此行应成功

关键逻辑：主键冲突报错信息固定含PRIMARY KEY constraint字样；唯一约束冲突含UNIQUE KEY constraint。二者错误码不同（如 2627 vs 2601），但日志里一眼可辨。这是排错第一现场。

3. 引用完整性实战：主表更新失败不是 bug，是数据库在拉警报

3.1 主表更新被拒：不是 SQL 写错，是外键在拦截非法变更

实验中将dept表DeptNo = '00101'改为'00108'失败，报错UPDATE 语句与 REFERENCE 约束"FK__person__DeptNo__2B0A656D"冲突。这个错误名里的FK__person__DeptNo__...明确指出：person表的DeptNo列上存在外键，它正参照dept表。当主表dept的DeptNo被修改，所有person表中DeptNo = '00101'的记录将失去参照对象——这就是参照完整性（Referential Integrity）的刚性保护。

-- 查看该外键具体指向哪张主表、哪列（关键！） SELECT fk.name AS fk_name, tp.name AS pk_table, cp.name AS pk_column, tr.name AS fk_table, cr.name AS fk_column FROM sys.foreign_keys fk JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id JOIN sys.tables tp ON fkc.referenced_object_id = tp.object_id JOIN sys.columns cp ON fkc.referenced_object_id = cp.object_id AND fkc.referenced_column_id = cp.column_id JOIN sys.tables tr ON fkc.parent_object_id = tr.object_id JOIN sys.columns cr ON fkc.parent_object_id = cr.object_id AND fkc.parent_column_id = cr.column_id WHERE fk.name = 'FK__person__DeptNo__2B0A656D';

执行后你会看到：pk_table = 'dept',pk_column = 'DeptNo',fk_table = 'person',fk_column = 'DeptNo'。这证实了约束关系。此时强行更新主表，等于让person表中数百条记录变成“孤儿数据”，SQL Server 绝不允许。

3.2 从表更新被拒：外键不是摆设，它实时校验每一笔变更

实验中尝试将pay表No = '000002'改为'000020'失败，报错FOREIGN KEY 约束"fk_no"。这说明pay.No是外键，参照的是另一张主表（很可能是person表的No列）。修改pay.No时，SQL Server 会立即检查'000020'是否存在于person.No中。若不存在，更新即被拒绝。

-- 验证参照关系：检查 pay.No 是否真在外键中 SELECT OBJECT_NAME(fk.parent_object_id) AS fk_table, COL_NAME(fkc.parent_object_id, fkc.parent_column_id) AS fk_column, OBJECT_NAME(fk.referenced_object_id) AS pk_table, COL_NAME(fkc.referenced_object_id, fkc.referenced_column_id) AS pk_column FROM sys.foreign_keys fk JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id WHERE OBJECT_NAME(fk.parent_object_id) = 'pay' AND COL_NAME(fkc.parent_object_id, fkc.parent_column_id) = 'No'; -- 若返回 pk_table = 'person', pk_column = 'No'，则执行验证： SELECT COUNT(*) FROM person WHERE No = '000020'; -- 若返回 0，证明参照缺失，更新必败

提示：外键校验发生在语句执行时，而非事务提交时。这意味着即使你在事务中先插入person.No = '000020'，再更新pay.No，也必须确保两条语句在同一事务内，且插入在前。否则单独执行UPDATE pay仍会失败。

3.3 避坑：外键约束名混乱、参照缺失、NULL 值陷阱的三大翻车现场

• 现象：执行ALTER TABLE person DROP CONSTRAINT FK__person__DeptNo__2B0A656D报错 “找不到对象”
  原因：约束名是系统生成的，不同 SQL Server 实例、不同数据库重建后名字完全不同。你复制的错误信息里的名字，只在当前数据库有效。
  解决：永远用SELECT name FROM sys.foreign_keys WHERE parent_object_id = OBJECT_ID('person')动态查出真实约束名，再删除。

• 现象：UPDATE dept SET DeptNo = '00108' WHERE DeptNo = '00101'失败，但SELECT * FROM person WHERE DeptNo = '00101'返回空结果
  原因：person.DeptNo列允许 NULL，而dept.DeptNo是主键（非空）。当person表中存在DeptNo IS NULL的记录时，SQL Server 仍会阻止主表更新——因为 NULL 值在参照完整性中被视为“未知参照”，系统保守处理为禁止变更。
  解决：先清理或修正person表中的 NULL 值：UPDATE person SET DeptNo = '00108' WHERE DeptNo IS NULL;，再执行主表更新。

• 现象：INSERT INTO pay (No, Year, Month, Amount) VALUES ('000020', 2024, 1, 8000)成功，但UPDATE pay SET No = '000020' WHERE No = '000002'失败
  原因：INSERT时'000020'已存在于person.No，所以通过；但UPDATE时，SQL Server 会先校验新值'000020'是否存在，再执行更新。若person表在此期间被其他事务删除了'000020'，则更新失败。
  解决：在事务中执行，或使用MERGE语句原子化处理。

4. 级联更新为何“配了却没用”：SQL Server 的 ON UPDATE CASCADE 真实行为解析

4.1 级联更新不是魔法开关：它依赖主表主键 + 从表外键的双向严格匹配

实验中设置级联更新后，dept.DeptNo从'00101'改为'00108'，person.DeptNo确实同步更新了。但这背后有硬性前提：dept.DeptNo必须是主键（或唯一索引），且person.DeptNo的外键定义中必须显式声明ON UPDATE CASCADE。很多人以为只要建了外键就默认级联，这是最大误区。

-- 正确创建带级联更新的外键（必须显式写 ON UPDATE CASCADE） ALTER TABLE person ADD CONSTRAINT FK_person_DeptNo FOREIGN KEY (DeptNo) REFERENCES dept(DeptNo) ON UPDATE CASCADE; -- 关键！缺这一行，级联不会生效 -- 若外键已存在，需先删除再重建（SQL Server 不支持 ALTER FOREIGN KEY） ALTER TABLE person DROP CONSTRAINT FK_person_DeptNo; ALTER TABLE person ADD CONSTRAINT FK_person_DeptNo FOREIGN KEY (DeptNo) REFERENCES dept(DeptNo) ON UPDATE CASCADE;

注意：ON UPDATE CASCADE只影响UPDATE操作，不影响DELETE。若要删除主表记录时自动删从表，需额外加ON DELETE CASCADE。二者独立控制。

4.2 课外任务失败真相：sc 表的 cno 外键级联失败，是因为主表 course 的 cno 不是主键

课外任务要求给sc表的cno列加外键，参照course.cno，并启用ON UPDATE CASCADE。但测试发现：UPDATE course SET cno = '0809023601' WHERE cno = '0809023501'后，sc表的cno未更新。根本原因在于：course.cno列很可能只是唯一约束（UNIQUE），而非主键（PRIMARY KEY）。SQL Server 要求级联更新的参照列必须是主键或具有唯一索引，且该索引不能包含 NULL 值。

-- 验证 course.cno 是否为主键 SELECT i.is_primary_key, i.is_unique_constraint, i.is_unique FROM sys.indexes i JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id WHERE i.object_id = OBJECT_ID('course') AND c.name = 'cno'; -- 若 is_primary_key = 0 且 is_unique_constraint = 0，则需先建唯一索引 CREATE UNIQUE INDEX IX_course_cno ON course(cno);

只有当course.cno具备唯一性保障后，ON UPDATE CASCADE才能被 SQL Server 接受并生效。

4.3 级联更新的隐式成本：一次主表更新可能触发 N 次从表扫描

级联更新看似省事，实则暗藏性能雷区。当dept.DeptNo更新时，SQL Server 会：

1. 锁定dept表中被更新的行；
2. 扫描person表中所有DeptNo = '00101'的记录（无索引时全表扫描）；
3. 对每条匹配记录执行UPDATE；
4. 若person表还有外键参照dept，则继续级联。

-- 为避免全表扫描，必须确保从表外键列有索引 CREATE INDEX IX_person_DeptNo ON person(DeptNo);

没有这个索引，10 万行person表更新一个DeptNo，可能耗时数秒甚至超时。这是生产环境级联更新突然变慢的最常见原因。

5. 外键级联失效后的兜底方案：手写存储过程比硬扛约束更可控

5.1 当 ON UPDATE CASCADE 被禁用或不可用时，用存储过程封装原子化更新

某些场景下（如跨数据库引用、历史表结构无法修改），级联更新无法启用。此时必须用存储过程手动保证一致性。核心原则：所有相关表的更新必须在单个事务内完成，且按依赖顺序执行（先主表，再从表）。

CREATE PROCEDURE usp_UpdateDeptNo @OldDeptNo CHAR(5), @NewDeptNo CHAR(5) AS BEGIN SET NOCOUNT ON; BEGIN TRY BEGIN TRANSACTION; -- 1. 更新主表 dept UPDATE dept SET DeptNo = @NewDeptNo WHERE DeptNo = @OldDeptNo; -- 2. 更新从表 person（DeptNo 是外键） UPDATE person SET DeptNo = @NewDeptNo WHERE DeptNo = @OldDeptNo; -- 3. 更新从表 pay（若 pay 中也有 DeptNo 外键） UPDATE pay SET DeptNo = @NewDeptNo WHERE DeptNo = @OldDeptNo; COMMIT TRANSACTION; PRINT '部门代号更新成功：' + @OldDeptNo + ' → ' + @NewDeptNo; END TRY BEGIN CATCH ROLLBACK TRANSACTION; DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE(); RAISERROR(@ErrorMessage, 16, 1); END CATCH END;

调用：EXEC usp_UpdateDeptNo '00101', '00108';。事务保证：任一表更新失败，全部回滚，数据零污染。

5.2 外键约束的“合理提示”不是弹窗，而是 RETURN CODE + 自定义错误消息

思考题第二问：“被约束拒绝时，该删约束还是给提示？”答案是后者。但提示不能是裸RAISERROR，而应返回业务可捕获的代码：

-- 在存储过程中加入约束检查，提前告知用户 IF EXISTS (SELECT 1 FROM person WHERE DeptNo = @OldDeptNo) BEGIN -- 允许更新，但记录日志 INSERT INTO UpdateLog (TableName, OldValue, NewValue, Operator) VALUES ('dept', @OldDeptNo, @NewDeptNo, SUSER_SNAME()); END ELSE BEGIN -- 主表无从表引用，可安全更新，无需级联 UPDATE dept SET DeptNo = @NewDeptNo WHERE DeptNo = @OldDeptNo; RETURN 0; -- 成功 END

应用层收到RETURN 0即知成功；收到RAISERROR则解析消息内容，前端显示“该部门已被员工引用，请选择级联更新或先调整员工归属”。

5.3 避坑：级联更新死锁、事务膨胀、日志暴增的三个高危信号

• 现象：执行UPDATE dept时，person表查询变慢，sp_who2显示大量LCK_M_U等待
  原因：级联更新会为person表中所有匹配行加更新锁（U lock），若并发高，极易与SELECT ... WITH (UPDLOCK)冲突。
  解决：在存储过程中改用WITH (ROWLOCK)提示，或拆分为小批量更新（每次更新 1000 行）。

• 现象：事务日志文件.ldf突然暴涨，备份失败
  原因：级联更新本质是多表UPDATE，每行变更都记日志。10 万行person更新，日志量可能达 GB 级。
  解决：在大表级联前，先CHECKPOINT截断日志，并确保恢复模式为FULL且日志备份策略到位。

• 现象：UPDATE dept执行 30 秒后超时，但person表部分记录已更新
  原因：SQL Server 级联更新是原子的，但超时后事务回滚，理论上不应有部分更新。若出现，说明有触发器干扰或XACT_ABORT OFF导致部分语句未回滚。
  解决：始终SET XACT_ABORT ON，并在存储过程中显式BEGIN TRY...CATCH控制。

6. 从那以后我每次改主表数据，都强制走一遍“三查一测”流程

6.1 三查：查约束、查索引、查数据分布，缺一不可

所谓“三查”，是我在线上库踩坑十年总结的铁律，每次执行主表UPDATE或DELETE前必做：

这三步加起来不到 10 秒，却能避开 80% 的线上事故。我曾因跳过“查索引”，在凌晨两点被报警电话叫醒——一个UPDATE dept触发person表全表扫描，拖垮了整套报表服务。

6.2 一测：用最小数据集验证级联路径是否真实打通

“一测”不是跑通 SQL，而是构造可验证的最小闭环：

-- 1. 插入测试数据（仅 1 行） INSERT INTO dept (DeptNo, DepartmentName) VALUES ('99999', '测试部'); INSERT INTO person (PersonID, Name, DeptNo) VALUES (99999, '测试员', '99999'); INSERT INTO pay (No, Year, Month, Amount, DeptNo) VALUES ('99999', 2024, 1, 10000, '99999'); -- 2. 执行级联更新 UPDATE dept SET DeptNo = '88888' WHERE DeptNo = '99999'; -- 3. 验证三表是否同步 SELECT 'dept' AS tbl, DeptNo FROM dept WHERE DeptNo IN ('88888', '99999') UNION ALL SELECT 'person', DeptNo FROM person WHERE DeptNo IN ('88888', '99999') UNION ALL SELECT 'pay', DeptNo FROM pay WHERE DeptNo IN ('88888', '99999');

结果必须三行都返回'88888'。若person或pay仍为'99999'，说明级联未生效，立刻停手，回查外键定义。

6.3 一个硬核技巧：用触发器模拟级联，但保留完整审计日志

当客户明确禁止ON UPDATE CASCADE（如金融系统合规要求），又不愿用存储过程时，可用INSTEAD OF UPDATE触发器实现可控级联，并记录每一笔变更：

CREATE TRIGGER tr_dept_UpdateCascade ON dept INSTEAD OF UPDATE AS BEGIN SET NOCOUNT ON; -- 记录主表变更 INSERT INTO dept_audit (DeptNo_Old, DeptNo_New, UpdatedBy, UpdatedAt) SELECT d.DeptNo, i.DeptNo, SUSER_SNAME(), GETDATE() FROM deleted d JOIN inserted i ON d.DeptNo = i.DeptNo; -- 手动级联更新 UPDATE person SET DeptNo = i.DeptNo FROM person p JOIN deleted d ON p.DeptNo = d.DeptNo JOIN inserted i ON d.DeptNo = i.DeptNo; UPDATE pay SET DeptNo = i.DeptNo FROM pay py JOIN deleted d ON py.DeptNo = d.DeptNo JOIN inserted i ON d.DeptNo = i.DeptNo; -- 最后更新主表 UPDATE dept SET DeptNo = i.DeptNo, DepartmentName = i.DepartmentName FROM dept d JOIN inserted i ON d.DeptNo = i.DeptNo; END;

这个触发器把“级联”变成了可审计、可回滚、可监控的操作。dept_audit表成了黄金数据源，法务查数据流向时，直接SELECT * FROM dept_audit即可。

从那以后我每次改主表数据，都强制走一遍“三查一测”：查约束名是否真实存在、查从表索引是否覆盖外键列、查数据量是否在安全阈值内，最后用三行测试数据跑通全链路。这套流程让我在三年内零线上事故，也让我彻底告别了对着FK__person__DeptNo__2B0A656D报错干瞪眼的日子。希望帮到你。

本文还有配套的精品资源，点击获取