数据库管理与开发项目教程---教案---12项目七：保护数据完整性.docx

项目七保证数据完整性任务1实现域完整性任务2实现实体完整性任务3实现参照完整性实训十一实现sale数据库完整性复习：1、清写出使用T-SQL语句创建索引的语法。2、索引的分类都包括哪些？3、请写出使用1X1索引查询student表的语句？4、请对索引1X1写出显示索引、重命名为1X2、删除索引1X2语句？5、请写出使用T-SQL语句创建视图的语法。答案：1、CREATE UNIQUE nonclustered nonclusteredINDEX索引名ON表名(字段)2、聚集索引、非聚集索引、唯一索引、符合索引3、select * from studentwith(indcx(IXl)4、EXEC sp heIpindex StudentEXEC sp_rename student. 1X1, 1X2DROP INDEX student. 1X25、CREATE VIEW v_StudentASSELECT语句任务1实现域完整性数据完整性就是用于保证数据库中的数据在逻辑上的一致性、正确性和可靠性。强制数据完 整性可确保数据库中的数据质量。数据完整性一般包括3种类型：域完整性、实体完整性、参照完整性。域完整性又称为列完整性，指给定列输入的有效性，即保证指定列的数据具有正确的数据类 型、格式和有效的数据范围。实现域完整性可通过定义相应的CHECK约束、默认值约束、默 认值对象、规则对象等方法来实现，另外，通过为表的列定义数据类型和NOT NULL也可以 实现域完整性。表完整性按需求在XS数据库完成以下实体完整性相关的操作。1. 通过修改XSDA表，在学号(如果该字段上己经有约束，那么先把约束删除)字段上创 建 PRIMARY KEY 约束。2. 在book表的“书名”字段上创建UNIQUE约束。参照完整性设“course”表为主表“stucou”表为从表通过修改表定义两个表之间的参照关系答案：域完整性1. 使用CREATE语句创建表book (书号char (6)、书名char (20)、类型char (20) 价 格int)字段。给“价格”字段定义一个名为max_price的check约束，使得价格不超过 200 oUSE XSCREATE table book(书号 char (6),类型 char (20),价格 int CONSTRAINT max price CHECK (价格 =200)GO2. 定义一个名为type_rule的规则，并绑定到book表的“类型”字段，使得“类型” 字段只接受该规则中列出的值(计算机类、科普类、文学类、自然科学)。USE XSGO此处加GO是因为CREATE RULE必须是批查询中的第一条语句CREATE RULE type_rule AS 类型IN (，计算机、，科普类，文学类，,自然科学，)GOUSE XSEXEC sp_bindrule type_rulebook.类型GO3. 解除第2题中的规则绑定关系。USE XSEXEC sp_unbindrule book.类型GO4. 删除第2题中的规则。USE XSDROP RULE type_ruleGO5. 修改book表，为“类型”字段设置-个默认值约束，约束名为BookType,默认值为 “NEW BOOK”。USE XSALTER TABLE bookADD CONSTRAINT BookType DEFAULT NEW BOOK FOR 类型GO6. 从book表中删除默认值约束BookTypc。USE XSALTER TABLE bookDROP CONSTRAINT BookTypeGO7. 定义默认值对象Day,取值为geldateO。USE XSGOCREATE DEFAULT Day AS getdate () 1-该语句必须是批查询中的第一条语句GO8. 修改XSDA表，向学生表中添加字段“入学时间”。USE XSGOALTER TABLE XSDAADD 入学时间 datetime nullGO9. 将默认值对象Day绑定到XSDA表的“入学时间”字段。USE XSGOEXEC sp_bindefault DayXSDA.入学时间GO1().定义一个用户定义数据类型：Today。USE XSGOEXEC spaddtype Today,datetimeNULLGO11. 将默认值对象Day绑定到用户自定义数据类型ToDayoUSE XSGOEXEC sp_bindefault Day, TodayGO12. 修改book表,向book表中添加字段“购买日期”，定义类型为ToDay。ALTER TABLE bookADD购书时间Today nullGO13. 解除默认值对象Day与XSDA表入学时间”、自定义数据类型Today的绑定关系。EXEC sp_unbindefault XSDA.入学时间EXEC sp_unbindefault TodayGo14. 删除默认值对象Day。DROP DEFAULT Day15. 阐述数据完整性的作用、分类，以及每类完整性都由哪些约束实现。数据完整性就是用于保证数据库中的数据在逻辑上的一致性、正确性和可靠性。强制数据完整性可确保数据库中的数据质量。数据完整性一般包括3种类型：域完整性、实体完整性、 参照完整性。实现域完整性可以通过定义CHECK约束、规则、默认值约束、默认值对象等。实体完整性可以通过索引、PRIMARY KEY约束、UNIQUE约束或IDENTITY属性来实现。 参照完整性是对两个相关联的表（主表与从表）进行数据插入和删除时，保证它们之间 数据的一致性。可以使用从表FOREIGN KEY定义从表的外键，主表的主键或唯一键 PRIMARY KEY或UNIQUE约束（不允许为空），可实现主表与从表之间的参照完整性。定 义表间参照关系应先定义主表主键约束（或唯一键约束），再对从表定义外键约束。表完整性1. 通过修改XSDA表，在学号（如果该字段上己经有约束，那么先把约束删除）字段上创 建 PRIMARY KEY 约束。ALTER TABLE XSDAADD CONSTRAINT XSDA_pk PRIMARY KEY CLUSTERED （学号）GO一删除主键索引要与建立主键索引的方法匹配，如果用SSMS建立只能在SSMS中删除主键：-如果 是用T-SQL建立的主键约束，则可以用T-SQL和SSMS两种方法删除主键约束ALTER TABLE XSDADROP CONSTRAINT XSDA_pkGO2. 在book表的“书名字段上创在UNIQUE约束。USE XSALTER TABLE bookADD CONSTRAINT book_uk UNIQUE NONCLUSTERED （书号）GO参照完整性例如，KCXX表中每门课程的学分应在010之间，为了对学分这一数据项输入的数据范围进 行限制，可以在定义KCXX表结构的同时通过定义学分的CHECK约束来实现。CHECK约束添加check约束语法ALTER TABLE table_name WITH CHECK | WITH NOCHECKADD CONSTRAINT check_name CHECK (logical_expression)说明：关键字ADD CONSTRAINT表示在己经定义的table_name表中增加一个约束定义，约束 名由 check_name 指定，约束表达式为 logical_expression。WITH CHECK 选项表示 CHECK 约束同时作用于己有数据和新数据；当省略该选项，取默认设置时，也表示CHECK约束同时 作用于2有数据和新数据；WITH NOCIIECK选项表示CHECK约束仅作用于新数据，对2有数 据不强制约束检查。【例7. 13USE XkGOALTER TABLE StudentADDCONSTRAINTCK_StuNoCHECK(StuNolike0-9 0-90-90-90-90-90-90-9,)GO删除CHECK约束语法格式：ALTER TABLE tablenameDROP CONSTRAINT check_name说明：在table_name指定的表中，删除名为check_name的约束。一【例7. 14USE XkGOALTER TABLE StudentDROP CONSTRAINT CK_StuNoGO创建表时创建CKECK约束语法格式：CREATE TABLE table_name/*指定表名(column_name datatype NOT NULL | NULL/*定义列名、数据类型、是否空值CONSTRAINT check_name CHECK (logical_expression)，n) /*定义 CHECK 约束 说明:关键字CHECK表示定义CHECK约束,其后的logical_expression是逻辑表达式,称 为CHECK约束表达式。【例8-1在XS数据库中创建学生信息表XSXX并定义CHECK约束。USE XSCREATE TABLE XSXX(学号 char (6),姓名 char(8),性别char(2) CHECK (性别IN C男，女), 定义CHECK约束入学口期datetime）GO默认值DEFAULT约束添加default约束语法格式1：ALTER TABLE table_name/*指定表名ADD CONSTRAINT default_name DEFAULT constraint_expression FOR column_name ,n/*对己存在的列添加默认值约束说明：此格式是对表中己存在的列添加默认值约束。语法格式2：ALTER TABLE table_name/*指定表名ADD column name datatype NOT NULL | NULL/*为增加的新列定义列名、数据类型、是否空值CONSTRAINT default_name DEFAULT constraint_expression，n /*定义默认值约束 说明：此格式是对表增加新列时定义默认值约束。一【例7. 18USE XkGOALTER TABLE StuCouADD CONSTRAINT DF_StuCouState DEFAULT （报名）FOR StateGOALTER TABLE StuCouADD birthday datetime null CONSTRAINT DF_birthday DEFAULT （2000/1/1）GO删除default约束ALTER TABLE StuCoudrop constraint DF_birthdayALTER TABLE StuCoudrop column birthdayGO创建表时，创建default约束语法格式：CREATE TABLE table_name/*指定表名（column name datatype NOT NULL | NULL /*定义列名、数据类型、是否空值CONSTRAINT default name fDEFAULT constraint expression，n）/*定义默认值约束说明：table_namo为创建的表名，column_name为列名，datatype为对应列的数据类型； DEFAULT关键字表示其后的constraint_cxprcssion表达式为默认值约束表达式，此表达式 只能是常量、系统函数或NULL。对于timestamp或带IDENTITY属性的字段不能定义默认值 约束。参数n表示可定义多个数据字段。【例8-10在创建表时定义一个字段的默认值约束。USE XSCREATE TABLE XSDA1(学号 char (6) NOT NULL,姓名 char (6) NOT NULL,专业名 char(10) NULL,性别 char (2) NOT NULL DEFAULT , 男)GO默认值对象定义默认值对象默认值对象的定义只能通过T-SQL语句实现。语法格式：CREATE DEFAULT defaultAS constant_expression【例7. 22USE XkGO一创建默认值CREATE DEFAULT UnsureDefault AS 待定GO绑定默认值到Course表的Teacher列EXEC sp_bindefault UnsureDefault,5 Course. Teacher5GO删除默认值对象语法格式：DROP DEFAULT defaultl，.nJ【例7. 23】USE XkGO一首先解除默认值与Teacher列的绑定sp_unbindefau11 Course. TeacherGO一删除默认值DROP DEFAULT UnsureDefaultGO任务2实现实体完整性实体完整性又称为表的完整性，是用于保证数据表中每一个特定实体的记录都是唯一的。通 过索引、UNIQUE约束、PRIMARY KEY约束或IDENTITY属性可以实现数据的实体完整 性。例如，对于XSDA表，学号作为主键，每一个学生的学号都能唯一地标识该学生对应的行记录信息，那么在输入数据时，就不能有相同学号的行记录，通过对学号字段建立PRIMARY KEY约束可以实现XSDA表的实体完整性。PRIMARY KEY 约束添加主键ALTER TABLE table_nameADD CONSTRAINT constraint.name PRIMARY KEY (col_name,.)CLUSTERED | NONCLUSTERED说明：ADD CONSTRAINT :增力口约束PRIMARY KEY :主关键字，主键CLUSTERED :聚集，表示表中数据行的存放是按照主键值额顺序存放。一般主键为聚集 索引。 为Class表创建基于ClassNo列名为PK_Class的主键约束ALTER TABLE ClassADD CONSTRAINT PK_Class PRIMARY KEY (ClassNo)GO删除主键语法格式：ALTER TABLE table_nameDROP CONSTRAINT constraint_name,. .n-【例7. 5注意删除后要恢复USE XkGOALTER TABLE DepartmentDROP CONSTRAINT PKJ)epartmentGO 一恢复ALTER TABLE Department(DepartNo)(DepartNo)(DepartNo)(DepartNo)/*指定表名/*定义列名、数据类型、是否空值/*指定约束名/*定义约束类型/*定义约束的索引类型ADD CONSTRAINT PK_Department PRIMARY KEYGO创建表时直接添加主键语法格式：CREATE TABLE table_name(cohimn_name datatype NOT NULL| NULLCONSTRAINT constraint_namejPRIMARY KEYCLUSTERED | NONCLUSTERED【例8-19创建XSDA2表时对学号字段创建PRIMARY KEY约束。USE XSCREATE TABLE XSDA2(学号 char(6) NOT NULL CONSTRAINT xh_pk PRIMARY KEY, 姓名 char (8) NOT NULL,性别 char (2) NOT NULL,系名 char (10) NOT NULL,出生日期 smal 1 datetime NOT NULL,民族 char (4) NOT NULL)GOUNIQUE约束特点：(1) 允许一个表建立多个UNIQUE索引(2) UNIQUE约束允许被约束列值为空，但不允许表中受约束列空值多于一个。1. 添加unique约束语法格式：ALTER TABLE table_nameADD CONSTRAINT constraint_name UNIQUECLUSTERED | NONCLUSTERED(column,.nj)说明：各项参数的说明同上述的PRIMARY KEY约束。【例7. 12一在查询窗口中执行如下SQL语句：USE XkGOALTER TABLE CourseADD CONSTRAINT UN_CouName UNIQUE(CouName)GO2. 删除unique约束一在查询窗口中执行如下SQL语句删除唯一约束：USE XkGOALTER TABLE CourseDROP CONSTRAINT UN_CouNameGO3. 创建表时直接创建unique约束【例8-22在创建XSDA3表时对身份证号码字段定义UNIQUE约束。USE XSCREATE TABLE XSDA3(学号 char (6) NOT NULL CONSTRAINT xhl_pk PRIMARY KEY, 姓名 char (8) NOT NULL,性别 char (2) NOT NULL,身份证号码 char (18) CONSTRAINT sfzhm_uk UNIQUE,系名 char (10) NOT NULL,出生口期 smalldatetime NOT NULL,民族 char (4) NOT NULL)GO任务3实现参照完整性当增加、修改或删除数据表中的记录时，可以借助参照完整性来保证相关联表之间数据 的一致性。参照完整性可以保证主表中的数据与从表中数据的一致性。在SQL SERVER中， 参照完整性是通过定义外键与主键之间或外键与唯一键之间的对应关系来实现的。参照完整 性确保同一键值在所有表中一致。例如，对于XS数据库中的XSDA表中的每个学生的学号，在XSCJ表中都有相关的课程成 绩记录，将XSDA表作为主表，学号字段定义为主键，XSCJ表作为从表，表中的学号字段 定义为外键，从而建立主表和从表之间的联系实现参照完整性。XSDA表和XSCJ表的对应关系如表8-1、表8-2所示。表 8-1 XSDA 表学号（主键）姓名性别系名总学分201901王红女信息60A201902刘林男信息54表 8-2 XSCJ 表学号（外键）课程编号成绩-20190110481_ OA1OA110877zuiyui20289201901.20190120790-20190210492-20190210895-20190220293-20190220790如果定义了两个表之间的参照完整性，则要求：（1）从表不能引用不存在的键值。例如：对于XSCJ表的行记录中出现的学号必须是XSDA 表中己经存在的学号。（2）如果主表中的键值更改了，那么在整个数据库中，对从表中该键值的所有引用要进行 -致的更改。例如：如果XSDA表中的某-学号修改了，XSCJ表中所有对应学号也要进行相应的修改。（3）如果主表中没有关联的记录，则不能将记录添加到从表中。（4）如果要删除主表中的某一记录，应先删除从表中与该记录匹配的相关记录。外键FOREING KEY约束添加外键语法格式：ALTER TABLE table nameADI） CONSTRAINT constraint_nameFOREIGN KEY （column ，n）REFERENCES ref table （ref_column, n）ON DELETE CASCADE|ON UPDATE CASCADE说明：参数table_name指定被修改的从表名；column为从表中外键的列名，当外键由多列 组合时列之间用逗号分隔；rentable为主表的表名；ref_column为主表中主键的列名，当 主键由多列组合时，列名之间用逗号分隔：n表示可指定多列。【例7. 6(注意:必须先完成例7. 1)USE XkGO/*StuCou表StuNo列要参照Student表StuNo列，外键约束名为StuNo_FK */ALTER TABLE StuCouADD CONSTRAINT StuNo FK FOREIGN KEY(StuNo)REFERENCES Student (StuNo)GO表示如果要更改或删除主表中的这些行，应先取消外键与主键值之间的联系。【例7. 8USE XkGO/*StuCou表StuNo列要参照Student表StuN。列，外键约束名为StuNo_ FK */ALTER TABLE StuCouADD CONSTRAINT StuNo. FK FOREIGN KEY (StuNo)REFERENCES Student (StuNo) ON DELETE CASCADEGO删除级联表示删除主表中StuN。行时，如果该学生己选择课程，则同时删除相应的数据行。 ON UPDATE CASCADE 是更新级联。1. 删除外键 一【例7. 7USE XkGOALTER TABLE StuCouDROP CONSTRAINT StuNo_FKGO创建表示创建外键语法格式：CREATE TABLE table_name/*指定表名(column name datatype CONSTRAINT constraintname FOREIGN KEYREFERENCES rentable (ref.column) ON DELETE CASCADE|ON UPDATE CASCADE)说明：参数tab 1 e_name为所建从表的表名；column_name为定义的字段名；字段类型由参 数datatype指定；关键字FOREIGN KEY指明该字段为外键，并且该外键与参数ref table 指定的主表中的主键对应，主表中主键字段由参数ref_column指定。ON DELETE CASCADE 表示级联删除，当在主键表中删除外键引用的主键记录时，为防止孤立外键的产生，将同时 删除外键表中引用它的外键记录；ON UPDATE CASCADE表示级联更新，当在主键表中更新外 键引用的主键记录时，外键表中引用它的外键记录也一起被更新。【例8-25在XS数据库中创建主表XSDA4,定义XSDA4.学号为主键,然后创建从表XSCJ4, 定义XSCJ4.学号为外键。一定义主键USE XSCREATE TABLE XSDA4(学号 char(6) NOT NULL CONSTRAINT xh_pk PRIMARY KEY,姓名 char (8) NOT NULL,性别 chAR(2) NOT NULL ,系名 char (10) NOT NULL,出生日期 smalldatetime NOT NULL,民族 char (4) NOT NULL,总学分 tinyint NULL,备注 text NULL)GO一定义外键CREATE TABLE XSCJ4(学号 char (6) NOT NULL FOREIGN KEY REFERENCES XSDA4 (学号)ON UPDATE CASCADI； 课程编号 char (3) NOT NULL,成绩 tinyint)实训八实现数据库完整性域完整性按需求在XS数据库完成以下域完整性相关的操作。1. 使用CREATE语句创建表book (书号char(6).书名char(20)、类型char(20).价 格int)字段。给“价格”字段定义一个名为max, price的check约束，使得价格不超过 200。2. 定义一个名为type_mle的规则，并绑定到book表的“类型”字段，使得“类型” 字段只接受该规则中列出的值(计算机类、科普类、文学类、自然科学)。3. 解除第2题中的规则绑定关系。4. 删除第2题中的规则。5. 修改bock表，为“类型”字段设置一个默认值约束，约束名为BockType,默认值为 “NEW BOOK”。6. 从book表中删除默认值约束BookTypea7. 定义默认值对象Day,取值为geldaleO。8. 修改XSDA表，向学生表中添加字段“入学时间”。9. 将默认值对象Day绑定到XSDA表的入学时间”字段。10. 定义一个用户定义数据类型：Todayo11. 将默认值对象Day绑定到用户自定义数据类型ToDay。12. 修改book表,向book表中添加字段“购买日期，定义类型为ToDay。13. 解除默认值对象Day与XSDA表入学时间”、自定义数据类型Today的绑定关系。14. 删除默认值对象Day。15. 阐述数据完整性的分类，以及每类完整性都由哪些约束实现。