第五章数据库完整性

1、数据库概论1第五章第五章 数据库完整性数据库完整性5.1 5.1 实体完整性实体完整性 5.2 5.2 参照完整性参照完整性5.3 5.3 用户定义的完整性用户定义的完整性5.4 5.4 完整性约束命名子句完整性约束命名子句5.6 5.6 触发器触发器数据库概论2数据库完整性数据库完整性p数据库的完整性：数据库的完整性：指数据库的指数据库的正确性、有效性正确性、有效性和和相容性相容性，其目的是防止错误的其目的是防止错误的数据进入数据库数据进入数据库即完整性体现是数据库能否真实地反映现实世界。其中：即完整性体现是数据库能否真实地反映现实世界。其中：正确性：指数据的合法性正确性：指数据的合法性有效

2、性：指数据是否属于所定义域的有效范围有效性：指数据是否属于所定义域的有效范围相容性：指表示同一事实的两个数据应当一致，不一致即相容性：指表示同一事实的两个数据应当一致，不一致即是不相容的是不相容的p完整性被破坏的原因：完整性被破坏的原因：由更新操作不遵守完整性约束条件引起的由更新操作不遵守完整性约束条件引起的数据库概论3(1)概念不同：概念不同：v数据库的完整性：数据库的完整性： 是为了防止数据库中存在部分不符合语义的数据，防止是为了防止数据库中存在部分不符合语义的数据，防止错误信息的输入和输出。用以保证数据的正确、有效。错误信息的输入和输出。用以保证数据的正确、有效。防防范对象是不合语义的数

3、据。范对象是不合语义的数据。v数据库的安全性：数据库的安全性： 是为了保护数据库防止恶意的破坏和非法的存取。是为了保护数据库防止恶意的破坏和非法的存取。防范防范对象是非法用户和非法操作。对象是非法用户和非法操作。(2)联系：联系： 二者又是密切相关的，都是使得系统随时可正常运转，二者又是密切相关的，都是使得系统随时可正常运转，防止机密信息泄露和破坏。防止机密信息泄露和破坏。数据安全性与完整性区别数据安全性与完整性区别数据库概论4DBMSDBMS为维护数据库的完整性所做的工作为维护数据库的完整性所做的工作p提供定义完整性约束条件的机制提供定义完整性约束条件的机制SQL标准使用标准使用DDL来描述

4、完整性来描述完整性p提供完整性检查的方法提供完整性检查的方法DBMS中检查数据是否满足完整性约束条件的机制中检查数据是否满足完整性约束条件的机制p违约处理违约处理DBMS若发现用户的操作违背了完整性约束条件，就采取若发现用户的操作违背了完整性约束条件，就采取措施进行违约处理措施进行违约处理数据库概论55.1 5.1 实体完整性实体完整性v关系模型的实体完整性在关系模型的实体完整性在CREATE TABLECREATE TABLE中用中用PRIMARY KEYPRIMARY KEY定定义义v单属性构成的码：单属性构成的码：v列级约束条件列级约束条件v表级约束条件表级约束条件v多属性构成的码：多属

5、性构成的码：v表级约束条件表级约束条件数据库概论6例例1将将Student 表中的表中的Sno属性定义为码属性定义为码 CREATE TABLE Student (Sno CHAR(9) , Sname CHAR(20) NOT NULL, Ssex CHAR(2), Sage SMALLINT, Sdept CHAR(20) );PRIMARY KEY(Sno)例例2将将SC 表中的表中的Sno,Cno属性定义为码属性定义为码 CREATE TABLE SC (Sno CHAR(9) NOT NULL, Cno CHAR(4) NOT NULL, Grade SMALLINT, PRIMAR

6、Y KEY (Sno, Cno) );列级定义主码列级定义主码表级定义主码表级定义主码表级定义主码表级定义主码PRIMARY KEY(Sno)数据库概论7实体完整性检查和违约处理实体完整性检查和违约处理用用PRIMARYKEY短语定义了关系的主码之后，每当用户程短语定义了关系的主码之后，每当用户程序对基本表进行插入或更新操作之前，序对基本表进行插入或更新操作之前，RDBMS会对实体完整性会对实体完整性进行检查：进行检查：检查主码值是否唯一，如果不唯一则拒绝插入或修改检查主码值是否唯一，如果不唯一则拒绝插入或修改检查主码的各个属性是否为空，只要有一个为空就拒绝插入检查主码的各个属性是否为空，只要

