数据库课件第五章

1、第第5章章 数据库完整性数据库完整性l5.1 实体完整性实体完整性l5.2 参照完整性参照完整性l5.3 用户自定义完整性用户自定义完整性l5.4 完整性约束命名子句完整性约束命名子句l5.6 触发器触发器l5.7 小结小结数据库完整性（续）数据库完整性（续）l什么是数据库的完整性什么是数据库的完整性 数据库的完整性是指数据的正确性和相容性，数据库的完整性是指数据的正确性和相容性，防止不合语义的数据进入数据库。防止不合语义的数据进入数据库。例例: 学生的年龄必须是整数，取值范围为学生的年龄必须是整数，取值范围为14-29； 学生的性别只能是男或女；学生的性别只能是男或女； 学生的学号一定是唯一

2、的；学生的学号一定是唯一的； 学生所在的系必须是学校开设的系；学生所在的系必须是学校开设的系； 数据库是否具备完整性关系到数据库系统能数据库是否具备完整性关系到数据库系统能否真实地反映现实世界，因此维护数据库的否真实地反映现实世界，因此维护数据库的完整性是非常重要的。完整性是非常重要的。数据库完整性（续）数据库完整性（续）l完整性控制机制完整性控制机制1.完整性约束条件定义机制完整性约束条件定义机制2.完整性检查机制完整性检查机制3.违约处理违约处理 数据库完整性（续）数据库完整性（续）l完整性控制机制完整性控制机制(续续)1.完整性约束条件定义机制完整性约束条件定义机制 完整性约束条件是数据

3、模型的一个重要组成完整性约束条件是数据模型的一个重要组成部分部分,它约束了数据库中数据的语义。它约束了数据库中数据的语义。 dbms应提供手段让用户根据现实世界的语应提供手段让用户根据现实世界的语义定义数据库的完整性约束条件，并把它们义定义数据库的完整性约束条件，并把它们作为模式的一部分存入数据库中。作为模式的一部分存入数据库中。 数据库完整性（续）数据库完整性（续）l完整性控制机制完整性控制机制(续续)2.完整性检查机制完整性检查机制 检查用户发出的操作请求是否违背了完整性检查用户发出的操作请求是否违背了完整性约束条件。约束条件。 一般在一般在insert、update、delete语句语句

4、执行后开始检查，也可在事务提交时检查。执行后开始检查，也可在事务提交时检查。 数据库完整性（续）数据库完整性（续）l完整性控制机制完整性控制机制(续续)3.违约处理违约处理 如果发现用户的操作请求使数据违背了完整如果发现用户的操作请求使数据违背了完整性约束条件，则采取一定的动作来保证数据性约束条件，则采取一定的动作来保证数据的完整性。的完整性。第第5章章 数据库完整性数据库完整性l5.1 实体完整性实体完整性l5.2 参照完整性参照完整性l5.3 用户自定义完整性用户自定义完整性l5.4 完整性约束命名子句完整性约束命名子句l5.6 触发器触发器l5.7 小结小结5.1 实体完整性实体完整性l

5、5.1.1 实体完整性定义实体完整性定义l5.1.2 实体完整性检查和违约处理实体完整性检查和违约处理5.1 实体完整性实体完整性l5.1.1 实体完整性定义实体完整性定义l5.1.2 实体完整性检查和违约处理实体完整性检查和违约处理5.1.1 实体完整性定义实体完整性定义l实体完整性在实体完整性在create table中用中用primary key定义定义l单属性构成的码有两种说明方法：单属性构成的码有两种说明方法： - 定义为列级约束条件定义为列级约束条件 - 定义为表级约束条件定义为表级约束条件l多个属性构成的码只有一种说明方法：多个属性构成的码只有一种说明方法： - 定义为表级约束条

