第二章 数据库的完整性控制

第二章 数据库的完整性控制 数据完整性(Data Integrity)是指数据的精确性(accuracy)和可靠性(reliability)，它是防止数据库中存在不符合语义规定的数据和防止因错误的信息的输入造成无效操作或错误信息而提出的，保证在一个应用程序更新数据的过程中数据的语义正确性。 数据完整性主要分为三类:实体完整性(Entity Integrity)，参照完整性(Referential Integrity)以及用户定义的完整性(User-defined Integrity)，其中用户定义的完整性主要包括域完整性(Domain Integrity)和其他自定义完整性。 为维护数据库德完整性，数据库管理系统(DBMS)必须提供一种机制来检查数据库中的数据，看其是否满足语义规定的条件。这些加在数据库数据上的语义约束条件称为数据库完整性规则，它们作为模式的一部分存入数据库中。 完整性控制是指对数据库进行更新操作，要遵守完整性规则，才能保证数据的语义正确性，防止数据库中存在不符合语义的数据。目的是在合法用户访问数据库的过程中，保证数据的正确性和准确性。完整性检查机制是指在DBMS中检查数据是否满足完整性条件的机制。由DBMS在执行更新动作时，检查是否满足预定的完整性约束条件，来进行控制。广义的完整性控制包括故障恢复、并发控制。而一般所指的完整性控制是指基于数据库的完整性约束规则，如:实体完整性、引用完整性等。SQL中两种主要的数据完整性控制机制是指完整性约束规则的定义和检查以及触发器(Trigger)机制。 完整性控制机制应具有三个功能: (1) 定义功能，即提供定义完整性约束条件的机制; (2) 检查功能，即检查用户发出的操作请求书否违反了约束条件; (3) 如果发现用户操作请求使数据违背了完整性约束条件，则采取一定的动作来保 证数据的完整性。 完整性控制机制的工作原理基本上分为两类，一种是定义完整性时就立刻进行检查的，例如实体完整性的定义;另外一种是定义完整性之后进行检查的，例如参照完整性的定义。 SQL Server提供了一些工具来帮助用户实现数据完整性，其中最主要的是:规则(Rule)，默认值(Default)，约束(Constraint)和触发器(Trigger) 实验 报告 软件系统测试报告下载sgs报告如何下载关于路面塌陷情况报告535n,sgs报告怎么下载竣工报告下载 2.1 实体完整性 实验目的: 学习实体完整性的建立，以及实践违反实体完整性的结果。 实验原理: 1、实体完整性(Entity Integrity)定义 实体完整性规定表的每一行在表中是唯一的实体。SQL语法中，表中的UNIQUE、PRIMARY KEY和IDENTITY约束就是实体完整性的体现。 实体完整性规则:每个关系中主码的任何属性不能取空值。 注意:空值为NULL，不是0，也不是空格，而是一个“不知道”或“不确定”的数据值。 2、实施完整性检查的时机 实施完整性规则检查的时机分为立即检查和延迟检查(Immediate or deferred Checking)，只有选择正确的检查时机才能保证语义的正确性，即保证数据的完整性。 实体完整性规则检查的时机是立即检查的，而参照完整性和触发器一般都是延迟检查。 3、事务处理 事务是一组数据库操作的集合，这些操作要么一起成功，要么一起失败。操作的提交或回退是一同生效的。事务处理的概念对维护数据的完整性和一致性是十分重要的。 数据库操作(如INSERT、UPDATE和DELETE)如果是一个事务中的操作，那么它们要在事务控制之下完成。数据库对象的begin Transaction、commit Transaction和rollback Transaction方法分别用来启动、提交和回退事务。 实验内容: (1) 在数据库School中建立表Stu_Union，进行主键约束，在没有违反实体完整性的前 提下插入并更新一条记录。 (2) 演示违反实体完整性的插入操作。 (3) 演示违反实体完整性的更新操作。 (4) 演示事务的处理，包括事务的建立、处理以及出错时的事务回滚。 (5) 通过建立Scholarship表，插入数据，演示当与现有的数据环境不等时，无法建立实 体完整性以及参照完整性。 实验步骤: 代码实现参见数据库实验习题2.1.sql文档 心得体会 决胜全面小康心得体会学党史心得下载党史学习心得下载军训心得免费下载党史学习心得下载 : 1、 学习了实体完整性的相关内容，加深了对于这部分 知识点 高中化学知识点免费下载体育概论知识点下载名人传知识点免费下载线性代数知识点汇总下载高中化学知识点免费下载 的认识; (实验课本第59页—— 思考:为什么sno置为‘’没有违反非空的约束, 答:SQL系统默认这里定义的‘’为一个值;如果执行sno=NULL，则将被系统拒绝) 实验2.2 参照完整性 实验目的: 学习建立外键，以及利用FOREIGN KEY---REFERENCES子句以及各种约束保证参照完整性。 实验原理: 1) 参照完整性(Referential Integrity) 参照完整性是指两个表的主关键字和外关键字的数据对应一致。它确保了有主关键字的表中对应其他表的外关键字的行存在，即保证了表之间的数据的一致性，防止了数据丢失或无意义的数据在数据库中扩散。参照完整性是建立在外关键字和主关键之间或外关键字和唯一性关键字之间的关系上的。 参照完整性规则:关系R的外来码取值必须是关系S中某个元组的主码值，或者可以是一个“空值”。 定义外键时定义参照完整性、约束参照表A和被参照表B。对于违反参照完整性的操作有时候并不是简单拒绝执行，而是接受该操作，同时执行必要的附加操作。DBMS提供机制来定义是否必须制定外键的具体值而非空值。主键列和外键列可以有不同的名字，空值的要求也可以不一致，默认值也可以不同，但数据类型必须相同。 2) SL Server中完整性的体现 在SQL Server中参照完整性作用表现在如下几个方面: ?禁止在从表中插入包括主要中不存在的外关键字的数据行。 ?禁止会导致从表中的相应值孤立的主表中的外关键字值改变。 ?禁止删除在从表中的有对应记录的主表记录。 3) SQL语句中删除和插入基本关系元组 (1) 在被参照关系中删除元组的三种控制方式: ?级联删除(CASCADES):将参照关系中与被参照关系中要删除的元组主键值相同的元组一起删除。 ?受限删除(RESTRICTED):只有参照关系中没有元组与被参照关系中要删除的元组主键值相同时才执行删除操作，否则拒绝。 ?置空值删除(SET NULL):删除被参照关系中的元组，同时将参照关系中相应元组的外键值置空。 (2) 在参照关系中插入元组。 ?受限插入:只是被参照关系中有元组与参照关系中要插入元组外键值相同时，才执行插入操作，否则拒绝。 ?递归插入:插入元组外键值在被参照关系中没有相同元组，则首先向被参照关系插入元组，其主键值等于参照关系插入元组的外键值，然后再向参照关系插入元组。 4) DBMS对参照完整性进行检查的四种情况 (1) 在四种情况下DBMS要进行检查，分别是对参照表进行插入和修改以及对被参照表 进行删除和修改。 (2) SQL Server四种情况违反参照完整性的处理方法如表所示: 相关操作 INSERT DELETE UPDATE 被参照表 不需要检查 ON DELETE„(用户显示定ON UPDATE„(用户显示方 义的方式，提供两种:cascade 式，提供两种:cascade 和no 和no action)OR default(系action)OR default(系统默认 统默认的方式no action) 的方式no action) 参照表 拒绝执行 不需要检查 拒绝执行 5) 参照完整性的特殊问题 (1) 表的自参考问题。 问题1:无法定义。 处理方法:先用create table创建主键约束，再用alter table创建外键约束。 问题2:容易造成无法启动的情况。(系统通过事务完毕后再检查) (2) 两张表互相参照的问题。 问题1:无法定义。 处理方法:同表的自参照问题的解决方法相同。 问题2:容易造成无法启动的情况。(系统通过事务完毕后再检查) (3) 既是外键又是主键中的属性。 处理方法:既要遵从实体完整性也要遵从参照完整性。 实验内容: (1) 为演示参照完整性，建立表Course，令cno为主键，并在Stu_Union中插入数据。 为下面的实验步骤做预先准备。 (2) 建立表SC，令sno和cno分别为参照Stu_Union表以及Course表的外键，设定为级 联删除，并令(sno，cno)为其主键。在不违反参照完整性的前提下，插入数据。 (3) 演示违反参照完整性的插入数据。 (4) 在Stu_Union中删除数据，演示级联删除。 (5) 在Course中删除数据，演示级联删除。 (6) 为了演示多重级联删除，建立Stu_Union表，令stu_id为参照Stu_Union表的外键， 令card_id为其主键，并插入数据。 (7) 为了演示多重级联删除，建立ICBC_Card表，令stu_card_id为参照Stu_Card表的 外键，令bank_id为其主键，并插入数据。 (8) 通过删除students表中的一条记录，演示三个表的多重级联删除。 (9) 演示事务中进行多重级联删除失败的处理。修改ICBC_Card表的外键属性，使其变 为On delete No action，演示事务中通过删除students表中的一条记录，多重级联删 除失败，整个事务回滚到事务的初始状态。 (10) 演示互参照问题以及其解决方法。要建立教师授课和课程指定教师停课关系的两张 表，规定一个教师可以授多门课，但是每个课程只能指定一个教师去听课，所以要 为两张表建立相互之间的参照关系。 实验步骤: 代码实现参见数据库实验习题2.2.sql文档 心得体会: 1、学习了参照完整性的相关内容，加深了对于这部分知识点的认识; (习题: (1)用alter table语句将SC表中的on delete cascade 改为on delete restrict。重新插入SC的数据。重复本实验的实验步骤中(4)(5)，观察结果， 分析 定性数据统计分析pdf销售业绩分析模板建筑结构震害分析销售进度分析表京东商城竞争战略分析 原因。 (2)用alter table语句将SC表中的on delete cascade 改为on delete set NULL。重新插入SC的数据。重复本实验的实验步骤中(4)(5)，观察结果，分析原因。 解答:SQL不提供on delete restrict/on delete set NULL，用on delete no action;结果:数据库不允许删除STU_UNION表以及NEW_COUSE表中对应的元组;原因:(1)由于on delete restrict的约束，数据库不允许任何引用关系存在对应元组的删除操作(2)约束on delete set NULL是将要删除的对应元组的外键置空值，如果sno和cno不是SC表的主键，删除操作是可以完成的，但是由于主键不可以取空值，所以删除操作不能进行。 ) 实验2.3用户自定义完整性 实验目的: 学习用户自定义约束，并实践用户自定义完整性，利用SQL查询分析器用短语NOT NULL、UNIQUE、CHECK保证用户定义完整性。 实验原理: 1)用户自定义完整性(User-defined Integrity) 不同的关系数据库系统根据其应用环境的不同，往往需要一些特殊的约束条件。用户自定义的完整性即是针对某个特定关系数据库的约束条件，它反映某一具体应用所涉及的数据必须满足的语义要求。它主要包括: (1) 域完整性(Domain Integrity) [1] 域完整性是指数据库中的列必须满足某种特定的数据类型或约束。域约束是最常见的用户自定义完整性约束，当有新数据插入到数据库中，系统可以按照定义进行关系属性取值是否正确的检测。其中，约束又包括取值范围精度等规定。表中的CHECK FOREIGN KEY约束和DEFAULT NOT NULL定义都属于域完整性的范畴。 [2] 现在的RDBMS中，一般都有域完整性检查功能。SQL Server提供了定义和检验这类完整性的机制，以便用统一的系统方法来处理它们。而不是用应用程序来承担这一功能。其他的完整性类型都支持用户定义的完整性。 [3] 一个属性能否取空值一般由语义决定，也是域约束的内容之一。 (2) 其他用户自定义完整性 不同的关系数据库系统根据其应用环境的不同，往往还需要一些特殊的约束条件。其他类型的用户自定义的完整性即是针对某个特定关系数据库的约束条件，它反映某个具体应 用所涉及的数据必须满足的语义要求。 2)SQL中的约束机制 约束主要包括如下两种。 (1) 静态约束:对静态对象的约束是反映数据库状态合理性的约束，如实体完整性。 (2) 动态约束:对动态对象的约束是反映数据库状态变迁的约束，如触发器。 SQL中用于属性约束方面的有NOT NULL、CHECK等子句;而用于全局约束方面的有CREATE ASSERTION、CREATE RULE等语句。 3)一般的规则组成 规则一般是由规则标识(可默认)以及规则语句组成。规则语句由约束作用的数据对象、约束定义(如断言Assertion)、触发条件以及违反规则时的响应动作购成。 4)规则的一般分类 类型 属性级 元组级 关系级 静态 类型、格式、值域、空值 元组的各个属性之间的取实体、引用统计完整性函 值限制 数依赖 动态 属性/值改变 元组值修改时各属性间的关系变化的前后一致性 约束 5)自定义数据类型和规则 (1)如果多个列使用同一类型的约束，或在一些特殊的情况下，通过自定义的数据类型和规则就为数据库 设计 领导形象设计圆作业设计ao工艺污水处理厂设计附属工程施工组织设计清扫机器人结构设计 提供了更高层次的抽象，如工资数据类型肯定比smallmoney能建立更多的特征及更容易被理解。 (2)规则的创建(当约束条件要为多列使用时)。 步骤一: CREATE RULE rule AS condition_expression 步骤二: Sp_bindrule[@rulename =]’rule’,[@objname=]’object_name’ 规则一般绑定到某一列，或者是用户自己定义的数据类型。 6) Check约束 (1) CHECK约束是对列或列的组合的取值限制，它采用SQL语句中WHERE子句相同 的表达方式来表达，分为表一级和列一级的约束。 (2) 空值的问题。空值意味着检查约束的值是未知的，所以空值的出现不会违反检查约 束的条件。 (3) 潜在的语义问题。 [1] 大多数的DBMS不会检查约束和默认值定义的语句，所以要注意语义冲突; [2] check和check之间的冲突。 [3] 定义了置空删除，但表中检查约束要求此列不能为空;定义该列不能为空，检查约束要求此列为空。 7) Rule约束的建立 规则可以是WHERE子句中任何有效的表达式，并且可以包括诸如算术运算符、关系运算符和谓词(如IN、LIKE、BETWEEN)之类的元素。规则不能引用列或其他数据库对象。可以包含不引用数据库对象的内置函数。 Condition_expression包括一个变量。每个局部变量的前面都有一个@符号。该表达式引用通过UPDATE或INSERT语句输入的值。在创建规则时，可以使用任何名称或符号表示值，但第一个字符必须是@符号。 8) Rule的绑定以及松绑 创建规则后，规则仅仅只是一个存在于数据库中的对象，并未发生作用。需要将规则与数据库表或用户自定义对象联系起来，才能达到创建规则的目的。联系的方法称为“绑定”，所谓“绑定”就是指定规则作用于哪个表的哪个列，或哪个用户自定义数据类型。表的一列或一个用户自定义数据类型只能与一个规则相绑定，而一个规则可以绑定多对象。这正是规则的魅力所在。解除规则与对象的绑定称为“松绑”。 (1) 存储过程Sp_bindrule绑定规则。 存储过程Sp_bindrule可以绑定一个规则到表的一个列或一个用户自定义数据类型上。其语法如下: Sp_bindrule[@rulename=]’rule’, [@objname=]’object_name’ [,’futureonly’] 各参数说明如下: [@rulename=]’rule’指定规则名称。 [@objname=]’object_name’指定规则绑定的对象。 ‘futureonly’选项仅在绑定规则到用户定义数据类型上时才可以使用。当指定此选项时，仅以后使用此用户自定义数据类型的列会应用新规则，而当前已经使用此数据类型的列则不受影响。 (2) 存储过程Sp_unbindrule解除规则的绑定。 存储过程Sp_unbindrule可解除规则与列或用户自定义数据类型的绑定其语法如下: Sp_unbindrule[@objname=]’object_name’ [,’futureonly’] 其中‘futureonly’选项同绑定时一样，仅用于用户自定义数据类型。它指定现有的此用户自定义数据类型定义的列仍然保持与此规则的绑定，如果不指定此项则所有由此用户自定义数据类型定义的列也随之解除与此规则的绑定。 实验内容: (1) 创建worker表，并自定义2个约束U1以及U2，其中U1规定Name字段唯一，U2 规定sage(级别)字段的上限是28。 (2) 在worker表中插入一条合法记录。 (3) 演示插入违反U2约束的例子，U2规定元组的sage属性的值必须<=28。 (4) 去除U2约束。 (5) 重新插入(3)中想要插入的数据，由于去除了U2约束，所以插入成功。 (6) 创建规则rule_sex，规定插入或更新的值只能是M或F，并绑定到worker的sex字段。 (7) 演示违反规则rule_sex的插入操作。 实验步骤: 代码实现参见数据库实验习题2.3.sql文档 心得体会: 1、学习了参照完整性的相关内容，加深了对于这部分知识点的认识; 实验报告2.4 触发器 实验目的: 通过实验使学生加深对数据库完整性的理解，学会创建和使用触发器。 实验原理: 1、触发器的概述 触发器是SQL提供的一种维护数据完整性的工具。触发器过程是由程序员给定，例如 一个和完整性控制动作有关的处理过程。当系统规定的触发条件发生时，给定的过程被调用。触发条件是多种多样的，例如:进入或退出程序的某层结构(如Block、Form等);查询、修改等操作发生之前或之后;某个按键动作;Trigger过程调用(相当于子程序调用)。 触发器是实施复杂完整性的特殊类型的存储类型。触发器不需要专门语句调用，当对它所保护数据进行修改时自动激活，以防止对数据进行不正确，未授权或不一致的修改。 2、触发器的类型以及其具有的特殊表 一个触发器只适用于一个表，每个表最多只能有三个触发器，它们分别是INSERT、UPDATE和DELETE触发器。触发器仅在实施数据完整性和处理业务规则时使用。 每个触发器有两个特殊的表，即插入表(INSERT TABLE)和删除表(DELETE TABLE)。这两个表是逻辑表，并且这两个表是由系统管理的，存储在内存中，不是存储在数据库中。因此不允许用户直接对其修改，并且这两个表的结构总是与被该触发器作用的表有相同的表结构。 3、三种触发器的工作原理 (1)INSERT 触发器:先向INSERTED表中插入一个新行的副本，然后检查INSERTED表的新行是否有效，确定是否要阻止该插入操作。如果所插入的行中的值是有效的，则将该行插入触发器表。 (2)UPDATE 触发器:先将原始数据行移到DELETED表中，然后将一个新行插入INSERTED表中，最后计算DELETED表和INSERTED表中的值以确定是否进行干预。 (3)DELETE触发器:将原始数据行移到DELETE表中，计算DELETED表中的值决定是否进行干预，如果不进行，那么把数据行删除。 4、SQL中创建触发器的语法 创建触发器的语法为: CREATE TRIGGER<触发器>ON<表名> [WITH ENCRYPTION] FOR {[DELETE][,][INSERT][,][UPDATE]} [WITH APPEND] [NOT FOR REPLICATION] AS 5、触发器和存储过程的区别 (1)是否附属于唯一的表。 触发器附属于唯一的表，而存储过程不附属任何的表。 (2)是否事件驱动。 触发器由事件驱动，而存储过程由显式的指令调用。 (3)是否有数量的限制。 一般不允许在表级建立太多的触发器，对触发器的数目有要求，而存储过程没有这方面的要求。 实验内容: (1) 为WORKER表建立触发器T1，当插入或是更新表中数据时，保证所操作的记录的 sage值大于0。 (2) 为WORKER表建立触发器T2，禁止删除编号为00001的CEO。 (3) WORKER表中的人员的编号是唯一且不可改变的，创建触发器T3实现更新中编号 的不可改变性。 (4) 演示违反T1触发器的约束的插入操作。 (5) 演示违反T1触发器的约束的更新操作。 (6) 演示违反T2触发器的约束的插入操作。 (7) 演示违反T2触发器的约束的插入操作。 实验步骤: 代码实现参见数据库实验习题2.4.sql文档 心得体会: 1、 学习了实体完整性的相关内容，加深了对于这部分知识点的认识; 2、 实验中，触发器的代码相对于课本作业来说，友好许多，简化许多; 3、 通过实验习题2，了解到并证实了在SQL Server 2000中，数据更新的过程，其实分解为 先删除需要更新的内容，然后插入更新的内容。