第5章 数据库.ppt

数据库的完整性数据库的完整性l什么是数据库的完整性数据的正确性正确性和相容性相容性防止不合语义的数据不合语义的数据进入数据库。例:大学生的年龄必须是整数，取值范围为14-50；学生的性别只能是男或女；学生的学号一定是唯一的；学生所在的系必须是学校开设的系；完整性：真实地反映现实世界真实地反映现实世界数据库完整性数据库完整性l数据的完整性和安全性是两个不同概念数据的完整性防止数据库中存在不符合语义的数据，也就是防止数据库中存在不正确的数据防范对象：不合语义的、不正确的数据数据的安全性保护数据库防止恶意的破坏和非法的存取防范对象：非法用户和非法操作数据库完整性数据库完整性(续续)为维护数据库的完整性，DBMS必须：n1.提供定义完整性约束条件的机制提供定义完整性约束条件的机制n2.提供完整性检查的方法提供完整性检查的方法n3.违约处理违约处理 完整性控制机制完整性约束条件定义完整性约束条件定义l完整性约束条件：是数据模型的重要组成部分，约束数据库中数据的语义数据的语义实体完整性实体完整性参照完整性参照完整性用户定义完整性用户定义完整性l整个完整性控制都是围绕完整性约束条件进行的，从这个角度说，完整性约束条件是完整性控制机制的核心核心。lDBMS应能定义这些完整性约束条件（一般由SQL的DDL语句语句来实现），并把它们作为模式的一部分模式的一部分存入数据字典数据字典中完整性控制机制完整性控制机制l完整性检查：检查用户发出的操作请求是否违背了完整性约束条件 违约反应违约反应l如果发现用户的操作请求使数据违背了完整性约束条件，则采取一定的动作动作来保证数据的完整性。第五章第五章 数据库完整性数据库完整性5.1 实体完整性实体完整性5.2 参照完整性参照完整性5.3 用户定义的完整性用户定义的完整性5.4 完整性约束命名字句完整性约束命名字句*5.5 域中的完整性限制域中的完整性限制5.6 触发器触发器5.7 小结小结5.1 实体完整性实体完整性l5.1.1 实体完整性定义实体完整性定义l5.1.2 实体完整性检查和违约处理实体完整性检查和违约处理5.1.1 实体完整性定义实体完整性定义l关系模型的实体完整性CREATE TABLE中用PRIMARY KEY定义l单属性构成的码有两种说明方法 定义为列级约束条件定义为表级约束条件l对多个属性构成的码只有一种说明方法定义为表级约束条件 实体完整性定义实体完整性定义(续续)例1 将Student表中的Sno属性定义为码 (1)在列级定义主码在列级定义主码 CREATE TABLE Student (Sno CHAR(9)PRIMARY KEY，Sname CHAR(20)NOT NULL，Ssex CHAR(2)，Sage SMALLINT，Sdept CHAR(20);实体完整性定义实体完整性定义(续续)(2)在表级定义主码在表级定义主码 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，PRIMARY KEY(Sno，Cno)/*只能在表级定义主码*/);5.1 实体完整性实体完整性l5.1.1 实体完整性定义实体完整性定义l5.1.2 实体完整性检查和违约处理实体完整性检查和违约处理5.1.2 实体完整性检查和违约处理实体完整性检查和违约处理l插入或对主码列进行更新操作时，RDBMS按照实体完整性规则自动进行检查。包括：1.检查主码值是否唯一是否唯一，如果不唯一则拒绝插入或修改2.检查主码的各个属性是否为空各个属性是否为空，只要有一个为空就拒绝插入或修改实体完整性检查和违约处理实体完整性检查和违约处理(续续)l检查记录中主码值是否唯一的一种方法是进行全表扫描全表扫描实体完整性检查和违约处理实体完整性检查和违约处理(续续)l索引索引 第五章第五章 数据库完整性数据库完整性5.1 实体完整性实体完整性5.2 参照完整性参照完整性5.3 用户定义的完整性用户定义的完整性5.4 完整性约束命名字句完整性约束命名字句*5.5 域中的完整性限制域中的完整性限制5.6 触发器触发器5.7 小结小结5.2 参照完整性参照完整性l5.2.1 参照完整性定义参照完整性定义l5.2.2 参照完整性检查和违约处理参照完整性检查和违约处理5.2.1 参照完整性定义参照完整性定义l关系模型的参照完整性定义在CREATE TABLE中用FOREIGN KEY短语定义哪些列为外码外码用REFERENCES短语指明这些外码参照哪些表的主码主码 参照完整性定义参照完整性定义(续续)例如，关系SC中一个元组表示一个学生选修的某门课程的成绩，（Sno，Cno）是主码。Sno，Cno分别参照引用Student表的主码和Course表的主码 例3 定义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 参照完整性检查和违约处理参照完整性检查和违约处理参照完整性检查和违约处理参照完整性检查和违约处理可能破坏参照完整性的情况及违约处理被参照表（例如Student）参照表（例如SC）违约处理可能破坏参照完整性 插入元组拒绝可能破坏参照完整性 修改外码值拒绝删除元组 可能破坏参照完整性拒绝/级联删除/设置为空值修改主码值 可能破坏参照完整性拒绝/级联修改/设置为空值违约处理违约处理l参照完整性违约处理参照完整性违约处理1.拒绝拒绝(NO ACTION)执行执行l默认策略默认策略2.级联级联(CASCADE)操作操作3.设置为空值（设置为空值（SET-NULL）l对于参照完整性，除了应该定义外码，还应定义对于参照完整性，除了应该定义外码，还应定义外码列是否允外码列是否允许空值许空值违约处理违约处理(续续)例例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 /*级联删除级联删除SC表中相应的元组表中相应的元组*/ON UPDATE CASCADE，/*级联更新级联更新SC表中相应的元组表中相应的元组*/FOREIGN KEY(Cno)REFERENCES Course(Cno)ON DELETE NO ACTION /*当删除当删除course 表中的元组造成了与表中的元组造成了与SC表不一致时表不一致时拒绝删除拒绝删除*/ON UPDATE CASCADE /*当更新当更新course表中的表中的cno时，时，级联更新级联更新SC表中相应的元组表中相应的元组*/)；第五章第五章 数据库完整性数据库完整性5.1 实体完整性实体完整性5.2 参照完整性参照完整性5.3 用户定义的完整性用户定义的完整性5.4 完整性约束命名字句完整性约束命名字句*5.5 域中的完整性限制域中的完整性限制5.6 触发器触发器5.7 小结小结5.3 用户定义的完整性用户定义的完整性l用户定义的完整性就是针对某一具体应用某一具体应用的数据必须满足的语义要求 lRDBMS提供，而不必由应用程序承担5.3 用户定义的完整性用户定义的完整性l5.3.1 属性上的约束条件的定义属性上的约束条件的定义l5.3.2 属性上的约束条件检查和违约处理属性上的约束条件检查和违约处理 l5.3.3 元组上的约束条件的定义元组上的约束条件的定义 l5.3.4元组上的约束条件检查和违约处理元组上的约束条件检查和违约处理5.3.1 属性上的约束条件的定义属性上的约束条件的定义lCREATE TABLE时定义列值非空（NOT NULL）列值唯一（UNIQUE）检查列值是否满足一个布尔表达式（CHECK）属性上的约束条件的定义属性上的约束条件的定义(续续)l1.不允许取空值 例5 在定义SC表时，说明Sno、Cno、Grade属性不允许取空值。CREATE TABLE SC （Sno CHAR(9)NOT NULL，Cno CHAR(4)NOT NULL，Grade SMALLINT NOT NULL，PRIMARY KEY(Sno，Cno)，/*如果在表级定义实体完整性，隐含了Sno，Cno不允许取空值，则在列级不允许取空值的定义就不必写了*/）；属性上的约束条件的定义属性上的约束条件的定义(续续)l2.列值唯一 例6 建立部门表DEPT，要求部门名称Dname列取值唯一，部门编号Deptno列为主码 CREATE TABLE DEPT (Deptno NUMERIC(2)，Dname CHAR(9)UNIQUE，/*要求要求Dname列值唯一列值唯一*/Location CHAR(10)，PRIMARY KEY(Deptno)；属性上的约束条件的定义属性上的约束条件的定义(续续)l3.用CHECK短语指定列值应该满足的条件例7 Student表的Ssex只允许取“男”或“女”。CREATE TABLE Student (Sno CHAR(9)PRIMARY KEY，Sname CHAR(8)NOT NULL，Ssex CHAR(2)CHECK(Ssex IN(男男，女女)，/*性别属性性别属性Ssex只允许取只允许取男男或或女女*/Sage SMALLINT，Sdept CHAR(20);5.3 用户定义的完整性用户定义的完整性l5.3.1 属性上的约束条件的定义属性上的约束条件的定义l5.3.2 属性上的约束条件检查和违约处理属性上的约束条件检查和违约处理 l5.3.3 元组上的约束条件的定义元组上的约束条件的定义 l5.3.4元组上的约束条件检查和违约处理元组上的约束条件检查和违约处理5.3.2 属性上的约束条件检查和违约处理属性上的约束条件检查和违约处理l插入元组或修改属性的值时，RDBMS检查属性上的约束条件是否被满足l如果不满足则操作被拒绝执行拒绝执行 5.3 用户定义的完整性用户定义的完整性l5.3.1 属性上的约束条件的定义属性上的约束条件的定义l5.3.2 属性上的约束条件检查和违约处理属性上的约束条件检查和违约处理 l5.3.3 元组上的约束条件的定义元组上的约束条件的定义 l5.3.4元组上的约束条件检查和违约处理元组上的约束条件检查和违约处理5.3.3 元组上的约束条件的定义元组上的约束条件的定义l在CREATE TABLE时可以用CHECK短语定义元组上的约束条件，即元组级的限制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.%)/*定义了元组中定义了元组中Sname和和 Ssex两个属性值之间的约束条件两个属性值之间的约束条件*/)；性别是女性的元组都能通过该项检查，因为Ssex=女成立；当性别是男性时，要通过检查则名字一定不能以Ms.打头5.3 用户定义的完整性用户定义的完整性l5.3.1 属性上的约束条件的定义属性上的约束条件的定义l5.3.2 属性上的约束条件检查和违约处理属性上的约束条件检查和违约处理 l5.3.3 元组上的约束条件的定义元组上的约束条件的定义 l5.3.4 元组上的约束条件检查和违约处理元组上的约束条件检查和违约处理5.3.4 元组上的约束条件检查和违约处理元组上的约束条件检查和违约处理l插入元组或修改属性的值时，RDBMS检查元组上的约束条件是否被满足l如果不满足则操作被拒绝执行拒绝执行 第五章第五章 数据库完整性数据库完整性5.1 实体完整性实体完整性5.2 参照完整性参照完整性5.3 用户定义的完整性用户定义的完整性5.4 完整性约束命名子句完整性约束命名子句*5.5 域中的完整性限制域中的完整性限制5.6 触发器触发器5.7 小结小结5.4 完整性约束命名子句完整性约束命名子句lCONSTRAINT 约束CONSTRAINT PRIMARY KEY短语短语|FOREIGN KEY短语短语|CHECK短语短语完整性约束命名子句完整性约束命名子句(续续)例10 建立学生登记表Student，要求学号在9000099999之间，姓名不能取空值，年龄小于30，性别只能是“男”或“女”。CREATE TABLE Student (Sno NUMERIC(6)CONSTRAINT C1 CHECK(Sno BETWEEN 90000 AND 99999)，Sname CHAR(20)CONSTRAINT C2 NOT NULL，Sage NUMERIC(3)CONSTRAINT C3 CHECK(Sage 30)，Ssex CHAR(2)CONSTRAINT C4 CHECK(Ssex IN(男男,女女)，CONSTRAINT StudentKey PRIMARY KEY(Sno)；在Student表上建立了5个约束条件，包括主码约束（命名为StudentKey）以及C1、C2、C3、C4四个列级约束。完整性约束命名子句完整性约束命名子句(续续)l2.修改表中的完整性限制修改表中的完整性限制使用ALTER TABLE语句修改表中的完整性限制完整性约束命名子句完整性约束命名子句(续续)例13 修改表Student中的约束条件，要求学号改为在900000999999之间，年龄由小于30改为小于40n可以先删除原来的约束条件，再增加新的约束条件 ALTER TABLE Student DROP CONSTRAINT C1;ALTER TABLE Student ADD CONSTRAINT C1 CHECK(Sno BETWEEN 900000 AND 999999)，ALTER TABLE Student DROP CONSTRAINT C3;ALTER TABLE Student ADD CONSTRAINT C3 CHECK(Sage 40)；第五章第五章 数据库完整性数据库完整性5.1 实体完整性实体完整性5.2 参照完整性参照完整性5.3 用户定义的完整性用户定义的完整性5.4 完整性约束命名字句完整性约束命名字句*5.5 域中的完整性限制域中的完整性限制5.6 触发器触发器5.7 小结小结5.5 域中的完整性限制域中的完整性限制lSQL支持域的概念，并可以用CREATE DOMAIN语句建立一个域以及该域应该满足的完整性约束条件。（自定义数据类型）例14建立一个性别域，并声明性别域的取值范围 CREATE DOMAIN GenderDomain CHAR(2)CHECK(VALUE IN(男男，女女);这样例10中对Ssex的说明可以改写为 Ssex GenderDomain例15建立一个性别域GenderDomain，并对其中的限制命名 CREATE DOMAIN GenderDomain CHAR(2)CONSTRAINT GD CHECK(VALUE IN(男，女);域中的完整性限制域中的完整性限制(续续)例16删除域GenderDomain的限制条件GD。ALTER DOMAIN GenderDomain DROP CONSTRAINT GD;例17在域GenderDomain上增加限制条件GDD。ALTER DOMAIN GenderDomain ADD CONSTRAINT GDD CHECK(VALUE IN(1，0);通过例16和例17，就把性别的取值范围由(男，女)改为(1，0)第五章第五章 数据库完整性数据库完整性5.1 实体完整性实体完整性5.2 参照完整性参照完整性5.3 用户定义的完整性用户定义的完整性5.4 完整性约束命名字句完整性约束命名字句*5.5 域中的完整性限制域中的完整性限制5.6 触发器触发器5.7 小结小结触发器触发器l触发器（Trigger）是用户定义在关系表上的一类由事件驱动事件驱动的特殊过程过程由服务器自动激活由服务器自动激活可以进行更为复杂的检查和操作，具有更精细和更强大的数据控制能力 5.6 触发器触发器l5.6.1 定义触发器定义触发器 l5.6.2 激活触发器激活触发器 l5.6.3 删除触发器删除触发器 5.6.1 定义触发器定义触发器lCREATE TRIGGER语法格式 CREATE TRIGGER ON|FOR|INSTEAD OF|AFTER AS 定义触发器定义触发器(续续)l定义触发器的语法说明:1.创建者：表的拥有者2.触发器名3.表名/视图名：触发器的目标表/目标视图4.触发事件：INSERT、DELETE、UPDATE5.触发器类型行级触发器（FOR EACH ROW，Oracle）语句级触发器（FOR EACH STATEMENT，Oracle）INSTEAD OF触发器触发器AFTER触发器触发器定义触发器定义触发器(续续)l例如,假设在例11的TEACHER表上创建了一个AFTER UPDATE触发器。如果表TEACHER有1000行，执行如下语句：UPDATE TEACHER SET Deptno=5;如果该触发器为语句级触发器，那么执行完该语句后，触发动作只发生一次如果是行级触发器，触发动作将执行1000次（Oracle触发器）定义触发器定义触发器(续续)l6.触发条件（Oracle触发器）触发条件为真省略WHEN触发条件l7.触发动作体触发动作体可以是一个匿名PL/SQL(T-SQL)过程块也可以是对已创建存储过程的调用定义触发器定义触发器(续续)例例18 定义一个定义一个BEFORE行级（行级（Oracle）触发器，为教师表触发器，为教师表Teacher定义完整性规则定义完整性规则“教授的工资不得低于教授的工资不得低于4000元，如果低元，如果低于于4000元，自动改为元，自动改为4000元元”。CREATE TRIGGER Insert_Or_Update_Sal ON Teacher FOR INSERT,UPDATE /*触发事件是插入或更新操作触发事件是插入或更新操作*/AS /*定义触发动作体定义触发动作体*/declare Tno int,Job char(8),Sal numeric(7,2)select Tno=Tno,Job=Job,Sal=Sal from inserted?if Job=教授教授 and Sal4000 update Teacher set Sal=4000 where Tno=Tno 定义触发器定义触发器(续续)例例19定义定义AFTER行级（行级（Oracle）触发器，当教师表触发器，当教师表Teacher的的工资发生变化后就自动在工资变化表工资发生变化后就自动在工资变化表Sal_log中增加一条相应记录中增加一条相应记录 首先建立工资变化表首先建立工资变化表Sal_log CREATE TABLE Sal_log (Tno int references teacher(Tno)，Sal numeric(7,2)，Username char(10)，Date datetime )；定义触发器定义触发器(续续)例例19（续）（续）CREATE TRIGGER Insert_Sal ON Teacher AFTER INSERT/*触发事件是触发事件是INSERT*/AS INSERT INTO Sal_log select Tno,Sal,USER,getdate()from inserted定义触发器定义触发器(续续)例例19（续）（续）CREATE TRIGGER Update_Sal ON Teacher AFTER UPDATE/*触发事件是触发事件是UPDATE*/AS declare Tno int,new_sal numeric(7,2),old_sal numeric(7,2)select Tno=Tno,new_sal=sal from inserted select old_sal=sal from deleted where Tno=Tno IF(new_salold_sal)INSERT INTO Sal_log SELECT Tno,Sal,USER,getdate()FROM inserted where Tno=Tno5.6 触发器触发器l5.6.1 定义触发器定义触发器 l5.6.2 激活触发器激活触发器 l5.6.3 删除触发器删除触发器 5.6.2 激活触发器激活触发器l触发器的执行，是由触发事件激活的，并由数据库服务器自动执行l一个数据表上可以定义多个触发器同一个表上的多个触发器激活时遵循如下的执行顺序（Oracle触发器）l（1）执行该表上的BEFORE触发器；l（2）激活触发器的SQL语句；l（3）执行该表上的AFTER触发器。激活触发器激活触发器(续续)例20执行修改某个教师工资的SQL语句，激活上述定义的触发器。UPDATE Teacher SET Sal=800 WHERE Ename=陈平;执行顺序是：执行触发器Insert_Or_Update_Sal执行SQL语句“UPDATE Teacher SET Sal=800 WHERE Ename=陈平;”执行触发器Insert_Sal；执行触发器Update_Sal 5.6 触发器触发器l5.6.1 定义触发器定义触发器 l5.6.2 激活触发器激活触发器 l5.6.3 删除触发器删除触发器 5.6.3 删除触发器删除触发器l删除触发器的SQL语法：DROP TRIGGER ON l触发器必须是一个已经创建的触发器，并且只能由具有相应权限的用户删除。例21 删除教师表Teacher上的触发器Insert_Sal DROP TRIGGER Insert_Sal ON Teacher;第五章第五章 数据库完整性数据库完整性5.1 实体完整性实体完整性5.2 参照完整性参照完整性5.3 用户定义的完整性用户定义的完整性5.4 完整性约束命名字句完整性约束命名字句*5.5 域中的完整性限制域中的完整性限制5.6 触发器触发器5.7 小结小结5.7 小结小结l数据库的完整性是为了保证数据库中存储的数据数据库的完整性是为了保证数据库中存储的数据是正确的，是符合语义的是正确的，是符合语义的lRDBMS完整性实现的机制完整性实现的机制完整性约束定义机制完整性约束定义机制完整性检查机制完整性检查机制违背完整性约束条件时违背完整性约束条件时RDBMS应采取的动作（违约处应采取的动作（违约处理）理）