7、有一个为空就拒绝插入或修改或修改检查主码值是否唯一的方法：检查主码值是否唯一的方法：全表扫描全表扫描为主码建立一个索引为主码建立一个索引数据库概论85.2 5.2 参照完整性参照完整性v关系模型的参照完整性在关系模型的参照完整性在CREATE TABLECREATE TABLE中用中用FOREIGN KEYFOREIGN KEY定定义外码，用义外码，用REFERENCESREFERENCES指明外码参照哪些表的主码指明外码参照哪些表的主码例例3 3定义定义SCSC中的参照完整性中的参照完整性CREATE TABLE SCCREATE TABLE SC (Sno CHAR(9) (Sno CHA

8、R(9) NOT NULLNOT NULL， Cno CHAR(4) NOT NULL,Cno CHAR(4) NOT NULL, Grade SMALLINT, Grade SMALLINT, PRIMARY KEY (Sno, Cno), PRIMARY KEY (Sno, Cno), / /* *在表级定义实体完整性在表级定义实体完整性* */ / FOREIGN KEY (Sno) REFERENCES Student(Sno), FOREIGN KEY (Sno) REFERENCES Student(Sno),/ /* *在表级定义参照完整性在表级定义参照完整性* */ / FOR

9、EIGN KEY (Cno) REFERENCES Course(Cno) FOREIGN KEY (Cno) REFERENCES Course(Cno) / /* *在表级定义参照完整性在表级定义参照完整性* */ / ); );数据库概论9参照完整性检查和违约处理参照完整性检查和违约处理对参照表和被参照表进行增删改操作时必须进行检查对参照表和被参照表进行增删改操作时必须进行检查被参照表（被参照表（Student）参照表（参照表（SC）违约处理违约处理可能破坏参照完整性可能破坏参照完整性插入元组插入元组拒绝拒绝可能破坏参照完整性可能破坏参照完整性修改外码值修改外码值拒绝拒绝删除元组删除元组

10、可能破坏参照完整性可能破坏参照完整性拒绝拒绝/级连删除级连删除/设置为空值设置为空值修改主码值修改主码值可能破坏参照完整性可能破坏参照完整性拒绝拒绝/级连删除级连删除/设置为空值设置为空值违约处理：违约处理：拒绝执行：拒绝执行：不允许操作不允许操作（系统默认策略）（系统默认策略）级连操作：级连操作：删除或修改参照表中的所有造成不一致的元组删除或修改参照表中的所有造成不一致的元组设置为空值：设置为空值：将参照表中的所有造成不一致的元组的对应属将参照表中的所有造成不一致的元组的对应属性设置为空值性设置为空值数据库概论10(1)(1)外码允许取空值：外码允许取空值： 学生学生( (学号学号，姓名，性

11、别，专业号，年龄，姓名，性别，专业号，年龄) ) 专业专业( (专业号专业号，专业名，专业名) ) 学生关系学生关系( (参照参照)专业关系专业关系( (被参照被参照) )(2)(2)外码不允许取空值：外码不允许取空值： 学生关系学生关系( (被参照被参照) )选课关系选课关系( (参照参照) )对于参照完整性，除应该定义外码，还应定义外码是否允许取对于参照完整性，除应该定义外码，还应定义外码是否允许取空值。一般地，系统采用默认方式，即拒绝。如果想让系统采空值。一般地，系统采用默认方式，即拒绝。如果想让系统采用其它方式则必须在建表之时显式的说明用其它方式则必须在建表之时显式的说明外码能否接受空

12、值的问题外码能否接受空值的问题修改修改修改修改数据库概论11例例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/*当删除当删除Student表中的元组时，级连删

13、除表中的元组时，级连删除SC表中相应的元组表中相应的元组*/ ON UPDATE CASCADE，/*当更新当更新Student表中的表中的Sno时，级连更新时，级连更新SC表中相应的元组表中相应的元组*/ FOREIGN KEY (Cno) REFERENCES Course(Cno), /*在表级定义参照完整性在表级定义参照完整性*/ON DELETE NO ACTION/*当删除当删除Course表中的元组造成了与表中的元组造成了与SC表不一致时拒绝删除表不一致时拒绝删除*/ ON UPDATE CASCADE，/*当更新当更新Course表中的表中的Cno时，级连更新时，级连更新SC表

14、中相应的元组表中相应的元组*/ );数据库概论125.3 5.3 用户定义的完整性用户定义的完整性v在在CREATE TABLECREATE TABLE中定义属性的同时可以根据应用要求，定义中定义属性的同时可以根据应用要求，定义属性上的约束条件，即属性值限制，包括：属性上的约束条件，即属性值限制，包括：v列值非空（列值非空（NOTNOTNULLNULL）v列值唯一（列值唯一（UNIQUEUNIQUE）v检查列值是否满足一个布尔表达式（检查列值是否满足一个布尔表达式（CHECKCHECK短语）短语）数据库概论131.不允许取空值不允许取空值例例5 在定义在定义SC表时，说明表时，说明Sno、Cn

