常熟理工数据库原理教程PPT-05.ppt

1、数据库原理与应用数据库原理与应用数据库系统概论第四版王 珊 萨师煊第第5章章 数据库完整性数据库完整性数据库完整性机制触发器存储过程第第5章章 数据库完整性数据库完整性 数据库的完整性是指数据的数据库的完整性是指数据的正确性正确性和和相容性相容性。完完整整性性检检查查和和控控制制的的防防范范对对象象是是不不合合语语义义的的、不不正正确的数据。确的数据。DBMSDBMS对完整性的维护机制：对完整性的维护机制：1 1、提供定义完整性约束条件的机制、提供定义完整性约束条件的机制 2 2、提供完整性检查的方法、提供完整性检查的方法 3 3、违约处理、违约处理5.1 实体完整性实体完整性1、实体完整性定

2、义PRIMARY KEY单属性构成的码有两种说明方法单属性构成的码有两种说明方法 定义为列级约束条件定义为表级约束条件多个属性构成的码只有一种说明方法多个属性构成的码只有一种说明方法定义为表级约束条件 5.1 实体完整性（续）实体完整性（续）如：如：CREATE TABLE Student(Sno CHAR(9)PRIMARY KEY,/*在列级定义主码*/Sname CHAR(20)NOT NULL,Ssex CHAR(2),Sage SMALLINT,Sdept CHAR(20);5.1 实体完整性（续）实体完整性（续）如：如：CREATE TABLE Student(Sno CHAR(9

3、),Sname CHAR(20)NOT NULL,Ssex CHAR(2),Sage SMALLINT,Sdept CHAR(20),PRIMARY KEY(Sno)/*在表级定义主码*/);5.1 实体完整性（续）实体完整性（续）如：如：CREATE TABLE SC(Sno CHAR(9)NOT NULL,Cno CHAR(4)NOT NULL,Grade SMALLINT,PRIMARY KEY(Sno,Cno)/*只能在表级定义主码*/);5.1 实体完整性（续）实体完整性（续）2、实体完整性检查和违约处理插插入入记记录录或或对对主主码码列列进进行行更更新新操操作作时时，按按照照实实体

4、完整性规则自动进行检查。包括：体完整性规则自动进行检查。包括：检查主码值是否唯一，如果不唯一则拒绝插入或修改。检查主码的各个属性是否为空，只要有一个为空就拒绝插入或修改。检查方法：全表扫描（耗时），索引检查方法：全表扫描（耗时），索引5.2 参照完整性参照完整性可能破坏参照完整性的情况及违约处理被参照表被参照表(如如Student)Student)参照表参照表(如如SC)SC)违约处理违约处理可能破坏参照完整性可能破坏参照完整性插入元组插入元组拒绝拒绝可能破坏参照完整性可能破坏参照完整性修改外码值修改外码值拒绝拒绝删除元组删除元组可能破坏参照完整性可能破坏参照完整性拒绝拒绝/级连删除级连删除/

