第五章数据库的完整性

1、第五章第五章 数据库完整性数据库完整性数据库的安全性是尽可能保证非法用户不破坏数据的正确性。数据库的安全性是尽可能保证非法用户不破坏数据的正确性。数据库的完整性是尽可能保证合法用户不破坏数据的正确性。数据库的完整性是尽可能保证合法用户不破坏数据的正确性。数据库的完整性是为了防止数据库中存在不符合语义的数据。数据库的完整性是为了防止数据库中存在不符合语义的数据。问题问题：你还记得实体完整性约束和参照完整性约束是怎么回事吗？：你还记得实体完整性约束和参照完整性约束是怎么回事吗？为此，为此，DBMS需要完成：需要完成：提供定义完整性约束条件的机制提供定义完整性约束条件的机制提供完整性检查的方法提供完

2、整性检查的方法违约处理违约处理完整性分类完整性分类：实体完整性、参照完整性和用户定义完整性。：实体完整性、参照完整性和用户定义完整性。约束约束约束约束：就是一种强制性的规定。：就是一种强制性的规定。1、建立非空约束。、建立非空约束。alter table coursealter column credit smallint not null其中，必须给出列的类型。其中，必须给出列的类型。SQL Server的六种约束：的六种约束：not null 非空约束非空约束check 检查约束检查约束unique 唯一约束唯一约束primary key 主码约束主码约束foreign key 外码约束外

3、码约束default 默认约束默认约束约束的建立：约束的建立：在创建表的同时建立在创建表的同时建立在已有表上建立在已有表上建立注意注意：若表中已有数据，则建立：若表中已有数据，则建立 约束时可能会失败。约束时可能会失败。2、建立唯一约束。、建立唯一约束。alter table courseadd constraint UQ_cname unique(cname)其中：其中：constraint关键词即对该约束进行命关键词即对该约束进行命 名，即名，即UQ_cname是是约束名约束名。注意注意：对每个约束都进行命名是个好习惯：对每个约束都进行命名是个好习惯check约束约束1、创建表时建立创建表