15、o、Grade属性不允许取空值属性不允许取空值CREATE TABLE SC (Sno CHAR(9) NOT NULL， Cno CHAR(4) NOT NULL, Grade SMALLINTNOT NULL, PRIMARY KEY (Sno, Cno) /*在表级定义实体完整性在表级定义实体完整性*/ );数据库概论142.列值唯一列值唯一例例6 建立部门表建立部门表DEPT，要求部门名称，要求部门名称Dname列取值唯一，部列取值唯一，部门编号门编号Deptno列为主码列为主码CREATE TABLE DEPT (Deptno NUMERIC(2)， Dname CHAR(9) UN

16、IQUE, /*要求要求Dname列值唯一列值唯一*/ Location CHAR(10), PRIMARY KEY (Deptno) /*在表级定义实体完整性在表级定义实体完整性*/ );数据库概论153.用用CHECK短语指定列值应该满足的条件短语指定列值应该满足的条件例例7 Student表的表的Ssex只允许取只允许取“男男”和和“女女”。CREATE TABLE Student (Sno CHAR(9) PRIMARY KEY， Sname CHAR(8) NOT NULL, Ssex CHAR(2) CHECK (Ssex IN (男男, 女女), Sage SMALLINT, S

17、dept CHAR(20) );注：当往表中插入元组或修改属性的值时，注：当往表中插入元组或修改属性的值时，RDBMS就会检查就会检查属性属性上的约束条件是否被满足，如果不满足则操作被拒绝上的约束条件是否被满足，如果不满足则操作被拒绝数据库概论16元组上的条件的定义：元组上的条件的定义：在在CREATETABLE语句中可以用语句中可以用CHECK短语定义元组短语定义元组上的约束，即元组级的限制。同属性值限制相比，元组级的上的约束，即元组级的限制。同属性值限制相比，元组级的限制可以设置不同属性之间的取值的相互约束条件限制可以设置不同属性之间的取值的相互约束条件例例9当学生的性别是男时，其名字不能

18、以当学生的性别是男时，其名字不能以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.%/*定义了元组中定义了元组中Sname和和Ssex两个属性值之间的约束条件两个属性值之间的约束条件*/ );当往表中插入元组或修改属性的值时，当往表中插入元组或修改属性的值时，RDBMS就会检查就会检查元组元组上的约束条件是否被满足

19、，如果不满足则操作被拒绝上的约束条件是否被满足，如果不满足则操作被拒绝数据库概论17完整性约束条件分类及含义完整性约束条件分类及含义粒度粒度状态状态属性列级属性列级元组级元组级关系表级关系表级静态静态静态属性列级约束静态属性列级约束属性列定义属性列定义数据类型约束数据类型约束数据格式约束数据格式约束值域约束值域约束 空值约束空值约束是规定一个元组是规定一个元组的各个列值之间的各个列值之间应满足的条件的应满足的条件的约束约束 是在一个关系的各个元组是在一个关系的各个元组之间或若干个关系之间之间或若干个关系之间存在的各种联系或约束存在的各种联系或约束实体完整性约束实体完整性约束参照完整性约束参照完

20、整性约束函数依赖约束函数依赖约束 统计约束统计约束动态动态是修改列定义或列是修改列定义或列值时的约束值时的约束是在修改某个元是在修改某个元组时对元组的新组时对元组的新旧值之间应满足旧值之间应满足的约束的约束是对某个关系的新、旧状态是对某个关系的新、旧状态间应满足的约束条件间应满足的约束条件数据库概论18SQL在在CREATETABLE语句中提供了完整性约束命名子句语句中提供了完整性约束命名子句CONSTRAINT，用来对完整性约束条件命名，从而可以灵活地增删一个完整性约束条件用来对完整性约束条件命名，从而可以灵活地增删一个完整性约束条件1.完整性约束命名子句完整性约束命名子句格式格式CONST

