第五章 数据库完整性

第五章数据库完整性§5.1实体完整性§5.2参照完整性§5.3用户定义的完整性§5.4完整性约束命名子句§5.6触发器数据库完整性数据库的完整性：指数据库的正确性、有效性和相容性，其目的是防止错误的数据进入数据库即完整性体现是数据库能否真实地反映现实世界。其中：正确性：指数据的合法性有效性：指数据是否属于所定义域的有效范围相容性：指表示同一事实的两个数据应当一致，不一致即是不相容的完整性被破坏的原因：由更新操作不遵守完整性约束条件引起的(1)概念不同：数据库的完整性：是为了防止数据库中存在部分不符合语义的数据，防止错误信息的输入和输出。用以保证数据的正确、有效。防范对象是不合语义的数据。数据库的安全性：是为了保护数据库防止恶意的破坏和非法的存取。防范对象是非法用户和非法操作。(2)联系：

二者又是密切相关的，都是使得系统随时可正常运转，防止机密信息泄露和破坏。数据安全性与完整性区别DBMS为维护数据库的完整性所做的工作提供定义完整性约束条件的机制SQL标准使用DDL来描述完整性提供完整性检查的方法DBMS中检查数据是否满足完整性约束条件的机制违约处理DBMS若发现用户的操作违背了完整性约束条件，就采取措施进行违约处理§5.1实体完整性关系模型的实体完整性在CREATETABLE中用PRIMARYKEY定义单属性构成的码：列级约束条件表级约束条件多属性构成的码：表级约束条件例1

将Student表中的Sno属性定义为码

CREATETABLEStudent(SnoCHAR(9),

SnameCHAR(20)NOTNULL,

SsexCHAR(2),SageSMALLINT,

SdeptCHAR(20)

);PRIMARYKEY(Sno)例2将SC表中的Sno,Cno属性定义为码

CREATETABLESC(SnoCHAR(9)NOTNULL,

CnoCHAR(4)NOTNULL,GradeSMALLINT,PRIMARYKEY(Sno,Cno));列级定义主码表级定义主码表级定义主码PRIMARYKEY(Sno)实体完整性检查和违约处理用PRIMARY

KEY短语定义了关系的主码之后，每当用户程序对基本表进行插入或更新操作之前，RDBMS会对实体完整性进行检查：检查主码值是否唯一，如果不唯一则拒绝插入或修改检查主码的各个属性是否为空，只要有一个为空就拒绝插入或修改检查主码值是否唯一的方法：全表扫描为主码建立一个索引§5.2参照完整性关系模型的参照完整性在CREATETABLE中用FOREIGNKEY定义外码，用REFERENCES指明外码参照哪些表的主码例3

定义SC中的参照完整性

CREATETABLESC(SnoCHAR(9)NOTNULL，

CnoCHAR(4)NOTNULL,GradeSMALLINT,PRIMARYKEY(Sno,Cno),/*在表级定义实体完整性*/FOREIGNKEY(Sno)REFERENCESStudent(Sno),

/*在表级定义参照完整性*/FOREIGNKEY(Cno)REFERENCESCourse(Cno)/*在表级定义参照完整性*/);参照完整性检查和违约处理对参照表和被参照表进行增删改操作时必须进行检查被参照表（Student）参照表（SC）违约处理可能破坏参照完整性插入元组拒绝可能破坏参照完整性修改外码值拒绝删除元组可能破坏参照完整性拒绝/级连删除/设置为空值修改主码值可能破坏参照完整性拒绝/级连删除/设置为空值违约处理：拒绝执行：不允许操作（系统默认策略）级连操作：删除或修改参照表中的所有造成不一致的元组设置为空值：将参照表中的所有造成不一致的元组的对应属性设置为空值(1)外码允许取空值：

学生(学号，姓名，性别，专业号，年龄)

专业(专业号，专业名)

学生关系(参照)→专业关系(被参照)(2)外码不允许取空值：

学生关系(被参照)←选课关系(参照)对于参照完整性，除应该定义外码，还应定义外码是否允许取空值。一般地，系统采用默认方式，即拒绝。如果想让系统采用其它方式则必须在建表之时显式的说明外码能否接受空值的问题修改修改例4

显式说明参照完整性的违约处理示例

