数据库系统原理第五章——完整性控制.ppt

1、1,第5章 数据库完整性,5.1 概述 1、定义 数据的正确性，有效性和相容性。 （防止不符合语义的数据的I/O） 2、功能 1）完整性要求定义； 2）监督事务执行，测试是否违反完整性限制条件； 3）若发生违反情况，则进行相应处理（拒绝、报告、纠正）。 5.2 完整性约束条件 1、基本概念 定义 施加于DB中数据之上的语义限制条件 约束对象 列级、元组级、关系级,2, 约束对象状态 静态：反映DB状态合理性的约束。 动态：反映DB状态变迁的约束。 约束时机（Immediate constraints） （1）立即约束 一条语句执行完后立即检查。 （2）延迟约束（deferred constra

2、ints） 事务执行结束后检查。 转帐，从A到B后，帐才能平，才能进行检查。 2、静态列级约束 对一个列的取值域的约束。 数据类型约束 类型、长度、单位、精度 如XM为C型，长8位；YL为I型，长3位。 数据格式约束 如工作证号前2位表示省，后3位表示县，后4位表示单位顺序号，后5位表示个人顺序号。,3, 值域约束 CJ100，NL150，XB=男，女 空值约束 是否允许空值列。 如CJ可为空。 其它约束（如：列的排序、是否唯一等等） 3、静态元组约束 对一个（仅一个）元组中各列值间联系的约束。 如：总额单价 工龄年龄 4、静态关系约束 对一个关系中若干元组之间或若干关系的联系的约束。 实体完

3、整性约束； 参照完整性约束； 函数依赖约束； 统计约束； 如：职工最低工资不能低于本部门职工平均工资的50%。,4,5、动态列级约束 修改列定义或列值时的约束。 修改定义约束 修改定义时新老值间的约束。 如：将允许空值列改为不允许空值时，若该列值已有空值，则不可修改。 修改值约束 修改时新旧值间的约束。 如年龄不能修改得更小。 6、动态元组约束 修改元组时新旧值间的约束。 例如新工资不低于原工资+工龄*1.5。 7、动态关系约束 施加于关系上的前后状态的约束 一致性 原子性,5,5.3 完整性控制 1、实体完整性（entity integrity） 对关系模式主属性施加的完整性控制。 不允许空

4、，在关系中取值唯一 例： student (XH,XM,XB,YL)XH不能为空且唯一 course(KH,KM)KH不能为空且唯一 Sc(XH,KH,CJ) (XH，KH)不能为空且唯一 Create table student (XH,Char (6) NOT NULL,); 2、参照完整性（referential integrity） 对外码施加的完整性控制。（回忆第二章定义） 参照关系：外码所在关系，如SC 被参照关系：主码（同时又是另一关系中的外码）所在关系，如student，course。 外码：sc中的XH，KH,6,1）空值情况 为空（SC中XH为空，表示无这个学生，或无学号学

5、生选修了课程，不合应用语义） ，或； 对应被参照关系中该元组存在； 是否可为空，据应用语义确定。 DEPT（DH，DM，DD） EMPL（DH，EH，XM） EMPL中DH可为空，表示该职员还未分配到任何部门工作。 2）删除被参照关系元组情况 捆绑删除（cascades） 参照与被参照关系中相关者一起删除。 被参照关系中外码元组删除 例如：删除99001号学生（或者01号课程） 删去student中XH=99001的元组 （删去course中KH=01的元组） 则捆绑删除SC中学号为99001（课号为01）的所有元组 参照关系中与被参照关系中码值对应元组删除,7,可能层层牵连 如：若SC又是另

6、一参照关系的被参照关系，则可能又删除之。 受限删除（restricted） 参照关系没有一个外码与要删除的被参照关系的主码值相对应时才执行删除。 例如：若SC中外码值XH，无一个与主码值(Student.XH, course.KH)对应时才删去student、course中相应元组。 置空值删除（nullifies/set null） 删去被参照关系中元组； 参照关系中所有与被参照关系中已删去的主码值相等的外码值置为空值。 如：删去部门表中的某个部门，则职员表中原来属于该部门的职员的所属部门号置空。,8, 说明 上述三种，选择哪一种实施，视应用需求确定。 如：学籍管理中，学生毕业了，删去选课及

7、学生信息，故需捆绑删除（course不删）student信息。 DBMS提供相应选择机制。 3）修改被参照关系主码值情况 捆绑修改（cascades） 修改被参照关系中主码值。 如修改student中的XH=99003改为XH=99020 同时修改参照关系中相等外码值。 如：同时SC中所有99003改为99020 可能逐层牵连。,9, 受限修改（restricted） 仅当参照关系中没有一个外码值与被参照关系中某个元组主码值相等时才可修改被参照关系中的该元组主码值。 如：仅当SC中学生无99003时，才可修改student中XH=99003。 置空值修改（nullifies） 修改被参照关系中

