数据库原理及应用实验报告
实验题目实验8参照完整性
实验目的
学习建立外键，以及利用FOREIGN KEY…REFERENCES子句以及各种约束保证参照完整性。
实验内容为演示参照完整性，建立表Course，令xn_Cno为其主键，并在xun_Stu_Union表中插入数据。为下面的实验步骤做预先准备。建立表xun_SC，令xn_Sno和xn_Cno分别为参照xun_Stu_Union表以及Course表的外键，设定为级联删除，并令(xn_Sno,xn_Cno)为其主键。在不违反参照完整性的前提下，插入数据。演示违反参照完整性的插入数据。在xun_Stu_Union中删除数据，演示级联删除。在Course中删除数据，演示级联删除。为了演示多重级联删除，建立xun_Stu_Card表，令xn_Sno为参数xun_Students表的外键，令xn_Card_id为其主键，并插入数据。为了演示多重级联删除，建立xun_ICBC_Card表，令xn_Stu_card_id为参数xun_Stu_Union表的外键，令xn_Card_id为其主键，并插入数据。通过删除xun_Students表中的一条记录，演示三个表的多重级联删除。演示事务中进行多重级联删除失败的处理。修改xun_ICBC_Card表的外键属性，使其变为On delete No action，演示事务中通过删除xun_Students表中的一条记录，多重级联删除失败，整个事务回滚到事务的初始状态。演示互参考问题及其解决方法。要建立教师授课和课程指定教师听课关系的两张表，规定一个教师可以授多门课，但是每个课程只能指定一个教师去听课，所以要为两张表建立相互之间的参照关系。
做“实验8参照完整性”的准备工作
1、建立xun_Stu_Union
CREATE TABLE xun_Stu_Union(xn_Sno CHAR(8) not null primary key, xn_Sname CHAR(8), xn_Ssex CHAR(1), xn_Sage INT, xn_Sdept CHAR(20))
2、插入相应数据，结果如图1
图1
insert into xun_Stu_Unionvalues('S02','王兵','M','23','CS')insert into xun_Stu_Unionvalues('S03','黄浩','F','25','EE')insert into xun_Stu_Unionvalues('S05','黄浩','F','25','EE')insert into xun_Stu_Unionvalues('S09','李永','M','25','EE')
实验步骤
以系统管理员登录到SSMS，在新建查询窗口中输入如下命令，运行并观察和记录结果。
在新建查询窗口中输入如下SQL语句：
Xuni_University_Mis:
建立表Course,令xn_Cno为其主键，在xun_Stu_Union Values和Course插入数据：
INSERT xun_Stu_Union Values(‘S01’,‘李用’,‘0’,24,‘FF’)SELECT * FROM xun_Stu_Union;CREATE TABLE Course(xn_Cno CHAR(4) NOT NULL UNIQUE,xn_Cname VARCHAR(50) NOT NULL,xn_Cpoints INT,CONSTRAINT PK PRIMARY KEY(xn_Cno));INSERT Course VALUES(‘C01’,’ComputerNetworks’,2);INSERT Course VALUES(‘C02’,’ArtificialIntelligence’,3);
在新建查询窗口中输入如下SQL语句：
Xuni_University_Mis:CREATE TABLE xun_SC(xn_Sno CHAR(8) ,xn_Cno CHAR(8) ,Scredit INT,CONSTRAINT PK_SC PRIMARY KEY(xn_Sno,xn_Cno),CONSTRAINT FK_SC_Sno FOREIGN KEY(xn_Sno) REFERENCES xun_Stu_Union (xn_Sno) ON DELETE CASCADE,CONSTRAINT FK_SC_Cno FOREIGN KEY(xn_Cno) REFERENCES Course(xn_Cno) ON DELETE CASCADE);INSERT INTO xun_SC VALUES('S02','C01',2);INSERT INTO xun_SC VALUES ('S02','C02',2);INSERT INTO xun_SC VALUES ('S01','C01',2);INSERT INTO xun_SC VALUES ('S01','C02',2);SELECT * FROM xun_SC;
在新建查询窗口中输入如下SQL语句：
Xuni_University_Mis:
演示违反参照完整性的插入数据：
insert into xun_SCvalues('S99','C99',2)
报错：
消息 547，级别 16，状态 0，第 1 行
INSERT 语句与 FOREIGN KEY 约束"FK_SC_Sno"冲突。该冲突发生于数据库"Xuni_University_Mis"，
表"dbo.xun_Stu_Union", column 'xn_Sno'。
语句已终止。
结果图2所示：
图2INSERT 失败
因为违反了参照完整性，因为xun_Stu_Union没有xn_Sno为’S99’的元组，而xun_Stu_Union的xn_Sno是xun_SC 的外键，所以这样插入违反了参照完整性，不可行。
在新建查询窗口中输入如下SQL语句：
Xuni_University_Mis:
在xun_Stu_Union表中删除数据，演示级联删除
DELETE FROM xun_Stu_Union WHERE xn_Sno='S01'SELECT * FROM xun_SC;
结果图3，可知xun_SC表中xn_Sno='S01'的元组也被删除。
图3删除S01
在新建查询窗口中输入如下SQL语句：
Xuni_University_Mis:
在Course表中删除数据，演示级联删除。
DELETE FROM Courses WHERE xn_Cno='C02'SELECT * FROM xun_SC;
结果如图4，可知xun_SC中xn_Cno='C02'的元组也被删除：
图4删除C02
在新建查询窗口中输入如下SQL语句：
Xuni_University_Mis:
建立表xun_Stu_Card，令xn_Card_id为主键，并插入数据，令xn_Sno作为xun_Students表的外键。
CREATE TABLE xun_Stu_Card(xn_Card_id CHAR(14),xn_Sno CHAR(8),Remained_money DECIMAL(10,2),Constraint PK_Stu_Card PRIMARY KEY(xn_Card_id),Constraint FK_Stu_Card_Sno FOREIGN KEY(xn_Sno) REFERENCES xun_Students(xn_Sno) ON DELETE CASCADE)INSERT INTO xun_Stu_Card VALUES('05212567','S03',400.25);INSERT INTO xun_Stu_Card VALUES('05212222','S23',600.50);SELECT * FROM xun_Stu_card;
因为xun_Students表中不存在xn_Sno=S09的元组，插入会失败，所以改成了S23
在新建查询窗口中输入如下SQL语句：
Xuni_University_Mis
建立表xun_ICBC_Card，xn_Stu_card_id作为主键，xn_Stu_card_id为xun_Stu_card的外键。
CREATE TABLE xun_ICBC_Card(xn_Bank_id CHAR(20),xn_Stu_card_id CHAR(14),xn_Restored_money DECIMAL(10,2),constraint PK_ICBC_Card PRIMARY KEY(xn_Bank_id),constraint FK_ICBC_Card_Stu_id FOREIGN KEY(xn_Stu_card_id) REFERENCES xun_Stu_card(xn_card_id) ON DELETE CASCADE)INSERT INTO xun_ICBC_Card VALUES('9558844022312','05212567',15000.1);INSERT INTO xun_ICBC_Card VALUES('9558844023645','05212222',50000.3);SELECT * FROM xun_ICBC_Card;
在新建查询窗口中输入如下SQL语句：
Xuni_University_Mis:
删除 xun_Students 的记录，演示三个表的多重级联删除。
ALTER TABLE xun_Reports DROP [students_report];ALTER TABLE xun_Reports ADDCONSTRAINT [FK_Reports_Students] FOREIGN KEY([xn_Sno]) REFERENCES [dbo].[xun_Students] ([xn_Sno]) ON DELETE CASCADE;DELETE FROM xun_Students WHERE xn_Sno='S03';SELECT * FROM xun_Stu_card;SELECT * FROM xun_ICBC_Card;
结果如下图，可知xun_Stu_Card 表中xn_Sno='S03'的记录已被删除（该记录为’05212567','S03',400.25）,也可知xun_ICBC_Card表中xn_Card_id =’05212567’的记录也被删除（该记录为'9558844022312','05212567',15000.1）：
图5删除S03
(9)在新建查询窗口中输入如下SQL语句：
演示事务中多重级联删除失败的处理，修改xun_ICBC_Card表的外键属性，变为 ON DELETE NO ACTION。
ALTER TABLE xun_ICBC_CardDROP CONSTRAINT FK_ICBC_Card_Stu_id;ALTER TABLE xun_ICBC_CardADD CONSTRAINT FK_ICBC_Card_Stu_id FOREIGN KEY (xn_Stu_card_id)REFERENCES xun_Stu_card(xn_Card_id) ON DELETE NO ACTION;
在新建查询窗口中输入如下SQL语句：
Begin Transaction DelDELETE FROM xun_Stu_Card WHERE xn_Card_id ='05212222';SELECT * FROM xun_Stu_card;SELECT * FROM xun_ICBC_card;Commit Transaction Del
结果如图6，可知两个表中的记录都删除失败，该条记录依然存在。
图6 05212222
错误消息弹出：
消息 547，级别 16，状态 0，第 2 行
DELETE 语句与 REFERENCE 约束"FK_ICBC_Card_Stu_id"冲突。该冲突发生于数据库"Xuni_University_Mis"，表"dbo.xun_ICBC_Card", column 'xn_Stu_card_id'。
语句已终止。
在新建查询窗口中输入如下SQL语句：
Xuni_University_MisSELECT * FROM Stu_card;SELECT * FROM ICBC_card;
结果如图7：
图7
在新建查询窗口中输入如下SQL语句：
演示互参考问题及其解决方案，建立xun_Listen_course表和xun_Teach_course
表。
CREATE TABLE xun_Listen_course(xn_Tno CHAR(6),xn_Tname VARCHAR(20),xn_Cno CHAR(4)CONSTRAINT PK_listen_course PRIMARY KEY(xn_Tno)CONSTRAINT FK_listen_course FOREIGN KEY(xn_Cno)REFERENCES xun_Teach_course(xn_Cno))CREATE TABLE xun_Teach_course(xn_Cno CHAR(4),xn_Cname VARCHAR(30),xn_Tno CHAR(6)CONSTRAINT PK_Teach_course PRIMARY KEY(xn_Cno)CONSTRAINT FK_Teach_course FOREIGN KEY(xn_Tno)REFERENCES xun_Listen_course(xn_Tno))
报错：
消息 1767，级别 16，状态 0，第 1 行
外键 'FK_listen_course' 引用了无效的表 'xun_Teach_course'。
消息 1750，级别 16，状态 0，第 1 行
无法创建约束或索引。请参阅前面的错误。
实验结果如图8：
图8
(11) 在新建查询窗口中输入如下SQL语句：
Xuni_University_Mis
建立表 xun_Listen_course，xn_Tno来自PK_listen_Course的外键
CREATE TABLE xun_Listen_course(xn_Tno CHAR(6),xn_Tname VARCHAR(20),xn_Cno CHAR(4)CONSTRAINT PK_listen_Course PRIMARY KEY(xn_Tno))
成功
(12) 在新建查询窗口中输入如下SQL语句：
Xuni_University_Mis
CREATE TABLE xun_Teach_course(xn_Cno CHAR(4),xn_Cname VARCHAR(30),xn_Tno CHAR(6)CONSTRAINT PK_teach_course PRIMARY KEY(xn_Cno)CONSTRAINT FK_teach_course FOREIGN KEY(xn_Tno)REFERENCES xun_Listen_course(xn_Tno))ALTER TABLE xun_Listen_course ADD CONSTRAINT FK_listen_course FOREIGN KEY(xn_Cno)REFERENCES xun_Teach_course(xn_Cno);
成功，如图9：
图9
实验要求
(1) 实验之前请细细阅读实验总体要求与说明指导书
(2 ) 在SQL Server 或或的SSMS环境中，完成以上实验(1)-(10)步所有SQL数据定义操作，并将其中粗体步骤的操作窗口剪贴到实验报告中。
8.5实验体会
1）实验反思
无
2）实验收获
学习力参照完整性的相关内容，加深了对于这部分知识点的认识
附录：
准备工作CREATE TABLE xun_Stu_Union(xn_Sno CHAR(8) not null primary key, xn_Sname CHAR(8), xn_Ssex CHAR(1), xn_Sage INT, xn_Sdept CHAR(20))insert into xun_Stu_Unionvalues('S02','王兵','M','23','CS')insert into xun_Stu_Unionvalues('S03','黄浩','F','25','EE')insert into xun_Stu_Unionvalues('S05','黄浩','F','25','EE')insert into xun_Stu_Unionvalues('S09','李永','M','25','EE')1.INSERT xun_Stu_Union Values(‘S01’,‘李用’,‘0’,24,‘FF’)SELECT * FROM xun_Stu_Union;CREATE TABLE Course(xn_Cno CHAR(4) NOT NULL UNIQUE,xn_Cname VARCHAR(50) NOT NULL,xn_Cpoints INT,CONSTRAINT PK PRIMARY KEY(xn_Cno));INSERT Course VALUES(‘C01’,’ComputerNetworks’,2);INSERT Course VALUES(‘C02’,’ArtificialIntelligence’,3);2.CREATE TABLE xun_SC(xn_Sno CHAR(8) ,xn_Cno CHAR(8) ,Scredit INT,CONSTRAINT PK_SC PRIMARY KEY(xn_Sno,xn_Cno),CONSTRAINT FK_SC_Sno FOREIGN KEY(xn_Sno) REFERENCES xun_Stu_Union (xn_Sno) ON DELETE CASCADE,CONSTRAINT FK_SC_Cno FOREIGN KEY(xn_Cno) REFERENCES Course(xn_Cno) ON DELETE CASCADE);INSERT INTO xun_SC VALUES('S02','C01',2);INSERT INTO xun_SC VALUES ('S02','C02',2);INSERT INTO xun_SC VALUES ('S01','C01',2);INSERT INTO xun_SC VALUES ('S01','C02',2);SELECT * FROM xun_SC;3.insert into xun_SC values('S99','C99',2)消息 547，级别 16，状态 0，第 1 行INSERT 语句与 FOREIGN KEY 约束"FK_SC_Sno"冲突。该冲突发生于数据库"Xuni_University_Mis"，表"dbo.xun_Stu_Union", column 'xn_Sno'。语句已终止。4.DELETE FROM xun_Stu_Union WHERE xn_Sno='S01'SELECT * FROM xun_SC;图5.DELETE FROM Courses WHERE xn_Cno='C02'SELECT * FROM xun_SC;图6.CREATE TABLE xun_Stu_Card(xn_Card_id CHAR(14),xn_Sno CHAR(8),Remained_money DECIMAL(10,2),Constraint PK_Stu_Card PRIMARY KEY(xn_Card_id),Constraint FK_Stu_Card_Sno FOREIGN KEY(xn_Sno) REFERENCES xun_Students(xn_Sno) ON DELETE CASCADE)INSERT INTO xun_Stu_Card VALUES('05212567','S03',400.25);INSERT INTO xun_Stu_Card VALUES('05212222','S23',600.50);SELECT * FROM xun_Stu_card;7.CREATE TABLE xun_ICBC_Card(xn_Bank_id CHAR(20),xn_Stu_card_id CHAR(14),xn_Restored_money DECIMAL(10,2),constraint PK_ICBC_Card PRIMARY KEY(xn_Bank_id),constraint FK_ICBC_Card_Stu_id FOREIGN KEY(xn_Stu_card_id) REFERENCES xun_Stu_card(xn_card_id) ON DELETE CASCADE)INSERT INTO xun_ICBC_Card VALUES('9558844022312','05212567',15000.1);INSERT INTO xun_ICBC_Card VALUES('9558844023645','05212222',50000.3);SELECT * FROM xun_ICBC_Card;8.ALTER TABLE xun_Reports DROP [students_report];ALTER TABLE xun_Reports ADDCONSTRAINT [FK_Reports_Students] FOREIGN KEY([xn_Sno]) REFERENCES [dbo].[xun_Students] ([xn_Sno]) ON DELETE CASCADE;DELETE FROM xun_Students WHERE xn_Sno='S03';SELECT * FROM xun_Stu_card;SELECT * FROM xun_ICBC_Card;9.1ALTER TABLE xun_ICBC_CardDROP CONSTRAINT FK_ICBC_Card_Stu_id;ALTER TABLE xun_ICBC_CardADD CONSTRAINT FK_ICBC_Card_Stu_id FOREIGN KEY (xn_Stu_card_id)REFERENCES xun_Stu_card(xn_Card_id) ON DELETE NO ACTION;9.2Begin Transaction DelDELETE FROM xun_Stu_Card WHERE xn_Card_id ='05212222';SELECT * FROM xun_Stu_card;SELECT * FROM xun_ICBC_card;Commit Transaction Del消息 547，级别 16，状态 0，第 2 行DELETE 语句与 REFERENCE 约束"FK_ICBC_Card_Stu_id"冲突。该冲突发生于数据库"Xuni_University_Mis"，表"dbo.xun_ICBC_Card", column 'xn_Stu_card_id'。语句已终止。(1 行受影响)(1 行受影响)SELECT * FROM xun_Stu_card;SELECT * FROM xun_ICBC_card;10.CREATE TABLE xun_Listen_course(xn_Tno CHAR(6),xn_Tname VARCHAR(20),xn_Cno CHAR(4)CONSTRAINT PK_listen_course PRIMARY KEY(xn_Tno)CONSTRAINT FK_listen_course FOREIGN KEY(xn_Cno)REFERENCES xun_Teach_course(xn_Cno))CREATE TABLE xun_Teach_course(xn_Cno CHAR(4),xn_Cname VARCHAR(30),xn_Tno CHAR(6)CONSTRAINT PK_Teach_course PRIMARY KEY(xn_Cno)CONSTRAINT FK_Teach_course FOREIGN KEY(xn_Tno)REFERENCES xun_Listen_course(xn_Tno))报错：：消息 1767，级别 16，状态 0，第 1 行外键 'FK_listen_course' 引用了无效的表 'xun_Teach_course'。消息 1750，级别 16，状态 0，第 1 行无法创建约束或索引。请参阅前面的错误。11.CREATE TABLE xun_Listen_course(xn_Tno CHAR(6),xn_Tname VARCHAR(20),xn_Cno CHAR(4)CONSTRAINT PK_listen_Course PRIMARY KEY(xn_Tno))成功12.CREATE TABLE xun_Teach_course(xn_Cno CHAR(4),xn_Cname VARCHAR(30),xn_Tno CHAR(6)CONSTRAINT PK_teach_course PRIMARY KEY(xn_Cno)CONSTRAINT FK_teach_course FOREIGN KEY(xn_Tno)REFERENCES xun_Listen_course(xn_Tno))ALTER TABLE xun_Listen_course ADD CONSTRAINT FK_listen_course FOREIGN KEY(xn_Cno)REFERENCES xun_Teach_course(xn_Cno);、成功
