第五章数据库完整性

第五章数据库完整性数据库的安全性是尽可能保证非法用户不破坏数据的正确性。数据库的完整性是尽可能保证合法用户不破坏数据的正确性。数据库的完整性是为了防止数据库中存在不符合语义的数据。问题：你还记得实体完整性约束和参照完整性约束是怎么回事吗？为此，DBMS需要完成：提供定义完整性约束条件的机制提供完整性检查的方法违约处理完整性分类：实体完整性、参照完整性和用户定义完整性。约束约束：就是一种强制性的规定。1、建立非空约束。altertablecoursealtercolumncreditsmallintnotnull其中，必须给出列的类型。SQLServer的六种约束：notnull非空约束check检查约束unique唯一约束primarykey主码约束foreignkey外码约束default默认约束约束的建立：在创建表的同时建立在已有表上建立注意：若表中已有数据，则建立约束时可能会失败。2、建立唯一约束。altertablecourseaddconstraintUQ_cnameunique(cname)其中：constraint关键词即对该约束进行命名，即UQ_cname是约束名。注意：对每个约束都进行命名是个好习惯check约束1、创建表时建立check约束。

createtablestu(snochar(9)constraintPK_stuprimarykey,namechar(8)notnull,agesmallintconstraintc_age

check(age>0andage<100)

)逻辑表达式问题：此时age列值可以是空吗？或createtablestu(snochar(9),namechar(8)notnull,agesmallint,constraintPK_stuprimarykey(sno),

constraintc_agecheck(age>0andage<100))2、在表中增加check约束

altertablestudentaddconstraintc_agecheck(Sage>0andSage<100)check约束可用于建立表中属性取值上的某些约束关系。答：可以是空。check约束（续）3、规定性别的取值只能是男或女。4、check的表达式可以包含多个属性。如规定CS系学生必须25岁以下。问题：此时ssex列值可空吗？altertablestudentaddconstraintc_ssexcheck(ssexin('男','女'))答：可以是空。问题：若要求该列取值同时不可为空，则如何表达？altertablestudentaddconstraintc_cs_agecheck(sdept<>'cs'orsage<=25)问题：你知道这个约束条件是怎么得到的吗？altertablestudentaddconstraintc_ssexcheck(ssexin('男','女')andssexisnotnull)default约束、删除约束default约束：可以对列设置默认值。如对性别列默认取值为男。1、创建表时建立default约束。

createtablestudent(snochar(9)primarykey,ssexchar(2)constraintc_ssex_defdefault'男')2、在表中增加default约束。

altertablestudentaddconstraintc_ssex_defdefault'男'forssex删除约束：altertable表名dropconstraint约束名如：altertablestudentdropconstraintc_ssex_def注意：表中的约束不能修改，只能删除后重建。问题：下列命名的执行结果是什么？

