简介《数据库实验四.docx》是一份面向数据库课程实验的完整作业文档聚焦关系数据库的完整性约束与T-SQL语句操作适合正在学习数据库原理、需要完成类似实验任务的高校学生参考。文档以实验四为载体系统演示了主键与唯一约束的创建和删除、主表与从表间引用完整性的影响测试以及级联引用设置并通过将部门代号“00101”改为“00108”、工号“000002”改为“000020”等操作记录了冲突提示和验证结果帮助读者理解违反参照完整性时的失败原因。此外还包括多道思考题及课外任务的探究对外键级联更新失败的处理思路进行了剖析。资源共包含1个docx文件压缩包大小711KB内容结构清晰可直接作为实验报告模板或复习资料。目前已有343人学习下载适合数据库初学者借以厘清主键、外键、唯一约束和级联操作的实际应用细节。1. 数据库实验四用 T-SQL 把完整性约束一次测透做数据库课程实验最怕的不是写不出 SQL而是明明语句没错却被一大堆约束冲突的报错弹回来。这次要拆的实验四核心就是用 T-SQL 实操主键、唯一约束、引用完整性和级联引用场景是经典的 dept、person、pay 三张表。很多人卡在同一个地方外键约束一冲突系统报一长串REFERENCE 约束 FK__person__DeptNo__2B0A656D 冲突看着像乱码根本不知道错在哪。其实这套报错是有明确规律的实验做一遍比背十页理论都管用。这份资源适合正在做数据库课设、或者想补约束实操的读者内容不啰嗦直接对着表结构和语句来。2. 先动手把主键和唯一约束搞明白pay 与 dept 两张表的 T-SQL 写法2.1 联合主键的创建与删除为什么从单列变成了三列实验的第一个任务是把表pay的No、Year、Month三列联合定义为主键。这个设计意图需要先理解pay表存的是工资发放记录同一个工号No会在不同年份、不同月份重复出现单靠No根本没法唯一标识一条记录。只有No Year Month三者组合在一起才能确定“某个人某年某月的工资记录”。创建联合主键的常用做法是ALTER TABLE pay ADD CONSTRAINT PK_pay PRIMARY KEY (No, Year, Month);这里的逻辑很直接给表pay添加一个名为PK_pay的主键约束括号里列出构成主键的三个列。约束名的命名规范我一般习惯用PK_表名的格式方便后续删除或排查时一眼认出。执行成功后你可以用sp_helpconstraint pay查看表的约束信息能看到PK_pay的类型是PRIMARY KEY约束列是No, Year, Month三列。删除主键的对应语句是ALTER TABLE pay DROP CONSTRAINT PK_pay;这里有个容易踩的细节如果当初建表时没显式指定约束名系统会自动生成一个类似PK__pay__xxxx的名字删除时得先用系统视图查出来不能凭感觉写。我一般会用这条语句查SELECT name FROM sys.key_constraints WHERE parent_object_id OBJECT_ID(pay) AND type PK;查到系统生成的约束名后再执行DROP CONSTRAINT否则会报“找不到对象”的错误。2.2 唯一约束的创建与删除和主键的边界在哪里第二个任务是把dept表部门名称列上的唯一约束删除。这个任务的价值在于搞清楚唯一约束和主键的异同两者都要求列值唯一但唯一约束允许一个NULL值而且一个表可以建多个唯一约束主键则只允许一个。创建唯一约束的 T-SQL 写法是ALTER TABLE dept ADD CONSTRAINT UK_DeptName UNIQUE (DepartmentName);我习惯用UK_列名来命名唯一约束和PK区分开。如果实验环境里dept表已经存在这个约束直接删除即可ALTER TABLE dept DROP CONSTRAINT UK_DeptName;但这里有一个很重要的实操问题如果dept表里已经存在两条相同的部门名称那么创建唯一约束这一步就会直接失败报UNIQUE KEY冲突。这是实验中不会明说、但实际很容易遇见的坑。我一般会先查一下列里有没有重复值SELECT DepartmentName, COUNT(*) FROM dept GROUP BY DepartmentName HAVING COUNT(*) 1;如果查出有重复数据要么清理数据要么这个唯一约束本来就建不上。删除约束则不存在这个问题——只要约束存在就能删掉。实验里要求“删除”而不是“创建”大概率是表里已经预置了这个约束省去了处理脏数据的环节。2.3 验证约束是否生效两个查询习惯建议保留每做完一个约束操作不要直接进下一步先用系统视图确认一下。我的固定操作是SELECT name, type_desc FROM sys.objects WHERE parent_object_id OBJECT_ID(pay) AND type IN (PK, UQ); SELECT name, type_desc FROM sys.objects WHERE parent_object_id OBJECT_ID(dept) AND type UQ;这样能清楚看到pay表上还有没有主键、dept表上还有没有唯一约束。很多同学做完删除操作后没验证后面实验步骤全乱套就是因为旧约束还残留在表结构里新语句一直被拦。验证这一步不算复杂但能省下后面排查问题的不少时间。3. 测试引用完整性更新 dept 主表的那条经典报错3.1 实验场景里主表与从表是怎么定义的引用完整性的核心是外键关系。在这套实验里dept部门表是主表person人员表是从表person表里有一个DeptNo字段引用dept表的部门代号。换句话说person里的每个人必须属于一个真实存在的部门不能凭空挂到一个不存在的部门代号上。这个约束关系是通过建表语句或ALTER TABLE语句添加外键来实现的。实验中执行更新操作时系统报错信息里的REFERENCE 约束 FK__person__DeptNo__2B0A656D实际上就是 SQL Server 自动为person表生成的默认外键约束名。它的命名规则不算复杂但一眼看去确实容易懵。3.2 把 00101 改成 00108UPDATE 与 REFERENCE 约束冲突的完整解码实验的第三个任务是这样的执行下面这条更新语句把部门代号从00101改成00108。UPDATE dept SET DeptNo 00108 WHERE DeptNo 00101;正常情况下这条语句会执行失败报错的核心内容如下UPDATE 语句与 REFERENCE 约束 FK__person__DeptNo__2B0A656D 冲突。现在我解释一下这个报错是怎么来的。person表里有几行记录的DeptNo是00101这些记录通过外键约束引用了dept表。现在你要把dept表里的这个部门代号改成00108数据库需要检查如果dept表改了person表里那几行指向00101的记录是不是就断了是的它们失去了参照对象。数据库拒绝执行这个操作并不是因为它不知道你要做什么而是因为在默认的NO ACTION规则下主表的更新会破坏从表数据的参照完整性。这里有一个非常关键的概念区分外键约束本身并不“反对”主表更新——它反对的是“会让从表失效”的更新。理解了这个底层逻辑就不会对报错感到莫名其妙。3.3 为什么 update 后查询 person 你会看到“双重结果”我自己的血泪经验主表dept更新失败后很多人会不死心再去查一遍person表看DeptNo是不是已经被改了。这种操作其实没意义。UPDATE语句是一个原子操作执行失败代表整个事务被回滚了dept表的数据根本没有变化。你看到的person表里的记录自然也是没变的。正确的验证方式是SELECT * FROM dept WHERE DeptNo IN (00101, 00108); SELECT * FROM person WHERE DeptNo IN (00101, 00108);执行完UPDATE后这两条查询的结果应该跟执行前完全一致dept表里依然是00101没有00108。这才是“未完成主表更新操作”的正确验证思路。提示碰到外键冲突报错时先区分清楚是哪个表的哪个约束在拦你。报错信息里带REFERENCE 约束的是外键带PRIMARY KEY的是主键带UNIQUE KEY的是唯一约束。3.4 这个报错在提醒你NO ACTION 和 CASCADE 的处世哲学完全不同实验里这个失败结果本质是NO ACTION默认规则的体现。它不主动改任何东西只是拒绝可能破坏完整性的操作。很多教材会说“默认外键不允许更新”更准确的说法是“默认外键在NO ACTION规则下拒绝可能让从表失去参照的操作”。这种规则的哲学是“宁可操作失败也不留脏数据”。当你把00101改成00108时数据库在提交前检查了一遍person表发现有记录还引着旧值于是整个 UPDATE 被拦下来。而级联更新CASCADE的哲学正好相反它会在主表更新时自动同步更新从表的所有记录。实验的第五个任务就是针对这种差别先别急留着后面详细说。4. 从表操作的对称性pay 表更新失败背后的两条约束4.1 person 与 pay两个方向的外键约束对照实验的第四个任务是把pay表中的工号000002改为000020预期是更新失败。这里涉及的外键关系是person表是主表pay表是从表pay.No引用person.No。这和前一个任务刚好形成对称前面是更新主表约束去查从表有没有关联记录这次是更新从表约束去查主表有没有对应记录。两个方向都测一遍才算是把引用完整性理解透了。只看一个方向很容易产生错觉碰上反向的约束之前根本不会意识到这个问题的严重性。4.2 从表更新失败的根因修改的不是参照来源执行的语句如下UPDATE pay SET No 000020 WHERE No 000002;执行后系统报错UPDATE 语句与 FOREIGN KEY 约束 fk_no 冲突。这个报错的含义是pay表有一条记录的No是000002你要把它改成000020但person主表里根本不存在工号000020。如果这条更新成功pay表里就会出现一个不属于任何人的工资记录参照完整性被破坏。数据库因此拒绝操作。进一步用查询验证SELECT * FROM person WHERE No 000020; SELECT * FROM pay WHERE No 000002;person表里查不到000020说明pay表里那条000002的记录失去参照对象这就是更新失败的根本原因。注意约束名fk_no是创建外键时手动指定的实验环境里如果约束是系统自动生成的名字也可能是FK__pay__No__xxxx。不管名字长什么样报错机制是一样的。这里有一个思考题的映射在什么样的主表和从表增删改操作里数据的完整性约束会被破坏这个实验已经把两类核心场景测出来了——修改主表的主键值会让从表失去参照修改从表的外键值会让记录指向不存在的主表数据。其实删除操作也一样危险比如删掉course表里的某门课但sc表里还有选了这门课的成绩记录外键约束同样是拦截的。4.3 从表插入的隐性问题为什么插入也可能被拦实验里只测了更新但实际场景中插入操作同样会触发外键约束。考虑这个场景INSERT INTO pay (No, Year, Month, Salary) VALUES (000030, 2026, 1, 8000);如果person表里不存在工号000030这条插入语句一样会被fk_no拦截。很多人以为只有更新才触发外键检查其实插入、删除、更新三兄弟全都在检查范围内只是暴露的频率和时机不同。做课外练习时如果卡在插入的报错上优先去主表查一下关联字段是否存在多半是这个问题。5. 级联引用与课外任务设置 CASCADE 后主表更新为何能成功5.1 修改外键定义支持级联更新的完整 T-SQL 操作实验的第五个任务是把dept表里的部门代号00101改成00108这次要让它成功执行。关键在于把外键约束设置成ON UPDATE CASCADE让主表更新时自动同步修改从表的关联记录。在 SQL Server 里级联更新不是直接修改现有约束而是要先把旧约束删掉再按CASCADE规则重建。操作流程是ALTER TABLE person DROP CONSTRAINT FK__person__DeptNo__2B0A656D; ALTER TABLE person ADD CONSTRAINT FK_person_dept_cascade FOREIGN KEY (DeptNo) REFERENCES dept(DeptNo) ON UPDATE CASCADE;这里有个非常关键的点要强调SQL Server 不允许直接用ALTER TABLE ... ALTER CONSTRAINT给现有外键添加级联选项。网上很多教程直接写一条语句就完事实际执行会报语法错误。必须先删后建这是唯一可行的路子。5.2 执行更新并验证级联效果两张表同步变化外键重建完成后再执行之前失败的更新语句UPDATE dept SET DeptNo 00108 WHERE DeptNo 00101;这一次语句执行成功。验证方式如下SELECT * FROM dept WHERE DeptNo 00108; SELECT * FROM person WHERE DeptNo 00108;你会看到dept表里部门代号变成了00108同时person表里原来部门代号为00101的所有员工的记录也自动同步成了00108。这就是CASCADE级联更新的行为特征主表动从表跟着动。5.3 课外任务 3 和 4 的翻车现场为什么 course 级联更新会失败课外任务更狠要求先给sc表加ON UPDATE CASCADE外键再给course表的cpno先修课号定义自引用级联更新。很多人在这里做一半就放弃因为实验结果跟预期完全对不上——级联更新失败报错信息看不懂。先说第一个sc表外键设为级联更新后执行如下更新UPDATE course SET cno 0809023601 WHERE cno 0809023501;如果失败报错大概率还是外键冲突。这里有个很容易被忽略的细节虽然你改了sc表的外键定义但前提是sc.cno引用course.cno时只有一个外键约束。而course表里cpno还引用着course.cno即自引用外键。course表的主键cno一改cpno指向旧值的那些行没有级联更新于是约束冲突整体失败。第二个任务给course.cpno加级联更新需要做的是ALTER TABLE course DROP CONSTRAINT FK__course__cpno__xxxx; ALTER TABLE course ADD CONSTRAINT FK_course_cpno_cascade FOREIGN KEY (cpno) REFERENCES course(cno) ON UPDATE CASCADE;加完之后更新cno你会发现cpno字段并没有级联更新。这是 SQL Server 的经典限制自引用外键的级联更新行为在很多场景下并不可靠系统会返回错误信息明确说 self-referencing 表上存在多个级联路径。这类限制和实现机制有关手动改数据反而更可控。课外任务里那个“请记录提示信息”实际就是把这条报错如实记录下来。5.4 保证数据正确性的补救方案手动写 UPDATE 同步既然CASCADE在自引用场景下靠不住那怎么保证数据一致我的做法是用事务包住多次更新。BEGIN TRANSACTION; UPDATE course SET cno 0809023601 WHERE cno 0809023501; UPDATE sc SET cno 0809023601 WHERE cno 0809023501; UPDATE course SET cpno 0809023601 WHERE cpno 0809023501; COMMIT TRANSACTION;这段脚本的思路是先改主表再手动同步所有从表和自引用字段最后一次性提交。如果中途某一步失败整个事务回滚不会留下半改的脏数据。实际项目里我会先模拟数据确认影响行数再执行真实更新。这个“先查后改”的习惯帮我避免了不止一次误更新全表。6. 避坑与常见问题约束操作中的五个经典翻车现场6.1 约束名是系统自动生成的别靠猜现象执行DROP CONSTRAINT FK__person__DeptNo__2B0A656D时把名字抄错了系统报找不到对象。 原因SQL Server 自动生成的外键名带一串十六进制后缀实验指导书里打印的约束名和实际库里的不一定完全一致不同环境、不同建表顺序生成的名称都不同。 解决先查再删固定操作如下SELECT name FROM sys.foreign_keys WHERE parent_object_id OBJECT_ID(person);不管环境怎么变以查出来的实际名字为准。6.2 创建主键时提示“与某列冲突”现象执行ALTER TABLE pay ADD CONSTRAINT PK_pay PRIMARY KEY (No, Year, Month)报错说有行与主键冲突。 原因表中已有重复的(No, Year, Month)组合值不满足主键唯一性。 解决先分组查重复值SELECT No, Year, Month, COUNT(*) FROM pay GROUP BY No, Year, Month HAVING COUNT(*) 1;把重复数据清理掉再建主键或者换一组能唯一标识数据的列。6.3 加了外键约束后主表删除操作被拦现象删除dept表里的某个部门提示外键冲突。 原因默认NO ACTION规则下person表还有记录引用了该部门代号。 解决两步走——先处理从表数据再删主表记录DELETE FROM person WHERE DeptNo 00101; DELETE FROM dept WHERE DeptNo 00101;如果业务允许也可以把外键改成ON DELETE CASCADE让从表记录随主表删除自动清掉。6.4 在course表加外键约束时失败现象执行ALTER TABLE course ADD CONSTRAINT FK_course_cpno FOREIGN KEY (cpno) REFERENCES course(cno);报错说cpno列的值在cno列中不存在。 原因course表已有数据里某些行的cpno指向了表中不存在的cno。 解决用NOT EXISTS查一下非法数据SELECT * FROM course AS a WHERE a.cpno IS NOT NULL AND NOT EXISTS (SELECT 1 FROM course AS b WHERE b.cno a.cpno);把查出来的问题行修正或删除再重新添加外键约束。思考题里专门问这个问题其实就是引导你先看数据再动结构。6.5 课外任务里cpno不加约束更新照样失败现象只给sc表加了级联更新course.cno依然报外键冲突。 原因course表的cpno自引用外键挡在前面没有级联处理。 解决手动事务同步更新把sc表和cpno都改到位。具体脚本参考前面 5.4 节的做法。这种组合拳比单纯依赖CASCADE可靠得多。7. 最后的技巧用三段式检查脚本判断约束状态是否正常每次实验做完我习惯跑一套三段式检查脚本确认约束状态没有残留问题。第一步查主键和外键SELECT OBJECT_NAME(parent_object_id) AS table_name, name AS constraint_name, type_desc AS constraint_type FROM sys.objects WHERE type IN (PK, FQ, UQ) ORDER BY table_name, constraint_type;第二步查外键的级联动作SELECT OBJECT_NAME(parent_object_id) AS table_name, name AS constraint_name, delete_referential_action_desc, update_referential_action_desc FROM sys.foreign_keys;第三步查数据冲突比如department改名后person表是否同步SELECT (SELECT COUNT(*) FROM person) AS person_count, (SELECT COUNT(DISTINCT DeptNo) FROM person) AS distinct_deptno, (SELECT COUNT(*) FROM dept) AS dept_count;这套检查脚本的价值在于实验报告里要写的“验证结果”全部来自实际查询结果不是靠回忆记下来的。比如你看到update_referential_action_desc显示CASCADE就能明确确认级联已生效。从那以后我每次做约束实验都会强制走一遍这三段式脚本不再凭感觉判断约束状态报错信息也就自然对上了。希望帮到你。本文还有配套的精品资源点击获取