6、件定义为表级约束条件例题例题例例1 将将student表中的表中的sno属性定义为码属性定义为码 create table student ( sno char(9) primary key, sname char(20) not null, ssex char(2), sage smallint, sdept char(20) ); 注：在列级定义主码注：在列级定义主码 例题例题或者：或者： create table student ( sno char(9), sname char(20) not null, ssex char(2), sage smallint, sdept char(

7、20), primary key (sno) ); 注：在表级定义主码注：在表级定义主码例题例题例例2 将将sc表中的表中的sno,cno属性组定义为码属性组定义为码 create table sc ( sno char(9) not null, cno char(4) not null, grade smallint, primary key (sno,cno) ); 注：只能在表级定义主码注：只能在表级定义主码5.1 实体完整性实体完整性l5.1.1 实体完整性定义实体完整性定义l5.1.2 实体完整性检查和违约处理实体完整性检查和违约处理5.1.2 实体完整性检查和违约处理实体完整性检查

8、和违约处理l用用primary key短语定义了关系的主码后，短语定义了关系的主码后，每当用户程序对基本表进行插入或更新操作时，每当用户程序对基本表进行插入或更新操作时，rdms将按照实体完整性规则自动进行检查。将按照实体完整性规则自动进行检查。 - 检查主码值是否唯一，如果不唯一则拒绝插入或修检查主码值是否唯一，如果不唯一则拒绝插入或修改改 - 检查主码的各个属性是否为空，只要有一个为空就检查主码的各个属性是否为空，只要有一个为空就拒绝插入或修改拒绝插入或修改实体完整性检查和违约处理实体完整性检查和违约处理(续续)l检查记录中主码值是否唯一的一种方法是进行检查记录中主码值是否唯一的一种方法是

9、进行全表扫描。依次判断表中每一条记录的主码值全表扫描。依次判断表中每一条记录的主码值与将插入记录上的主码值（或者修改的新主码与将插入记录上的主码值（或者修改的新主码值）是否相同。值）是否相同。l全表扫描是十分耗时的全表扫描是十分耗时的lrdbms核心一般都在主码上自动建立一个索核心一般都在主码上自动建立一个索引，通过索引查找基本表中是否已经存在新的引，通过索引查找基本表中是否已经存在新的主码值，将大大提高效率。主码值，将大大提高效率。第第5章章 数据库完整性数据库完整性l5.1 实体完整性实体完整性l5.2 参照完整性参照完整性l5.3 用户自定义完整性用户自定义完整性l5.4 完整性约束命名

10、子句完整性约束命名子句l5.6 触发器触发器l5.7 小结小结5.2 参照完整性参照完整性l5.2.1 参照完整性定义参照完整性定义l5.2.2 参照完整性检查和违约处理参照完整性检查和违约处理5.2 参照完整性参照完整性l5.2.1 参照完整性定义参照完整性定义l5.2.2 参照完整性检查和违约处理参照完整性检查和违约处理5.2.1 参照完整性定义参照完整性定义l参照完整性在参照完整性在cretat table中用中用foreign key短短语定义哪些列为外码，用语定义哪些列为外码，用references短语指明这短语指明这些外码参照哪些表的主码。些外码参照哪些表的主码。 例例3：定义：定

