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

数据库系统原理第五章完整性控制1第1页，课件共19页，创作于2023年2月③

约束对象状态·静态：反映DB状态合理性的约束。·动态：反映DB状态变迁的约束。④

约束时机（Immediateconstraints）（1）立即约束——一条语句执行完后立即检查。（2）延迟约束（deferredconstraints）——事务执行结束后检查。转帐，从A到B后，帐才能平，才能进行检查。2、静态列级约束——对一个列的取值域的约束。①

数据类型约束类型、长度、单位、精度如XM为C型，长8位；YL为I型，长3位。②

数据格式约束如工作证号前2位表示省，后3位表示县，后4位表示单位顺序号，后5位表示个人顺序号。2第2页，课件共19页，创作于2023年2月③

值域约束CJ≤100，NL≤150，XB={男，女}④

空值约束是否允许空值列。如CJ可为空。⑤

其它约束（如：列的排序、是否唯一等等）3、静态元组约束——对一个（仅一个）元组中各列值间联系的约束。如：总额≥单价工龄<年龄4、静态关系约束——对一个关系中若干元组之间或若干关系的联系的约束。①

实体完整性约束；②

参照完整性约束；③

函数依赖约束；④

统计约束；如：职工最低工资不能低于本部门职工平均工资的50%。3第3页，课件共19页，创作于2023年2月5、动态列级约束——修改列定义或列值时的约束。①

修改定义约束——修改定义时新老值间的约束。如：将允许空值列改为不允许空值时，若该列值已有空值，则不可修改。②

修改值约束——修改时新旧值间的约束。如年龄不能修改得更小。6、动态元组约束——修改元组时新旧值间的约束。例如新工资不低于原工资+工龄*1.5。7、动态关系约束——施加于关系上的前后状态的约束①

一致性②

原子性

4第4页，课件共19页，创作于2023年2月5.3完整性控制1、实体完整性（entityintegrity）——对关系模式主属性施加的完整性控制。

不允许空，在关系中取值唯一例：

student(XH,XM,XB,YL) XH不能为空且唯一course(KH,KM) KH不能为空且唯一Sc(XH,KH,CJ) (XH，KH)不能为空且唯一Createtablestudent(XH,Char(6)NOTNULL,…,);2、参照完整性（referentialintegrity）——对外码施加的完整性控制。（回忆第二章定义）参照关系：外码所在关系，如SC被参照关系：主码（同时又是另一关系中的外码）所在关系，如student，course。外码：sc中的XH，KH5第5页，课件共19页，创作于2023年2月1）空值情况①

为空（SC中XH为空，表示无这个学生，或无学号学生选修了课程，不合应用语义），或；②

对应被参照关系中该元组存在；③

是否可为空，据应用语义确定。

DEPT（DH，DM，DD）EMPL（DH，EH，XM）EMPL中DH可为空，表示该职员还未分配到任何部门工作。2）删除被参照关系元组情况①

捆绑删除（cascades）——参照与被参照关系中相关者一起删除。·被参照关系中外码元组删除例如：删除99001号学生（或者01号课程）删去student中XH=‘99001’的元组（删去course中KH=‘01’的元组）则捆绑删除SC中学号为99001（课号为01）的所有元组·参照关系中与被参照关系中码值对应元组删除6第6页，课件共19页，创作于2023年2月·可能层层牵连如：若SC又是另一参照关系的被参照关系，则可能又删除之……。②

受限删除（restricted）——参照关系没有一个外码与要删除的被参照关系的主码值相对应时才执行删除。例如：若SC中外码值XH，无一个与主码值(Student.XH,course.KH)对应时才删去student、course中相应元组。③

置空值删除（nullifies/setnull）·删去被参照关系中元组；·参照关系中所有与被参照关系中已删去的主码值相等的外码值置为空值。如：删去部门表中的某个部门，则职员表中原来属于该部门的职员的所属部门号置空。7第7页，课件共19页，创作于2023年2月④

说明·上述三种，选择哪一种实施，视应用需求确定。如：学籍管理中，学生毕业了，删去选课及学生信息，故需捆绑删除（course不删）student信息。·DBMS提供相应选择机制。

3）修改被参照关系主码值情况①

捆绑修改（cascades）·修改被参照关系中主码值。如修改student中的XH=99003改为XH=99020·同时修改参照关系中相等外码值。如：同时SC中所有99003改为99020·可能逐层牵连。8第8页，课件共19页，创作于2023年2月②

受限修改（restricted）——仅当参照关系中没有一个外码值与被参照关系中某个元组主码值相等时才可修改被参照关系中的该元组主码值。如：仅当SC中学生无99003时，才可修改student中XH=99003。③

