实验9 数据完整性实验

1、德州学院德州学院 数据库技术与应用实验数据库技术与应用实验 实验实验9 数据完整性实验数据完整性实验 拓展练习拓展练习 实验步骤实验步骤 实验要求实验要求 实验目的实验目的 预备知识预备知识 预备知识预备知识 -约束机制的分类约束机制的分类v 为了保证数据完整性，为了保证数据完整性，Sql Server 2008提供提供了下列约束机制：了下列约束机制： （1）PRIMARY KEY约束（主键约束），用于实现实约束（主键约束），用于实现实体完整性；体完整性； （2）FOREIGN KEY约束（外键约束），用于实现参约束（外键约束），用于实现参照完整性；照完整性； （3）UNIQUE约束（唯一约束

2、）、约束（唯一约束）、NOT NULL约束约束(非空约束非空约束)、CHECK约束（检查约束）和约束（检查约束）和DEFAULT约束（默认值约束），用于实现用户自定义的完整性。约束（默认值约束），用于实现用户自定义的完整性。 预备知识预备知识 -创建约束创建约束 在在Sql Server 2008中，所有约束即可以在中，所有约束即可以在创建表时同时创建，也可以在表创建完毕创建表时同时创建，也可以在表创建完毕后添加。约束又分列级约束和表级约束，后添加。约束又分列级约束和表级约束，除除NOT NULL只能做列级约束外，其余约只能做列级约束外，其余约束即可做列级约束，也可以做表级约束，束即可做列级约

3、束，也可以做表级约束，只是列级约束只能引用被约束的列，而表只是列级约束只能引用被约束的列，而表级约束能引用表中的任意列。级约束能引用表中的任意列。 预备知识预备知识 -创建约束创建约束 PRIMARY KEY 约束v 列级约束格式： CONSTRAINT 约束名 PRIMARY KEY CLUSTERED | NONCLUSTEREDv 表级约束格式： CONSTRAINT 约束名 PRIMARY KEY CLUSTERED | NONCLUSTERED (字段名) 关键字CLUSTERED和NONCLUSTERED指定主键约束是聚集索引还是非聚集索引。如果省略，并且没有为unique约束指定

4、聚集索引，则对该主键约束使用CLUSTERE 预备知识预备知识 -创建约束创建约束 【例9-1】建立CourseInfo表，并指定Cno为主键。CREATE TABLE CourseInfo( Cno char(6) CONSTRAINT PK_CourseInfo PRIMARY KEY, /*列级主键约束*/Cname varchar(50) NULL,Cpno char(6) NULL,Credit decimal(2, 1) NULL) 预备知识预备知识 -创建约束创建约束 FOREIGN KEY约束v CONSTRAINT 约束名 FOREIGN KEY (关联字段) REFEREN

5、CES 被参照(被关联字段) ON DELETE CASCADE | NO ACTION | SET NULL | SET DEFAULT ON UPDATE CASCADE | NO ACTION | SET NULL | SET DEFAULTv 其中，ON DELETE CASCADE | NO ACTION | SET NULL | SET DEFAULT是指当被参照表删除元组时，可以采用以下四种方式之一保证参照完整性：级联删除参照表中相应元组，或者被参照表拒绝删除元组，或者将参照表中相应元组的值设置为null，或者将参照表中相应元组的值设置为默认值。主键表更新元组时，也可以采用这四种