11、义sc中的参照完整性中的参照完整性 create table sc (sno char(9) not null, cno char(4) not null, grade smallint, primary key (sno,cno), foreign key(sno) references student(sno), foreign key(cno) references course(cno);5.2 参照完整性参照完整性l5.2.1 参照完整性定义参照完整性定义l5.2.2 参照完整性检查和违约处理参照完整性检查和违约处理5.2.2 参照完整性检查和违约处理参照完整性检查和违约处理l一个参

12、照完整性将两个表中的相应元组联系起一个参照完整性将两个表中的相应元组联系起来了，因此对被参照表和参照表进行增删改操来了，因此对被参照表和参照表进行增删改操作时有可能破坏参照完整性，必须进行检查。作时有可能破坏参照完整性，必须进行检查。l当不一致发生时，系统可以采用以下的策略进当不一致发生时，系统可以采用以下的策略进行处理行处理 拒绝执行，一般为默认策略拒绝执行，一般为默认策略 级连操作级连操作 设置为空值设置为空值参照完整性检查和违约处理参照完整性检查和违约处理(续续)l当对参照表和被参照表的操作违反了参照完整性，系当对参照表和被参照表的操作违反了参照完整性，系统采用默认策略。若想让系统采用其

13、他的策略必须在统采用默认策略。若想让系统采用其他的策略必须在创建表的时候显式地加以说明。创建表的时候显式地加以说明。 例例4：create table sc (sno char(9) not null, cno char(4) not null, grade smallint, primary key (sno,cno), foreign key(sno) references student(sno) on delete cascade on update cascade, foreign key(cno) references course(cno) on delete no action

14、 on update cascade);第第5章章 数据库完整性数据库完整性l5.1 实体完整性实体完整性l5.2 参照完整性参照完整性l5.3 用户自定义完整性用户自定义完整性l5.4 完整性约束命名子句完整性约束命名子句l5.6 触发器触发器l5.7 小结小结5.3 用户定义的完整性用户定义的完整性l5.3.1 属性上的约束条件的定义属性上的约束条件的定义l5.3.2 属性上的约束条件检查和违约处理属性上的约束条件检查和违约处理l5.3.3 元组上的约束条件的定义元组上的约束条件的定义l5.3.4 元组上的约束条件检查和违约处理元组上的约束条件检查和违约处理5.3.1 属性上的约束条件的定

15、义属性上的约束条件的定义l在在create table 中定义属性的同时可以根中定义属性的同时可以根据应用要求，定义属性上的约束条件，即属性据应用要求，定义属性上的约束条件，即属性值限制，包括：值限制，包括： - 列值非空（列值非空（not null短语）短语） - 列值唯一（列值唯一（unique短语）短语） - 检查列值是否满足一个布尔表达式（检查列值是否满足一个布尔表达式（check短语）短语）例题例题例例5 在定义在定义sc表时，说明表时，说明sno,cno,grade属性不属性不允许取空值允许取空值 create table sc ( sno char(9) not null, cn

16、o char(4) not null, grade smallint not null, primary key(sno,cno);例题例题例例6 建立部门表建立部门表dept，要求部门名称，要求部门名称dname列列取值唯一，部门编号取值唯一，部门编号deptno列为主码列为主码 create table dept ( deptno numeric(2), dname char(9) unique, location char(10), primary key (deptno);例题例题例例7 student表的表的ssex只允许取只允许取“男男”或或“女女” create table st

17、udent ( sno char(9) primary key, sname char(8) not null, ssex char(2) check(ssex in (男男,女女), sage smallint, sdept char(20) ); 例题例题例例8 sc表的表的grade的值应该在的值应该在0和和100之间之间 create table sc (sno char(9) not null, cno char(4) not null, grade smallint check (grade=0 and grade=100), primary key (sno,cno), fore

18、ign key(sno) references student(sno), foreign key(cno) references course(cno);5.3 用户定义的完整性用户定义的完整性l5.3.1 属性上的约束条件的定义属性上的约束条件的定义l5.3.2 属性上的约束条件检查和违约处理属性上的约束条件检查和违约处理l5.3.3 元组上的约束条件的定义元组上的约束条件的定义l5.3.4 元组上的约束条件检查和违约处理元组上的约束条件检查和违约处理5.3.2 属性上的约束条件检查和违属性上的约束条件检查和违约处理约处理l当往表中插入元组或修改属性的值时，当往表中插入元组或修改属性的值时

19、，rdbms就检查属性上的约束条件是否被就检查属性上的约束条件是否被满足，若不满足则操作被拒绝执行。满足，若不满足则操作被拒绝执行。5.3 用户定义的完整性用户定义的完整性l5.3.1 属性上的约束条件的定义属性上的约束条件的定义l5.3.2 属性上的约束条件检查和违约处理属性上的约束条件检查和违约处理l5.3.3 元组上的约束条件的定义元组上的约束条件的定义l5.3.4 元组上的约束条件检查和违约处理元组上的约束条件检查和违约处理5.3.3 元组上的约束条件的定义元组上的约束条件的定义l在在create table语句中可以用语句中可以用check短语定义元组上的约束条件，即短语定义元组上的

20、约束条件，即元组级的限制。元组级的限制。l元组级的限制可以设置不同属性之间的元组级的限制可以设置不同属性之间的取值的相互约束条件取值的相互约束条件例题例题 例例9 当学生的性别是男时，其名字不能以当学生的性别是男时，其名字不能以ms.打头打头 create table student ( sno char(9), sname char(8) not null, ssex char(2), sage smallint, sdept char(20) primary key （sno）, check (ssex=女女 or sname not like ms.%);5.3 用户定义的完整性用户定义

21、的完整性l5.3.1 属性上的约束条件的定义属性上的约束条件的定义l5.3.2 属性上的约束条件检查和违约处理属性上的约束条件检查和违约处理l5.3.3 元组上的约束条件的定义元组上的约束条件的定义l5.3.4 元组上的约束条件检查和违约处理元组上的约束条件检查和违约处理5.3.4 元组上的约束条件检查和违元组上的约束条件检查和违约处理约处理l当往表中插入元组或修改属性的值时，当往表中插入元组或修改属性的值时，rdbms就检查元组上的约束条件是否被就检查元组上的约束条件是否被满足，如果不满足则操作被拒绝执行。满足，如果不满足则操作被拒绝执行。第第5章章 数据库完整性数据库完整性l5.1 实体完

22、整性实体完整性l5.2 参照完整性参照完整性l5.3 用户自定义完整性用户自定义完整性l5.4 完整性约束命名子句完整性约束命名子句l5.6 触发器触发器l5.7 小结小结5.4 完整性约束命名子句完整性约束命名子句lsql还在还在create table语句中提供了语句中提供了完整性约束命名子句完整性约束命名子句constraint，用，用来对完整性约束条件命名。从而可以灵来对完整性约束条件命名。从而可以灵活地增加、删除一个完整性约束条件活地增加、删除一个完整性约束条件l完整性约束命名子句完整性约束命名子句 constraint primary key短语短语foreign key 短语短语

23、check短语短语例题例题例例10 建立学生登记表建立学生登记表student，要求学号在，要求学号在90000-99999之间，姓名之间，姓名不能取空值，年龄小于不能取空值，年龄小于30，性别只能是，性别只能是“男男”或或“女女”。 create table student ( sno numeric(6) constraint c1 check(sno between 900000 and 99999), sname char(20) constraint c2 not null, sage numeric(3) constraint c3 check (sage=3000);修改表中的完

24、整性限制修改表中的完整性限制l可以使用可以使用alter table 语句修改表中语句修改表中的完整性限制的完整性限制 例例12 去掉去掉student表中对性别的限制表中对性别的限制 alter table student drop constraint c4; 例题例题例例13 修改表修改表student中的约束条件，要求学号改为在中的约束条件，要求学号改为在900000-999999之间，年龄由小于之间，年龄由小于30改为小于改为小于40 可以先删除再增加可以先删除再增加 alter table student drop constraint c1; alter table studen

25、t add constraint c1 check (sno between 900000 and 999999); alter table student drop constraint c3; alter table student add constraint c3 check (sage40);第第5章章 数据库完整性数据库完整性l5.1 实体完整性实体完整性l5.2 参照完整性参照完整性l5.3 用户自定义完整性用户自定义完整性l5.4 完整性约束命名子句完整性约束命名子句l5.6 触发器触发器l5.7 小结小结5.6 触发器触发器l触发器是用户定义在关系表上的一类由事件驱触发器是用

26、户定义在关系表上的一类由事件驱动的特殊过程动的特殊过程l一旦定义，任何用户对表的增、删、改操作均一旦定义，任何用户对表的增、删、改操作均由服务器自动激活相应的触发器，在由服务器自动激活相应的触发器，在dbms核核心层进行集中的完整性控制。心层进行集中的完整性控制。l类似于约束，但比约束更加灵活，可以实施比类似于约束，但比约束更加灵活，可以实施比foreign key约束、约束、check约束更为复杂约束更为复杂的检查和操作，具有更精细和更强大的数据控的检查和操作，具有更精细和更强大的数据控制能力。制能力。5.6 触发器触发器l5.6.1 定义触发器定义触发器l5.6.2 激活触发器激活触发器l

27、5.6.3 删除触发器删除触发器5.6.1 定义触发器定义触发器lsql使用使用create trigger命令建立触发器，命令建立触发器，一般格式为：一般格式为： create trigger beforeafter on for each rowstatement when 定义触发器（续）定义触发器（续）l表的拥有者即创建表的用户才可以在表上创建触发器，表的拥有者即创建表的用户才可以在表上创建触发器，并且一个表上只能创建一定数量的触发器。并且一个表上只能创建一定数量的触发器。l触发器名可以包含模式名，也可以不包含模式名。同触发器名可以包含模式名，也可以不包含模式名。同一模式下，触发器名必

28、须是唯一的；且触发器名和表一模式下，触发器名必须是唯一的；且触发器名和表名必须在同一模式下。名必须在同一模式下。l当表的数据发生变化时，将激活定义在表上相应触发当表的数据发生变化时，将激活定义在表上相应触发事件的触发器，因此，表也被称为触发器的目标表事件的触发器，因此，表也被称为触发器的目标表l触发事件可以是触发事件可以是insert、delete或或update，也，也可以是这几个事件的组合。可以是这几个事件的组合。update后面还可以有后面还可以有of，用于进一步指明修改哪些列时触发，用于进一步指明修改哪些列时触发器激活。器激活。定义触发器（续）定义触发器（续）l按照所触发动作的间隔尺寸

29、可以分为行级触发器按照所触发动作的间隔尺寸可以分为行级触发器(for each row)和语句级触发器和语句级触发器(for each statement)l语句级触发器触发动作只执行一次，行级触发器的触语句级触发器触发动作只执行一次，行级触发器的触发动作执行的次数与涉及到的元组数相关发动作执行的次数与涉及到的元组数相关l触发器被激活时，只有当触发条件为真时触发动作体触发器被激活时，只有当触发条件为真时触发动作体才执行；否则触发动作体不执行，若省略才执行；否则触发动作体不执行，若省略when触发触发条件，则触发动作体在触发器激活后立即执行条件，则触发动作体在触发器激活后立即执行l触发动作体既可

30、以是一个匿名触发动作体既可以是一个匿名pl/sql过程块，也可以过程块，也可以是对已创建存储过程的调用。是对已创建存储过程的调用。定义触发器（续）定义触发器（续）l在行级触发器中，不管是匿名在行级触发器中，不管是匿名pl/sql过程块还是对已过程块还是对已创建存储过程的调用，用户都可以在过程体中使用创建存储过程的调用，用户都可以在过程体中使用new和和old引用引用update/insert事件之后的新值和事件之后的新值和update/delete事件之前的旧值。若是语句级触发事件之前的旧值。若是语句级触发器，则不能在触发动作体中使用器，则不能在触发动作体中使用new或或old进行引用。进行引

31、用。l若触发动作体执行失败，激活触发器的事件就会终止若触发动作体执行失败，激活触发器的事件就会终止执行，触发器的目标表或触发器可能影响的其他对象执行，触发器的目标表或触发器可能影响的其他对象不发生任何变化。不发生任何变化。例题例题例例18 定义一个定义一个before行级触发器，为教师表行级触发器，为教师表teacher定义完整性规则定义完整性规则“教授的工资不得低于教授的工资不得低于4000元，若低元，若低于于4000元，自动改为元，自动改为4000元元” create trigger insert_or_update_sal before insert or update on teach

32、er for each row as begin if (new.job=教授教授) and (new.sal4000) then new.sal:=4000; end if; end;例题（续）例题（续）例例19 定义定义after行级触发器，当教师表行级触发器，当教师表teacher的工资的工资发生变化后就自动在工资变化表发生变化后就自动在工资变化表sal_log中增加一条相中增加一条相应记录应记录 先建立工资变化表先建立工资变化表sal_log： create table sal_lot (eno numeric(4) references teacher(eno), sal numer

33、ic(7,2), username char(10), date timestamp);例题（续）例题（续）接着分别为接着分别为insert和和update操作创建触发器：操作创建触发器： create trigger insert_sal after insert on teacher for each row as begin insert into sal_log values (new.eno,new.sal,current_user, current_timestamp); end;例题（续）例题（续） create trigger update_sal after update on teacher for each row as begin if (new.salold.sal) then insert into sal_log values (new.eno,new.sal,current_user, current_timestamp); end if; end;5.6 触发器触发器