4、时建立check约束。约束。 create table stu ( sno char(9) constraint PK_stu primary key, name char(8) not null, age smallint constraint c_age check(age 0 and age 0 and age 0 and Sage 100)check约束可用于建立表中属性取值上的某些约束关系。约束可用于建立表中属性取值上的某些约束关系。答答：可以是空。：可以是空。check 约束（续）约束（续）3、规定性别的取值只能是男或女。规定性别的取值只能是男或女。4、check的表达式可以包含多

5、个属性。如规定的表达式可以包含多个属性。如规定CS系学生必须系学生必须 25岁以下。岁以下。问题问题：此时：此时ssex列值可空吗？列值可空吗？alter table student add constraint c_ssex check(ssex in (男男, 女女)答答：可以是空。：可以是空。问题问题：若要求该列取值同时不可为空，则如何表达？：若要求该列取值同时不可为空，则如何表达？alter table studentadd constraint c_cs_age check(sdept cs or sage 0 and range 100规则规则规则规则：可用来限制属性取值的范围，实

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

7、ate rule pattern_ruleasvalue like _ _-%0-9规则（续）规则（续）该列即受绑定规则的约束该列即受绑定规则的约束该列解除规则约束该列解除规则约束若该规则已被绑定，则拒绝删除。若该规则已被绑定，则拒绝删除。2、将规则绑定到表的列将规则绑定到表的列 exec sp_bindrule range_rule, student.sage exec sp_bindrule range_rule, sc.grade3、取消表列的规则绑定取消表列的规则绑定 exec sp_unbindrule student.sage4、删除规则删除规则 drop rule range_r

8、ule1、默认值的创建默认值的创建create default def_sexas 男男默认值默认值默认值：默认值：作用与作用与default约束相同，用法类似于规则。约束相同，用法类似于规则。默认名默认名此处为常量表达式此处为常量表达式若该列已定义了若该列已定义了default约束，则该绑定会失败。约束，则该绑定会失败。2、将默认值绑定到表的列将默认值绑定到表的列 exec sp_bindefault def_sex, student.ssex3、取消表列的默认绑定取消表列的默认绑定 exec sp_unbindefault student.sdept4、删除默认值删除默认值 drop de

9、fault def_sex完整性的违约处理完整性的违约处理1、实体完整性违约处理：实体完整性是通过在表中定义主码来实现。当更新、实体完整性违约处理：实体完整性是通过在表中定义主码来实现。当更新 操作违反了实体完整性，则该更新操作被拒绝执行操作违反了实体完整性，则该更新操作被拒绝执行3、参照完整性违约处理、参照完整性违约处理问题问题： 在在SC表插入违反了参照完整性的数据时，你认为怎么处理合理？表插入违反了参照完整性的数据时，你认为怎么处理合理？ 将将1号学生从号学生从Student表删除，若他已选课，你认为对表删除，若他已选课，你认为对SC表怎么处理合理？表怎么处理合理？ 将将1号学生的学号改

10、为号学生的学号改为10号，你认为对号，你认为对SC表怎么处理合理？表怎么处理合理？ 将将1号课从号课从Course表删除，若表删除，若2号课的先行课为号课的先行课为1号，则怎么做合理？号，则怎么做合理？ 将将1号课编号改为号课编号改为10号，若号，若2号课的先行课为号课的先行课为1号，则怎么做合理？号，则怎么做合理？2、用户定义完整性违约处理：、用户定义完整性违约处理：DBMS默认采用拒绝执行。默认采用拒绝执行。参照完整性的违约处理参照完整性的违约处理create table sc( sno char(9), cno char(4), grade smallint, primary key(s

11、no, cno), constraint fk_sno foreign key(sno) references student(sno) on delete cascade on update cascade, constraint fk_cno foreign key(cno) references course(cno) on delete no action on update cascade )create table course( cno char(4) primary key, cname char(40) not null, cpno char(4), credit small

12、int, constraint fk_cpno foreign key(cpno) references course(cno) on delete set null on update cascade )注意注意：在表自身上建立的外键违约处理：在表自身上建立的外键违约处理 SQL Server并不接受。并不接受。 参照完整性违约处理有三种方式参照完整性违约处理有三种方式：拒绝执行：拒绝执行no action、级联、级联cascade和设和设置为空置为空set null。默认为拒绝执行。默认为拒绝执行。问题问题：难道没办法实现这一功能要求吗？：难道没办法实现这一功能要求吗？触发器触发器触发器触

13、发器：是由用户定义的，且在关系表上的一类由事件驱动的特殊过程。：是由用户定义的，且在关系表上的一类由事件驱动的特殊过程。问题问题： 这段程序什么时候被执行？这段程序什么时候被执行？ 若将事件若将事件insert写成写成delete或或update，或，或它们的组合会怎样？它们的组合会怎样？ 触发器有什么用？触发器有什么用？常用事件常用事件：insert、delete、update（即数据更新操作）（即数据更新操作）定义触发器的一般语法格式。定义触发器的一般语法格式。 create trigger on table_name | view_name for | after | instead o

14、f insert , delete , update as begin T-SQL statement end例如：例如： create trigger insert_student on student for insert as begin print Hi trigger! end触发器（续）触发器（续）after：在触发事件的在触发事件的SQL语句的所有操作（包括各种约束检查）都已语句的所有操作（包括各种约束检查）都已成功执成功执行后行后触发器被触发。触发器被触发。 若使用若使用for关键词，则默认为关键词，则默认为after。只能定义在表上只能定义在表上可为表的同一操作定义多个该类触

15、发器可为表的同一操作定义多个该类触发器instead of：不执行其所触发的更新操作，而仅执行触发器本身。不执行其所触发的更新操作，而仅执行触发器本身。可定义在表和视图上可定义在表和视图上对同一触发操作只能定义一个该类触发器对同一触发操作只能定义一个该类触发器alter trigger 命令用于修改触发器正文，格式与命令用于修改触发器正文，格式与create trigger完全一致。完全一致。drop trigger trigger_name 删除触发器。删除触发器。exec sp_rename old_name, new_name 更改触发器名称。更改触发器名称。exec sp_helptr

16、igger table_name 查看表中的触发器信息。查看表中的触发器信息。exec sp_helptext trigger_name 查看触发器定义信息。查看触发器定义信息。disable trigger on 禁用触发器禁用触发器enable trigger on 启用触发器启用触发器触发器执行顺序触发器执行顺序问题问题：当同一个表上对同一更新操作定义了多个触发器，它们被激活时的执：当同一个表上对同一更新操作定义了多个触发器，它们被激活时的执 行顺序是什么？行顺序是什么？exec sp_settriggerorder triggername, value, type 其中，第其中，第2个

17、参数可以是个参数可以是first、last、none字符串；字符串； 第第3个参数可以是个参数可以是insert、delete、update字符串。字符串。 该存储过程指定要对表执行的第一个和最后一个该存储过程指定要对表执行的第一个和最后一个 after触发器。对于一触发器。对于一个表，只能为每个更新操作指定一个第一个和最后一个个表，只能为每个更新操作指定一个第一个和最后一个after触发器。如果在触发器。如果在同一个表上还有其他该操作的同一个表上还有其他该操作的after 触发器，这些触发器将随机执行。触发器，这些触发器将随机执行。触发器的递归触发触发器的递归触发create table t

18、mpT ( id int primary key identity, num int)在表中创建标识列，用于标识唯一的一行在表中创建标识列，用于标识唯一的一行默认初值默认初值1，步长，步长1。查看和更改数据库选项。查看和更改数据库选项。sp_dboption与与sp_configure 其中，如果其中，如果recursive triggers选项设选项设为为 true，则将启用触发器的递归触发。，则将启用触发器的递归触发。如果为如果为 false（默认值）将只禁止直接递（默认值）将只禁止直接递归。归。 若要禁用间接递归，使用若要禁用间接递归，使用sp_configure将将nested tri

19、ggers服务器选项设置为服务器选项设置为0。或。或使用数据库属性进行设置。使用数据库属性进行设置。注意注意：是否进行递归触发取决于应用的需：是否进行递归触发取决于应用的需 要，但一定注意递归的出口问题。要，但一定注意递归的出口问题。create trigger ins_tmpTon tmpTfor insertas begininsert into tmpT(num) values(3)endinsert into tmpT(num) values(1)select * from tmpT问题问题：查询结果是什么？：查询结果是什么？这个功能怎么实现？这个功能怎么实现？ 要求要求：将每个转系学

20、生的原所在系信息记录到：将每个转系学生的原所在系信息记录到history_dept_info表中。该表中。该表需存储如下信息：学号、学生原所在系、学生转入系、转系时间。表需存储如下信息：学号、学生原所在系、学生转入系、转系时间。create table history_dept_info ( id int primary key identity, sno char(9), old_dept char(20), new_dept char(20), date datetime)内置函数内置函数getdate()即可获得系统当前的日期和时间。即可获得系统当前的日期和时间。分析：分析： 建立建立h

21、istory_dept_info表。表。 触发器的触发条件是什么？触发器中需要做什么？触发器的触发条件是什么？触发器中需要做什么？ 若将修改数据库的时间作为转系时间，则如何获取系统当前时间？若将修改数据库的时间作为转系时间，则如何获取系统当前时间？ 触发器中怎么能知道触发器中怎么能知道sdept属性修改前和修改后的值得呢？属性修改前和修改后的值得呢？触发条件触发条件：当对：当对Student表中的表中的Sdept属性属性 进行进行update时触发。时触发。触发器动作触发器动作：当：当Sdept属性修改前和修改后属性修改前和修改后 的值一起存入所建新表。的值一起存入所建新表。deleted表和

22、表和inserted表表在触发器执行过程中，在触发器执行过程中，SQL Server自动建立和管理这两个临时的虚拟表自动建立和管理这两个临时的虚拟表这两个表的结构与激发触发器的更新操作的对象表结构一致这两个表的结构与激发触发器的更新操作的对象表结构一致这两个表的值包含了在激发触发器的更新操作中插入和删除的所有记录这两个表的值包含了在激发触发器的更新操作中插入和删除的所有记录这两个表可供用户查询这两个表可供用户查询可用这两个表在可用这两个表在SQL命令与触发器之间传递数据命令与触发器之间传递数据T-SQL语句语句 deleted表表inserted表表insert空空新的行新的行update旧的

23、行旧的行新的行新的行delete删除的行删除的行空空问题问题：你理解该表的含义吗？：你理解该表的含义吗？deleted表和表和inserted表示例表示例观察触发器的运行结果。观察触发器的运行结果。create trigger chg_stuon studentfor insert,delete,updateas beginselect * from deletedselect * from insertedendcreate trigger instead_chg_courseon courseinstead of insert,delete,updateas beginselect * f

24、rom deletedselect * from insertedendinsert into student values(10,刘德华刘德华,男男,23,CS)update student set sage = 27 where sno = 10delete from student where sno = 10update student set sage = 25insert into course values(12,Matlab,null,3)select * from courseupdate course set credit = 6 where cno = 1select *

25、 from coursedelete from courseselect * from course转系历史数据的跟踪存储转系历史数据的跟踪存储create trigger upd_sdepton studentfor updateas beginif update(sdept)begindeclare stu_sno char(9)declare o_dept char(20)declare n_dept char(20)set stu_sno = (select sno from deleted)set o_dept = (select sdept from deleted)set n_d

26、ept = (select sdept from inserted)insert into history_dept_info(sno, old_dept, new_dept, date) values(stu_sno, o_dept, n_dept, getdate()endend也可视为触发条件：即对也可视为触发条件：即对student表表sdept属性属性update时。时。声明局部变量，其类型应与表属性定义的类型一致声明局部变量，其类型应与表属性定义的类型一致set为赋值语句为赋值语句将所需信息插入到将所需信息插入到history_dept_info表表问题的解决办法问题的解决办法 上

27、例触发器适用于一次仅处理一个学生的转系操作。当全体上例触发器适用于一次仅处理一个学生的转系操作。当全体MA系学生转系学生转入入CS系，若执行系，若执行 update student set sdept = CS where sdept = IS 语句，该触语句，该触发器会出错。发器会出错。解决办法解决办法：1、要求应用程序每次只执行对一个学生的转系更新操作，多个学生转系则循、要求应用程序每次只执行对一个学生的转系更新操作，多个学生转系则循环执行。环执行。2、修改触发器，使之能够处理批量转系更新操作。可采用游标方式实现。、修改触发器，使之能够处理批量转系更新操作。可采用游标方式实现。 显然，该做

28、法会降低系统效率。显然，该做法会降低系统效率。这不是个好主意，合格的程序员不能这这不是个好主意，合格的程序员不能这样想问题。样想问题。实现实现CS_S视图的数据插入视图的数据插入create trigger instead_inson cs_sinstead of insertas begindeclare in_sno char(9), in_sname char(20), in_ssex char(2) declare in_sage smallint select in_sno = sno, in_sname = sname, in_ssex = ssex, in_sage = sage

29、 from inserted insert into student(sno, sname, ssex, sage, sdept) values(in_sno, in_sname, in_ssex, in_sage, CS)endcreate view CS_S(sno, sname, ssex, sage)as select sno, sname, ssex, sage from student where sdept = cs问题问题：有办法实现在：有办法实现在CS_S视图上插入视图上插入CS系学生数据吗？系学生数据吗？问题问题：你还记得在：你还记得在CS系学生视图上系学生视图上 插入数据

30、所存在的问题吗？插入数据所存在的问题吗？办法办法：CS_S视图上针对视图上针对insert命令建立替代触发器，数据的插入由触发器实现命令建立替代触发器，数据的插入由触发器实现实现实现Course表的违约处理表的违约处理create trigger del_courseon courseinstead of deleteas begin declare del_cno char(4) set del_cno = (select cno from deleted) update course set cpno = null where cpno = del_cno delete from cour

31、se where cno = del_cnoend问题问题：你还记得在表自身上建立的外键违约处理：你还记得在表自身上建立的外键违约处理SQL Server并不接受吗？并不接受吗？ 例如在例如在Course表中删除表中删除1号课，若号课，若2号课的直接先修课为号课的直接先修课为1号，则将号，则将2号课的号课的直接先修课设置为空比较合理。问题是如何实现呢？直接先修课设置为空比较合理。问题是如何实现呢？办法办法：在：在Course表上定义替代触发器，上述工作由替代触发器完成。表上定义替代触发器，上述工作由替代触发器完成。显然，该触发器只适合于一次仅删除一门课程的显然，该触发器只适合于一次仅删除一门课程的delete命令。命令。实现实现Course表的违约处理（续）表的违约处理（续） 例如例如2号课的直接