8、的主码值； 将参照关系中的与该主码值相等的外码置为空值。 说明 具体应用中，根据应用需求选择上述方法执行。 DBMS提供机制支持用户选择。,10,3、用户定义完整性（integrity of user definition） 1）空值控制 对给定属性施加不允许空值限制（NOT NULL） 2）单个属性控制 为：（CJ is Null） OR (CJ BETWEEN 0 AND 100) 3）多属性控制 如：XB=男 AND YL=0 AND GRADE=100);,11,例3：CREATE TABLE STUDENT (SNO INT PRIMARY KEY, SNAME CHAR(30) N

9、OT NULL, SSEX CHAR(2), SDEPT INT, CHECK (SSEX=女 OR SNAME NOT LIKE Ms.%); 例4：CREATE TABLE TEACHER (ENO INT, ENAME CHAR(30) NOT NULL, SAL INT, DEDUCT INT, CONSTRAINT C1 CHECK (SQL+DEDUCT=1000);,12,例5：ALTER TABLE TEACHER DROP CONSTRAINT C1; ALTER TABLE TEACHER ADD CONSTRAINT C2 CHECK (SAL+DEDUCT=2000 A

10、ND SAL=1000); 例6：域的使用 CREATE DOMAIN GenderDomain CHAR(2) CHECK (VALUE IN(男,女); 或者 CREATE DOMAIN GenderDomain CHAR(2) CONSTRAINT C3 CHECK (VALUE IN(男,女); ssex GenderDomain, 例7：ALTER DOMAIN GenderDomain DROP CONSTRAINT C3;,13,4、ORACLE完整性 1）实体完整性 CREATE TABLE student (XH NUMBER (8)， XM VARCHAR(8), YL N

11、UMBER(3)， Constraint PK-XH PRIMARY KEY (XH); 一旦定义了主码，则DBMS自动进行完整性检查： 主码不能为空； 主码值须唯一。,14,2）参照完整性 语句 FOREIGN KEY ：指定外码属性 REFERENCES：指定外码对应主码 ON DELETE CASCADE：指定捆绑删除要求 例：CREATE TABLE SC (XH NUMBER (8)，KH NUMBER (3)， CJ NUMBER (3)， CONSTRAINT FK_SCXH FOREIGN KEY (XH) REFERENCES student (XH) ON DELETE C

12、ASCADE, CONSTRAINT FK_SCKH FOREIGN KEY (KH) REFERENCES course (KH) ON DELETE CASCADE)；,15, 说明： SC中外码为XH，KH。 对应student主码为XH。 对应course主码为KH。 当修改student中XH时，先检查SC中有无元组XH值与之相等，若有，则不能执行该修改；当修改course中KH，先检查SC中有无元组的KH值与之相等，若有，则不能执行该修改。 当删除student或course某元组时，则先在SC中找到相应元组，进行捆绑删除。 3）用户定义完整性 列值非空（NOT NULL） 列值唯

13、一（UNIQUE） CREAE TABLE COURSE ( KH NUMBER (3), KM VARCHAR (20) CONSTRAINT U1 UNIQUE, XS NUMBER (2)；,16,其中：CONSTRAINT U1 UNIQUE：KM唯一，约束名为U1。 列值范围限制 例： XB VARCHAR（2） CONSTRAINT CNS_XB1 CHECK（XB IN（男，女）； 例： GZ NUMBER（8.2） CONSTRAINT CNS_GZ1 CHECK（GZ1000.00）； 例：CRETATE TRIGGER UPDATE-CJ BEFORE INSERT OR

14、UPDATE ON CJ FOR EACH ROW WHEN（:NEW.KH=001） AS BEGIN IF ：NEW.CJ50 THEN ：NEW.CJ :=50; ENDIF END,17,说明： 利用触发器，当对SC中进入插入元组和修改CJ值时，若为001号课程，则CJ50时一律自动改为50分。 定义触发器语句为CREATE OR REPLACE TRIGGER。 （先定义后使用） CREATE TRIGGER语句定义触发器的约束条件。 一条完整性规则可以用一个五元组（D,O,A,C,P）来形式化地表示，其中： D: 数据对象Data O：引发动作地操作Operation A：数据对象必须满足的断言或语义约束Assertion C：选择A作用的数据对象的谓词Condition P：被触发的过程Procedure 例如：教授的工资不得低于1000元。 D教师的工资，O修改或插入，A不低于1000元，C职称为教授，P拒绝修改。,18,FOR EACH