实验数据完整性实验

实验数据完整性实验第1页，共38页。预备知识-约束机制的分类

为了保证数据完整性，SqlServer2008提供了下列约束机制：（1）PRIMARYKEY约束（主键约束），用于实现实体完整性；（2）FOREIGNKEY约束（外键约束），用于实现参照完整性；（3）UNIQUE约束（唯一约束）、NOTNULL约束(非空约束)、CHECK约束（检查约束）和DEFAULT约束（默认值约束），用于实现用户自定义的完整性。AnIntroductiontoDatabaseSystem第2页，共38页。预备知识-创建约束在SqlServer2008中，所有约束即可以在创建表时同时创建，也可以在表创建完毕后添加。约束又分列级约束和表级约束，除NOTNULL只能做列级约束外，其余约束即可做列级约束，也可以做表级约束，只是列级约束只能引用被约束的列，而表级约束能引用表中的任意列。

AnIntroductiontoDatabaseSystem第3页，共38页。预备知识-创建约束

PRIMARYKEY约束列级约束格式：[CONSTRAINT约束名]PRIMARYKEY[CLUSTERED|NONCLUSTERED]表级约束格式：[CONSTRAINT约束名]PRIMARYKEY[CLUSTERED|NONCLUSTERED](字段名)关键字CLUSTERED和NONCLUSTERED指定主键约束是聚集索引还是非聚集索引。如果省略，并且没有为unique约束指定聚集索引，则对该主键约束使用CLUSTEREAnIntroductiontoDatabaseSystem第4页，共38页。预备知识-创建约束

【例9-1】建立CourseInfo表，并指定Cno为主键。CREATETABLECourseInfo( Cnochar(6)CONSTRAINTPK_CourseInfoPRIMARYKEY,/*列级主键约束*/ Cnamevarchar(50)NULL, Cpnochar(6)NULL, Creditdecimal(2,1)NULL)AnIntroductiontoDatabaseSystem第5页，共38页。预备知识-创建约束

FOREIGNKEY约束[CONSTRAINT约束名]FOREIGNKEY(关联字段)REFERENCES被参照(被关联字段)[ONDELETECASCADE|NOACTION|SETNULL|SETDEFAULT][ONUPDATECASCADE|NOACTION|SETNULL|SETDEFAULT]其中，ONDELETECASCADE|NOACTION|SETNULL|SETDEFAULT是指当被参照表删除元组时，可以采用以下四种方式之一保证参照完整性：级联删除参照表中相应元组，或者被参照表拒绝删除元组，或者将参照表中相应元组的值设置为null，或者将参照表中相应元组的值设置为默认值。主键表更新元组时，也可以采用这四种方式中的一种。AnIntroductiontoDatabaseSystem第6页，共38页。预备知识-创建约束

【例9-2】建立TeachTasksInfo表，并将字段Cno与CourseInfo表中的Cno建立外键联系，指定当被参照表（CourseInfo表）删除元组造成与参照表（TeachTasksInfo表）数据不一致时，拒绝删除被参照表中的元组；指定当被参照表（CourseInfo表）更新元组时，则级联更新参照表（TeachTasksInfo表）中相应的元组数据。AnIntroductiontoDatabaseSystem第7页，共38页。预备知识-创建约束

CREATETABLETeachTasksInfo(TeachTaskIDchar(26)primarykey,Cnochar(6),TeacherIDchar(5),AcadYearchar(9),Termchar(1),ExamMethodvarchar(10),Departvarchar(30),TimeTablevarchar(50),CONSTRAINTFK_TeachTaskCourseInfoFOREIGNKEY(Cno)REFERENCESCourseInfo(Cno)ONUPDATECASCADE/*当更新CourseInfo中的Cno时，级联更新TeachTasksInfo*/ONDELETENOACTION/*当删除CourseInfo表中的元组造成不一致时，拒绝删除*/)AnIntroductiontoDatabaseSystem第8页，共38页。预备知识-创建约束

UNIQUE约束列级约束格式：

[CONSTRAINT约束名]UNIQUE表级约束格式：