CREATETABLESC(SnoCHAR(9)NOTNULL，

CnoCHAR(4)NOTNULL,GradeSMALLINT,PRIMARYKEY(Sno,Cno),/*在表级定义实体完整性*/FOREIGNKEY(Sno)REFERENCESStudent(Sno)/*在表级定义参照完整性*/ONDELETECASCADE

/*当删除Student表中的元组时，级连删除SC表中相应的元组*/

ONUPDATECASCADE，

/*当更新Student表中的Sno时，级连更新SC表中相应的元组*/FOREIGNKEY(Cno)REFERENCESCourse(Cno),/*在表级定义参照完整性*/

ONDELETENOACTION

/*当删除Course表中的元组造成了与SC表不一致时拒绝删除*/

ONUPDATECASCADE，

/*当更新Course表中的Cno时，级连更新SC表中相应的元组*/);§5.3用户定义的完整性在CREATETABLE中定义属性的同时可以根据应用要求，定义属性上的约束条件，即属性值限制，包括：列值非空（NOT

NULL）列值唯一（UNIQUE）检查列值是否满足一个布尔表达式（CHECK短语）1.不允许取空值例5在定义SC表时，说明Sno、Cno、Grade属性不允许取空值

CREATETABLESC(SnoCHAR(9)NOTNULL，

CnoCHAR(4)NOTNULL,GradeSMALLINT

NOTNULL,PRIMARYKEY(Sno,Cno)/*在表级定义实体完整性*/);2.列值唯一例6建立部门表DEPT，要求部门名称Dname列取值唯一，部门编号Deptno列为主码

CREATETABLEDEPT(DeptnoNUMERIC(2)，

DnameCHAR(9)UNIQUE,

/*要求Dname列值唯一*/LocationCHAR(10),PRIMARYKEY(Deptno)/*在表级定义实体完整性*/);3.用CHECK短语指定列值应该满足的条件例7Student表的Ssex只允许取“男”和“女”。

CREATETABLEStudent(SnoCHAR(9)PRIMARYKEY，

SnameCHAR(8)NOTNULL,

SsexCHAR(2)CHECK(SsexIN(‘男’,‘女’)),SageSMALLINT,

SdeptCHAR(20));注：当往表中插入元组或修改属性的值时，RDBMS就会检查属性上的约束条件是否被满足，如果不满足则操作被拒绝元组上的条件的定义：在CREATE

TABLE语句中可以用CHECK短语定义元组上的约束，即元组级的限制。同属性值限制相比，元组级的限制可以设置不同属性之间的取值的相互约束条件例9当学生的性别是男时，其名字不能以Ms.打头

CREATETABLEStudent(SnoCHAR(9)，

SnameCHAR(8)NOTNULL,

SsexCHAR(2),SageSMALLINT,

SdeptCHAR(20)PRIMARYKEY(Sno)CHECK(Ssex=‘女’)ORSnameNOTLIKE‘Ms.%’

/*定义了元组中Sname和Ssex两个属性值之间的约束条件*/);当往表中插入元组或修改属性的值时，RDBMS就会检查元组上的约束条件是否被满足，如果不满足则操作被拒绝完整性约束条件分类及含义粒度状态属性列级元组级关系表级静态静态属性列级约束属性列定义数据类型约束数据格式约束值域约束空值约束是规定一个元组的各个列值之间应满足的条件的约束是在一个关系的各个元组之间或若干个关系之间存在的各种联系或约束实体完整性约束参照完整性约束函数依赖约束统计约束动态是修改列定义或列值时的约束是在修改某个元组时对元组的新旧值之间应满足的约束是对某个关系的新、旧状态间应满足的约束条件SQL在CREATE

TABLE语句中提供了完整性约束命名子句CONSTRAINT，用来对完整性约束条件命名，从而可以灵活地增删一个完整性约束条件1.完整性约束命名子句格式

CONSTRAINT＜完整性约束条件名＞[PRIMARYKEY短语|FOREIGNKEY|CHECK短语]例10

建立学生登记表Student，要求学号在90000~99999之间，姓名不能取空值，年龄小于30，性别只能是“男”或“女”CREATETABLEStudent(SnoNUMERIC(6)CONSTRAINTC1CHECK(SnoBETWEEN90000AND99999)，

SnameCHAR(20)CONSTRAINTC2NOTNULL,SageNUMERIC(3)CONSTRAINTC3CHECK(Sage<30),

SsexCHAR(2)CONSTRAINTC4CHECK(SsexIN(‘男’,’女’)),CONSTRAINTStudentKeyPRIMARYKEY(Sno));§5.4完整性约束命名子句例11

建立教师表TEACHER，要求每个教师的应发工资不低于3000元。应发工资实际上就是实发工资列Sal与扣除项Deduct之和

