• 数据库原理及应用实验报告-实验8-参照完整性

    数据库原理及应用实验报告

     实验题目  实验8参照完整性  

      1. 实验目的

    学习建立外键，以及利用FOREIGN KEY…REFERENCES子句以及各种约束保证参照完整性。

      1. 实验内容
      1. 为演示参照完整性，建立表Course，令xn_Cno为其主键，并在xun_Stu_Union表中插入数据。为下面的实验步骤做预先准备。
      2. 建立表xun_SC，令xn_Sno和xn_Cno分别为参照xun_Stu_Union表以及Course表的外键，设定为级联删除，并令(xn_Sno,xn_Cno)为其主键。在不违反参照完整性的前提下，插入数据。
      3. 演示违反参照完整性的插入数据。
      4. 在xun_Stu_Union中删除数据，演示级联删除。
      5. 在Course中删除数据，演示级联删除。
      6. 为了演示多重级联删除，建立xun_Stu_Card表，令xn_Sno为参数xun_Students表的外键，令xn_Card_id为其主键，并插入数据。
      7. 为了演示多重级联删除，建立xun_ICBC_Card表，令xn_Stu_card_id为参数xun_Stu_Union表的外键，令xn_Card_id为其主键，并插入数据。
      8. 通过删除xun_Students表中的一条记录，演示三个表的多重级联删除。
      9. 演示事务中进行多重级联删除失败的处理。修改xun_ICBC_Card表的外键属性，使其变为On delete No action，演示事务中通过删除xun_Students表中的一条记录，多重级联删除失败，整个事务回滚到事务的初始状态。
      10. 演示互参考问题及其解决方法。要建立教师授课和课程指定教师听课关系的两张表，规定一个教师可以授多门课，但是每个课程只能指定一个教师去听课，所以要为两张表建立相互之间的参照关系。

    做 “实验8参照完整性”的准备工作


    1、建立xun_Stu_Union

    1. CREATE TABLE xun_Stu_Union
    2. (xn_Sno CHAR(8) not null primary key,                 
    3. xn_Sname CHAR(8),                   
    4. xn_Ssex CHAR(1),                   
    5. xn_Sage INT,                  
    6. xn_Sdept CHAR(20))


    2、插入相应数据，结果如图1

     图1

    1. insert into xun_Stu_Union
    2. values('S02','王兵','M','23','CS')
    3. insert into xun_Stu_Union
    4. values('S03','黄浩','F','25','EE')
    5. insert into xun_Stu_Union
    6. values('S05','黄浩','F','25','EE')
    7. insert into xun_Stu_Union
    8. values('S09','李永','M','25','EE')
      1. 实验步骤

    以系统管理员登录到SSMS，在新建查询窗口中输入如下命令，运行并观察和记录结果。

        1. 在新建查询窗口中输入如下SQL语句：

    Xuni_ University_Mis:

    建立表Course,令xn_Cno为其主键，在xun_Stu_Union Values和Course插入数据：

    1. INSERT xun_Stu_Union Values(‘S01’,‘李用’,‘0’,24,‘FF’)
    2. SELECT * FROM xun_Stu_Union;
    3. CREATE TABLE Course(
    4. 	xn_Cno CHAR(4) NOT NULL UNIQUE,
    5. 	xn_Cname VARCHAR(50) NOT NULL,
    6. 	xn_Cpoints INT,
    7. 	CONSTRAINT PK PRIMARY KEY(xn_Cno));
    8. INSERT Course VALUES(‘C01’,’ComputerNetworks’,2);
    9. INSERT Course VALUES(‘C02’,’ArtificialIntelligence’,3);

        1. 在新建查询窗口中输入如下SQL语句：
    1. Xuni_University_Mis:
    2.  
    3. CREATE TABLE xun_SC
    4. (xn_Sno CHAR(8) ,
    5.  xn_Cno CHAR(8) ,
    6.  Scredit INT,
    7. CONSTRAINT PK_SC PRIMARY KEY(xn_Sno,xn_Cno),
    8. CONSTRAINT FK_SC_Sno FOREIGN KEY(xn_Sno) REFERENCES xun_Stu_Union (xn_Sno) ON DELETE CASCADE,
    9. CONSTRAINT FK_SC_Cno FOREIGN KEY(xn_Cno) REFERENCES Course(xn_Cno) ON DELETE CASCADE
    10.  
    11. );
    12. INSERT INTO xun_SC VALUES('S02','C01',2);
    13. INSERT INTO xun_SC VALUES ('S02','C02',2);
    14. INSERT INTO xun_SC VALUES ('S01','C01',2);
    15. INSERT INTO xun_SC VALUES ('S01','C02',2);
    16. SELECT * FROM xun_SC;
        1. 在新建查询窗口中输入如下SQL语句：

    Xuni_University_Mis:

    演示违反参照完整性的插入数据：

    1. insert into xun_SC
    2. values('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 的外键，所以这样插入违反了参照完整性，不可行。

        1. 在新建查询窗口中输入如下SQL语句：

    Xuni_University_Mis:

    在xun_Stu_Union表中删除数据，演示级联删除

    1. DELETE FROM xun_Stu_Union WHERE xn_Sno='S01'
    2. SELECT * FROM xun_SC;

    结果图3，可知xun_SC表中xn_Sno='S01'的元组也被删除。

     

    图3删除S01

        1. 在新建查询窗口中输入如下SQL语句：

    Xuni_University_Mis:

    在Course表中删除数据，演示级联删除。

    1. DELETE FROM Courses WHERE xn_Cno='C02'
    2. SELECT * FROM xun_SC;

    结果如图4，可知xun_SC中xn_Cno='C02'的元组也被删除：

      图4删除C02

        1. 在新建查询窗口中输入如下SQL语句：

    Xuni_University_Mis:

    建立表xun_Stu_Card，令xn_Card_id为主键，并插入数据，令xn_Sno作为xun_Students表的外键。

    1. CREATE TABLE xun_Stu_Card(
    2. 	xn_Card_id CHAR(14),
    3. 	xn_Sno CHAR(8),
    4. 	Remained_money DECIMAL(10,2),
    5. 	Constraint PK_Stu_Card PRIMARY KEY(xn_Card_id),
    6.    Constraint FK_Stu_Card_Sno FOREIGN KEY(xn_Sno) REFERENCES xun_Students(xn_Sno) ON DELETE CASCADE
    7.         
    8. )
    9.  
    10. INSERT INTO xun_Stu_Card VALUES('05212567','S03',400.25);
    11. INSERT INTO xun_Stu_Card VALUES('05212222','S23',600.50);
    12. SELECT * FROM xun_Stu_card;

    因为xun_Students表中不存在xn_Sno=S09的元组，插入会失败，所以改成了S23

        1. 在新建查询窗口中输入如下SQL语句：

    Xuni_University_Mis

    建立表xun_ICBC_Card，xn_Stu_card_id 作为主键，xn_Stu_card_id为xun_Stu_card的外键。

    1.  
    2. CREATE TABLE xun_ICBC_Card(
    3. 	xn_Bank_id CHAR(20),
    4. 	xn_Stu_card_id CHAR(14),
    5. 	xn_Restored_money DECIMAL(10,2),
    6. 	constraint PK_ICBC_Card PRIMARY KEY(xn_Bank_id),
    7.    constraint FK_ICBC_Card_Stu_id FOREIGN KEY(xn_Stu_card_id) REFERENCES xun_Stu_card(xn_card_id) ON DELETE CASCADE
    8. )
    9. INSERT INTO xun_ICBC_Card VALUES('9558844022312','05212567',15000.1);
    10. INSERT INTO xun_ICBC_Card VALUES('9558844023645','05212222',50000.3);
    11. SELECT * FROM xun_ICBC_Card;
        1. 在新建查询窗口中输入如下SQL语句：

    Xuni_ University_Mis:

    删除 xun_Students 的记录，演示三个表的多重级联删除。

    1. ALTER TABLE xun_Reports DROP [students_report];
    2. ALTER TABLE xun_Reports ADD
    3. CONSTRAINT [FK_Reports_Students] FOREIGN KEY
    4. 	(
    5. 		[xn_Sno]
    6. 	) REFERENCES [dbo].[xun_Students] (
    7. 		[xn_Sno]
    8. 	) ON DELETE CASCADE;     
    9.  
    10.  
    11. DELETE FROM xun_Students WHERE xn_Sno='S03';
    12. SELECT * FROM xun_Stu_card;
    13. 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。

    1. ALTER TABLE xun_ICBC_Card
    2. DROP CONSTRAINT FK_ICBC_Card_Stu_id;
    3. ALTER TABLE xun_ICBC_Card
    4. ADD CONSTRAINT FK_ICBC_Card_Stu_id FOREIGN KEY (xn_Stu_card_id)
    5. REFERENCES xun_Stu_card(xn_Card_id) ON DELETE NO ACTION;

    在新建查询窗口中输入如下SQL语句：

    1. Begin Transaction Del
    2. 	DELETE FROM xun_Stu_Card WHERE xn_Card_id ='05212222';
    3. 	SELECT * FROM xun_Stu_card;
    4. 	SELECT * FROM xun_ICBC_card;
    5. 	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语句：

    1. Xuni_University_Mis
    2. SELECT * FROM Stu_card;
    3. SELECT * FROM ICBC_card;

    结果如图7：

     图7

        1. 在新建查询窗口中输入如下SQL语句：

    演示互参考问题及其解决方案，建立xun_Listen_course表和xun_Teach_course

    表。

    1. CREATE TABLE xun_Listen_course(
    2. 	xn_Tno CHAR(6),xn_Tname VARCHAR(20),xn_Cno CHAR(4)
    3. 	CONSTRAINT PK_listen_course PRIMARY KEY(xn_Tno)
    4. 	CONSTRAINT FK_listen_course FOREIGN KEY(xn_Cno)
    5. 	REFERENCES xun_Teach_course(xn_Cno)
    6. )
    7.  
    8. CREATE TABLE xun_Teach_course(
    9. 	xn_Cno CHAR(4),xn_Cname VARCHAR(30),xn_Tno CHAR(6)
    10. 	CONSTRAINT PK_Teach_course PRIMARY KEY(xn_Cno)
    11. 	CONSTRAINT FK_Teach_course FOREIGN KEY(xn_Tno)
    12. 	REFERENCES xun_Listen_course(xn_Tno)
    13. )

    报错：

    消息 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的外键

    1. CREATE TABLE xun_Listen_course(
    2. 	xn_Tno CHAR(6),xn_Tname VARCHAR(20),xn_Cno CHAR(4)
    3. 	CONSTRAINT PK_listen_Course PRIMARY KEY(xn_Tno)
    4. )

    成功

    (12) 在新建查询窗口中输入如下SQL语句：

    Xuni_ University_Mis

    1. CREATE TABLE xun_Teach_course(
    2. 	xn_Cno CHAR(4),xn_Cname VARCHAR(30),xn_Tno CHAR(6)
    3. 	CONSTRAINT PK_teach_course PRIMARY KEY(xn_Cno)
    4. 	CONSTRAINT FK_teach_course FOREIGN KEY(xn_Tno)
    5. 	REFERENCES xun_Listen_course(xn_Tno)
    6. )
    7. ALTER TABLE xun_Listen_course 
    8. 	ADD CONSTRAINT FK_listen_course FOREIGN KEY(xn_Cno)
    9. 	REFERENCES xun_Teach_course(xn_Cno);

    成功，如图9：

     

    图9

      1. 实验要求

    (1) 实验之前请细细阅读实验总体要求与说明指导书

    (2 ) 在SQL Server 2005或2008或2014的SSMS环境中，完成以上实验(1)-(10)步所有SQL数据定义操作，并将其中粗体步骤的操作窗口剪贴到实验报告中。

    8.5实验体会

    1）实验反思

      无

    2）实验收获

    学习力参照完整性的相关内容，加深了对于这部分知识点的认识

    附录：

    1. 准备工作
    2. CREATE TABLE xun_Stu_Union
    3. (xn_Sno CHAR(8) not null primary key,                 
    4. xn_Sname CHAR(8),                   
    5. xn_Ssex CHAR(1),                   
    6. xn_Sage INT,                  
    7. xn_Sdept CHAR(20))
    8.  
    9.  
    10. insert into xun_Stu_Union
    11. values('S02','王兵','M','23','CS')
    12.  
    13. insert into xun_Stu_Union
    14. values('S03','黄浩','F','25','EE')
    15.  
    16. insert into xun_Stu_Union
    17. values('S05','黄浩','F','25','EE')
    18.  
    19. insert into xun_Stu_Union
    20. values('S09','李永','M','25','EE')
    21.  
    22. 1.
    23. INSERT xun_Stu_Union Values(‘S01’,‘李用’,‘0’,24,‘FF’)
    24. SELECT * FROM xun_Stu_Union;
    25. CREATE TABLE Course(
    26. 	xn_Cno CHAR(4) NOT NULL UNIQUE,
    27. 	xn_Cname VARCHAR(50) NOT NULL,
    28. 	xn_Cpoints INT,
    29. 	CONSTRAINT PK PRIMARY KEY(xn_Cno));
    30. INSERT Course VALUES(‘C01’,’ComputerNetworks’,2);
    31. INSERT Course VALUES(‘C02’,’ArtificialIntelligence’,3);
    32.  
    33. 2.
    34. CREATE TABLE xun_SC
    35. (xn_Sno CHAR(8) ,
    36.  xn_Cno CHAR(8) ,
    37.  Scredit INT,
    38. CONSTRAINT PK_SC PRIMARY KEY(xn_Sno,xn_Cno),
    39. CONSTRAINT FK_SC_Sno FOREIGN KEY(xn_Sno) REFERENCES xun_Stu_Union (xn_Sno) ON DELETE CASCADE,
    40. CONSTRAINT FK_SC_Cno FOREIGN KEY(xn_Cno) REFERENCES Course(xn_Cno) ON DELETE CASCADE
    41.  
    42. );
    43. INSERT INTO xun_SC VALUES('S02','C01',2);
    44. INSERT INTO xun_SC VALUES ('S02','C02',2);
    45. INSERT INTO xun_SC VALUES ('S01','C01',2);
    46. INSERT INTO xun_SC VALUES ('S01','C02',2);
    47. SELECT * FROM xun_SC;
    48.  
    49.  
    50.  
    51. 3.
    52. insert into xun_SC 
    53. values('S99','C99',2)
    54.  
    55. 消息 547，级别 16，状态 0，第 1 行
    56. INSERT 语句与 FOREIGN KEY 约束"FK_SC_Sno"冲突。该冲突发生于数据库"Xuni_University_Mis"，
    57. 表"dbo.xun_Stu_Union", column 'xn_Sno'。
    58. 语句已终止。
    59.  
    60. 4.
    61. DELETE FROM xun_Stu_Union WHERE xn_Sno='S01'
    62. SELECT * FROM xun_SC;
    63. 图
    64.  
    65. 5.
    66. DELETE FROM Courses WHERE xn_Cno='C02'
    67. SELECT * FROM xun_SC;
    68. 图
    69.  
    70. 6.
    71. CREATE TABLE xun_Stu_Card(
    72. 	xn_Card_id CHAR(14),
    73. 	xn_Sno CHAR(8),
    74. 	Remained_money DECIMAL(10,2),
    75. 	Constraint PK_Stu_Card PRIMARY KEY(xn_Card_id),
    76.    Constraint FK_Stu_Card_Sno FOREIGN KEY(xn_Sno) REFERENCES xun_Students(xn_Sno) ON DELETE CASCADE
    77.         
    78. )
    79.  
    80. INSERT INTO xun_Stu_Card VALUES('05212567','S03',400.25);
    81. INSERT INTO xun_Stu_Card VALUES('05212222','S23',600.50);
    82. SELECT * FROM xun_Stu_card;
    83.  
    84. 7.
    85. CREATE TABLE xun_ICBC_Card(
    86. 	xn_Bank_id CHAR(20),
    87. 	xn_Stu_card_id CHAR(14),
    88. 	xn_Restored_money DECIMAL(10,2),
    89. 	constraint PK_ICBC_Card PRIMARY KEY(xn_Bank_id),
    90.    constraint FK_ICBC_Card_Stu_id FOREIGN KEY(xn_Stu_card_id) REFERENCES xun_Stu_card(xn_card_id) ON DELETE CASCADE
    91. )
    92. INSERT INTO xun_ICBC_Card VALUES('9558844022312','05212567',15000.1);
    93. INSERT INTO xun_ICBC_Card VALUES('9558844023645','05212222',50000.3);
    94. SELECT * FROM xun_ICBC_Card;
    95.  
    96. 8.
    97. ALTER TABLE xun_Reports DROP [students_report];
    98. ALTER TABLE xun_Reports ADD
    99. CONSTRAINT [FK_Reports_Students] FOREIGN KEY
    100. 	(
    101. 		[xn_Sno]
    102. 	) REFERENCES [dbo].[xun_Students] (
    103. 		[xn_Sno]
    104. 	) ON DELETE CASCADE;     
    105.  
    106.  
    107. DELETE FROM xun_Students WHERE xn_Sno='S03';
    108. SELECT * FROM xun_Stu_card;
    109. SELECT * FROM xun_ICBC_Card;
    110.  
    111. 9.1
    112.  
    113. ALTER TABLE xun_ICBC_Card
    114. DROP CONSTRAINT FK_ICBC_Card_Stu_id;
    115. ALTER TABLE xun_ICBC_Card
    116. ADD CONSTRAINT FK_ICBC_Card_Stu_id FOREIGN KEY (xn_Stu_card_id)
    117. REFERENCES xun_Stu_card(xn_Card_id) ON DELETE NO ACTION;
    118.  
    119. 9.2
    120. Begin Transaction Del
    121. 	DELETE FROM xun_Stu_Card WHERE xn_Card_id ='05212222';
    122. 	SELECT * FROM xun_Stu_card;
    123. 	SELECT * FROM xun_ICBC_card;
    124. 	Commit Transaction Del
    125.  
    126. 消息 547，级别 16，状态 0，第 2 行
    127. DELETE 语句与 REFERENCE 约束"FK_ICBC_Card_Stu_id"冲突。该冲突发生于数据库"Xuni_University_Mis"，表"dbo.xun_ICBC_Card", column 'xn_Stu_card_id'。
    128. 语句已终止。
    129.  
    130. (1 行受影响)
    131.  
    132. (1 行受影响)
    133.  
    134. SELECT * FROM xun_Stu_card;
    135. SELECT * FROM xun_ICBC_card;
    136.  
    137. 10.
    138. CREATE TABLE xun_Listen_course(
    139. 	xn_Tno CHAR(6),xn_Tname VARCHAR(20),xn_Cno CHAR(4)
    140. 	CONSTRAINT PK_listen_course PRIMARY KEY(xn_Tno)
    141. 	CONSTRAINT FK_listen_course FOREIGN KEY(xn_Cno)
    142. 	REFERENCES xun_Teach_course(xn_Cno)
    143. )
    144.  
    145. CREATE TABLE xun_Teach_course(
    146. 	xn_Cno CHAR(4),xn_Cname VARCHAR(30),xn_Tno CHAR(6)
    147. 	CONSTRAINT PK_Teach_course PRIMARY KEY(xn_Cno)
    148. 	CONSTRAINT FK_Teach_course FOREIGN KEY(xn_Tno)
    149. 	REFERENCES xun_Listen_course(xn_Tno)
    150. )
    151.  
    152. 报错：：
    153. 消息 1767，级别 16，状态 0，第 1 行
    154. 外键 'FK_listen_course' 引用了无效的表 'xun_Teach_course'。
    155. 消息 1750，级别 16，状态 0，第 1 行
    156. 无法创建约束或索引。请参阅前面的错误。
    157. 11.
    158. CREATE TABLE xun_Listen_course(
    159. 	xn_Tno CHAR(6),xn_Tname VARCHAR(20),xn_Cno CHAR(4)
    160. 	CONSTRAINT PK_listen_Course PRIMARY KEY(xn_Tno)
    161. )
    162.  
    163. 成功
    164.  
    165. 12.
    166. CREATE TABLE xun_Teach_course(
    167. 	xn_Cno CHAR(4),xn_Cname VARCHAR(30),xn_Tno CHAR(6)
    168. 	CONSTRAINT PK_teach_course PRIMARY KEY(xn_Cno)
    169. 	CONSTRAINT FK_teach_course FOREIGN KEY(xn_Tno)
    170. 	REFERENCES xun_Listen_course(xn_Tno)
    171. )
    172. ALTER TABLE xun_Listen_course 
    173. 	ADD CONSTRAINT FK_listen_course FOREIGN KEY(xn_Cno)
    174. 	REFERENCES xun_Teach_course(xn_Cno);、
    175.  
    176. 成功
    177.  
    178.

  • 相关阅读:
    flutter百度地图定位, poi检索功能
    C++11
    LabVIEW什么时候需要使用DLL封装 Calling External APIs
    【算法|动态规划No.9】leetcodeLCR 091. 粉刷房子
    人工智能基础_机器学习040_Sigmoid函数详解_单位阶跃函数与对数几率函数_伯努利分布---人工智能工作笔记0080
    Spring三级缓存解决循环依赖
    Android学习笔记 17. RecyclerView
    P1966 [NOIP2013 提高组] 火柴排队
    工业相机常见的工作模式、触发方式
    从零开始写 Docker(一)---实现 mydocker run 命令
  • 原文地址：https://blog.csdn.net/cangzhexingxing/article/details/125563268