第五章完整性

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

2、义完整性约束条件的机制 提供完整性检查的方法提供完整性检查的方法 违约处理违约处理 完整性分类：完整性分类：实体完整性、参照完整性和用户定义完整性实体完整性、参照完整性和用户定义完整性 第五章第五章 数据库完整性数据库完整性 约束约束 约束：约束：就是一种强制性的规定。就是一种强制性的规定。 SQL Server的六种约束：的六种约束： not null 非空约束非空约束 check 检查约束检查约束 unique 唯一约束唯一约束 primary key 主码约束主码约束 foreign key 外码约束外码约束 default 默认约束默认约束 约束的建立：约束的建立： 在创建表的同时建立

3、在创建表的同时建立 在已有表上建立在已有表上建立 注意：注意：若表中已有数据，则建立若表中已有数据，则建立 约束时可能会失败。约束时可能会失败。 1、建立非空约束：、建立非空约束： alter table course alter column credit smallint not null 其中，必须给出列的类型。其中，必须给出列的类型。 2、建立唯一约束：、建立唯一约束： alter table course add constraint UQ_cname unique(cname) 其中，其中，constraint关键词对该约束进关键词对该约束进 行命名，即行命名，即UQ_cname是

4、对该约是对该约 束的束的约束名约束名。 注意：注意：对每个约束都进行命名是个对每个约束都进行命名是个 好习惯。好习惯。 第五章第五章 数据库完整性数据库完整性 check 约束约束 1、创建表时建立、创建表时建立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 age 100) 逻辑表达式逻辑表达式 问题：问题：此时此时a