CREATETABLETEACHER(EnoNUMERIC(6)PRIMARYKEY,

EnameCHAR(10),JobCHAR(8),SalNUMERIC(7,2),DeductNUMERIC(7,2),

DeoptnoNUMERIC(7,2),CONSTRAINTEMPFKeyFOREIGNKEY(Deptno)REFERENCESDEPT(Deptno),CONSTRAINTC1CHECK(Sal+Deduct>=3000));外码约束EMPFKey,列级约束C12.修改表中的完整性限制应用ALTER

TABLE语句修改表中的完整性例12

去掉例10中对表Student中对性别的限制

ALTER

TABLE

StudentDROPCONSTRAINTC4;例13

修改表Student中的约束条件，要求学号改为在900000~999999之间，年龄由小于30改为小于40ALTER

TABLE

StudentDROPCONSTRAINTC1;ALTER

TABLE

StudentADDCONSTRAINTC1CHECK(SnoBETWEEN90000AND99999)；ALTER

TABLE

StudentDROPCONSTRAINTC3;ALTER

TABLE

StudentADDCONSTRAINTC3CHECK(Sage<40)；引入触发器原因：在很多情况下，当一个完整性约束被违背时，DBMS除了终止事务外，还需要执行一些其他操作。为了在数据库完整性约束被违背时能够及时执行必要的操作，人们提出了触发器技术§5.6触发器§5.6触发器功能：主要功能：能够实现由主码和外码所不能保证的、复杂的参照完整性强化约束：强制实现业务规则和数据完整性跟踪变化：可以跟踪数据库内的数据变化，阻止非法更新和删除级联运行：可以监测数据库内的操作，并自动将影响蔓延到多个表存储过程的调用：为了响应数据库更新，触发器可以调用一个或多个存储过程，甚至可调用DBMS外的外部过程§5.6触发器触发器（Trigger）：是用户定义在关系表上的一类由事件驱动的特殊过程，或者说是一个能由系统自动执行对数据库修改的语句，亦称为主动规则一旦定义，任何用户对表的增删改操作均由服务器自动激活相应的触发器，在DBMS核心层进行集中的完整性控制，它类似于约束，但较之约束更加灵活，具有更精细和更强大的数据控制能力触发器结构：一个触发器由两部分组成：（1）触发事件：指对数据库的更新等操作，在这些操作进行时，触发器将被激发。（2）触发器条件和动作：条件准则，以确定更新语句是否导致执行触发器动作。条件成立，则由DBMS执行触发器动作。这些动作可以是一系列对数据库的操作触发器的定义

CREATE

TRIGGER＜触发器名＞｛BEFORE|AFTER|＜触发事件＞ON＜表名＞

FOR

EACH｜ROW｜STATEMENT｝

[WHEN＜触发条件＞]＜触发动作体＞其中：1.表的拥有者即创建表的用户才可以在表上创建触发器，并且一个表上只能创建一定数量的触发器2.触发器名：可以包含模式名，也可以不包含模式名。同一模式下，触发器名必须是唯一的；并且触发器名和<表名>必须在同一模式下3.表名：当这个表的数据发生变化时，将激活定义在该表相应<触发事件>的触发器，该表也称为触发器的目标表触发器的定义

CREATE

TRIGGER＜触发器名＞｛BEFORE|AFTER|＜触发事件＞ON＜表名＞

FOR

EACH｜ROW｜STATEMENT｝

[WHEN＜触发条件＞]＜触发动作体＞4.触发事件：可以是INSERT，DELETE或UPDATE，也可以是这几个事件的组合。UPDATE后面还可以有OF<触发列，…>，即进一步指明修改哪些列时触发器激活5.触发器类型：行级触发器和语句级触发器6.触发条件：只有当触发条件为真时，触发动作才执行，反之不执行7.触发动作体：可以是一个匿名PL/SQL过程块，也可以是对已创建存储过程的调用例18

定义一个BEFORE行级触发器，为老师表Teacher定义完整性规则“教授的工资不得低于4000元，如果低于4000元，自动改为4000元”

CREATETRIGGERInsert_Or_Update_SalBEFOREINSERTORUPDATEONTeacherFOREACHROWASBEGIN

IF(new.pJob=‘教授’)AND(new.Sal<4000)THEN

new.Sal:=4000;ENDIF;END;例19

定义AFTER行级触发，当教师表Teacher的工资发生变化后就自动在工资变化表Sal_log中增加一条相应记录

CREATETABLESal_log(EnoNUMERIC(4)referenceteacher(eno),SalNUMERIC(7,2),Usernamechar(10),DateTIMESTAMP);

CREATETRIGGERInsert_SalAFTERINSERTONTeacherFOREACHROWASBEGININSERTINTOSal_log

VALUES(new.Eno,new.Sal,CURRENT_USER,CUR