insertintostudent(sno,sname,ssex,sage,sdept)values('5','张飞',null,29,'cs')insertintostudent(sno,sname)values('6’,'张小飞')1、规则的创建

createrulerange_ruleas@range>0and@range<100规则规则：可用来限制属性取值的范围，实现强制数据的域完整性，作用类似于check约束。规则名定义规则的逻辑表达式。其中的局部变量值为通过update或insert语句输入的值。check约束可针对一个属性多次应用一个属性只能应用一个规则规则需要单独创建，也只需创建一次，以后可多次应用于多个表createrulelist_ruleas@listin('男','女',null)

createrulepattern_ruleas@valuelike'__-%[0-9]'规则（续）该列即受绑定规则的约束该列解除规则约束若该规则已被绑定，则拒绝删除。2、将规则绑定到表的列

execsp_bindrule'range_rule','student.sage'execsp_bindrule'range_rule','sc.grade'3、取消表列的规则绑定execsp_unbindrule'student.sage'4、删除规则droprulerange_rule1、默认值的创建createdefaultdef_sexas'男'默认值默认值：作用与default约束相同，用法类似于规则。默认名此处为常量表达式若该列已定义了default约束，则该绑定会失败。2、将默认值绑定到表的列

execsp_bindefault'def_sex','student.ssex'3、取消表列的默认绑定

execsp_unbindefault'student.sdept'4、删除默认值

dropdefaultdef_sex完整性的违约处理1、实体完整性违约处理：实体完整性是通过在表中定义主码来实现。当更新操作违反了实体完整性，则该更新操作被拒绝执行3、参照完整性违约处理问题：①在SC表插入违反了参照完整性的数据时，你认为怎么处理合理？②将1号学生从Student表删除，若他已选课，你认为对SC表怎么处理合理？③将1号学生的学号改为10号，你认为对SC表怎么处理合理？④将1号课从Course表删除，若2号课的先行课为1号，则怎么做合理？⑤将1号课编号改为10号，若2号课的先行课为1号，则怎么做合理？2、用户定义完整性违约处理：DBMS默认采用拒绝执行。参照完整性的违约处理createtablesc(snochar(9),cnochar(4),gradesmallint,primarykey(sno,cno),constraintfk_snoforeignkey(sno)referencesstudent(sno)

ondeletecascadeonupdatecascade,constraintfk_cnoforeignkey(cno)referencescourse(cno)

ondeletenoactiononupdatecascade)createtablecourse(cnochar(4)primarykey,cnamechar(40)notnull,cpnochar(4),creditsmallint,constraintfk_cpnoforeignkey(cpno)referencescourse(cno)

ondeletesetnullonupdatecascade)注意：在表自身上建立的外键违约处理SQLServer并不接受。参照完整性违约处理有三种方式：拒绝执行noaction、级联cascade和设置为空setnull。默认为拒绝执行。问题：难道没办法实现这一功能要求吗？触发器触发器：是由用户定义的，且在关系表上的一类由事件驱动的特殊过程。问题：①这段程序什么时候被执行？②若将事件insert写成delete或update，或它们的组合会怎样？③触发器有什么用？常用事件：insert、delete、update（即数据更新操作）定义触发器的一般语法格式。

createtrigger<trigger_name>on{table_name|view_name}{for|after|insteadof}{[insert][,][delete][,][update]}asbeginT-SQLstatementend例如：

createtriggerinsert_studentonstudentforinsertasbeginprint'Hitrigger!'end触发器（续）after：在触发事件的SQL语句的所有操作（包括各种约束检查）都已成功执行后触发器被触发。若使用for关键词，则默认为after。只能定义在表上可为表的同一操作定义多个该类触发器insteadof：不执行其所触发的更新操作，而仅执行触发器本身。可定义在表和视图上对同一触发操作只能定义一个该类触发器altertrigger

命令用于修改触发器正文，格式与createtrigger完全一致。droptriggertrigger_name

删除触发器。execsp_rename'old_name','new_name'更改触发器名称。execsp_helptrigger'table_name'查看表中的触发器信息。execsp_helptext'trigger_name'查看触发器定义信息。disabletrigger<triggername>

on<tablename>禁用触发器enabletrigger<triggername>on<tablename>启用触发器触发器执行顺序问题：当同一个表上对同一更新操作定义了多个触发器，它们被激活时的执行顺序是什么？execsp_settriggerorder'triggername','value','type'

其中，第2个参数可以是first、last、none字符串；第3个参数可以是insert、delete、update字符串。该存储过程指定要对表执行的第一个和最后一个after触发器。对于一个表，只能为每个更新操作指定一个第一个和最后一个after触发器。如果在同一个表上还有其他该操作的after触发器，这些触发器将随机执行。触发器的递归触发createtabletmpT(idintprimarykeyidentity,numint)在表中创建标识列，用于标识唯一的一行默认初值1，步长1。查看和更改数据库选项。sp_dboption与sp_configure其中，如果recursivetriggers选项设为true，则将启用触发器的递归触发。如果为false（默认值）将只禁止直接递归。若要禁用间接递归，使用sp_configure将nestedtriggers服务器选项设置为0。或使用数据库属性进行设置。注意：是否进行递归触发取决于应用的需要，但一定注意递归的出口问题。createtriggerins_tmpTontmpTforinsertasbegin insertintotmpT(num)values(3)endinsertintotmpT(num)values(1)select*fromtmpT问题：查询结果是什么？这个功能怎么实现？要求：将每个转系学生的原所在系信息记录到history_dept_info表中。该表需存储如下信息：学号、学生原所在系、学生转入系、转系时间。createtablehistory_dept_info(idintprimarykeyidentity,snochar(9),old_deptchar(20),new_deptchar(20),datedatetime)内置函数getdate()即可获得系统当前的日期和时间。分析：①建立history_dept_info表。②触发器的触发条件是什么？触发器中需要做什么？③若将修改数据库的时间作为转系时间，则如何获取系统当前时间？④触发器中怎么能知道sdept属性修改前和修改后的值得呢？触发条件：当对Student表中的Sdept属性进行update时触发。触发器动作：当Sdept属性修改前和修改后的值一起存入所建新表。deleted表和inserted表在触发器执行过程中，SQLServer自动建立和管理这两个临时的虚拟表这两个表的结构与激发触发器的更新操作的对象表结构一致这两个表的值包含了在激发触发器的更新操作中插入和删除的所有记录这两个表可供用户查询可用这两个表在SQL命令与触发器之间传递数据T-SQL语句deleted表inserted表insert空新的行update旧的行新的行delete删除的行空问题：你理解该表的含义吗？deleted表和inserted表示例观察触发器的运行结果。createtriggerchg_stuonstudentforinsert,delete,updateasbegin select*fromdeleted select*frominsertedendcreatetriggerinstead_chg_courseoncourseinsteadofinsert,delete,updateasbegin select*fromdeleted select*frominsertedendinsertintostudentvalues('10','刘德华','男',23,'CS')updatestudentsetsage=27wheresno='10'deletefromstudentwheresno='10'updatestudentsetsage=25insertintocoursevalues('12','Matlab',null,3)select*fromcourseupdatecoursesetcredit=6wherecno='1'select*fromcoursedeletefromcourseselect*fromcourse转系历史数据的跟踪存储createtriggerupd_sdeptonstudentforupdateasbegin ifupdate(sdept) begin declare@stu_snochar(9) declare@o_deptchar(20) declare@n_deptchar(20) set@stu_sno=(selectsnofromdeleted) set@o_dept=(selectsdeptfromdeleted) set@n_dept=(selectsdeptfrominserted) insertintohistory_dept_info(sno,old_dept,new_dept,date)values(@stu_sno,@o_dept,@n_dept,getdate()) endend也可视为触发条件：即对student表sdept属性update时。声明局部变量，其类型应与表属性定义的类型一致set为赋值语句将所需信息插入到history_dept_info表问题的解决办法

上例触发器适用于一次仅处理一个学生的转系操作。当全体MA系学生转入CS系，若执行updatestudentsetsdept='CS'wheresdept='IS'语句，该触发器会出错。解决办法：1、要求应用程序每次只执行对一个学生的转系更新操作，多个学生转系则循环执行。2、修改触发器，使之能够处理批量转系更新操作。可采用游标方式实现。显然，该做法会降低系统效率。这不是个好主意，合格的程序员不能这样想问题。实现CS_S视图的数据插入createtriggerinstead_insoncs_sinsteadofinsertasbegin declare@in_snochar(9),@in_snamechar(20),@in_ssexchar(2)declare@in_sagesmallintselect@in_sno=sno,@in_sname=sname,@in_ssex=ssex,@in_sage=sagefrominsertedinsertintostudent(sno,sname,ssex,sage,sdept)values(@in_sno,@in_sname,@in_ssex,@in_sage,'CS')endcreateviewCS_S(sno,sname,ssex,sage)asselectsno,sname,ssex,sagefromstudentwheresdept='cs'问题：有办法实现在CS_S视图上插入CS系学生数据吗？问题：你还记得在CS系学生视图上插入数据所存在的问题吗？办法：CS_S视图上针对insert命令建立替代触发器，数据的插入由触发器实现实现Course表的违约处理createtriggerdel_courseoncourseinsteadofdeleteasbegindeclare@del_cnochar(4)set@del_cno=(selectcnofromdeleted)updatecoursesetcpno=nullwherecpno=@del_cnodeletefromcoursewherecno=@del_cnoend问题：你还记得在表自身上建立的外键违约处理SQLServer并不接受吗？例如在Course表中删除1号课，若2号课的直接先修课为1号，则将2号课的直接先修课设置为空比较合理。问题是如何实现呢？办法：在Course表上定义替代触发器，上述工作由替代触发器完成。显然，该触发器只适合于一次仅删除一门课程的delete命令。实现Course表的违约处理（续）