1数据库系统概论AnIntroductiontoDatabaseSystem第五章数据库完整性潍坊科技学院2数据库完整性数据库的完整性是指数据库的正确性和数据库的相容性。数据的正确性是指数据是符合现实世界语义,反映了当前实际状况的;数据的相容性是指数据库同一对象在不同关系表中的数据是符合逻辑的;例如:学生的学号必须唯一;性别只能是男或女;本科学生年龄的取值范围为14~50的整数;学生所选的课程必须是学校开设的课程,学生所在的院系必须是学校已成立的院系等;3数据库完整性(续)数据的完整性和安全性是两个不同概念数据的完整性防止数据库中存在不符合语义的数据,也就是防止数据库中存在不正确的数据;防范对象:不合语义的、不正确的数据;数据的安全性保护数据库防止恶意的破坏和非法的存取;防范对象:非法用户和非法操作;4数据库完整性(续)为维护数据库的完整性,数据库管理系统必须:1.提供定义完整性约束条件的机制完整性约束条件也称为完整性规则,是数据库中的数据必须满足的语义约束条件;SQL标准使用了一系列概念来描述完整性,包括关系模型的实体完整性、参照完整性和用户定义完整性;这些完整性一般由SQL的数据定义语言语句来实现;5数据库完整性(续)2.提供完整性检查的方法数据库管理系统中检查数据是否满足完整性约束条件的机制称为完整性检查;一般在INSERT、UPDATE、DELETE语句执行后开始检查,也可以在事务提交时检查;6数据库完整性(续)3.违约处理数据库管理系统若发现用户的操作违背了完整性约束条件,就采取一定的动作:拒绝(NOACTION)执行该操作级连(CASCADE)执行其他操作7第五章数据库完整性5.1实体完整性5.2参照完整性5.3用户定义的完整性5.4完整性约束命名字句*5.5域中的完整性限制5.6断言5.7触发器5.8小结85.1实体完整性5.1.1实体完整性定义5.1.2实体完整性检查和违约处理95.1.1实体完整性定义关系模型的实体完整性CREATETABLE中用PRIMARYKEY定义单属性构成的码有两种说明方法定义为列级约束条件定义为表级约束条件对多个属性构成的码只有一种说明方法定义为表级约束条件10实体完整性定义(续)[例5.1]将Student表中的Sno属性定义为码(1)在列级定义主码CREATETABLEStudent(SnoCHAR(9)PRIMARYKEY,SnameCHAR(20)NOTNULL,SsexCHAR(2),SageSMALLINT,SdeptCHAR(20));11实体完整性定义(续)(2)在表级定义主码CREATETABLEStudent(SnoCHAR(9),SnameCHAR(20)NOTNULL,SsexCHAR(2),SageSMALLINT,SdeptCHAR(20),PRIMARYKEY(Sno));12实体完整性定义(续)[例5.2]将SC表中的Sno,Cno属性组定义为码CREATETABLESC(SnoCHAR(9)NOTNULL,CnoCHAR(4)NOTNULL,GradeSMALLINT,PRIMARYKEY(Sno,Cno)/*只能在表级定义主码*/);135.1实体完整性5.1.1实体完整性定义5.1.2实体完整性检查和违约处理145.1.2实体完整性检查和违约处理插入或对主码列进行更新操作时,关系数据库管理系统按照实体完整性规则自动进行检查。包括:检查主码值是否唯一,如果不唯一则拒绝插入或修改检查主码的各个属性是否为空,只要有一个为空就拒绝插入或修改15实体完整性检查和违约处理(续)检查记录中主码值是否唯一的一种方法是进行全表扫描依次判断表中每一条记录的主码值与将插入记录上的主码值(或者修改的新主码值)是否相同16实体完整性检查和违约处理(续)表扫描缺点十分耗时为避免对基本表进行全表扫描,RDBMS核心一般都在主码上自动建立一个索引17实体完整性检查和违约处理(续)B+树索引例如,新插入记录的主码值是25通过主码索引,从B+树的根结点开始查找读取3个结点:根结点(51)、中间结点(1230)、叶结点(152025)该主码值已经存在,不能插入这条记录18第五章数据库完整性5.1实体完整性5.2参照完整性5.3用户定义的完整性5.4完整性约束命名字句*5.5域中的完整性限制5.6断言5.7触发器5.8小结19实体完整性内容总结关系模型的实体完整性CREATETABLE中用PRIMARYKEY定义单属性构成的码有两种说明方法定义为列级约束条件定义为表级约束条件对多个属性构成的码只有一种说明方法定义为表级约束条件205.2参照完整性5.2.1参照完整性定义5.2.2参照完整性检查和违约处理215.2.1参照完整性定义关系模型的参照完整性定义在CREATETABLE中用FOREIGNKEY短语定义哪些列为外码用REFERENCES短语指明这些外码参照哪些表的主码22参照完整性定义(续)例如,关系SC中(Sno,Cno)是主码。Sno,Cno分别参照Student表的主码和Course表的主码[例5.3]定义SC中的参照完整性CREATETABLESC(SnoCHAR(9)NOTNULL,CnoCHAR(4)NOTNULL,GradeSMALLINT,PRIMARYKEY(Sno,Cno),/*在表级定义实体完整性*/FOREIGNKEY(Sno)REFERENCESStudent(Sno),/*在表级定义参照完整性*/FOREIGNKEY(Cno)REFERENCESCourse(Cno)/*在表级定义参照完整性*/);235.2参照完整性5.2.1参照完整性定义5.2.2参照完整性检查和违约处理24参照完整性检查和违约处理一个参照完整性将两个表中的相应元组联系起来当对被参照表和参照表进行增删改操作时有可能破坏参照完整性,必须进行检查25参照完整性检查和违约处理(续)例如,对表SC和Student有四种可能破坏参照完整性的情况:SC表中增加一个元组,该元组的Sno属性的值在表Student中找不到一个元组,其Sno属性的值与之相等。修改SC表中的一个元组,修改后该元组的Sno属性的值在表Student中找不到一个元组,其Sno属性的值与之相等。从Student表中删除一个元组,造成SC表中某些元组的Sno属性的值在表Student中找不到一个元组,其Sno属性的值与之相等。修改Student表中一个元组的Sno属性,造成SC表中某些元组的Sno属性的值在表Student中找不到一个元组,其Sno属性的值与之相等26参照完整性检查和违约处理(续)表5.1可能破坏参照完整性的情况及违约处理被参照表(例如Student)参照表(例如SC)违约处理可能破坏参照完整性插入元组拒绝可能破坏参照完整性修改外码值拒绝删除元组可能破坏参照完整性拒绝/级连删除/设置为空值修改主码值可能破坏参照完整性拒绝/级连修改/设置为空值27参照完整性检查和违约处理(续)参照完整性违约处理(1)拒绝(NOACTION)执行不允许该操作执行。该策略一般设置为默认策略(2)级联(CASCADE)操作当删除或修改被参照表(Student)的一个元组造成了与参照表(SC)的不一致,则删除或修改参照表中的所有造成不一致的元组(3)设置为空值(SET-NULL)当删除或修改被参照表的一个元组时造成了不一致,则将参照表中的所有造成不一致的元组的对应属性设置为空值。28参照完整性检查和违约处理(续)例如,有下面2个关系学生(学号,姓名,性别,专业号,年龄)专业(专业号,专业名)假设专业表中某个元组被删除,专业号为12按照设置为空值的策略,就要把学生表中专业号=12的所有元组的专业号设置为空值对应语义:某个专业删除了,该专业的所有学生专业未定,等待重新分配专业外码29参照完整性检查和违约处理(续)对于参照完整性的,除了应该定义外码,还应定义外码列是否允许空值30参照完整性检查和违约处理(续)[例5.4]显式说明参照完整性的违约处理示例CREATETABLESC(SnoCHAR(9)NOTNULL,CnoCHAR(4)NOTNULL,GradeSMALLINT,PRIMARYKEY(Sno,Cno),FOREIGNKEY(Sno)REFERENCESStudent(Sno)ONDELETECASCADE/*级联删除SC表中相应的元组*/ONUPDATECASCADE,/*级联更新SC表中相应的元组*/FOREIGNKEY(Cno)REFERENCESCourse(Cno)ONDELETENOACTION/*当删除course表中的元组造成了与SC表不一致时拒绝删除*/ONUPDATECASCADE/*当更新course表中的cno时,级联更新SC表中相应的元组*/);31第五章数据库完整性5.1实体完整性5.2参照完整性5.3用户定义的完整性5.4完整性约束命名字句*5.5域中的完整性限制5.6断言5.7触发器5.8小结325.3用户定义的完整性用户定义的完整性是:针对某一具体应用的数据必须满足的语义要求关系数据库管理系统提供了定义和检验用户定义完整性的机制,不必由应用程序承担335.3用户定义的完整性5.3.1属性上的约束条件5.3.2元组上的约束条件341.属性上约束条件的定义CREATETABLE时定义属性上的约束条件有3个列值非空(NOTNULL)列值唯一(UNIQUE)检查列值是否满足一个条件表达式(CHECK)35属性上约束条件的定义(续)(1)列值不允许取空值[例5.5]在定义SC表时,说明Sno、Cno、Grade属性不允许取空值。CREATETABLESC(SnoCHAR(9)NOTNULL,CnoCHAR(4)NOTNULL,GradeSMALLINTNOTNULL,PRIMARYKEY(Sno,Cno),…/*如果在表级定义实体完整性,隐含了Sno,Cno不允许取空值,则在列级不允许取空值的定义可以不写*/);36属性上约束条件的定义(续)(2)列值唯一[例5.6]建立部门表DEPT,要求部门名称Dname列取值唯一,部门编号Deptno列为主码CREATETABLEDEPT(DeptnoNUMERIC(2),DnameCHAR(9)UNIQUENOTNULL,/*要求Dname列值唯一,并且不能取空值*/LocationCHAR(10),PRIMARYKEY(Deptno));37属性上约束条件的定义(续)(3)用CHECK短语指定列值应该满足的条件[例5.7]Student表的Ssex只允许取“男”或“女”。CREATETABLEStudent(SnoCHAR(9)PRIMARYKEY,SnameCHAR(8)NOTNULL,SsexCHAR(2)CHECK(SsexIN(‘男’,’女’)),/*性别属性Ssex只允许取'男'或'女'*/SageSMALLINT,SdeptCHAR(20));38属性上约束条件的定义(续)[例5.8]SC表的Grade的值应该在0和100之间。CREATETABLESC(SnoCHAR(9),CnoCHAR(4),GradeSMALLINTCHECK(Grade=0ANDGrade=100),/*Grade取值范围是0到100*/PRIMARYKEY(Sno,Cno),FOREIGNKEY(Sno)REFERENCESStudent(Sno),FOREIGNKEY(Cno)REFERENCESCourse(Cno));39属性上的约束条件检查和违约处理插入元组或修改属性的值时,关系数据库管理系统检查属性上的约束条件是否被满足如果不满足则操作被拒绝执行2.属性上的约束条件检查和违约处理405.3用户定义的完整性5.3.1属性上的约束条件5.3.2元组上的约束条件411.元组上约束条件的定义在C