第五章数据库完整性Convertor

数据库原理咸阳师范学院信息工程学院第五章数据库完整性本章主要教学内容:5.1实体完整性5.2参照完整性5.3用户定义的完整性5.4完整性约束命名子句5.6触发器重点及难点:了解什么是数据库完整性掌握数据库的三类完整性第五章数据库完整性数据库的完整性数据的正确性和相容性防止不合语义的数据进入数据库。完整性：真实地反映现实世界1.提供定义完整性约束条件的机制2.提供完整性检查的方法3.违约处理提供定义完整性约束条件的机制完整性约束条件：数据模型的组成部分，约束数据库中数据的语义DBMS应提供定义数据库完整性约束条件，并把它们作为模式的一部分存入数据库中提供完整性检查的方法检查用户发出的操作请求是否违背了完整性约束条件违约处理如果发现用户的操作请求使数据违背了完整性约束条件，则采取一定的动作来保证数据的完整性。第五章数据库完整性5.1实体完整性5.2参照完整性5.3用户定义的完整性5.4完整性约束命名子句5.6触发器5.7小结5.1实体完整性5.1.1实体完整性定义5.1.2实体完整性检查和违约处理5.1.1实体完整性定义关系模型的实体完整性在CREATETABLE中用PRIMARYKEY定义。对单属性构成的码有两种说明方法列级约束条件表级约束条件对多个属性构成的码有一种说明方法表级约束条件实体完整性定义例1将Student表中Sno属性定义为码CREATETABLEStudent(SnoCHAR(9)PRIMARYKEY,/*在列级定义主码*/SnameCHAR(20)NOTNULL,SsexCHAR(2),SageSMALLINT,SdeptCHAR(20));或者实体完整性定义CREATETABLEStudent(SnoCHAR(9),SnameCHAR(20)NOTNULL,SsexCHAR(2),SageSMALLINT,SdeptCHAR(20),PRIMARYKEY(Sno)/*在表级定义主码*/);实体完整性定义例2将SC表中的Sno,Cno属性组定义为码CREATETABLESC(SnoCHAR(9)NOTNULL,CnoCHAR(4)NOTNULL,GradeSMALLINT,PRIMARYKEY(Sno,Cno)/*只能在表级定义主码*/);5.1实体完整性5.1.1实体完整性定义5.1.2实体完整性检查和违约处理5.1.2实体完整性检查和违约处理当用户程序对基本表插入一条记录或者对主码列进行更新操作时，RDBMS则进行实体完整性检查，包括1.检查主码值是否唯一，如果不唯一则拒绝插入或修改。2.检查主码的各个属性是否为空，只要有一个为空就拒绝插入或修改。从而保证了实体完整性。实体完整性检查和违约处理检查记录中主码值是否唯一的一种方法是进行全表扫描。依次判断表中每一条记录的主码值与将插入记录上的主码值（或者修改的新主码值）是否相同。Key1F21F31F41F51Key2F22F32F42F52Key3F23F33F43F53KeyiF2iF3iF4iF5i实体完整性检查和违约处理全表扫描是十分耗时的。为了避免对基本表进行全表扫描，RDBMS核心一般都在主码上自动建立一个索引，通过索引查找基本表中是否已经存在新的主码值，将大大提高效率。实体完整性检查和违约处理第五章数据库完整性5.1实体完整性5.2参照完整性5.3用户定义的完整性5.4完整性约束命名子句5.6触发器5.7小结5.2参照完整性5.2.1参照完整性定义5.2.2参照完整性检查和违约处理关系模型的参照完整性在CREATETABLE中用FOREIGNKEY短语定义哪些列为外码，用REFERENCES短语指明这些外码参照哪些表的主码。5.2.1参照完整性定义例3定义SC中的参照完整性CREATETABLESC（SnoCHAR(9)NOTNULL,CnoCHAR(4)NOTNULL,GradeSMALLINT,PRIMARYKEY(Sno,Cno),/*在表级定义实体完整性*/FOREIGNKEY(Sno)REFERENCESStudent(Sno),/*在表级定义参照完整性*/FOREIGNKEY(Cno)REFERENCESCourse(Cno)/*在表级定义参照完整性*/）；5.2参照完整性5.2.1参照完整性定义5.2.2参照完整性检查和违约处理5.2.2参照完整性检查和违约处理一个参照完整性将两个表中的相应元组联系起来了。对被参照表和参照表进行增删改操作时有可能破坏对照完整性，必须进行检查。参照完整性检查和违约处理当破坏参照完整性时，系统可以采用以下策略1拒绝（NOACTION）执行不允许该操作执行。该策略一般设置为默认策略。2级连（CASCADE）操作当删除或修改被参照表的一个元组造成了与参照表的不一致，则删除或修改参照表中的所有造成不一致的元组。参照完整性检查和违约处理3设置为空值（SET-NULL）当删除或修改被参照表的一个元组时造成了不一致，则将参照表中的所有造成不一致的元组的对应属性设置为空值。参照完整性检查和违约处理例4显式说明参照完整性的违约处理示例CREATETABLESC(SnoCHAR(9)NOTNULL,CnoCHAR(4)NOTNULL,GradeSMALLINT,PRIMARYKEY(Sno,Cno)，/*在表级定义实体完整性*/FOREIGNKEY(Sno)REFERENCESStudent(Sno)/*在表级定义参照完整性*/ONDELETECASCADE/*当删除student表中的元组时，级连删除SC表中相应的元组*/ONUPDATECASEADE，/*当更新student表中的sno时，级连更新SC表中相应的元组*/参照完整性检查和违约处理FOREIGNKEY(Cno)REFERENCESCourse(Cno)/*在表级定义参照完整性*/ONDELETENOACTION/*当删除course表中的元组造成与SC表不一致时拒绝删除*/ONUPDATECASEADE，/*当更新course表中的cno时，级连更新SC表中相应的元组*/);第五章数据库完整性5.1实体完整性5.2参照完整性5.3用户定义的完整性5.4完整性约束命名子句5.6触发器5.7小结用户定义的完整性就是针对某一具体应用的数据必须满足的语义要求。5.3用户定义的完整性5.3.1属性上的约束条件的定义5.3.2属性上的约束条件检查和违约处理5.3.3元组上的约束条件的定义5.3.4元组上的约束条件检查和违约处理5.3.1属性上的约束条件的定义在CREATETABLE中定义属性的同时可以根据应用要求，定义属性上的约束条件，即属性限制，包括：列值非空（NOTNULL短语）列值唯一（UNIQUE短语）检查列值是否满足一个布尔表达式（CHECK短语）列值非空（NOTNULL短语）例5在定义SC表时，说明Sno,Cno,Grade属性不允许取空值CREATETABLESC（SnoCHAR(9)NOTNULL,/*Sno属性不允许取空值*/CnoCHAR(4)NOTNULL,/*Cno属性不允许取空值*/GradeSMALLINTNOTNULL,/*Grade属性不允许取空值*/PRIMARYKEY(Sno,Cno),/*如果在表级定义实体完整性，隐含了Sno,Cno不允许取空值，则在列级不允许取空值的定义就不必须写了*/……）；列值唯一例6建立部门表DEPT，要求部门名称Dname列取值唯一，部门编号Deptno列为主码CREATETABLEDEPT（DeptnoNUMERIC(2),DnameCHAR(9)UNIQUE,/*要求Dname列值唯一*/LocationCHAR(10),PRIMARYKEY(Deptno)）；用CHECK短语指定列值应该满足的条件例7Student表的Ssex只允许取得“男”或“女”CREATETABLEStudent(SnoCHAR(9)PRIMARYKEY,/*在列级定义主码*/SnameCHAR(8)NOTNULL,/*Sname属性不允许取空值*/SsexCHAR(2)CHECK（SsexIN(‘男‘，’女‘）），/*性别属性Ssex只允许取‘男’或‘女’*/SageSMALLINT,SdeptCHAR(20));例8SC表的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));5.3.2属性上的约束条件检查和违约处理当往表中插入元组或修改属性的值时，RDBMS就检查属性上的约束条件是否被满足，如果不满足则操作被拒绝执行。5.3.3元组上的约束条件的定义在CREATETABLE语句中可以用CHECK短语定义元组上的约束条件，即元组级的限制。同属性值限制相比，元组级的限制可以设置不同属性之间的取值的相互约束条件。元组上的约束条件的定义例9当学生的性别是男时，其名字不能以Ms.打头CREATETABLEStudent(SnoCHAR(9),SnameCHAR(20)NOTNULL,SsexCHAR(2)，SageSMALLINT,SdeptCHAR(20),PRIMARYKEY(Sno),CHECK(Sex=‘女’ORSnameNOTLIKE‘Ms.%’));/*定义了元组中Sname和Ssex两个属性值之间的约束条件*/5.3.4元组上的约束条件检查和违约处理当往表中插入元组或修改属性的值时，RDBMS就检查元组上的约束条件是否被满足，如果不满足则操作被拒绝执行。第五章数据库完整性5.1实体完整性5.2参照完整性5.3用户定义的完整性5.4完整性约束命名子句5.6触发器5.7小结完整性约束命名子句SQL提供了完整性约束命名子句CONSTRAINT，用来对完整性约束条件命名。1.完整性约束命名子句2.修改表中的完整性限制1.完整性约束命名子句语句格式:CONSTRAINT完整性约束条件名[PRIMARYKEY短语|FOREIGNKEY短语|CHECK短语]完整性约束命名子句例10建立学生登记表Student，要求学号在90000~99999之间，姓名不能取空值，年龄小于30，性别只能是“男”或“女”CREATETABLEStudent(SnoNUMERIC(6)CONSTRAINTC1CHECK(SnoBETWEEN90000AND99999),SnameCHAR(20)CONSTRAINTC2NOTNULL,SageNUMERIC(3)CONSTRAINTC3CHECK(Sage30),SsexCHAR(2)CONSTRAINTC4CHECK(SsexIN(‘男‘，’女‘），CONSTRAINTStudentKeyPRIMARYKEY(Sno));完整性约束命名子句例11建立教师表TEACHER，要求每个教师的应发工资不低于3000元。（应发工资实际上就是实发工资列Sal与扣除项Deduct之和）CREATETABLETEACHER（EnoNUMERIC(4)PRIMARYKEY,EnameCHAR(10),JobCHAR(8),SalNUMERIC(7,2),DeductNUMERIC(2),DeptnoNUMERIC(2),CONSTRAINTEMPFKeyFOREIGNKEY(Deptno)REFERENCESDEPT(Deptno),CONSTRAINTSC1CHECK(Sal+Deduct=3000));2.修改表中的完整性限制使用ALTERTABLE语句修改表中的完整性限制例12去掉[例10]student表中对性别的限制ALTERTABLEStudentDROPCONSTRAINTC4；修改表中的完整性限制例13修改表Student中