21、RAINT完整性约束条件名完整性约束条件名PRIMARY KEY 短语短语|FOREIGN KEY|CHECK短语短语例例10建立学生登记表建立学生登记表Student，要求学号在，要求学号在90 00099 999之间，之间，姓名不能取空值，年龄小于姓名不能取空值，年龄小于30，性别只能是，性别只能是“男男”或或“女女”CREATE TABLE Student (Sno NUMERIC(6) CONSTRAINT C1 CHECK(Sno BETWEEN 90000 AND 99999)， Sname CHAR(20) CONSTRAINT C2 NOT NULL, Sage NUMERIC

22、(3) CONSTRAINT C3 CHECK (Sage=3000) );外码约束外码约束EMPFKey,EMPFKey,列级约束列级约束C1C1数据库概论202.修改表中的完整性限制修改表中的完整性限制应用应用ALTERTABLE语句修改表中的完整性语句修改表中的完整性例例1212去掉例去掉例1010中对表中对表StudentStudent中对性别的限制中对性别的限制 ALTERALTERTABLETABLEStudentStudent DROP CONSTRAINT C4; DROP CONSTRAINT C4;例例1313修改表修改表StudentStudent中的约束条件，要求学号改

23、为在中的约束条件，要求学号改为在900 900 000999 999000999 999之间，年龄由小于之间，年龄由小于3030改为小于改为小于4040ALTERALTERTABLETABLEStudentStudentDROP CONSTRAINT C1;DROP CONSTRAINT C1;ALTERALTERTABLETABLEStudentStudentADD CONSTRAINT ADD CONSTRAINT C1 CHECK(Sno BETWEEN 90000 AND 99999)C1 CHECK(Sno BETWEEN 90000 AND 99999)；ALTERALTERTAB

24、LETABLEStudentStudentDROP CONSTRAINT C3;DROP CONSTRAINT C3;ALTERALTERTABLETABLEStudentStudentADD CONSTRAINT ADD CONSTRAINT C3 CHECK(Sage40)C3 CHECK(Sage40)；数据库概论21q引入触发器原因：引入触发器原因：q在很多情况下，当一个完整性约束被违背时，在很多情况下，当一个完整性约束被违背时，DBMSDBMS除了除了终止事务外，还需要执行一些其他操作。为了在数据库完终止事务外，还需要执行一些其他操作。为了在数据库完整性约束被违背时能够及时执行必要的

25、操作，人们提出了整性约束被违背时能够及时执行必要的操作，人们提出了触发器技术触发器技术5.6 5.6 触发器触发器数据库概论225.6 5.6 触发器触发器q功能：功能：q主要功能：能够实现由主码和外码所不能保证的、复杂的参主要功能：能够实现由主码和外码所不能保证的、复杂的参照完整性照完整性q强化约束：强制实现业务规则和数据完整性强化约束：强制实现业务规则和数据完整性q跟踪变化：可以跟踪数据库内的数据变化，阻止非法更新和跟踪变化：可以跟踪数据库内的数据变化，阻止非法更新和删除删除q级联运行：可以监测数据库内的操作，并自动将影响蔓延到级联运行：可以监测数据库内的操作，并自动将影响蔓延到多个表多个

26、表q存储过程的调用：为了响应数据库更新，触发器可以调用一存储过程的调用：为了响应数据库更新，触发器可以调用一个或多个存储过程，甚至可调用个或多个存储过程，甚至可调用DBMSDBMS外的外部过程外的外部过程数据库概论235.6 5.6 触发器触发器p触发器（触发器（Trigger）：是用户定义在关系表上的一类由事件驱）：是用户定义在关系表上的一类由事件驱动的特殊过程，或者说是一个能由系统自动执行对数据库修改动的特殊过程，或者说是一个能由系统自动执行对数据库修改的语句，亦称为的语句，亦称为主动规则主动规则p一旦定义，任何用户对表的增删改操作均由服务器自动激活一旦定义，任何用户对表的增删改操作均由服

27、务器自动激活相应的触发器，在相应的触发器，在DBMS核心层进行集中的完整性控制，它类核心层进行集中的完整性控制，它类似于约束，但较之约束更加灵活，具有更精细和更强大的数据似于约束，但较之约束更加灵活，具有更精细和更强大的数据控制能力控制能力p触发器结构：触发器结构： 一个触发器由两部分组成：一个触发器由两部分组成：（1）触发事件：指对数据库的更新等操作，在这些操作进行）触发事件：指对数据库的更新等操作，在这些操作进行时，触发器将被激发。时，触发器将被激发。（2）触发器条件和动作：条件准则）触发器条件和动作：条件准则 ，以确定更新语句是否导，以确定更新语句是否导致执行触发器动作。条件成立，则由致