置空值修改（nullifies）·修改被参照关系中的主码值；·将参照关系中的与该主码值相等的外码置为空值。④

说明·具体应用中，根据应用需求选择上述方法执行。·DBMS提供机制支持用户选择。

9第9页，课件共19页，创作于2023年2月3、用户定义完整性（integrityofuserdefinition）1）空值控制——对给定属性施加不允许空值限制（NOTNULL）2）单个属性控制为：（CJisNull）

OR(CJBETWEEN0AND100)3）多属性控制如：XB=‘男’

ANDYL<=30例1：CREATETABLEDEPT(DEPTNOINTPRIMARYKEY,//主码

DNAMECHAR(9)NOTNULLUNIQUE,//不允许为空，且不能重复

LOCATIONCHAR(10)NOTNULL);例2：CREATETABLESC(SNOINT,CNOINT,GRADEINTCHECK(GRADE>=0ANDGRADE<=100));10第10页，课件共19页，创作于2023年2月例3：CREATETABLESTUDENT(SNOINTPRIMARYKEY,SNAMECHAR(30)NOTNULL,SSEXCHAR(2),SDEPTINT,CHECK(SSEX=‘女’

ORSNAMENOTLIKE‘Ms.%’));例4：CREATETABLETEACHER(ENOINT,ENAMECHAR(30)NOTNULL,SALINT,DEDUCTINT,CONSTRAINTC1CHECK(SQL+DEDUCT>=1000));11第11页，课件共19页，创作于2023年2月例5：ALTERTABLETEACHERDROPCONSTRAINTC1;ALTERTABLETEACHERADDCONSTRAINTC2CHECK(SAL+DEDUCT>=2000ANDSAL>=1000);例6：域的使用

CREATEDOMAINGenderDomainCHAR(2)CHECK(VALUEIN(‘男’,‘女’));或者CREATEDOMAINGenderDomainCHAR(2)CONSTRAINTC3CHECK(VALUEIN(‘男’,‘女’));

……ssexGenderDomain,……例7：ALTERDOMAINGenderDomainDROPCONSTRAINTC3;12第12页，课件共19页，创作于2023年2月4、ORACLE完整性1）实体完整性CREATETABLEstudent(XHNUMBER(8)，XMVARCHAR(8),YLNUMBER(3)，ConstraintPK-XHPRIMARYKEY(XH));一旦定义了主码，则DBMS自动进行完整性检查：·主码不能为空；·主码值须唯一。13第13页，课件共19页，创作于2023年2月2）参照完整性①

语句·FOREIGNKEY：指定外码属性·REFERENCES：指定外码对应主码·ONDELETECASCADE：指定捆绑删除要求例：CREATETABLESC

(XHNUMBER(8)，KHNUMBER(3)，CJNUMBER(3)，

CONSTRAINTFK_SCXHFOREIGNKEY(XH)REFERENCESstudent(XH)ONDELETECASCADE,CONSTRAINTFK_SCKHFOREIGNKEY(KH)REFERENCEScourse(KH)ONDELETECASCADE)；

14第14页，课件共19页，创作于2023年2月②

说明：·SC中外码为XH，KH。·对应student主码为XH。·对应course主码为KH。·当修改student中XH时，先检查SC中有无元组XH值与之相等，若有，则不能执行该修改；当修改course中KH，先检查SC中有无元组的KH值与之相等，若有，则不能执行该修改。·当删除student或course某元组时，则先在SC中找到相应元组，进行捆绑删除。3）用户定义完整性①

列值非空（NOTNULL）②

列值唯一（UNIQUE）CREAETABLECOURSE(KHNUMBER(3),KMVARCHAR(20)CONSTRAINTU1UNIQUE,XSNUMBER(2))；15第15页，课件共19页，创作于2023年2月其中：CONSTRAINTU1UNIQUE：KM唯一，约束名为U1。③

列值范围限制例：·

·

·XBVARCHAR（2）CONSTRAINTCNS_XB1CHECK（XBIN（‘男’，‘女’））；例：·

·

·GZNUMBER（8.2）CONSTRAINTCNS_GZ1CHECK（GZ≥1000.00））；例：CRETATETRIGGERUPDATE-CJBEFOREINSERTORUPDATEONCJFOREACHROWWHEN（:NEW.KH=‘001’）

ASBEGINIF：NEW.CJ>50THEN

：NEW.CJ:=50;ENDIFEND16第16页，课件共19页，创作于2023年2月说明：·利用触发器，当对SC中进入插入元组和修改CJ值时，若为‘001’号课程，则CJ>50时一律自动改为50分。·定义触发器语句为CREATEORREPLACETRIGGER。（先定义后使用）