数据库完整性

2020/2/241数据库系统概论AnIntroductiontoDatabaseSystem第五章数据库完整性第五章数据库完整性什么是数据库的完整性1）数据的正确性和相容性2）防止不合语义的数据进入数据库。例:学生的年龄必须是整数，取值范围为14--29；学生的性别只能是男或女；学生的学号一定是唯一的；学生所在的系必须是学校开设的系；3）完整性：是否真实地反映现实世界完整性控制机制1.完整性约束条件定义机制2.完整性检查机制一般在INSERT,UPDATE,DELETE执行后开始检查，也可以在事务提交时检查。3.违约处理拒绝或级联执行等。第五章数据库完整性5.1实体完整性5.2参照完整性5.3用户定义的完整性5.4完整性约束命名子句5.6触发器5.7小结5.1实体完整性•5.1.1实体完整性定义–PRIMARYKEY定义,表级完整性约束或列级完整性约束•5.1.2实体完整性检查和违约处理每当用户程序对基本表插入一条记录或者对主码列进行更新操作时，按照实体完整性规则,系统将进行检查:•主码值是否唯一，如果不唯一则拒绝插入或修改•主码各属性是否为空，只要有一个为空就拒绝插入或修改方法：全表扫描或者索引查找（一般在主码上自动建立一个索引）5.2参照完整性•5.2.1参照完整性定义–创建表时,FOREIGNKEY定义•5.2.2参照完整性检查和违约处理–以SC和S为例,四种情况:•SC中增加一个元组,而在student中找不到相应的元组•修改SC中的一个元组……•从student删除一个元组……•修改student中的一个元组……5.2.2参照完整性检查和违约处理•当上述的不一致发生时，系统可以采用以下的策略：1拒绝（NoAction）执行不允许该操作执行。该策略一般设置为默认策略。2级联（Cascade）操作当删除或修改被参照表（Student）的一个元组造成了与参照表（SC）的不一致，则删除或修改参照表中的所有造成不一致的元组。例如：删除Student表中的元组，Sno的值为200215121，则从要SC表中级联删除SC.Sno=‘200215121’的所有元组。5.2.2参照完整性检查和违约处理•３设置为空值•当删除或修改被参照表的一个元组时造成了不一致，则将参照表中的所有造成不一致的元组对应属性设置为空值。例：5.2.2参照完整性检查和违约处理因此，对于参照完整性，除了应该定义外码，还应定义外码列是否允许空值。一般，当对参照表和被参照表的操作违反了参照完整性，系统选用默认策略，即拒绝执行。如果想让系统采用其他的策略则必须在创建表的时候显示地加以说明。显示说明参照完整性的违约处理•[ONDELETE{NOACTION|CASCADE|SETNULL|SETDEFAULT}]•[ONUPDATE{NOACTION|CASCADE|SETNULL|SETDEFAULT}]5.3用户定义的完整性•5.3.1属性上的约束条件的定义–列值非空（NotNull）;–列值唯一（Unique）;–检查列值是否满足布尔表达式（CHECK）；•5.3.2属性上的约束条件检查和违约处理当往表中插入元组或修改属性的值时，检查属性上的约束是否被满足，如果不满足则操作被拒绝执行。•5.3.3元组上的约束条件的定义–CHECK短语（同属性值限制相比，元组级的限制可以设置不同属性之间的取值的相互约束条件）•5.3.4元组上的约束条件检查和违约处理当往表中插入元组或修改属性的值时，检查属性上的约束是否被满足，如果不满足则操作被拒绝执行。5.3用户定义的完整性5.3用户定义的完整性5.6触发器触发器的基础知识触发器是一种特殊的存储过程，是SQLServer为保证数据完整性、确保系统正常工作而设置的一种高级技术。触发器在特定的表上定义，该表也称为触发器表。当触发器所保护的数据发生变化时，触发器就会自动运行，以保证数据的完整性与正确性。1.触发器有如下作用：1）可以对数据库进行级联修改。2）可以完成比CHECK更复杂的约束。与CHECK约束不同，在触发器中可以引用其他的表。3）根据改变前后表中不同的数据进行相应的操作。4）对于一个表上的不同的操作(INSERT、UPDATE或DELETE）可以采用不同的触发器，即使是对相同的语句也可以调用不同的触发器完成不同的操作。•在创建数据表时，已经定义了各字段的类型及其他约束条件，比如主键、外键关系等。这些作为预选过滤，在数据写入数据库之前就会被校验，只有当这些校验全都通过后，触发器才会执行。如果前面的这些校验没有全部通过，触发器就不会执行。因为触发器是在操作之后才执行。2.触发器具有以下特点：1）它是在操作有效后才执行的，即其他约束优先于触发器。2）它与存储过程的不同之处在于存储过程可以由用户直接调用，而触发器不能被直接调用，是由事件触发的。3）一个表可以有多个触发器，在不同表上同一种类型的触发器也可以有多个。4）触发器允许嵌套，最多为32层。5）触发器可以提高对表及表行有级联操作的应用程序的性能。触发器定义之后，其名称存储于sysobjects表中，定义语句存储在syscomments表中。定义触发器的Transact-SQL语句中不能出现以下语句，否则SQLServer将拒绝编译、存储这些语句相关的触发器。·所有的CREATE命令·所有的DROP命令·ALTERTABLE和ALTERDATABASE命令·TRUNCATETABLE命令（删除表中所有行）·GRANT和REVOKE命令·UPDATESTATISTICS命令·SELECTINTO命令等。（创建表将结果集填充）在创建触发器时，还要遵循以下原则：(1)触发器的定义必须是批处理的第一条命令。(2)触发器只能在表上定义。(3)触发器不能处理TEXT和IMAGE数据类型的大型二进制对象表列。(4)建议不要使用触发器返回一个结果集。3.触发器的类型在SQLServer2008中，根据激活触发器执行的T-SQL语句类型，可以把触发器分为两类：•DML触发器当数据库服务器中发生数据操作语言（DataManipulationLanguage）事件（Insert,Update,Delete）时执行的存储过程。•DDL触发器•响应数据定义语言（DataDefinitionLanguage）事件（Create，Alter，Drop）时执行的存储过程。DML触发器•DML触发器根据引起触发时间的不同可分为After触发器（后触发器）和InsteadOf触发器（替代触发器）•After触发器（后触发器）在记录已经改变完后（执行完insert,update或delete和处理完约束后）才被激活执行，主要用于记录变更后的处理或检查，一旦发现错误，也可以用RollbackTransaction语句来回滚本次操作。•InsteadOf触发器（替代触发器）用来取代原本要进行的操作，在记录变更之前发生的，不执行原来SQL语句里的操作，而是代替insert,update,delete语句去执行触发器本身所定义的操作。DML触发器•DML触发器与表和视图是不能分开的，触发器定义在表和视图中，当表或视图中执行insert,update,delete操作时触发器被触发并自动执行。•当表或视图被删除时与它关联的触发器也一同被删除。•一个表或视图可以定义多个After触发器，一个表或视图只可以定义一个Instead触发器。创建DML触发器的语法格式为:CREATETRIGGER触发器名ON表名或视图名{[FOR|AFTER]|[INSTEADOF]}{[DELETE][,][INSERT][,][UPDATE]}ASSQL语句[．．．n]注：不能在视图或临时表上建立触发器，但是在触发器定义中可以引用视图或临时表。当触发器引用视图或临时表时，产生两个特殊的表：deleted表和inserted表。这两个表的结构总与激活触发器的表的结构相同，触发器执行完成后，与该触发器相关的这两个临时表也会被自动删除。用户可以用SELECT语句查询临时表的内容，但不能对它们进行修改。可以用于触发器的条件测试。例：对stju库中s表的DELETE操作定义触发器。USEstjuGOIFEXISTS(SELECTnameFROMsysobjectsWHEREname='reader_d'ANDtype='TR')DROPTRIGGERreader_dGOCREATETRIGGERreader_dONsFORDELETEASPRINT'数据被删除！'GOINSERT触发器和DELETE触发器当向表中插入数据时，所有数据约束都通过之后，INSERT触发器就会执行。新的记录不但加到触发器表中，而且还会有副本加入inserted表中。同样，DELETE触发器会将删除的内容保存在deleted表中。INSERTED表与DELETED表一样，它们的记录是可读的，可以进行比较，以便确认这些数据是否正确。UPDATE触发器利用UPDATE修改一条记录时，相当于删除一条记录然后再增加一条新记录。所以UPDATE操作使用inserted和deleted两个表。当使用UPDATE操作时，触发器表中原来的记录被移到deleted表中，修改过的记录插入到inserted表中，触发器可以检查这两个表，以便确定应执行什么样的操作。例1•例：创建触发器“T_学生删除”，从“学生”表中删除数据时，相应地从“成绩”表中删除数据。CreatetriggerT_学生删除On学生AfterdeleteAsDelete成绩Where学生编号=（select学生编号fromdeleted）例2•例：创建触发器“T_教师添加”，向“辅导员”表中添加数据时，相应地向“教师”表中添加数据。CreatetriggerT_教师添加On辅导员AfterinsertAsInsert教师（编号，姓名，出生年月）Select编号，姓名，出生年月frominserted例3•例：创建触发器“T_教师修改”，向“辅导员”表中修改“姓名”列时，相应地修改“教师”表中的对应数据。CreatetriggerT_教师修改On辅导员AfterupdateAsIfupdate(姓名)BeginUpdate教师Set姓名=（select姓名frominserted）Where编号=（select编号fromdeleted）End•局部变量局部变量是用户自定义的变量。使用范围是定义它的批、存储过程或触发器。局部变量前面通常加上@标记。–DECLARE定义局部变量，并指明此变量的数据类型–SET或SELECT命令对其赋值。局部变量的数据类型可以是用户自定义的数据类型，也可以是系统数据类型，但不能将其定义为TEXT或IMAGE数据类型。–定义局部变量的语法如下：DECLARE@local_variabledata_type[,@local_variabledata_type]…DECLARE命令可以定义多个局部变量，之间用逗号分隔。局部变量与全局变量用SELECT为局部变量赋值的语法如下：SELECT@variable_name=expressionselectstatement[，@variable_name=expressionselectstatement][FROMlistoftables][WHEREexpression][GROUPBY..．][HAVING..．][ORDERBY]说明：(1)SELECT命令可以将一个表达式的值赋给一个局部变量，也可以将一个SELECT查询的结果赋给一个局部变量。(2)SELECT命令通常返回一个值给局部变量。当返回多个值，则变量的值为最后一个返回值。【例4】多个返回值的赋值。–DECLARE@varlvarchar(8)–SELECT@varl='学生姓名'–SELECT@varl=sname–FROMs–Select@varlAS'学生姓名'执行结果为：学生姓名---------------------返回最后一名学生的姓名（注意顺序）【例5】DECLA