[CONSTRAINT约束名]UNIQUE(字段名)【例9-3】建立StudentInfo表，并指定Sno和TeachTaskID联合列值唯一。 CREATETABLESC ( Snochar(12), TeachTaskIDchar(26), Gradedecimal(5,2), UNIQUE(Sno,TeachTaskID) ）AnIntroductiontoDatabaseSystem第9页，共38页。预备知识-创建约束

CHECK约束：列级CHECK约束和表级CHECK约束格式相同：[CONSTRAINT约束名]CHECK(检验表达式)【例9-4】建立StudentInfo表，并指定Sex只能取“男”或“女”，LengSch的值应该在1和7之间。 CREATETABLE[dbo].[StudentInfo] ( Snochar(12)PRIMARYKEY, Snamenvarchar(20), Sexchar(2), Birthdaydate, Departvarchar(30), Majorvarchar(50), LengSchtinyint, CONSTRAINTC_SexCHECK(Sexin('男','女')),/*Sex只能取“男”或“女”*/ CONSTRAINTC_LengSchCHECK(LengSch>=1andLengSch<=7) /*LengSch的值应该在1和7之间，等价于CHECK(LengSchbetween1and7)*/ )AnIntroductiontoDatabaseSystem第10页，共38页。预备知识-创建约束

DEFAULT约束列级约束格式： [CONSTRAINT约束名]DEFAULT默认值表级约束格式： [CONSTRAINT约束名]DEFAULT默认值FOR字段名【例9-5】建立StudentInfo表，并指定Sex的默认值为“女”，LengSch的默认值为4。 CREATETABLE[dbo].[StudentInfo] ( Snochar(12)PRIMARYKEY, Snamenvarchar(20), Sexchar(2), Birthdaydate, Departvarchar(30), Majorvarchar(50), LengSchtinyintDEFAULT4,/*LengSch的默认值为4*/ CONSTRAINTDefault_SexDEFAULT'女'FORSex/*Sex的默认值为“女”*/ )AnIntroductiontoDatabaseSystem第11页，共38页。预备知识-创建约束表创建后添加约束如果表已经存在，则可以通过ALTERTABLE语句添加约束，格式为：

ALTERTABLE表名

ADD[CONSTRAINT约束名]约束

具体约束的格式与在创建表的同时创建约束时的表级约束格式相同。AnIntroductiontoDatabaseSystem第12页，共38页。预备知识-创建约束【例9-6】假设StudentInfo表已经建立，且没有建立任何约束。为StudentInfo添加主键约束，且要求Sno(字符型，长度为6)全部为数字。 ALTERTABLEStudentInfo ADDPRIMARYKEY(Sno) ALTERTABLEStudentInfo ADDcheck(Snolike'[0-9][0-9][0-9][0-9][0-9][0-9]')AnIntroductiontoDatabaseSystem第13页，共38页。预备知识–删除约束删除约束的格式为：ALTERTABLE表名

DROPCONSTRAINT约束名【例9-7】删除【例9-5】中Default_Sex约束。 ALTERTABLEStudentInfoDROPDefault_SexAnIntroductiontoDatabaseSystem第14页，共38页。实验9数据完整性实验拓展练习实验步骤实验要求实验目的预备知识AnIntroductiontoDatabaseSystem第15页，共38页。实验目的理解数据完整性的实现方式和作用。掌握应用ManagementStudio创建和删除约束的方法。掌握应用T-SQL语句创建和删除约束的方法。AnIntroductiontoDatabaseSystem第16页，共38页。实验9数据完整性实验拓展练习实验步骤实验要求实验目的预备知识AnIntroductiontoDatabaseSystem第17页，共38页。实验要求为不带任何约束的样例数据库ST中的各表建立适当的约束。对样例数据库ST中各表中的约束进行管理。撰写实验报告，并附实验结果与相应实验语句。AnIntroductiontoDatabaseSystem第18页，共38页。实验9数据完整性实验拓展练习实验步骤实验要求实验目的预备知识AnIntroductiontoDatabaseSystem第19页，共38页。实验步骤为StudentInfo表的Sno添加UNIQUE约束，并添加记录，体会唯一约束的作用。(1)在查询窗口中输入添加UNIQUE约束的SQL语句，并执行：USESTALTERTABLEStudentInfoADDCONSTRAINTUN_SnoUNIQUE(Sno)(2)输入以下添加记录的SQL语句，并执行2次：INSERTINTOStudentInfoVALUES('201001903029','明梅','女','1991-03-15','计算机系','计算机科学与技术(本科)',4)第一次执行成功，记录成功插入，第二次执行在消息窗口中显示以下消息，表示执行插入操作失败：消息2627，级别14，状态1，第4行违反了UNIQUEKEY约束'UN_Sno'。不能在对象'dbo.StudentInfo'中插入重复键。

AnIntroductiontoDatabaseSystem第20页，共38页。实验步骤2.为SC表添加PRIMARKYKEY约束，并添加记录，体会主键约束的作用。(1)在查询窗口中输入添加PRIMARYKEY约束的SQL语句，并执行：ALTERTABLESCADDCONSTRAINTPK_SCPRIMARYKEY(Sno,TeachTaskID)(2)输入以下添加记录的SQL语句，并执行2次：INSERTINTOSCVALUES('201001903029','(2009-2010-1)-140173-04001',85)第一次执行成功，记录成功插入，第二次执行在消息窗口中显示以下消息，表示执行插入操作失败：消息2627，级别14，状态1，第1行违反了PRIMARYKEY约束'PK_SC'。不能在对象'dbo.SC'中插入重复键。

AnIntroductiontoDatabaseSystem第21页，共38页。实验步骤(3)输入以下添加记录的SQL语句，并执行：INSERTINTOSC(TeachTaskID,Grade) VALUES('(2009-2010-1)-140173-04001',90)消息窗口中显示以下消息，表示执行插入操作失败：消息515，级别16，状态2，第1行不能将值NULL插入列'Sno'，表'ST.dbo.SC'；列不允许有Null值。INSERT失败。AnIntroductiontoDatabaseSystem第22页，共38页。实验步骤3.为SC表添加FOREIGNKEY约束，并指定当修改被参照表StudentInfo的元组造成与SC不一致时，采用级联修改策略，当删除被参照表StudentInfo的元组造成与SC不一致时，采用拒绝策略。并更新表中记录，体会外键约束的作用。(1)在查询窗口中输入添加外键约束的SQL语句，并执行： ALTERTABLESC ADDCONSTRAINTFK_SnoFOREIGNKEY(Sno) REFERENCESStudentInfo(Sno) ONDELETENOACTION ONUPDATECASCADEAnIntroductiontoDatabaseSystem第23页，共38页。实验步骤(2)输入以下为SC表添加记录的SQL语句，并执行： INSERTINTOSCVALUES('201001903028','(2009-2010-1)-140173-04001',85)消息窗口中显示以下信息，表示插入操作失败，因为StudentInfo表中不存在学号“201001903028”： 消息547，级别16，状态0，第1行 INSERT语句与FOREIGNKEY约束"FK_Sno"冲突。该冲突发生于数据库"new"，表"dbo.StudentInfo",column'Sno'。

AnIntroductiontoDatabaseSystem第24页，共38页。实验步骤(3)输入以下修改StudentInfo表中记录的SQL语句，并执行： UPDATEStudentInfo SETSno='201001903028'WHERESno ='201001903029'

执行完毕后查看SC中记录，会发现原来Sno值为“201001903029”的记录已经被级联修改为“201001903028”。

AnIntroductiontoDatabaseSystem第25页，共38页。实验步骤(4）输入以下删除StudentInfo中记录的SQL语句，并执行： DELETEStudentInfo WHERESno='201001903028' 消息窗口中显示以下信息，表示删除操作失败： 消息547，级别16，状态0，第1行 DELETE语句与REFERENCE约束"FK_Sno"冲突。该冲突发生于数据库"ST"，表"dbo.SC",column'Sno'。AnIntroductiontoDatabaseSystem第26页，共38页。实验步骤4.为StudentInfo表的Sex列添加CHECK约束，保证性别只能取“男”或“女”，并添加记录，体会CHECK约束的作用。(1)在查询窗口中输入以下为Sex添加CHECK约束的SQL语句，并执行： ALTERTABLEStudentInfo ADDCONSTRAINTCk_SexCHECK(SexIN('男','女'))AnIntroductiontoDatabaseSystem第27页，共38页。实验步骤(2)输入以下插入记录的SQL语句，并执行： INSERTINTOStudentInfo VALUES('201011801017','王鹏飞','家','1991- 01-05','服装系','服装设计与工程(专科)',3)消息窗口中会显示以下信息，表示插入操作失败：消息547，级别16，状态0，第1行INSERT语句与CHECK约束"Ck_Sex"冲突。该冲突发生于数据库"ST"，表"dbo.StudentInfo",column'Sex'。AnIntroductiontoDatabaseSystem第28页，共38页。实验步骤5.为StudentInfo表的Depart列添加DEFAULT约束，设置默认值为“计算机系”。并添加记录，体会DEFAULT约束的作用。(1)在查询窗口中输入以下为Depart添加DEFAULT约束的语句，并执行： ALTERTABLEStudentInfo ADDCONSTRAINTDK_DepartDEFAULT‘计算 机系'forDepartAnIntroductiontoDatabaseSystem第29页，共38页。实验步骤(2)输入以下插入记录的SQL语句，并执行：INSERTINTOStudentInfo(Sno,Sname,Sex,Birthday,Major,LengSch)values('201011801017','王鹏飞','男','1991-01-05','服装设计与工程(专科)',3)(3)查看StudentInfo新添加记录值为： ('201011801017','王鹏飞','男','1991-01-05','计算机系','服装设计与工程(专科)',3)AnIntroductiontoDatabaseSystem第30页，共38页。实验步骤6.为StudentInfo表中的Sname列设置NOTNULL约束，添加记录，体会非空约束的作用。(1)在查询窗口中输入以下更新数据列属性的SQL语句，并执行：ALTERTABLEStudentInfoALTERCOLUMNSnamevarchar(20)NOTNULLAnIntroductiontoDatabaseSystem第31页，共38页。实验步骤(2)输入以下插入记录的SQL语句，并执行：INSERTINTOStudentInfovalues('201011801018',NULL,'女','1991-09-09','服装系','服装设计与工程(1＋升本)',4)消息窗口中会显示以下信息，表示插入操作失败：消息515，级别16，状态2，第1行不能将值NULL插入列'Sname'，表'ST.dbo.StudentInfo'；列不允许有Null值。INSERT失败。AnIntroductiontoDatabaseSystem第32页，共38页。实验步骤7.删除以上建立的约束，体会约束之间的依赖关系。(1)在查询窗口中输入删除StudentInfo表中的唯一性约束的SQL语句，并执行： ALTERTABLEStudentInfo DROPCONSTRAINTUN_Sno消息窗口中会显示以下信息，表示删除约束操作失败：消息3725，级别16，状态0，第