简介：面向数据库课程学习者的实验四文档，聚焦利用 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__2B0A656D，pay 表上的外键名则是手写的 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 表重新允许出现同名部门，数据完整性退回到"无人看守"状态。两者对比可以整理成一张表：

删除唯一约束同样面临"约束名记不准"的问题，补救方式还是查系统视图，把 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（部门表）是主表，主键是部门代号 DeptNo；person（员工表）通过 DeptNo 引用 dept，所以 person 是从表。person 自己的主键是工号 No；pay 表通过 No 引用 person，所以 pay 是从表，person 此时又扮演主表角色。课外任务里还有一对关系：sc（选课表）通过 cno 引用 course（课程表）的 cno，而 course 表的 cpno 又引用自己的 cno，构成自引用。

引用完整性的规则是：从表（外键所在表）中的外键列取值，要么为 NULL，要么必须存在于主表被引用的列中。违反这条规则的操作会被 SQL Server 直接拒绝，报错信息里带着约束名，约束名的可读性直接影响排错速度。实验文档里 pay 表的外键名是 fk_no，person 表的外键名是 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 ACTION：dept 的 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 插入与删除：主表、从表各自的允许与拒绝

除了更新，实验还要求测插入和删除。这四类操作在外键约束下的表现可以归成一张行为表：

对应两条典型语句：

-- 从表插入指向不存在部门的记录：应被外键拒绝 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 主表更新 cno，sc 选课记录自动跟随。真正翻车的是第二个操作——给 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 查重，清掉重复行再创建约束。

坑 2：UPDATE 主表被系统命名约束拦截。现象是报错里出现 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 触发器替代，别硬刚。

坑 5：DROP 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 复查数据。这套流程帮我躲过了好几次同组人翻车的场景，也希望帮到你。

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