5、ge列值可以是空吗？列值可以是空吗？ 第五章第五章 数据库完整性数据库完整性 check 约束（续）约束（续） 3、check的表达式可以包含多个属性。如规定的表达式可以包含多个属性。如规定CS系学生必须系学生必须 25岁以下：岁以下： alter table student add constraint c_cs_age check(sdept cs or sage 0 and range 100 create rule list_rule as list in (男男, 女女) 规则名规则名 定义规则的逻辑表达式。定义规则的逻辑表达式。 其中的局部变量引用通过其中的局部变量引用通过upda

6、te 或或 insert语句输入的值。语句输入的值。 第五章第五章 数据库完整性数据库完整性 规则（续）规则（续） create rule pattern_rule as value like _ _-%0-9 2、将规则绑定到表的列、将规则绑定到表的列 exec sp_bindrule range_rule, student.sage exec sp_bindrule range_rule, sc.grade 3、取消表列的规则绑定、取消表列的规则绑定 exec sp_unbindrule student.sage 4、删除规则、删除规则 drop rule range_rule 该列即受绑

7、定规则的约束该列即受绑定规则的约束 该列解除规则约束该列解除规则约束 已被绑定的规则会删除失败已被绑定的规则会删除失败 第五章第五章 数据库完整性数据库完整性 默认值默认值 默认值：默认值：作用与作用与default约束相同，用法类似于规则。约束相同，用法类似于规则。 1、默认值的创建、默认值的创建 create default def_sex as 男男 2、将默认值绑定到表的列、将默认值绑定到表的列 exec sp_bindefault def_sex, student.ssex 3、取消表列的默认绑定、取消表列的默认绑定 exec sp_unbindefault student.sdep

8、t 4、删除默认值、删除默认值 drop default def_sex 默认名默认名 此处为常量表达式此处为常量表达式 若该列已定义了若该列已定义了default约约 束，则该绑定会失败。束，则该绑定会失败。 第五章第五章 数据库完整性数据库完整性 完整性的违约处理完整性的违约处理 1、实体完整性违约处理、实体完整性违约处理 实体完整性是通过在表中定义主码来实现。当更新操作违反了实体完实体完整性是通过在表中定义主码来实现。当更新操作违反了实体完 整性，则该更新操作被拒绝执行。整性，则该更新操作被拒绝执行。 2、参照完整性违约处理、参照完整性违约处理 问题：问题： 在在SC表插入违反了参照完整

9、性的数据时，你认为怎么处理合理？表插入违反了参照完整性的数据时，你认为怎么处理合理？ 将将1号学生从号学生从Student表删除，若他已选课，你认为对表删除，若他已选课，你认为对SC表怎么处理合理？表怎么处理合理？ 将将1号学生的学号改为号学生的学号改为10号，你认为对号，你认为对SC表怎么处理合理？表怎么处理合理？ 将将1号课从号课从Course表删除，若表删除，若2号课的先行课为号课的先行课为1号，则怎么做合理？号，则怎么做合理？ 将将1号课编号改为号课编号改为10号，若号，若2号课的先行课为号课的先行课为1号，则怎么做合理？号，则怎么做合理？ 3、用户定义完整性违约，则、用户定义完整性违

10、约，则DBMS默认拒绝执行。默认拒绝执行。 第五章第五章 数据库完整性数据库完整性 参照完整性的违约处理参照完整性的违约处理 2、参照完整性违约处理、参照完整性违约处理 create table sc ( sno char(9), cno char(4), grade smallint, primary key(sno, cno), constraint fk_sno foreign key(sno) references student(sno) on delete cascade on update cascade, constraint fk_cno foreign key(cno) r

11、eferences 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 smallint, constraint fk_cpno foreign key(cpno) references course(cno) on delete set null on update cascade ) 注意：注意：在表自身上建立的外键违约处理在表自身上建立的外键违约处理 S

12、QL Server并不接受。并不接受。 问题：问题：难道没办法实现这一功能要求吗？难道没办法实现这一功能要求吗？ 若违约处理没明确给出，则默认为若违约处理没明确给出，则默认为no action。 第五章第五章 数据库完整性数据库完整性 触发器触发器 触发器：触发器：是用户定义在关系表上的一类由事件驱动的特殊过程。是用户定义在关系表上的一类由事件驱动的特殊过程。 常用事件：常用事件：insert、delete、update（即数据更新操作）（即数据更新操作） 定义触发器的一般格式：定义触发器的一般格式： create trigger on table_name | view_name for |

13、 after | instead of insert , delete , update as begin T-SQL statement end 例如：例如： create trigger insert_student on student for insert as begin print Hi trigger! end 问题：问题： 这段程序什么时候被执行？这段程序什么时候被执行？ 若将事件若将事件insert写成写成delete或或update， 或它们的组合会怎样？或它们的组合会怎样？ 触发器有什么用？触发器有什么用？ 第五章第五章 数据库完整性数据库完整性 触发器（续）触发器（续）

14、 after：在触发事件的在触发事件的SQL语句的所有操作（包括各种约束检查）都已语句的所有操作（包括各种约束检查）都已成功执成功执 行后行后触发器被触发。触发器被触发。 若使用若使用for关键词，则默认为关键词，则默认为after。 只能定义在表上只能定义在表上 可为表的同一操作定义多个触发器可为表的同一操作定义多个触发器 instead of：不执行其所定义的更新操作，而仅执行触发器本身。不执行其所定义的更新操作，而仅执行触发器本身。 可定义在表和视图上可定义在表和视图上 对同一触发操作只能定义一个该种触发器对同一触发操作只能定义一个该种触发器 alter trigger 命令用于修改触发

15、器正文，格式与命令用于修改触发器正文，格式与create trigger完全一致。完全一致。 drop trigger trigger_name 删除触发器。删除触发器。 exec sp_rename old_name, new_name 更改触发器名称。更改触发器名称。 exec sp_helptrigger table_name 查看表中的触发器信息。查看表中的触发器信息。 exec sp_helptext trigger_name 查看触发器定义信息。查看触发器定义信息。 第五章第五章 数据库完整性数据库完整性 触发器（续）触发器（续） disable trigger on 禁用触发器禁

16、用触发器 enable trigger on 启用触发器启用触发器 问题：问题：当同一个表上对同一更新操作定义了多个触发器，它们被激活时的执当同一个表上对同一更新操作定义了多个触发器，它们被激活时的执 行顺序是什么？行顺序是什么？ exec sp_settriggerorder triggername, value, type 其中，第其中，第2个参数可以是个参数可以是first、last、none字符串；第字符串；第3个参数可以是个参数可以是insert、 delete、update字符串。该存储过程指定要对表执行的第一个和最后一个字符串。该存储过程指定要对表执行的第一个和最后一个 afte

17、r触发器。对于一个表，只能为每个更新操作指定一个第一个和最后触发器。对于一个表，只能为每个更新操作指定一个第一个和最后 一个一个after触发器。如果在同一个表上还有其他该操作的触发器。如果在同一个表上还有其他该操作的after 触发器，这触发器，这 些触发器将随机执行。些触发器将随机执行。 第五章第五章 数据库完整性数据库完整性 触发器的递归触发触发器的递归触发 create table tmpT ( id int primary key identity, num int) create trigger ins_tmpT on tmpT for insert as begin insert

18、 into tmpT(num) values(3) end insert into tmpT(num) values(1) select * from tmpT 问题：问题：查询结果是什么？查询结果是什么？ 在表中创建标识列，用于标识唯一的一在表中创建标识列，用于标识唯一的一 行。默认初值行。默认初值1，步长，步长1。 查看和更改数据库选项。查看和更改数据库选项。 sp_dboption与与sp_configure 其中，如果其中，如果recursive triggers选项设为选项设为 true，则将启用触发器的递归触发。，则将启用触发器的递归触发。 如果为如果为 false（默认值）将只禁

19、止直接（默认值）将只禁止直接 递归。若要禁用间接递归，使用递归。若要禁用间接递归，使用 sp_configure将将nested triggers服务器服务器 选项设置为选项设置为0。 注意：注意：是否进行递归触发取决于应用的是否进行递归触发取决于应用的 需要，但一定注意递归的出口问题。需要，但一定注意递归的出口问题。 第五章第五章 数据库完整性数据库完整性 这个功能怎么实现？这个功能怎么实现？ 要求：要求：将每个转系学生的原所在系信息记录到将每个转系学生的原所在系信息记录到history_dept_info表中。该表表中。该表 需存储如下信息：学号、学生原所在系、学生转入系、转系时间。需存储

20、如下信息：学号、学生原所在系、学生转入系、转系时间。 分析：分析： 如何获取系统当前时间？如何获取系统当前时间？ 触发器的触发条件是什么？触发器的触发条件是什么？ 触发器中需要做什么？触发器中需要做什么？ 触发器中怎么能知道触发器中怎么能知道sdept属性修改前和修改后的值得呢？属性修改前和修改后的值得呢？ create table history_dept_info ( id int primary key identity, sno char(9), old_dept char(20), new_dept char(20), date datetime) 内置函数内置函数getdate()

21、即可获得系即可获得系 统当前的日期和时间。统当前的日期和时间。 第五章第五章 数据库完整性数据库完整性 deleted表和表和inserted表表 在触发器执行过程中，在触发器执行过程中，SQL Server自动建立和管理这两个临时的虚拟表自动建立和管理这两个临时的虚拟表 这两个表结构与激发触发器的更新操作的对象表结构一致这两个表结构与激发触发器的更新操作的对象表结构一致 这两个表的值包含了在激发触发器的更新操作中插入和删除的所有记录这两个表的值包含了在激发触发器的更新操作中插入和删除的所有记录 这两个表可供用户查询这两个表可供用户查询 可用这两个表设置触发操作的条件可用这两个表设置触发操作的

22、条件 T-SQL语句语句 deleted表表 inserted表表 insert空空新的行新的行 update旧的行旧的行新的行新的行 delete删除的行删除的行空空 问题：问题：你理解了吗？你理解了吗？ 第五章第五章 数据库完整性数据库完整性 deleted表和表和inserted表（续）表（续） 观察触发器的运行结果。观察触发器的运行结果。 create trigger chg_stu on student for insert,delete,update as begin select * from deleted select * from inserted end insert i

23、nto student values(10,刘德华刘德华,男男,23,CS) update student set sage = 27 where sno = 10 delete from student where sno = 10 update student set sage = 25 create trigger instead_chg_course on course instead of insert,delete,update as begin select * from deleted select * from inserted end insert into course

24、values(12,Matlab,null,3) select * from course delete from course select * from course update course set credit = 6 where cno = 1 select * from course 第五章第五章 数据库完整性数据库完整性 转系历史数据的跟踪存储转系历史数据的跟踪存储 create trigger upd_sdept on student for update as begin if update(sdept) begin declare stu_sno char(9) decl

25、are 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_dept = (select sdept from inserted) insert into history_dept_info(sno, old_dept, new_dept, date) values(stu_sno, o_dept, n_dept, getdate() end end 触发器程序执行条件：当对触发器程序执行条件：

26、当对student 表表sdept属性进行属性进行update时。时。 定义变量定义变量 对变量赋值对变量赋值 将所需信息插入到将所需信息插入到history_dept_info表表 这里有什么问题吗？这里有什么问题吗？ 第五章第五章 数据库完整性数据库完整性 问题的解决办法问题的解决办法 上例触发器仅适用于对一个学生的转系修改操作。当全体上例触发器仅适用于对一个学生的转系修改操作。当全体MA系学生转入系学生转入CS 系，若执行系，若执行 update student set sdept = CS where sdept = IS 语句，该触语句，该触 发器会出错。发器会出错。 解决办法：解决

27、办法： 1、要求应用程序每次只执行对一个学生的转系更新操作，多个学生转系则、要求应用程序每次只执行对一个学生的转系更新操作，多个学生转系则 循环执行。但这会降低系统效率。循环执行。但这会降低系统效率。 2、修改触发器，使之能够处理批量转系更新操作修改触发器，使之能够处理批量转系更新操作。 你还记得在你还记得在CS系学生视图上插入数据所存在的问题吗？系学生视图上插入数据所存在的问题吗？ create view CS_S(sno, sname, ssex, sage) as select sno, sname, ssex, sage from student where sdept = cs 问题

28、：有办法实现在问题：有办法实现在CS_S视图上插入视图上插入 CS系学生数据吗？系学生数据吗？ 第五章第五章 数据库完整性数据库完整性 实现实现CS_S视图的数据插入视图的数据插入 在在CS_S视图上针对视图上针对insert操作定义替代触发器。操作定义替代触发器。 create trigger instead_ins on cs_s instead of insert as begin declare in_sno char(9), in_sname char(20), in_ssex char(2) declare in_sage smallint select in_sno = sno,

29、 in_sname = sname, in_ssex = ssex, in_sage = sage from inserted insert into student(sno, sname, ssex, sage, sdept) values(in_sno, in_sname, in_ssex, in_sage, CS) end 问题：问题：你还记得你还记得Course关系上的违约处理问题吗？关系上的违约处理问题吗？ 第五章第五章 数据库完整性数据库完整性 实现实现Course表的违约处理表的违约处理 1、在在Course表上针对表上针对delete操作定义替代触发器操作定义替代触发器 create trigger del_course on course instead of delete as begin declare del_cno char(4) set del_cno = (select cno from deleted) update course set cpno = null where cpno = del_cno delete from course where cno = del_cno end 第