数据库系统概论之数据库完整性

中国地质大学信息工程学院数据库系统概论AnIntroductiontoDatabaseSystem第五章数据库完整性10/1/1数据库系统概论之数据库完整性第1页什么是数据库完整性数据正确性和相容性预防不合语义数据进入数据库。关注是否真实地反应现实世界例:学生年纪必须是整数，取值范围为14--29；学生性别只能是男或女；学生学号一定是唯一；学生所在系必须是学校开设系；提要5.1实体完整性5.2参考完整性5.3用户定义完整性5.4完整性约束命名子句5.5触发器10/1/2数据库系统概论之数据库完整性第2页数据库完整性与安全性

区分和联络两个不一样概念,但有一定联络;前者是为了预防数据库中存在不符合语义数据,预防错误信息输入和输出,既所谓垃圾进垃圾出(GarbageinGarbageout)所造成无效操作和错误结果;后者是保护数据库预防恶意破坏和非法存取;安全性办法防范对象是非法用户,完整性办法防范对象是不合语义数据.完整性控制机制1.完整性约束条件定义机制2.完整性检验机制3.违约处理10/1/3数据库系统概论之数据库完整性第3页完整性约束条件定义完整性约束条件：数据模型组成部分约束数据库中数据语义DBMS应提供定义数据库完整性约束条件，并把它们作为模式一部分存入数据库中10/1/4数据库系统概论之数据库完整性第4页完整性检验机制检验用户发出操作请求是否违反了完整性约束条件

10/1/5数据库系统概论之数据库完整性第5页违约处理假如发觉用户操作请求使数据违反了完整性约束条件，则采取一定动作来确保数据完整性。10/1/6数据库系统概论之数据库完整性第6页完整性约束条件完整性约束条件作用对象列：对属性取值类型、范围、精度等约束条件元组：对元组中各个属性列间联络约束关系：对若干元组间、关系集合上以及关系之间联络约束10/1/7数据库系统概论之数据库完整性第7页完整性约束条件（续）静态对静态对象约束是反应数据库状态合理性约束动态对动态对象约束是反应数据库状态变迁约束10/1/8数据库系统概论之数据库完整性第8页完整性约束条件分类六类完整性约束条件静态列级约束静态元组约束静态关系约束动态列级约束动态元组约束动态关系约束10/1/9数据库系统概论之数据库完整性第9页完整性约束条件（续）1.静态列级约束静态列级约束：正确取值域说明最常见、最简单、最轻易实现一类完整性约束10/1/10数据库系统概论之数据库完整性第10页完整性约束条件（续）五类静态列级约束1)数据类型约束：数据类型、长度、单位、精度等例：学生姓名数据类型为字符型，长度为82)对数据格式约束 例：学号：前两位表示入年份，后四位为次序编号日期：YY.MM.DD。10/1/11数据库系统概论之数据库完整性第11页补充：完整性约束条件（续）3)取值范围或取值集合约束 例：要求成绩取值范围为0-100年纪取值范围为14-29性别取值集合为[男,女]4)对空值约束 空值：未定义或未知值空值：与零值和空格不一样有列允许空值，有则不允许，如成绩可为空值5)其它约束 例：关于列排序说明，组合列等10/1/12数据库系统概论之数据库完整性第12页补充：完整性约束条件（续）2.静态元组约束要求元组各个列之间约束关系例：订货关系中发货量<=订货量教师关系中教授工资>=700元

静态元组约束只局限在元组上10/1/13数据库系统概论之数据库完整性第13页补充：完整性约束条件（续）3.静态关系约束关系各个元组之间或若干关系之间存在各种联络或约束常见静态关系约束：1)实体完整性约束2)参考完整性约束3)函数依赖约束4)统计约束10/1/14数据库系统概论之数据库完整性第14页统计约束定义某个字段值一个关系多个元组统计值之间约束关系例：职员平均工资2倍<=部门经理工资<=职员平均工资5倍

