数据库系统及应用12.ppt

第十二章 数据库完整性,在数据库投入运行后，应该保证数据库中的数据是正确的，避免非法的更新。数据库中的数据发生错误，往往是由非法的更新引起的。,数据库的完整性（Integrity）： 数据库的完整性是指保证数据库中数据的正确性(Correctness)、准确性（Accuracy）和有效性(Validity)，防止不合语义的数据进入数据库。,例如：学生的年龄必须是整数，取值范围为14-29； 学生的性别只能是男或女； 学生的学号一定是唯一的； 学生所在的系必须是学校开设的系；,为维护数据库的完整性，DBMS必须提供一种机制来检查数据库的完整性，看其是否满足语义规定的条件。 DBMS中检查数据是否满足完整性条件的机制称为完整性检查。,现代数据库技术采用对数据完整性的语义约束和检查来保护数据库的完整性，其实现方式有两种：一种是通过定义和使用完整性约束规则（系统定义），另一种是通过触发器（Trigger）和存储过程（Stored Procedure）（用户自定义）等过程来实现。,完整性子系统：负责处理数据库的完整性语义约束的定义和检查，防止因错误的更新操作产生的不一致性。用户可以使用完整性保护机制，对某些数据规定一些语义约束。 当进行数据操作时，DBMS就由某个完整性语义约束的触发条件激发相应的检查程序，进行完整性语义约束检查。 若发现错误的更新操作，立即采取措施处理，或是拒绝执行该更新操作，或是发出警告信息，或者纠正已产生的错误。,DBMS的完整性子系统是负责数据库的完整性控制的。它具有3个功能： 完整性约束条件定义机制 完整性检查机制 违约反应,完整性约束条件：数据模型的组成部分，约束数据库中数据的语义。 DBMS应提供定义数据库完整性约束条件的机制，并把它们作为模式的一部分存入数据库中。 一般由SQL的DDL语句来实现。,完整性约束条件定义机制,检查用户发出的操作请求是否违背了完整性约束条件的方法,完整性检查机制,如果发现用户的操作请求使数据违背了完整性约束条件，则采取一定的动作，如拒绝用户执行该操作，来保证数据的完整性。,违约反应,12.1 完整性规则,数据库完整性子系统是根据“完整性规则集”工作的。完整性规则集包含一组完整性约束规则（Integrity Constraint）。一个完整性约束规则包含完整性约束条件、规则的触发条件和“ELSE子句”。 完整性检查是围绕完整性约束条件进行的，因此数据库的完整性约束条件是完整性控制机构的核心。,一个完整性约束条件可以看作是一个谓词（Predicate），所有正确的保持完整性的数据库状态都应当满足这个谓词。 例如，SCORE.GRADE0。 规则的触发条件规定何时使用该规则做检查。 “ELSE子句”规定当完整性约束条件不满足时须做的操作。,完整性约束条件作用的对象可以是关系、元组和列三种。 属性（列）约束：列的类型、取值范围、精度、排序等约束； 元组约束：元组中各个属性列间的联系的约束； 关系约束：若干元组间、关系集合上以及关系之间的联系的约束；,在关系数据库中，数据的完整性规则一般有以下类型。 域完整性规则：定义属性的取值范围； 关系完整性规则：定义更新操作对数据库中的值的影响和限制； 实体完整性约束：定义在一个关系中，主属性不能取空值 参照完整性约束：定义在一个或多个关系中，属性值间的联系、影响和约束。,这些规则是用DDL描述的，一旦一条完整性规则输入给系统，系统就开始执行这条规则。这种方法的主要优点是由系统处理违反规则的情况，而不是由用户处理。其次，规则集中存放在数据字典中，当需要修改时，可以很方便地修改。,定义域约束规定某个属性的值必须符合某种数据类型并且取自某个数据定义域。域完整性约束施加于单个数据上。 0人的年龄150 仓库库存量0 0一个月的工作天数31 长途电话号码格式为9999999999,1. 域完整性约束,对关系的完整性进行约束的主要目的是维持用户规定的函数依赖。例如，如果用户定义了下列函数依赖：NAMEADDRESS。,2. 关系完整性约束,主码（主关键字）是实体完整性约束（Entity Constraint）。,3. 实体完整性约束,外码（外部关键字）是参照完整性约束（Referential Constraint）的一个典型例子。,4. 参照完整性约束,数据库的完整性约束可以分为两种类型。 静态约束（Static Constraint） 动态约束（Dynamic Constraint）,静态约束：是对数据库状态的约束，即是指数据库每一确定状态时的数据对象所应满足的约束条件，反映数据库状态合理性的约束，这是最重要的一类完整性约束。 例如，在一个人事管理数据库的任何一个状态都必须满足条件 “0人的年龄150”。,动态约束：是指数据库从一种状态转变为另一种状态时，新、旧值之间所应满足的约束条件，反映数据库状态变迁的约束。 例如，一个人的年龄只会增加，不会减少，更新年龄数据时必须满足此约束。,在关系系统中，最重要的完整性约束是实体完整性约束和参照完整性约束，其他完整性约束条件则可以归入用户定义的完整性约束。 对于违反实体完整性和用户定义的完整性操作一般都采用拒绝执行的方式进行处理，而对于违反参照完整性的操作，并不都是简单地拒绝执行，有时要根据应用语义执行一些附加的操作，以保证数据库的正确性。,12.2 完整性控制机制,关系模型的实体完整性 CREATE TABLE中用PRIMARY KEY定义 单属性构成的码有两种说明方法 定义为列级约束条件 定义为表级约束条件 对多个属性构成的码只有一种说明方法 定义为表级约束条件,12.2.1 实体完整性,例12.1 将Student表中的Sno属性定义为码 (1) 在列级定义主码 CREATE TABLE Student (Sno CHAR(5) PRIMARY KEY， Sname CHAR(20) NOT NULL， Ssex CHAR(2) ， Sage NUMBER(2)， Sdept CHAR(2);,(2) 在表级定义主码 CREATE TABLE Student (Sno CHAR(5)， Sname CHAR(20) NOT NULL， Ssex CHAR(2) ， Sage NUMBER(2)， Sdept CHAR(2)， PRIMARY KEY (Sno) );,例12.2 将SCore表中的Sno，Cno属性组定义为码。 CREATE TABLE SCORE (Sno CHAR(5) ， /* NOT NULL */ Cno CHAR(3) ， /* NOT NULL */ Score NUMBER(6,2) ， PRIMARY KEY (Sno，Cno) /*只能在表级定义主码*/ );,插入或对主属性列进行更新操作时，RDBMS按照实体完整性规则自动进行检查。包括： 检查主码值是否唯一，如果不唯一则拒绝插入或修改 检查主码的各个属性是否为空，只要有一个为空就拒绝插入或修改,12.2.2 参照完整性,关系模型的参照完整性 在CREATE TABLE中用FOREIGN KEY短语定义哪些列为外码 用REFERENCES短语指明这些外码参照哪些表的主码,例如，关系SCore中一个元组表示一个学生选修的某门课程的成绩，(Sno，Cno)是主码。Sno，Cno分别参照引用Student表的主码和Course表的主码。 例12.3 定义SCore中的参照完整性 CREATE TABLE Score (Sno CHAR(5) ， Cno CHAR(3) ， Score NUMBER(6,2)， PRIMARY KEY (Sno，Cno)， /*在表级定义实体完整性*/ FOREIGN KEY (Sno) REFERENCES Student(Sno)， /*在表级定义参照完整性*/ FOREIGN KEY (Cno) REFERENCES Course(Cno) /*在表级定义参照完整性*/ );,参照完整性违约处理 拒绝(RESTRICT)执行 默认策略 级联(CASCADE)操作 设置为空值（SET NULL） 对于参照完整性，除了应该定义外码，还应定义外码列是否允许空值,在实现参照完整性时，系统除了应该提供定义外码的机制，还应提供定义外码列是否允许空值的机制。,1. 外码能否接受空值的问题,职工表,部门表,外码,空值，可 能符合语义,成绩表,学生表,外码,空值，不 符合语义,2. 可能破坏参照完整性的情况及违约处理,在被参照关系中删除元组的问题 外码值与被参照关系删除元组的主码值相同，这时可有三种不同的策略： 级联删除（CASCADES） 将参照关系中所有外码值与被参照关系中要删除元组的主码值（父项）相同的元组一起删除。如果参照关系同时又是另一个关系的被参照关系，则这种删除操作会继续级联下去。, 限制删除（RESTRICT） 仅当参照关系中没有任何元组的外码值与被参照关系中要删除元组的主码值（父项）相同时，系统才执行删除操作，否则拒绝执行此删除操作。 置空值删除（SET NULL） 删除被参照关系中的元组，并将参照关系中相应元组的外码值置成空值。,这三种方法究竟应采取哪一种方法，要依具体应用环境的语义来定。如在学生- 选课数据库中，显然删除学生信息时采用“级联删除”方法是对的。因为当一个学生毕业或退学后，他的个人记录从Student表中删除了，他的成绩记录也应随之从Score表中删除。,例：要删除Student关系中Sno=95001的元组，而SCORE关系中有4个元组的Sno都等于95001。 级联删除：将SCORE关系中所有4个Sno=95001的元组一起删除。如果参照关系同时又是另一个关系的被参照关系，则这种删除操作会继续级联下去,受限删除：系统将拒绝执行此删除操作。 置空值删除：将SCORE关系中所有Sno=95001的元组的Sno值置为空值。 在学生选课数据库中，显然第一种方法和第二种方法都是对的。第三种方法不符合应用环境语义。,在参照关系中插入元组时的问题 一般地，当参照关系（子表）插入某个元组，依据被参照关系（父表）的情况，这时可以有以下策略：, 限制插入 仅当被参照关系中存在相应的元组，其主码值与参照关系插入元组的外码值相同时，系统才执行插入操作，否则拒绝执行此操作。 递归插入（仅是理论上） 首先向被参照关系中插入相应的元组，其主码值等于参照关系插入元组的外码值，然后向参照关系插入元组。,例：向SCORE关系插入（99001，001，90）元组，而Student关系中尚没有Sno=99001的学生 受限插入：系统将拒绝向SCORE关系插入（99001，001，90）元组 递归插入：系统将首先向Student关系插入Sno=99001的元组，然后向SCORE关系插入（99001，001，90）元组。,修改被参照关系时的问题 不允许修改主码值 在有些RDBMS中，修改主码值的操作时不允许的。如果需要修改主码值，只能先删除该元组，然后再把具有新主码值的元组插入到关系中。 允许修改主码值 在有些RDBMS中，允许修改关系的主码值，但必须保证主码值的唯一性和非空，否则拒绝修改。分两种情况：,必须检查参照关系，是否存在这样的元组，其外码值等于被参照关系要修改的主码值。这时可以有级联修改、拒绝修改、置空值修改三种策略加以选择,级联修改 修改被参照关系中主码值同时，用相同的方法修改参照关系中相应的外码值。 受限修改 拒绝此修改操作。只当参照关系中没有任何元组的外码值等于被参照关系中某个元组的主码值时，这个元组的主码值才能被修改。 置空值修改 修改被参照关系中主码值，同时将参照关系中相应的外码值置为空值。,例：将Student关系中Sno=95001的元组中Sno值改为96123。而SCORE关系中有4个元组的Sno=95001 级联修改：将SCORE关系中4个Sno=95001元组中的Sno值也改为96123。如果参照关系同时又是另一个关系的被参照关系，则这种修改操作会继续级联下去。,受限修改：只有SCORE中没有任何元组的Sno=95001时，才能修改Student表中Sno=95001的元组的Sno值改为96123。 置空值修改：将Student表中Sno=95001的元组的Sno值改为96123。而将Student表中所有Sno=95001的元组的Sno值置为空值。 在学生选课数据库中只有第一种方法是正确的。,与在子表插入元组时的问题类似 必须检查被参照关系，是否存在这样的元组，其主码值等于被参照关系要修改的外码值。这时可以有限制插入、递归插入两种策略加以选择。,修改参照关系时的问题,RDBMS在实现参照完整性 需要向用户提供定义主码、外码的机制 向用户提供按照自己的应用要求选择处理依赖关系中对应的元组的方法 选择哪种策略，都要根据应用环境的要求确定。,3. 参照完整性的实现,12.2.3 用户定义的完整性,用户定义的完整性就是针对某一具体应用的数据必须满足的语义要求 RDBMS提供，而不必由应用程序承担,CREATE TABLE时定义 列值非空（NOT NULL） 列值唯一（UNIQUE） 检查列值是否满足一个布尔表达式（CHECK）,1. 属性上的约束条件的定义, 不允许取空值 例12.4 在定义SC表时，说明Sno、Cno、Grade属性不允许取空值。 CREATE TABLE Score （Sno CHAR(5) ， Cno CHAR(3) ， Score NUMBER(6, 2) NOT NULL， PRIMARY KEY (Sno， Cno)， /* 如果在表级定义实体完整性，隐含了Sno，Cno不允许取空值，则在列级不允许取空值的定义就不必写了 * / ）；, 列值唯一 例12.5 建立部门表DEPT，要求部门名称Dname列取值唯一，部门编号Deptno列为主码 CREATE TABLE DEPT (Deptno NUMERIC(2)， Dname CHAR(9) UNIQUE，/*要求Dname列值唯一*/ Location CHAR(10)， PRIMARY KEY (Deptno) )；, 用CHECK短语指定列值应该满足的条件 例12.6 Student表的Ssex只允许取“男”或“女”。 CREATE TABLE Student (Sno CHAR(5) PRIMARY KEY， Sname CHAR(8) NOT NULL， Ssex CHAR(2) CHECK (Ssex IN (男，女) ) ， /*性别属性Ssex只允许取男或女 */ Sage NUMBER(3)， Sdept CHAR(2) );,插入元组或修改属性的值时，RDBMS检查属性上的约束条件是否被满足 如果不满足则操作被拒绝执行,2. 属性上的约束条件检查和处理,在CREATE TABLE时可以用CHECK短语定义元组上的约束条件，即元组级的限制 同属性值限制相比，元组级的限制可以设置不同属性之间的取值的相互约束条件,3. 元组上的约束条件的定义,例12.7 当学生的性别是男时，其名字不能以Ms.打头。 CREATE TABLE Student (Sno CHAR(5)， Sname CHAR(8) NOT NULL， Ssex CHAR(2)， Sage NUMBER(2)， Sdept CHAR(20)， PRIMARY KEY (Sno)， CHECK (Ssex=女 OR Sname NOT LIKE Ms.%) /*定义了元组中Sname和 Ssex两个属性值之间的约束条件*/ )； 性别是女性的元组都能通过该项检查，因为Ssex=女成立； 当性别是男性时，要通过检查则名字一定不能以Ms.打头,插入元组或修改属性的值时，RDBMS检查元组上的约束条件是否被满足 如果不满足则操作被拒绝执行,4. 元组上的约束条件检查和违约处理,除此之外，还可以通过定义触发器来实现其他完整性规则。用触发器可定义很复杂的完整性约束条件。 一旦某一用户定义了一个完整性约束条件检查的触发器，任何用户对该数据的增、删、改操作均由服务器自动激活相应的触发器，在核心层进行集中的完整性控制。,12.3 完整性约束命名子句,SQL3主张显式命名所有的约束。约束可以命名，以便引用。约束的命名使用保留字CONSTRAINT。 CONSTRAINT PRIMARY KEY短语 |FOREIGN KEY短语 |UNIQUE |CHECK短语,1. CONSTRAINT 约束命名,例12.8 建立学生登记表Student，要求学号在9000099999之间，姓名不能取空值，年龄小于30，性别只能是“男”或“女”。 CREATE TABLE Student (Sno CHAR(5) CONSTRAINT C1 CHECK (Sno BETWEEN 90000 AND 99999)， Sname CHAR(20) CONSTRAINT C2 NOT NULL， Sage NUMBER(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四个列级约束。,使用ALTER TABLE语句修改表中的完整性约束,2. 修改表中的完整性限制,例12.9 修改表Student中的约束条件，要求学号改为在900000999999之间，年龄由小于30改为小于40。 可以先删除原来的约束条件，再增加新的约束条件 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)；,12.4 ORACLE的完整性约束,Oracle在CREATE TABLE 语句中提供了PRIMARY KEY子句，供用户在建表时指定关系中的主关键字列。 当用户程序对主关键字列进行更新操作时，系统自动进行完整性检查