跳到主要内容

实验五:数据完整性

实验目的

  1. 掌握完整性约束机制
  2. 掌握PRIMARY KEY实现实体完整性、FOREIGN KEY实现参照完整性的方法
  3. 掌握CHECK实现用户定义完整性约束的方法
  4. 掌握DEFAULT默认和RULE规则的定义及绑定方法

实验环境

  • SQL Server 12
  • 教务数据库Educ

实验内容与结果

1. Teacher表中Tsex的CHECK约束

-- 添加CHECK约束
ALTER TABLE Teacher
ADD CONSTRAINT CK_Tsex CHECK (Tsex IN ('男', '女'));

-- 测试合法操作
INSERT INTO Teacher (TID, Tname, Tsex) VALUES ('20230001', '张三', '男'); -- 成功

-- 测试违反约束操作
INSERT INTO Teacher (TID, Tname, Tsex) VALUES ('20230002', '李四', '未知'); -- 失败,违反CHECK约束

-- 删除约束
ALTER TABLE Teacher
DROP CONSTRAINT CK_Tsex;

结果分析 :CHECK约束成功限制了Tsex字段只能输入'男'或'女',其他值会被拒绝。

2. Teacher表中Tdept的DEFAULT约束

-- 添加DEFAULT约束
ALTER TABLE Teacher
ADD CONSTRAINT DF_Tdept DEFAULT 'SE' FOR Tdept;

-- 测试默认值验证
INSERT INTO Teacher (TID, Tname) VALUES ('20230003', '王五'); -- Tdept自动设为'SE'

-- 测试非默认值验证
INSERT INTO Teacher (TID, Tname, Tdept) VALUES ('20230004', '赵六', 'CS'); -- 可以指定其他值

-- 删除约束
ALTER TABLE Teacher
DROP CONSTRAINT DF_Tdept;

结果分析 :DEFAULT约束在没有指定Tdept值时自动填充'SE',但仍允许手动指定其他值。

3. 定义rule_sex规则并绑定到Student表

-- 创建规则
CREATE RULE rule_sex AS @sex IN ('男', '女');

-- 绑定规则
EXEC sp_bindrule 'rule_sex', 'Student.Sex';

-- 测试合法操作
INSERT INTO Student (Sno, Sname, Sex) VALUES ('20230001', '张三', '男'); -- 成功

-- 测试违反约束操作
INSERT INTO Student (Sno, Sname, Sex) VALUES ('20230002', '李四', '未知'); -- 失败

-- 解除绑定并删除规则
EXEC sp_unbindrule 'Student.Sex';
DROP RULE rule_sex;

结果分析 :规则成功限制了Sex字段的取值,与CHECK约束效果类似。

4. 定义Default_dept默认对象并绑定到Student表

-- 创建默认对象
CREATE DEFAULT Default_dept AS 'SE';

-- 绑定默认对象
EXEC sp_bindefault 'Default_dept', 'Student.Sdept';

-- 测试默认值验证
INSERT INTO Student (Sno, Sname, Sex) VALUES ('20230003', '王五', '男'); -- Sdept自动设为'SE'

-- 测试非默认值验证
INSERT INTO Student (Sno, Sname, Sex, Sdept) VALUES ('20230004', '赵六', '女', 'CS'); -- 可以指定其他值

-- 解除绑定并删除默认对象
EXEC sp_unbindefault 'Student.Sdept';
DROP DEFAULT Default_dept;

结果分析 :默认对象在没有指定Sdept值时自动填充'SE',但仍允许手动指定其他值。

5. 验证级联删除约束

1) NO ACTION类型参照完整性约束

-- 确保外键约束存在
ALTER TABLE SC ADD CONSTRAINT FK_SC_Course
FOREIGN KEY (Cno) REFERENCES Course(Cno);

-- 合法插入
INSERT INTO SC (Sno, Cno) VALUES ('20131322001', 'C001'); -- 成功

-- 违反参照完整性插入
INSERT INTO SC (Sno, Cno) VALUES ('20131322001', 'C999'); -- 失败,无此课程

2) CASCADE级联删除

-- 修改约束为CASCADE
ALTER TABLE SC DROP CONSTRAINT FK_SC_Course;
ALTER TABLE SC ADD CONSTRAINT FK_SC_Course
FOREIGN KEY (Cno) REFERENCES Course(Cno)
ON DELETE CASCADE;

-- 测试删除Course表中的数据
DELETE FROM Course WHERE Cno = 'C001'; -- SC表中Cno='C001'的记录也被删除

结果分析 :删除Course表中的记录会导致SC表中对应的记录也被级联删除。

3) UPDATE行为的CASCADE测试

-- 修改约束为UPDATE CASCADE
ALTER TABLE SC DROP CONSTRAINT FK_SC_Course;
ALTER TABLE SC ADD CONSTRAINT FK_SC_Course
FOREIGN KEY (Cno) REFERENCES Course(Cno)
ON UPDATE CASCADE;

-- 测试更新Course表中的数据
UPDATE Course SET Cno = 'C100' WHERE Cno = 'C001'; -- SC表中Cno='C001'的记录也被更新为'C100'

结果分析 :更新Course表中的主键会导致SC表中的外键也被级联更新。

6. 级联更新代码分析

ALTER TABLE SC ADD CONSTRAINT FK_SC_Course 
FOREIGN KEY (Cno) REFERENCES Course(Cno)
ON UPDATE CASCADE;
  1. 在SC表中将课程号'C001'改为'C033':
    • 操作会失败,因为SC表中的Cno是外键,不能直接修改为不存在的值。
  2. 在Course表中将课程号'C001'改为'C033':
    • 操作会成功,并且SC表中所有Cno='C001'的记录会被自动更新为'C033'。

7. 插入违反参照完整性的数据

INSERT INTO SC (Sno, Cno, Grade) VALUES ('20131207001', 'C087', 80);

结果分析 :操作会失败,因为:

  1. 学生'20131207001'在Student表中不存在
  2. 课程'C087'在Course表中不存在

解决方法

  1. 先在Student表中插入学生记录
  2. 在Course表中插入课程记录
  3. 然后再执行上述SC表的插入操作

实验总结

通过本次实验,我掌握了以下内容:

  1. 使用CHECK约束实现用户定义的完整性约束
  2. 使用DEFAULT约束设置默认值
  3. 使用规则(RULE)和默认对象(DEFAULT)实现更灵活的约束
  4. 理解并实践了外键约束的不同级联操作(NO ACTION, CASCADE)
  5. 理解了参照完整性的重要性及违反时的处理方式

级联操作在实际数据库设计中非常重要,可以确保数据的一致性,但也需要谨慎使用,避免意外的数据删除或修改。