28、执行触发器动作。条件成立，则由DBMS执行触发器动作。执行触发器动作。这些动作可以这些动作可以 是一系列对数据库的操作是一系列对数据库的操作数据库概论24触发器的定义触发器的定义 CREATETRIGGER触发器名触发器名 BEFORE | AFTER | 触发事件触发事件 ON 表名表名 FOREACHROWSTATEMENT WHEN触发条件触发条件 触发动作体触发动作体其中：其中：1.1.表的拥有者即创建表的用户才可以在表上创建触发器，并且表的拥有者即创建表的用户才可以在表上创建触发器，并且一个表上只能创建一定数量的触发器一个表上只能创建一定数量的触发器2.2.触发器名：触发器名：可以包

29、含模式名，也可以不包含模式名。同一模可以包含模式名，也可以不包含模式名。同一模式下，触发器名必须是唯一的；并且触发器名和式下，触发器名必须是唯一的；并且触发器名和 必须在必须在同一模式下同一模式下3.3.表名：表名：当这个表的数据发生变化时，将激活定义在该表相应当这个表的数据发生变化时，将激活定义在该表相应 的触发器，该表也称为触发器的目标表的触发器，该表也称为触发器的目标表数据库概论25触发器的定义触发器的定义 CREATETRIGGER触发器名触发器名 BEFORE | AFTER | 触发事件触发事件 ON 表名表名 FOREACHROWSTATEMENT WHEN触发条件触发条件 触发

30、动作体触发动作体4.4.触发事件：触发事件：可以是可以是INSERTINSERT，DELETEDELETE或或UPDATEUPDATE，也可以是这几，也可以是这几个事件的组合。个事件的组合。UPDATEUPDATE后面还可以有后面还可以有OFOF， 即进即进一步指明修改哪些列时触发器激活一步指明修改哪些列时触发器激活5.5.触发器类型：触发器类型：行级触发器和语句级触发器行级触发器和语句级触发器6.6.触发条件：触发条件：只有当触发条件为真时，触发动作才执行，反之只有当触发条件为真时，触发动作才执行，反之不执行不执行7.7.触发动作体：触发动作体：可以是一个匿名可以是一个匿名PL/SQLPL/

31、SQL过程块，也可以是对已过程块，也可以是对已创建存储过程的调用创建存储过程的调用数据库概论26例例1818 定义一个定义一个BEFOREBEFORE行级触发器，为老师表行级触发器，为老师表TeacherTeacher定义完整定义完整性规则性规则“教授的工资不得低于教授的工资不得低于40004000元，如果低于元，如果低于40004000元，自动元，自动改为改为40004000元元” CREATE TRIGGER Insert_Or_Update_SalCREATE TRIGGER Insert_Or_Update_Sal BEFORE INSERT OR UPDATE ON Teacher

32、BEFORE INSERT OR UPDATE ON Teacher FOR EACH ROW FOR EACH ROW AS BEGIN AS BEGIN IF(new.pJob= IF(new.pJob=教授教授) AND (new.Sal4000) THEN) AND (new.Sal4000) THEN new.Sal:=4000; new.Sal:=4000; END IF; END IF; END; END;数据库概论27例例1919 定义定义AFTERAFTER行级触发，当教师表行级触发，当教师表TeacherTeacher的工资发生变化后的工资发生变化后就自动在工资变化表就自动

33、在工资变化表Sal_logSal_log中增加一条相应记录中增加一条相应记录 CREATE TABLE Sal_logCREATE TABLE Sal_log (Eno NUMERIC(4) reference teacher(eno), (Eno NUMERIC(4) reference teacher(eno), Sal NUMERIC(7,2), Sal NUMERIC(7,2), Username char(10), Username char(10), Date TIMESTAMP); Date TIMESTAMP); CREATE TRIGGER Insert_SalCREATE

34、TRIGGER Insert_Sal AFTER INSERT ON Teacher AFTER INSERT ON Teacher FOR EACH ROW FOR EACH ROW AS BEGIN AS BEGIN INSERT INTO Sal_log VALUES(new.Eno,new.Sal, INSERT INTO Sal_log VALUES(new.Eno,new.Sal, CURRENT_USER,CURRENT_TIMESTAMP);CURRENT_USER,CURRENT_TIMESTAMP); END; END; CREATE TRIGGER Update_SalCREATE TRIGGER Update_Sal AFTER UPDATE ON Teacher AFTER UPDATE ON Teacher FOR EACH ROW FOR EACH ROW AS BEGIN AS BEGIN IF(new.Salold.Sal) THEN INSERT INTO Sal_log VALUES( IF(new.Salold.Sal) THEN INSERT INTO Sal_log VALUES( new.Eno,new.Sal,CUTTENT_USER,CURRENT_TIMESTAMP); n