6、方式中的一种。 预备知识预备知识 -创建约束创建约束v 【例9-2】建立TeachTasksInfo表，并将字段Cno与CourseInfo表中的Cno建立外键联系，指定当被参照表（CourseInfo表）删除元组造成与参照表（TeachTasksInfo表）数据不一致时，拒绝删除被参照表中的元组；指定当被参照表（CourseInfo表）更新元组时，则级联更新参照表（TeachTasksInfo表）中相应的元组数据。 预备知识预备知识 -创建约束创建约束 CREATE TABLE TeachTasksInfo( TeachTaskID char(26) primary key, Cno cha

7、r(6), TeacherID char(5), AcadYear char(9), Term char(1), ExamMethod varchar(10), Depart varchar(30), TimeTable varchar(50), CONSTRAINT FK_TeachTaskCourseInfo FOREIGN KEY(Cno) REFERENCES CourseInfo(Cno) ON UPDATE CASCADE /*当更新当更新CourseInfo中的中的Cno时，级联更新时，级联更新TeachTasksInfo */ ON DELETE NO ACTION /*当删除

8、当删除CourseInfo表中的元组造成不一致时，拒绝删除表中的元组造成不一致时，拒绝删除*/) 预备知识预备知识 -创建约束创建约束 UNIQUE约束v 列级约束格式： CONSTRAINT 约束名约束名 UNIQUEv 表级约束格式： CONSTRAINT 约束名约束名 UNIQUE (字段名字段名)v 【例9-3】建立StudentInfo表，并指定Sno和TeachTaskID联合列值唯一。CREATE TABLE SC(Sno char(12),TeachTaskID char(26),Grade decimal(5, 2),UNIQUE (Sno,TeachTaskID)） 预备知

9、识预备知识 -创建约束创建约束 CHECK约束：列级CHECK约束和表级CHECK约束格式相同：v CONSTRAINT 约束名 CHECK(检验表达式)v 【例9-4】建立StudentInfo表，并指定Sex只能取“男”或“女”，LengSch的值应该在1和7之间。CREATE TABLE dbo.StudentInfo(Sno char(12) PRIMARY KEY,Sname nvarchar(20) ,Sex char(2),Birthday date,Depart varchar(30),Major varchar(50),LengSch tinyint,CONSTRAINT C

10、_Sex CHECK(Sex in (男男,女女), /* Sex只能取只能取“男男”或或“女女”*/CONSTRAINT C_LengSch CHECK(LengSch=1 and LengSch=7 )/*LengSch的值应该在的值应该在1和和7之间，等价于之间，等价于CHECK( LengSch between 1 and 7)*/) 预备知识预备知识 -创建约束创建约束 DEFAULT约束v 列级约束格式：CONSTRAINT 约束名 DEFAULT 默认值v 表级约束格式：CONSTRAINT 约束名 DEFAULT 默认值 FOR 字段名v 【例例9-5】建立建立StudentI

11、nfo表，并指定表，并指定Sex的默认值为的默认值为“女女”，LengSch的默认值为的默认值为4。CREATE TABLE dbo.StudentInfo(Sno char(12) PRIMARY KEY,Sname nvarchar(20) ,Sex char(2),Birthday date,Depart varchar(30), Major varchar(50),LengSch tinyint DEFAULT 4, /* LengSch的默认值为4*/CONSTRAINT Default_Sex DEFAULT 女 FOR Sex /* Sex的默认值为“女”*/) 预备知识预备知识

12、 -创建约束创建约束表创建后添加约束表创建后添加约束v如果表已经存在，则可以通过ALTER TABLE语句添加约束，格式为： ALTER TABLE 表名 ADD CONSTRAINT 约束名 约束 具体约束的格式与在创建表的同时创建约束时的表级约束格式相同。 预备知识预备知识 -创建约束创建约束v【例9-6】假设StudentInfo表已经建立，且没有建立任何约束。为StudentInfo添加主键约束，且要求Sno(字符型，长度为6)全部为数字。ALTER TABLE StudentInfo ADD PRIMARY KEY (Sno)ALTER TABLE StudentInfo ADD c

13、heck (Sno like 0-90-90-90-90-90-9) 预备知识预备知识 删除删除约束约束v删除约束的格式为： ALTER TABLE 表名 DROP CONSTRAINT 约束名v【例9-7】删除【例9-5】中Default_Sex约束。ALTER TABLE StudentInfo DROP Default_Sex 实验实验9 数据完整性实验数据完整性实验 拓展练习拓展练习 实验步骤实验步骤 实验要求实验要求 实验目的实验目的 预备知识预备知识 实验目的实验目的 1. 理解数据完整性的实现方式和作用。理解数据完整性的实现方式和作用。2. 掌握应用掌握应用Management

14、Studio创建和删创建和删除约束的方法。除约束的方法。3. 掌握应用掌握应用T-SQL语句创建和删除约束的方语句创建和删除约束的方法。法。 实验实验9 数据完整性实验数据完整性实验 拓展练习拓展练习 实验步骤实验步骤 实验要求实验要求 实验目的实验目的 预备知识预备知识 实验要求实验要求 1. 为不带任何约束的样例数据库为不带任何约束的样例数据库ST中的各中的各表建立适当的约束。表建立适当的约束。2. 对样例数据库对样例数据库ST中各表中的约束进行管中各表中的约束进行管理。理。3. 撰写实验报告，并附实验结果与相应实验撰写实验报告，并附实验结果与相应实验语句。语句。 实验实验9 数据完整性实

15、验数据完整性实验 拓展练习拓展练习 实验步骤实验步骤 实验要求实验要求 实验目的实验目的 预备知识预备知识 实验步骤实验步骤1.为为StudentInfo表的表的Sno添加添加UNIQUE约束，并添加记录，体会唯一约约束，并添加记录，体会唯一约束的作用。束的作用。(1)在查询窗口中输入添加在查询窗口中输入添加UNIQUE约束的约束的SQL语句，并执行：语句，并执行：USE STALTER TABLE StudentInfo ADD CONSTRAINT UN_ Sno UNIQUE(Sno)(2)输入以下添加记录的输入以下添加记录的SQL语句，并执行语句，并执行2次：次：INSERT INTO

16、 StudentInfo VALUES(201001903029,明梅明梅,女女,1991-03-15,计算机系计算机系,计算机科学与技术计算机科学与技术(本本科科),4)第一次执行成功，记录成功插入，第二次执行在消息窗口中显示以下消息，表示执行插入操作第一次执行成功，记录成功插入，第二次执行在消息窗口中显示以下消息，表示执行插入操作失败：失败：消息消息2627，级别，级别14，状态，状态1，第，第4 行行违反了违反了UNIQUE KEY 约束约束 UN _Sno。不能在对象。不能在对象dbo.StudentInfo 中插入重复键。中插入重复键。 实验步骤实验步骤2.为为SC表添加表添加PRI

17、MARKY KEY约束，并添加记录，体会主键约束，并添加记录，体会主键约束的作用。约束的作用。(1)在查询窗口中输入添加在查询窗口中输入添加PRIMARY KEY约束的约束的SQL语句，并执行：语句，并执行：vALTER TABLE SCv ADD CONSTRAINT PK_SC PRIMARY KEY(Sno,TeachTaskID)(2)输入以下添加记录的输入以下添加记录的SQL语句，并执行语句，并执行2次：次：vINSERT INTO SCvVALUES(201001903029,(2009-2010-1)-140173-04001,85)第一次执行成功，记录成功插入，第二次执行在消息

18、窗口中显示以下消息，第一次执行成功，记录成功插入，第二次执行在消息窗口中显示以下消息，表示执行插入操作失败：表示执行插入操作失败：消息消息2627，级别，级别14，状态，状态1，第，第1 行行违反了违反了PRIMARY KEY 约束约束PK_SC。不能在对象。不能在对象dbo.SC 中插入重复键。中插入重复键。 实验步骤实验步骤(3)输入以下添加记录的输入以下添加记录的SQL语句，并执行：语句，并执行：vINSERT INTO SC(TeachTaskID,Grade) VALUES(2009-2010-1)-140173-04001,90)消息窗口中显示以下消息，表示执行插入操作失败：消息窗

19、口中显示以下消息，表示执行插入操作失败：消息消息515，级别，级别16，状态，状态2，第，第1 行行不能将值不能将值NULL插入列插入列Sno，表，表ST.dbo.SC；列不允许有；列不允许有Null 值。值。INSERT 失败。失败。 实验步骤实验步骤3.为为SC表添加表添加FOREIGN KEY约束，并指定当修改被参照表约束，并指定当修改被参照表StudentInfo的元组造成与的元组造成与SC不一致时，采用级联修改策不一致时，采用级联修改策略，当删除被参照表略，当删除被参照表StudentInfo的元组造成与的元组造成与SC不一致不一致时，采用拒绝策略。并更新表中记录，体会外键约束的作时

20、，采用拒绝策略。并更新表中记录，体会外键约束的作用。用。(1)在查询窗口中输入添加外键约束的在查询窗口中输入添加外键约束的SQL语句，并执行：语句，并执行：ALTER TABLE SCADD CONSTRAINT FK_Sno FOREIGN KEY(Sno) REFERENCES StudentInfo(Sno)ON DELETE NO ACTIONON UPDATE CASCADE 实验步骤实验步骤v(2)输入以下为输入以下为SC表添加记录的表添加记录的SQL语句，并执行：语句，并执行：INSERT INTO SC VALUES(201001903028,(2009-2010-1)-140

21、173-04001,85)v消息窗口中显示以下信息，表示插入操作失败，因为消息窗口中显示以下信息，表示插入操作失败，因为StudentInfo表中不存在学号表中不存在学号“201001903028”：v消息消息547，级别，级别16，状态，状态0，第，第1 行行vINSERT 语句与语句与FOREIGN KEY 约束约束FK_Sno冲突。该冲突冲突。该冲突发生于数据库发生于数据库new，表，表dbo.StudentInfo, column Sno。 实验步骤实验步骤(3)输入以下修改输入以下修改StudentInfo表中记录的表中记录的SQL语语句，并执行：句，并执行：UPDATE Stude

22、ntInfoSET Sno=201001903028 WHERE Sno =201001903029执行完毕后查看执行完毕后查看SC中记录，会发现原来中记录，会发现原来Sno值为值为“201001903029”的记录已经被级联修改为的记录已经被级联修改为“201001903028”。 实验步骤实验步骤(4）输入以下删除）输入以下删除StudentInfo中记录的中记录的SQL语句，并语句，并执行：执行：DELETE StudentInfo WHERE Sno =201001903028消息窗口中显示以下信息，表示删除操作失败：消息窗口中显示以下信息，表示删除操作失败：消息消息547，级别，级别

23、16，状态，状态0，第，第1 行行DELETE 语句与语句与REFERENCE 约束约束FK_Sno冲突。该冲冲突。该冲突发生于数据库突发生于数据库ST，表，表dbo.SC, column Sno。 实验步骤实验步骤4.为为StudentInfo表的表的Sex列添加列添加CHECK约束，保证性约束，保证性别只能取别只能取“男男”或或“女女”，并添加记录，体会，并添加记录，体会CHECK约束的作用。约束的作用。(1)在查询窗口中输入以下为在查询窗口中输入以下为Sex添加添加CHECK约束的约束的SQL语句，并执行：语句，并执行：ALTER TABLE StudentInfoADD CONSTRA

24、INT Ck_Sex CHECK(Sex IN(男男,女女) 实验步骤实验步骤v(2)输入以下插入记录的输入以下插入记录的SQL语句，并执行：语句，并执行：INSERT INTO StudentInfoVALUES(201011801017,王鹏飞王鹏飞,家家,1991-01-05,服装系服装系,服装设计与工程服装设计与工程(专科专科),3)v消息窗口中会显示以下信息，表示插入操作失败：消息窗口中会显示以下信息，表示插入操作失败：v消息消息547，级别，级别16，状态，状态0，第，第1 行行vINSERT 语句与语句与CHECK 约束约束Ck_Sex冲突。该冲突发生冲突。该冲突发生于数据库于数

25、据库ST，表，表dbo.StudentInfo, column Sex。 实验步骤实验步骤5.为为StudentInfo表的表的Depart列添加列添加DEFAULT约束，约束，设置默认值为设置默认值为“计算机系计算机系”。并添加记录，体会。并添加记录，体会DEFAULT约束的作用。约束的作用。(1)在查询窗口中输入以下为在查询窗口中输入以下为Depart添加添加DEFAULT约约束的语句，并执行：束的语句，并执行：ALTER TABLE StudentInfoADD CONSTRAINT DK_Depart DEFAULT 计算计算机系机系 for Depart 实验步骤实验步骤(2)输入以

26、下插入记录的输入以下插入记录的SQL语句，并执行：语句，并执行：INSERT INTO StudentInfo(Sno,Sname,Sex,Birthday,Major,LengSch) values(201011801017,王鹏飞王鹏飞,男男,1991-01-05,服装设计服装设计与工程与工程(专科专科),3)(3)查看查看StudentInfo新添加记录值为：新添加记录值为：(201011801017,王鹏飞王鹏飞,男男,1991-01-05, 计算机系计算机系,服服装设计与工程装设计与工程(专科专科),3) 实验步骤实验步骤6.为为StudentInfo表中的表中的Sname列设置列设

27、置NOT NULL约束，约束，添加记录，体会非空约束的作用。添加记录，体会非空约束的作用。(1)在查询窗口中输入以下更新数据列属性的在查询窗口中输入以下更新数据列属性的SQL语句，语句，并执行：并执行：ALTER TABLE StudentInfo ALTER COLUMN Sname varchar(20) NOT NULL 实验步骤实验步骤(2)输入以下插入记录的输入以下插入记录的SQL语句，并执行：语句，并执行：INSERT INTO StudentInfovalues(201011801018,NULL,女女,1991-09-09,服装服装系系,服装设计与工程服装设计与工程(1升本升本

28、),4)v消息窗口中会显示以下信息，表示插入操作失败：消息窗口中会显示以下信息，表示插入操作失败：v消息消息515，级别，级别16，状态，状态2，第，第1 行行v不能将值不能将值NULL插入列插入列Sname，表，表ST.dbo.StudentInfo；列不允许有列不允许有Null 值。值。INSERT 失败。失败。 实验步骤实验步骤7.删除以上建立的约束，体会约束之间的依赖关系。删除以上建立的约束，体会约束之间的依赖关系。(1)在查询窗口中输入删除在查询窗口中输入删除StudentInfo表中的唯一性约束的表中的唯一性约束的SQL语句，并执行：语句，并执行：ALTER TABLE StudentInfoDROP CONSTRAINT UN_ Snov消息窗口中会显示以下信息，表示删除约束操作失败：消息窗口中会显示以下信息，表示删除约束操作失败：v消息消息3725，级别，级别16，状态，状态0，第，第1 行行v约束约束 UN_ Sno 正由表正由表SC 的外键约束的外键约束FK_Sno 引用。引用。v消息消息3727，级别，级别16，状态，状态0，第，第1 行行v未能删除约束。请参阅前面的