5、设置为空值设置为空值修改主码值修改主码值可能破坏参照完整性可能破坏参照完整性拒绝拒绝/级连修改级连修改/设置为空值设置为空值5.2 参照完整性（续）参照完整性（续）1、参照完整性定义FOREIGN KEY (属性列)/*定义列为外码*/REFERENCES 表名(主码)/*参照哪些表的主码*/2、参照完整性检查和处理 拒绝拒绝(NO ACTION)执行执行默认策略 级联级联(CASCADE)操作操作 设置为空值（设置为空值（SET-NULL）对于参照完整性，除了应该定义外码，还应定义外码列是否允许空值。5.2 参照完整性（续）参照完整性（续）如：如：CREATE TABLE SC(Sno CH

6、AR(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 参照完整性（续）参照完整性（续）如：如：CREATE TABLE SC(Sno CHAR(9)NOT NULL,Cno CHAR(4)NOT NULL,Grade SMALLINT,PRIMARY KEY(Sno,Cno),FOREIGN KEY(Sno)REFERENCES Student(

7、Sno)ON DELETE CASCADE /*删除Student记录时级联删除SC相应记录*/ON UPDATE CASCADE,/*更新Student记录时级联更新SC相应记录*/FOREIGN KEY(Cno)REFERENCES Course(Cno)ON DELETE NO ACTION /*删除Course记录使与SC不一致则拒绝删除*/ON UPDATE CASCADE /*更新Course的Cno时级联更新SC相应记录*/);5.3 用户自定义完整性用户自定义完整性1、不允许取空值NOT NULL 2、列值唯一UNIQUE3、CHECK子句CHECK(约束条件表达式)4、违约处

8、理方式拒绝5.3 用户自定义完整性（续）用户自定义完整性（续）如：如：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.3 用户自定义完整性（续）用户自定义完整性（续）如：如：CREATE TABLE DEPT(Deptno NUMERIC(2),Dname CH

9、AR(9)UNIQUE,/*列值唯一*/Location CHAR(10),PRIMARY KEY(Deptno);5.3 用户自定义完整性（续）用户自定义完整性（续）如：如：CREATE TABLE Student(Sno CHAR(9)PRIMARY KEY,Sname CHAR(20)NOT NULL,Ssex CHAR(2)CHECK(Ssex IN男,女,),/*Ssex属性只允许取男或女*/Sage SMALLINT,Sdept CHAR(20);5.3 用户自定义完整性（续）用户自定义完整性（续）如：如：CREATE TABLE SC(Sno CHAR(9)NOT NULL,Cn

10、o CHAR(4)NOT NULL,Grade SMALLINT CHECK(Grade=0 AND Grade=100),/*Grade只能取0-100之间的整数*/PRIMARY KEY(Sno,Cno),FOREIGN KEY(Sno)REFERENCES Student(Sno),FOREIGN KEY(Cno)REFERENCES Course(Cno);5.3 用户自定义完整性（续）用户自定义完整性（续）如：如：CREATE TABLE Student(Sno CHAR(9),Sname CHAR(20)NOT NULL,Ssex CHAR(2)Sage SMALLINT,Sdep

11、t CHAR(20),PRIMARY KEY(Sno),CHECK(Ssex=女 OR Sname NOT LIKE Ms.%)/*元组上的约束条件定义*/);5.4 完整性约束命名子句完整性约束命名子句1、语法格式CONSTRAINT 如：如：CREATE TABLE Student(Sno NUMERIC(6),Sname CHAR(20)CONSTRAINT C1 NOT NULL,Ssex CHAR(2)CONSTRAINT C2 CHECK(Ssex IN(男,女),Sage SMALLINT CONSTRAINT C3 CHECK(Sage 1 beginprint Cannot

12、insertrollback transaction endUPDATE UPDATE 触发器的工作过程（续）触发器的工作过程（续）5.4 触发器（续）触发器（续）INSTEAD OF触发器可可以以在在表表和和视视图图上上定定义义INSTEAD OF触触发发器器。INSTEAD OF触触发发器器代代替替原原触触发发动动作作执执行行，增增加了视图上所能进行的更新的种类。加了视图上所能进行的更新的种类。每每个个表表上上对对每每个个触触发发动动作作(INSERT、UPDATE或或DELETE)只能定义一个只能定义一个INSTEAD OF触发器。触发器。不不能能在在具具有有WITH CHECK OPT

13、ION选选项项的的视视图图上创建上创建INSTEAD OF触发器。触发器。例6：创建一个包括学生和教师编号、姓名以及类别的视图，在视图上建立更新触发器，可通过视图更新结果返回到源基本表中。create view vw_s_t(ID,Name,Type)as SELECT gh,xm,teacher FROM js UNION SELECT xh,xm,student FROM xsCREATE TRIGGER trg_vw_s_t_updateON vw_s_tINSTEAD OF UPDATEASbegin IF UPDATE(Name)begin declare ID nvarchar(2

14、55),name nvarchar(255),type nvarchar(15)select ID=ID,name=name,type=type from inserted if type=student update xs set xm=name where xh=ID if type=teacher update js set xm=name where gh=ID endend1、插入选课记录时，检查若选课人数已满、插入选课记录时，检查若选课人数已满5人，则拒绝插入。人，则拒绝插入。2、在订单表、在订单表(ORDER)中插入或修改订购数量中插入或修改订购数量(NUM),必须满必须满足客户

15、相应等级的数量上限和下限。足客户相应等级的数量上限和下限。CUSTOMERCUSTOMERLEVELORDER练习练习 3、创创建建一一个个显显示示所所有有雇雇员员和和客客户户姓名的视图姓名的视图create view vw_AllRelativesasselect customerID as RelativeID,customerName as RelativeName,Customer as RelativeType from Customersunionselect EmployeeID as RelativeID,FirstName+LastName as RelativeName,E

16、mployee as RelativeType from EmployeesGo希望通过视图删除其中的雇员或顾客信息希望通过视图删除其中的雇员或顾客信息delete from vw_AllRelatives where relativeid=10008练习练习1、插入选课记录时，检查若该课程的选课人数已满、插入选课记录时，检查若该课程的选课人数已满5人，则人，则拒绝插入。拒绝插入。CREATE TRIGGER trg_SC_insert ON SC AFTER INSERTASIF(SELECT COUNT(*)FROM SC,INSERTED WHERE SC.CNO=INSERTED.CNO)5 BEGINPRINT 选课已满选课已满ROLLBACK transactionEND练习1：2、在订单表、在订单表(ORDER)中插入或修改订购数量中插入或修改订购数量(NUM),必须满必须满足客户相应等级的数量上限和下限。足客户相应等级的数量上限和下限。CUSTOMERCUSTOMERLEVELORDER练习2：CREATE TRIGGER CHECK_NUM ON ORDER AFTE