10/1/15数据库系统概论之数据库完整性第15页完整性约束条件（续）4.动态列级约束动态列级约束是修改列定义或列值时应满足约束条件10/1/16数据库系统概论之数据库完整性第16页完整性约束条件（续） 1)修改列定义时约束 例：将原来允许空值列改为不允许空值时:该列当前已存在空值，则拒绝这种修改2)修改列值时约束修改列值时新旧值之间要满足约束条件例：职员工资调整>=原来工资年纪只能增加10/1/17数据库系统概论之数据库完整性第17页完整性约束条件（续）5.动态元组约束修改元组值:各个字段之间要满足约束条件例:职员工资调整不得低于其原来工资+工龄*1.510/1/18数据库系统概论之数据库完整性第18页完整性约束条件（续）6.动态关系约束关系改变前后状态：限制条件例：事务一致性、原子性等约束条件10/1/19数据库系统概论之数据库完整性第19页5.1实体完整性5.1.1实体完整性定义5.1.2实体完整性检验和违约处理10/1/20数据库系统概论之数据库完整性第20页5.1.1实体完整性定义实体完整性（EntityIntegrity）若属性A是基本关系R主属性，则属性A不能取空值例SAP(SUPERVISOR，SPECIALITY，POSTGRADUATE)POSTGRADUATE属性为主码（假设硕士不会重名），则其不能取空值10/1/21数据库系统概论之数据库完整性第21页5.1.1实体完整性定义关系模型必须恪守实体完整性规则原因实体完整性规则是针对基本关系而言。一个基本表通常对应现实世界一个实体集或多对多联络。(2)现实世界中实体和实体间联络都是可区分，即它们含有某种唯一性标识。(3)关系模型中以主码作为唯一性标识。(4)主码中属性即主属性不能取空值。空值就是“不知道”或“无意义”值。主属性取空值，就说明存在某个不可标识实体，即存在不可区分实体，这与第（2）点相矛盾，所以这个规则称为实体完整性。10/1/22数据库系统概论之数据库完整性第22页5.1.1实体完整性定义关系模型实体完整性在Createtable中用PrimaryKey定义。对单属性组成码有两种说明方法：定义为列级约束条件表级约束条件对多个属性组成码只有一个说明方法，即定义为表级约束条件10/1/23数据库系统概论之数据库完整性第23页[例题1]将Student表中Sno属性定义为码。Createtablestudent(Snochar(9)primarykey,/*在列级定义主码*/Snamechar(20)notnull,Ssexchar(2),Sagesmallint,Sdeptchar(20));5.1.1实体完整性定义10/1/24数据库系统概论之数据库完整性第24页5.1.1实体完整性定义或者Createtablestudent(snochar(9),Snamechar(20)notnull,Ssexchar(2),Sagesmallint,Sdeptchar(20),primarykey(sno),/*在表级定义主码*/);10/1/25数据库系统概论之数据库完整性第25页5.1.1实体完整性定义[例题2]将SC表中Sno,Cno属性组定义为码CreatetableSC(Snochar(9)notnull,Cnochar(4)notnull,gradesmallint,Primarykey(Sno,Cno),/*只能在表级定义主码*/)；10/1/26数据库系统概论之数据库完整性第26页5.1.2实体完整性检验和违约处理用primarykey短语定义了关系主码后，每当应用程序对基本表插入一条统计或者对主码列进行更新操作时，RDBMS将自动检验：检验主码值是否唯一，假如不唯一则拒绝插入或修改。检验主码地各个属性是否为空，只要一个为空就拒绝插入或修改10/1/27数据库系统概论之数据库完整性第27页5.1.2实体完整性检验和违约处理检验主码值是否唯一方法（2种）：全表扫描keyif2if3if4if5ikey1f21f31f41f51key2f22f32f42f52key3f23f33f43f53..10/1/28数据库系统概论之数据库完整性第28页5.1.2实体完整性检验和违约处理全表扫描缺点：耗时10/1/29数据库系统概论之数据库完整性第29页5.1.2实体完整性检验和违约处理第二种方法：建索引（如B+树索引）25511230667815202537304168697176798493515465新插入统计主码值10/1/30数据库系统概论之数据库完整性第30页5.1.2实体完整性检验和违约处理RDBMS关键普通都在主码上自动建立一个索引经过索引查找基本表中是否存在新主码值——提升效率10/1/31数据库系统概论之数据库完整性第31页5.2参考完整性5.2.1参考完整性定义5.2.2参考完整性检验和违约处10/1/32数据库系统概论之数据库完整性第32页5.2.1参考完整性定义关系模型参考完整性在Createtable中用foreignkey短语定义哪些列为外码。10/1/33数据库系统概论之数据库完整性第33页5.2.1参考完整性定义[例题3]定义SC中参考完整性。Createtablesc(snochar(9)notnull,cnochar(4)notnull,gradesmallint,Primarykey(sno,cno),/*在表级定义实体完整性*/foreignkey(sno)referencesstudent(sno),/*在表级定义参考完整性*/foreignkey(Cno)referencesstudent(Cno),/*在表级定义参考完整性*/)；10/1/34数据库系统概论之数据库完整性第34页5.2.2参考完整性检验和违约处理参考完整性将两个表中对应元组联络起来后。当进行增删操作时有可能破坏参考完整性，必须进行检验。10/1/35数据库系统概论之数据库完整性第35页5.2.2参考完整性检验和违约处理对SC和Students有4种可能破坏参考完整性情况：SC表中增加一个元组，该元组Sno属性值在Students中找不到一个元组，其Sno属性值与之相等。修改SC表中一个元组，修改后该元组Sno属性在Students中找不到一个元组，其Sno属性值与之相等。从Students表中删除一个元组，造成SC表中一些元组Sno属性值在Students中找不到一个元组，其Sno属性值与之相等。修改Students表中一个元组Sno属性，造成SC表中一些元组Sno属性值在Students中找不到一个元组，其Sno属性值与之相等10/1/36数据库系统概论之数据库完整性第36页表5.1可能破坏参考完整性情况及违约处理被参考表（Students）参考表（比如SC）违约处理可能破坏参考完整性插入元组拒绝可能破坏参考完整性修改外码值拒绝删除元组可能破坏参考完整性拒绝/级联删除/设置为空修改主码值可能破坏参考完整性拒绝/级联修改除/设置为空10/1/37数据库系统概论之数据库完整性第37页处理策略1、拒绝（Noaction）执行不允许该操作执行。该策略普通为默认策略。2、级联（Cascade）操作当删除或修改被参考表(Students)一个元组造成了与参考表（SC）不一致，则删除或修改参考表中全部造成不一致元组。比如，删除Students表中元组，Sno值为15121则要从SC表中级联删除SC.Sno=‘15121’全部元组。3、设置空值（Set-Null） 比如：学生（学号，姓名，性别，专业号，年纪）专业（专业号，专业名）专业号能够取空值但在学生-选课数据库中，Students关系为被参考关系，其主码为Sno，SC为参考关系，Sno为外码。若SCSno为空值，则表明。。。。10/1/38数据库系统概论之数据库完整性第38页处理策略若违反了参考完整性，系统选取默认策略，即拒绝执行。假如想让系统采取其它策略则必须在创建表时候显式地加以说明。10/1/39数据库系统概论之数据库完整性第39页[例题4]显式说明参考完整性违约处理CreatetableSC(snochar(9)notnull,cnochar(4)notnull,gradesmallint,Primarykey(sno,cno),/*在表级定义实体完整性*/foreignkey(sno)referencesstudent(sno),/*在表级定义参考完整性*/

ondeletecascade/*当删除student表中元组时，级联删除SC表中对应元组*/onupdatecascade,/*当更新student表中Sno时，级联更新SC表中对应元组*/foreignkey(sno)referencescourses(sno),,/*在表级定义参考完整性*/

ondeletenoAction/*当删除courses表中元组时造成了与SC表不一致拒绝删除*/onupdatecascade

)；10/1/40数据库系统概论之数据库完整性第40页5.3用户定义完整性5.3.1属性上约束条件定义5.3.2属性上约束条件检验和违约处理5.3.3元组上约束条件定义5.3.4元组上约束条件检验和违约处理10/1/41数据库系统概论之数据库完整性第41页5.3.1属性上约束条件定义属性值限制，包含：列值非空（Notnull短语）列值唯一（unique短语）检验列值是否满足一个布尔表示式（Check短语）10/1/42数据库系统概论之数据库完整性第42页5.3.1属性上约束条件定义1、不允许取空值[例题5]在定义SC表时，说明Sno,Cno,Grade属性不允许取空值。CreatetableSC(Snochar(9)notnull,Cnochar(40)notnull,Gradesmallintnotnull,Primarykey(Sno,Cno),)10/1/43数据库系统概论之数据库完整性第43页5.3.1属性上约束条件定义2、列值唯一[例题6]建立部门表Dept,要求部门名称Dname列取值唯一，部门编号Deptno列为主码。Createtabledept(Deptnonumeric(2)，Dnamechar(9)nuique,Locationchar(10),Primarykey(Deptno));10/1/44数据库系统概论之数据库完整性第44页5.3.1属性上约束条件定义3、用Check短语指定列值应该满足条件[例题7]Student表Ssex只允许取“男”或“女”。CreatetableStudents(Snochar(9)primarykey,Snomechar(8)notnull,Ssexchar(2)check(SsexIN(‘男’，’女’))，Sagesmallint,SdeptChar(20))10/1/45数据库系统概论之数据库完整性第45页5.3.1属性上约束条件定义[例题8]SC表Grade值应该在0和100之间。CreatetableSC(Snochar(9)notnull,Cnochar(4)notnull,GradesmallintCheck(Grade>=0andGrade<=100),primarykey(Sno,Cno),Foreignkey(Sno)referencesStudent(Sno),Foreignkey(Cno)referencesCourse(Cno));10/1/46数据库系统概论之数据库完整性第46页5.3用户定义完整性5.3.1属性上约束条件定义5.3.2属性上约束条件检验和违约处理5.3.3元组上约束条件定义5.3.4元组上约束条件检验和违约处理10/1/47数据库系统概论之数据库完整性第47页5.3.2属性上约束条件检验和违约处理当往表中插入元组或修改属性值时，RDBMS就检验属性上约束条件是否被满足，不然拒绝执行。10/1/48数据库系统概论之数据库完整性第48页5.3用户定义完整性5.3.1属性上约束条件定义5.3.2属性上约束条件检验和违约处5.3.3元组上约束条件定义5.3.4元组上约束条件检验和违约处10/1/49数据库系统概论之数据库完整性第49页5.3.3元组上约束条件定义与属性上约束条件定义类似，在Create语句中能够用Check短语定义元组上约束条件，即元组级限制。 [例题9]当学生性别是男时，其名字不能以Ms.打头。 CreatetableStudent (Snochar(9),Snamechar(8)notnull,Ssexchar(2),Sagesmallint,Sdeptchar(20),Primarykey(Sno),check(Sex=‘女’orSnamenotlike‘Ms.%’));10/1/50数据库系统概论之数据库完整性第50页5.3用户定义完整性5.3.1属性上约束条件定义5.3.2属性上约束条件检验和违约处5.3.3元组上约束条件定义5.3.4元组上约束条件检验和违约处10/1/51数据库系统概论之数据库完整性第51页5.3.4元组上约束条件检验和违约处理当往表中插入元组或修改属性值时，RDBMS就检验元组上约束条件是否被满足，不然拒绝执行。10/1/52数据库系统概论之数据库完整性第52页5.4完整性约束命名子句1、完整性约束命令子句语法：Constraint<完整性约束条件>[primarykey短语|foreignkey短语|check短语]10/1/53数据库系统概论之数据库完整性第53页5.4完整性约束命名子句[例题10]建立学生记录表Students，要求学号在90000~99999之间，姓名不能取空值，年纪小于30，性别只能是“男”或“女”。CreatetableStudents(Snonumeric(6)constraintC1check(Snobetween90000and99999),Snamechar(20)constraintC2notnull,Sagenumeric(3)constraintC3check(Sage<30),Ssexchar(2)constraintC4check(SsexIn(‘男’，‘女’))，constraintStudentKeyprimarykey(Sno));10/1/54数据库系统概论之数据库完整性第54页5.4完整性约束命名子句[例题11]建立教师表teacher,要求每个老师应发工资不低于3000元。（应发工资实际上就是实发工资列Sal与扣除项Deduct之和）Createtableteacher(Enonumeric(4)primarykey,Enamechar(10),Jobchar(8),Salnumeric(7,2),Deductnumeric(2),Contraintempkeyforeignkey(Deptno)referencesdept(deptno),ContrantC1check(Sal+Deduct>=3000));10/1/55数据库系统概论之数据库完整性第55页5.4完整性约束命名子句2.修改表中完整性限制使用Altertable语句[例题12]去掉[例题10]Students表中对性别限制。AltertablestudentDropconstraintC410/1/56数据库系统概论之数据库完整性第56页5.4完整性约束命名子句[例题13]修改表Students表中约束条件，要求学号改为800000~999999之间，年纪小于30改为45。先删除原来约束条件，再增加新约束条件。AltertablestudentsDropconstraintC1;AltertablestudentsAddconstraintC1check(Snobetween800000and999999),AltertablestudentsDropconstraintC3;AltertablestudentsAddconstraintC3check(Sage<45);10/1/57数据库系统概论之数据库完整性第57页5.5触发器5.5.1定义触发器5.5.2激活触发器5.5.3删除触发器10/1/58数据库系统概论之数据库完整性第58页5.5.1定义触发器1、触发器（Trigger）:定义在关系表上一类由事件驱动特殊过程。2、格式Createtrigger<触发器名>{Before|After}<触发事件>on<表名>Foreach{Row|Statement}[When<触发条件>]<触发动作体>10/1/59数据库系统概论之数据库完整性第59页

5.5.1定义触发器举例[例题18]定义一个Before行级触发器，为教师表Teacher定义完整性规则“教授工资不得低于4000元，假如低于4000元，自动改为4000元”。CreatetriggerInser_or_SalBeforeinsertorupdateonteacherForeachrowAsbeginIf(new.pJob=‘教授’)and(new.Sal<4000)Thennew.sal:=4000;EndIf;End；10/1/60数据库系统概论之数据库完整性第60页

5.5.1定义触发器[例题19]定义一个After行级触发器，当教师表Teacher工资发生改变后就自动在工资改变表Sal_log中增加一条对应纪录。CreatetableSal_log(Enonumric(4)ref