第五章 数据库完整性(2010)

nullnull直属数理系 冯莉第五章 数据库完整性第五章 数据库完整性实体完整性参照完整性用户定义的完整性完整性约束命名子句触发器数据库完整性数据库完整性数据库是现实世界状态的正确的反映. 那么如何确保数据库能正确反映现实世界呢? 时间可能造成数据失真的因素可能造成数据失真的因素数据进入系统时的错误 与现实世界状态不符合的、不正确的数据 系统故障 丢失了数据 相互干扰 被他人覆写了 恶意破坏 非法入侵与修改数据库完整性数据库完整性有哪些具体的违反现实世界规则的情况？ （以学生选课数据库为例进行讨论） 学生的性别只能取“男”或者“女” 成绩应在0到100分之间 学生学号应唯一 学生所选课程应是存在的课程 学生的名字不能取“赵C” 其他数据库完整性数据库完整性数据库的完整性 数据的正确性和相容性。 即：防止不符合语义的数据进入数据库。DBMS必须提供完整性控制机制 提供定义完整性约束条件的机制（核心） 提供完整性检查方法 违约处理5.1 实体完整性5.1 实体完整性关系模型的实体完整性 CREATE TABLE中用PRIMARY KEY定义 单属性构成的码有两种说明方法: 定义为列级约束条件; 定义为 表 关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf 级约束条件. 对多个属性构成的码只有一种说明方法: 定义为表级约束条件 5.1.1实体完整性定义5.1.1 实体完整性定义5.1.1 实体完整性定义 (1)在列级定义主码 CREATE TABLE Student ( Sno CHAR(9) PRIMARY KEY， Sname CHAR(20) NOT NULL， Ssex CHAR(2) ， Sage SMALLINT， Sdept CHAR(20) );例1: 将Student表中的Sno属性定义为码.5.1.1 实体完整性定义5.1.1 实体完整性定义(2)在表级定义主码 CREATE TABLE Student ( Sno CHAR(9)， Sname CHAR(20) NOT NULL， Ssex CHAR(2) ， Sage SMALLINT， Sdept CHAR(20)， PRIMARY KEY (Sno) ); 5.1.1 实体完整性定义5.1.1 实体完整性定义 CREATE TABLE SC (Sno CHAR(9) NOT NULL， Cno CHAR(4) NOT NULL， Grade SMALLINT， PRIMARY KEY (Sno,Cno) /*只能在表级定义主码*/ ); 例2:将SC表中的Sno，Cno属性组定义为码.5.1.2 实体完整性检查和违约处理5.1.2 实体完整性检查和违约处理插入或对主码列进行更新操作时，RDBMS按照实体完整性规则自动进行检查。包括： 1. 检查主码值是否唯一，如果不唯一则拒绝插入或修改; 2. 检查主码的各个属性是否为空，只要有一个为空就拒绝插入或修改. 5.1.2 实体完整性检查和违约处理5.1.2 实体完整性检查和违约处理检查记录中主码值是否唯一的方法:全表扫描全表扫描缺点：十分耗时 为避免对基本表进行全表扫描，RDBMS核心一般都在主码 上自动建立一个索引5.1.2 实体完整性检查和违约处理5.1.2 实体完整性检查和违约处理RDBMS一般都在主码上自动建立一个索引 如果新插入记录的主码值是25 通过主码索引，从B+树的根结点开始查找 读取3个结点： 根结点（51） 中间结点（12 30） 叶结点（15 20 25） 该主码值已经存在，不能插入这条记录5.2 参照完整性5.2 参照完整性关系模型的参照完整性定义 在CREATE TABLE中用FOREIGN KEY短语定义哪些列为外码; 用REFERENCES短语指明这些外码参照哪些表的主码 .5.2.1 参照完整性定义5.2.1 参照完整性定义5.2.1 参照完整性定义例如:关系SC中一个元组表示一个学生选修的某门课程的成绩，（Sno，Cno）是主码。Sno，Cno分别参照引用Student表的主码和Course表的主码.Student( sno, sname, ssex, sage, sdept) Course( cno, cname, cpno, ccredit) Sc( sno,cno, grade)5.2.1 参照完整性定义5.2.1 参照完整性定义 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) );例3: 定义SC中的参照完整性5.2.2 参照完整性检查和违约处理5.2.2 参照完整性检查和违约处理可能破坏参照完整性的情况及违约处理5.2.2 参照完整性检查和违约处理5.2.2 参照完整性检查和违约处理 参照完整性违约处理1. 拒绝(NO ACTION)执行 2. 级联(CASCADE)操作3. 设置为空值（SET NULL）默认策略 对于参照完整性，除了应该定义外码，还应定义外码列是否允许空值.5.2.2 参照完整性检查和违约处理5.2.2 参照完整性检查和违约处理CREATE TABLE SC ( Sno VARCHAR(9) NOT NULL, Cno VARCHAR(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表中相应的元组*/ )例4: 显式说明参照完整性的违约处理示例 5.3 用户定义的完整性5.3 用户定义的完整性用户定义的完整性就是针对某一具体应用的数据必须满足的语义要求 . RDBMS提供相应的定义和检验机制，不必由应用程序承担.5.3.1 属性上的约束条件的定义5.3.1 属性上的约束条件的定义CREATE TABLE时定义 列值非空（NOT NULL） 列值唯一（UNIQUE） 检查列值是否满足一个布尔表达式（CHECK）5.3.1 属性上的约束条件的定义5.3.1 属性上的约束条件的定义1 不允许取空值 CREATE TABLE SC （ Sno CHAR(9) NOT NULL， Cno CHAR(4) NOT NULL， Grade SMALLINT NOT NULL， PRIMARY KEY (Sno， Cno)， /* 如果在表级定义实体完整性，隐含了Sno，Cno不允许取空值，则在列级不允许取空值的定义就不必写了 * / );例5:在定义SC表时，说明Sno、Cno、Grade属性不允许取空值。5.3.1 属性上的约束条件的定义5.3.1 属性上的约束条件的定义2 列值唯一 CREATE TABLE DEPT ( Deptno NUMERIC(2), Dname CHAR(9) UNIQUE, /*要求Dname列值唯一*/ Location CHAR(10), PRIMARY KEY (Deptno) );例6:建立部门表DEPT，要求部门名称Dname列取值唯一，部门编号Deptno列为主码.5.3.1 属性上的约束条件的定义5.3.1 属性上的约束条件的定义3 用CHECK短语指定列值应该满足的条件 CREATE TABLE Student ( Sno VARCHAR(9) PRIMARY KEY, Sname VARCHAR(8) NOT NULL, Ssex CHAR(2) CHECK (Ssex IN ('男','女') ) , /*性别属性Ssex只允许取'男'或'女' */ Sage SMALLINT, Sdept CHAR(20) );例7: Student表的Ssex只允许取“男”或“女”。5.3.1 属性上的约束条件的定义5.3.1 属性上的约束条件的定义插入元组或修改属性的值时，RDBMS检查属性上的约束条件是否被满足; 如果不满足则操作被拒绝执行 . 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)， FOREIGN KEY (Sno) REFERENCES Student(Sno)， FOREIGN KEY (Cno) REFERENCES Course(Cno) ); 例8: SC表的Grade值应该在0和100之间。5.3.3 元组上的约束条件的定义5.3.3 元组上的约束条件的定义在CREATE TABLE时可以用CHECK短语定义元组上的约束条件，即元组级的限制. 同属性值限制相比，元组级的限制可以设置不同属性之间的取值的相互约束条件 . CREATE TABLE Student3 ( Sno VARCHAR(9), Sname VARCHAR(8) NOT NULL, Ssex CHAR(2) CHECK (Ssex IN (‘男’,’女’)), Sage SMALLINT, Sdept CHAR(20), PRIMARY KEY (Sno), CHECK (Ssex='女' OR Sname NOT LIKE 'Ms.%') /*定义了元组中Sname和 Ssex两个属性值之间的约束条件*/ );例9:当学生的性别是男时，其名字不能以Ms.打头。 5.3.4 元组上的约束条件条件检查和违约处理5.3.4 元组上的约束条件条件检查和违约处理插入元组或修改属性的值时，RDBMS检查元组上的约束条件是否被满足; 如果不满足则操作被拒绝执行. 5.4 完整性约束命名子句5.4 完整性约束命名子句CONSTRAINT 约束 CONSTRAINT <完整性约束条件名> ［PRIMARY KEY短语 |FOREIGN KEY短语 |CHECK短语 ］1 完整性约束命名子句5.4 完整性约束命名子句5.4 完整性约束命名子句 CREATE TABLE Student4 ( 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) );例10:建立学生登记表Student，要求学号在90000 ~99999之间，姓名不能取空值，年龄小于30，性别只能是“男”或“女”。 5.4 完整性约束命名子句5.4 完整性约束命名子句2 修改表中的完整性限制 使用ALTER TABLE语句修改表中的完整性限制. 可以先删除原来的约束条件，再增加新的约束条件.5.4 完整性约束命名子句5.4 完整性约束命名子句 ALTER TABLE Student4 DROP CONSTRAINT C1; ALTER TABLE Student ADD CONSTRAINT C1 CHECK (Sno BETWEEN 900000 AND 999999)例13:修改表Student中的约束条件，要求学号改为在900000~999999之间，年龄由小于30改为小于40. ALTER TABLE Student4 DROP CONSTRAINT C3; ALTER TABLE Student ADD CONSTRAINT C3 CHECK (Sage < 40)5.6 触发器5.6 触发器触发器（Trigger）是用户定义在关系表上的一类由事件驱动的特殊过程. 任何用户对表的增,删,改操作均由服务器自动激活相应触发器，进行集中的完整性检查; 比约束更加灵活，可以进行更为复杂的检查和操作，具有更精细和更强大的数据控制能力; 注意:不同的RDBMS实现触发器的语法不同.补充:Sql server 中创建触发器(1)补充:Sql server 中创建触发器(1) CREATE TRIGGER 触发器名 ON { 表名 | 视图名 } [ WITH ENCRYPTION ] { { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }       [ NOT FOR REPLICATION ]   AS      [ 触发器代码 ] } } CREATE TRIGGER 语法格式:补充: Sql server 中创建触发器(2)补充: Sql server 中创建触发器(2)定义触发器的语法说明: 1. 创建者：表的拥有者 2. 触发器名 3. 表名：触发器的目标表 4. 触发事件：INSERT、DELETE、UPDATE 5. WITH ENCRYPTION :表示加密触发器代码,使其它用户无法查询到触发器的创建语句. 6. NOT FOR REPLICATION :表示在表的复制过程中对表的修改将不会激活触发器.补充: Sql server 中创建触发器(3)补充: Sql server 中创建触发器(3) 触发器分类: INSTEAD OF:在表或视图上执行增,删,改操作时,用该触发器中的SQL语句代替原语句,常用于视图操作. AFTER:在所有SQL语句执行成功后激活,如果只使用了FOR关键字,则默认为AFTER触发器.使用AFTER时,不能在视图上定义触发器.(使用较多)补充: Sql server 中创建触发器(4)补充: Sql server 中创建触发器(4)不能在触发器中使用的SQL语句有: 所有数据库库及对象的创建语句(CREATE); 所有DROP语句; 数据库对象修改语句:ALTER TABLE,ALTER DATABASE; 对象权限 GRANT,REVORK; 数据库装载操作； 创建临时表的操作； 等等…… 触发器的限制条件:补充: Sql server 中创建触发器(5)补充: Sql server 中创建触发器(5)当表的修改不符合触发器设定的规则时，触发器就认为修改无效，回滚事务，既撤消对表的修改操作． 语法：　ROLLBACK [TRAN] 若使该语句撤消操作时给出错误信息，可以提示： ROLLBACK TRAN PRINT ’．．．．．．’ RAISERROR(‘．．．．．．’，１６，１０)ROLLBACK触发器的事务回滚:补充: Sql server 中创建触发器(6)补充: Sql server 中创建触发器(6)1）由SQL SERVER自动执行； 2）在触发器所依存的基础表的所有修改操作完成之后，数据库事务会激活触发器； 3）对每个修改语句，触发器只执行一次，即使该操作同时修改了多个数据行。执行触发器:补充: Sql server 中创建触发器(7)补充: Sql server 中创建触发器(7)企业管理器中创建触发器的步骤: 打开将被使用的服务器,选择并展开要创建触发器的数据库,并选择要创建触发器的表; 右键单击->所有任务->管理触发器; 在打开的对话框中输入创建代码. 查询 分析 定性数据统计分析pdf销售业绩分析模板建筑结构震害分析销售进度分析表京东商城竞争战略分析 器中创建触发器的步骤: 选择要创建触发器的数据库; 在代码区域编写T-SQL语句; 执行该语句. 补充: Sql server 中使用触发器(１)补充: Sql server 中使用触发器(１)往基础表中插入数据时触发执行,该数据行同时复制到基础表和内存中的INSERTED表. 用途: 1) 检验要插入的数据行是否符合规则; 2) 在插入的数据行中增加数据; 3) 级联改变数据库中其他数据表. INSERT触发器:补充: Sql server 中使用触发器(2)补充: Sql server 中使用触发器(2)CREATE TRIGGER tr1_sc on SC for insert as declare @Grade int select @Grade=Grade from INSERTED if @Grade<60 begin rollback tran raiserror('Grade must be greater than 60',16,10) end go例1: 为SC表定义一个insert触发器，规定输入的成绩不得低于60,否则不能插入表中。补充: Sql server 中使用触发器(3)补充: Sql server 中使用触发器(3)变量的声明格式: DECLARE @变量名 数据类型为变量赋值: 1) SET @变量名=表达式 2) SELECT @变量名=表达式 FROM 表名 WHERE 条件表达式注: 若SELECT语句返回多个值,则局部变量取最后一个返回值.补充: Sql server 中使用触发器(4)补充: Sql server 中使用触发器(4)从基础表中删除数据时触发执行,执行触发器后,自动将删除的数据行保存在DELETED表中,也即数据行并没有消失,仍可在SQL语句中引用. 用途: 1) 防止删除数据库中的某些数据行; 2) 级联删除数据库中其他表的数据行. DELETE触发器:补充: Sql server 中使用触发器(5)补充: Sql server 中使用触发器(5)CREATE TRIGGER tr2_sc on SC for delete as declare @Sno char(9) select @Sno=Sno from DELETED if @Sno='200215121' begin Rollback tran Print 'the row is not deleted!' end go例2: 为SC表定义一个delete触发器，规定当用户从表中删除数据时,禁止删除学号为’200215121’的学生选课纪录。补充: Sql server 中使用触发器(6)补充: Sql server 中使用触发器(6)在用户发出update时触发执行.执行触发器后,原来的数据行保存在DELETED表中,同时基础表更新后的数据行也在INSERTED中保存备份. 可利用DELETED表和 INSERTED表,获取更新前后的数据行,完成比较操作. UPDATE触发器:补充: Sql server 中使用触发器(7)补充: Sql server 中使用触发器(7)CREATE TRIGGER tr3_sc on SC for update as declare @Grade int select @Grade=Grade from INSERTED if @Grade<60 begin update sc set Grade=60 where Grade=@Grade end go例3: 为SC表定义一个update触发器，规定当用户将成绩修改为60分以下时,一律调整为60分。补充: Sql server 中使用触发器(8)补充: Sql server 中使用触发器(8)CREATE TRIGGER tr4_sc on SC for update as declare @Grade int if update(Sno) begin print ‘can’t change student ID!’ ROLLBACK TRAN end 例4: 为SC表定义一个update触发器，使其先检测更新的列,当更新sno列时,禁止更新;当更新grade列时,规定当用户将成绩修改为60分以下时,利用事务回滚撤消更新操作。 函数 excel方差函数excelsd函数已知函数     2 f x m x mx m      2 1 4 2拉格朗日函数pdf函数公式下载 ： UPDATE(列名)补充: Sql server 中使用触发器(9)补充: Sql server 中使用触发器(9)If update(grade) begin select @Grade=Grade from INSERTED if @Grade<60 begin ROLLBACK TRAN print ‘The row can not be updated!’ end end go补充: Sql server 中使用触发器(10)补充: Sql server 中使用触发器(10)select * from student update student set sdept='MA‘ where sdept='CS' 例5: 为Student表定义一个update触发器，要求实现在用户对表student进行update操作时执行,显示共修改了多少行数据。create trigger tr1_stu on student for update as declare @msg varchar(100) select @msg=str(@@rowcount)+'student has been updated !' print @msg return 系统定义的全局变量,返回所执行的最后一个sql语句所影响的行数.补充: Sql server 中使用触发器(11)补充: Sql server 中使用触发器(11)替代操作触发器,当视图执行对基础表的插入,删除和更新操作时,用触发器的操作替代视图的操作. 由于视图有时显示的是表中的部分列,因此用视图修改基础表中的数据行时有可能导致失败. 解决方法之一就是针对视图的修改操作建立INSTEAD 触发器,通过触发器插入所缺的列值,完成更新. INSTEAD 触发器:补充: Sql server 中使用触发器(12)补充: Sql server 中使用触发器(12)CREATE view V_stud as select sname, ssex, sage, sdept from Student例6: 首先建立一个基于Student 的视图。Insert into V_stud values ('张一','男',21,'IS') 通过视图向基础表中插入数据行补充: Sql server 中使用触发器(13)补充: Sql server 中使用触发器(13)create trigger tr1_stud on V_stud instead of insert as declare @sno char(9), @sname char(20), @ssex char(2), @sage smallint, @sdept char(5) select @sname=sname,@ssex=ssex, @sage=sage, @sdept=sdept from INSERTED set @sno='200215140' insert into student values(@sno,@sname,@ssex,@sage,@sdept) go建立INSTEAD 触发器补充: Sql server 中使用触发器(14)补充: Sql server 中使用触发器(14)针对一个表可以将多个触发器（UPDATE，INSERT，DELETE）组合在一起使用，形成复合触发器，使数据库的管理工作变得更加简便。复合触发器:补充: Sql server 中使用触发器(15)补充: Sql server 中使用触发器(15)create trigger tr2_stud on student for update,delete as declare @sno char(9) select @sno=sno from DELETED if @sno='200215140' begin print 'Can not change this student!' ROLLBACK TRAN end go例7: 创建一个复合触发器。补充: Sql server 中修改和删除触发器(1)补充: Sql server 中修改和删除触发器(1)只有数据库所有者才能修改触发器; 系统实际上是先删除原触发器,然后在同样的表上重新建同名的触发器. 修改触发器的SQL语法:删除触发器的SQL语法： DROP TRIGGER <触发器名>例8 .删除触发器tr3_sc DROP TRIGGER tr3_sc补充: Sql server 中修改和删除触发器(2)补充: Sql server 中修改和删除触发器(2)ALTER TRIGGER 触发器名 ON { 表名 | 视图名 } [ WITH ENCRYPTION ] { { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }       [ NOT FOR REPLICATION ]   AS      [ 触发器代码 ] } } 小结小结数据库的完整性是为了保证数据库中存储的数据是正确的.能够实现完整性约束条件的定义:实体完整性参照完整性用户自定义的完整性掌握触发器的定义及执行.