第10章数据库完整性.ppt

刘安丰刘安丰()1数据库原理及应用数据库原理及应用数据库原理及应用数据库原理及应用Principle and Application of Database第十章第十章 数据库完整性数据库完整性刘安丰刘安丰()2数据库原理及应用数据库原理及应用学习目标学习目标n理解完整性约束条件理解完整性约束条件n掌握完整性控制掌握完整性控制n掌握掌握SQL Server的完整性技术的完整性技术刘安丰刘安丰()3数据库原理及应用数据库原理及应用10.1 完整性约束条件完整性约束条件数据库的完整性：数据库的完整性：指数据的正确性和有效性。如学生的年龄必指数据的正确性和有效性。如学生的年龄必须是整数，取值范围为须是整数，取值范围为1825；学生的性别只能是男或女；学；学生的性别只能是男或女；学生的学号必须唯一；学生所在的系必须是学校开设的系等。完生的学号必须唯一；学生所在的系必须是学校开设的系等。完整性不同于安全性，前者是为了防止数据库中存在不符合语义整性不同于安全性，前者是为了防止数据库中存在不符合语义的数据，防止错误信息的输入和输出；后者是为了防止非法用的数据，防止错误信息的输入和输出；后者是为了防止非法用户和非法操作。户和非法操作。完整性约束条件：完整性约束条件：是数据模型组成部分中施加在数据库数据之是数据模型组成部分中施加在数据库数据之上的语义约束条件，是完整性控制的核心，上的语义约束条件，是完整性控制的核心，DBMS应提供定义应提供定义数据库完整性约束条件，并把它们作为模式的一部分存入数据数据库完整性约束条件，并把它们作为模式的一部分存入数据库中。其作用的对象可以是列、元组、关系。列约束主要是列库中。其作用的对象可以是列、元组、关系。列约束主要是列的类型、取值范围、精度、排序等约束条件；元组约束是元组的类型、取值范围、精度、排序等约束条件；元组约束是元组中各个字段间联系的约束；关系的约束是若干元组间、关系集中各个字段间联系的约束；关系的约束是若干元组间、关系集合上以及关系之间的联系的约束。完整性约束条件涉及上述三合上以及关系之间的联系的约束。完整性约束条件涉及上述三类对象，其状态可以静态的，也可以是动态的。类对象，其状态可以静态的，也可以是动态的。刘安丰刘安丰()4数据库原理及应用数据库原理及应用静态约束：静态约束：指数据库每一确定状态时的数据对象所应满足的约指数据库每一确定状态时的数据对象所应满足的约束条件，反映了数据库状态合理性的约束，是最重要的一类完束条件，反映了数据库状态合理性的约束，是最重要的一类完整性约束。整性约束。动态约束：动态约束：指数据库从一种状态转变为另一种状态时，新、旧指数据库从一种状态转变为另一种状态时，新、旧值之间所应满足的约束条件，反映了数据库状态变迁的约束。值之间所应满足的约束条件，反映了数据库状态变迁的约束。六类完整性约束条件六类完整性约束条件静态列级约束：静态列级约束：对一个列的取值域的说明，是最常用也最容易对一个列的取值域的说明，是最常用也最容易实现的一类完整性约束。它包括以下几个方面：实现的一类完整性约束。它包括以下几个方面：对数据类型的约束对数据类型的约束(包括数据的类型、长度、单位、精度等包括数据的类型、长度、单位、精度等)。对数据格式的约束。如出生日期的格式为对数据格式的约束。如出生日期的格式为YY.MM.DD。对取值范围或取值集合的约束。如性别的取值范围是对取值范围或取值集合的约束。如性别的取值范围是男男,女女。对空值的约束。如学号不能为空，成绩可以为空。对空值的约束。如学号不能为空，成绩可以为空。其他约束。如关于列的排序。其他约束。如关于列的排序。刘安丰刘安丰()5数据库原理及应用数据库原理及应用静态元组约束：静态元组约束：一个元组由若干列组成，它规定元组的各个列一个元组由若干列组成，它规定元组的各个列之间的约束关系。如规定订货关系中满足发货量之间的约束关系。如规定订货关系中满足发货量=2000元。元。静态关系约束：静态关系约束：在一个关系的各个元组之间或者若干关系之间在一个关系的各个元组之间或者若干关系之间常常存在各种联系或约束。常见的静态关系约束有：常常存在各种联系或约束。常见的静态关系约束有：实体完整性约束。实体完整性约束。参照完整性约束。参照完整性约束。实体完整性和参照完整性约束称为关系的两个不变性。实体完整性和参照完整性约束称为关系的两个不变性。函数依赖约束。函数依赖约束。统计约束。如规定部门经理的工资不得低于该部门职工平均工统计约束。如规定部门经理的工资不得低于该部门职工平均工资的资的2倍，不得高于该部门职工平均工资的倍，不得高于该部门职工平均工资的5倍。倍。动态列级约束：动态列级约束：修改列定义或列值时应满足的约束条件。如允修改列定义或列值时应满足的约束条件。如允许为空修改为不允许为空、学生年龄只能增长。许为空修改为不允许为空、学生年龄只能增长。刘安丰刘安丰()6数据库原理及应用数据库原理及应用n动态元组约束：动态元组约束：修改元组值时元组中各个字段应满足的约束条修改元组值时元组中各个字段应满足的约束条件。如职工工资调整时新工资不得低于原工资件。如职工工资调整时新工资不得低于原工资+工龄工龄*1.5。n动态关系约束：动态关系约束：加在关系变化前后状态上的限制条件。如事务加在关系变化前后状态上的限制条件。如事务的一致性、原子性等约束条件。的一致性、原子性等约束条件。列列 级级 元元 组组 级级 关关 系系 级级 静静 态态 列列类型类型列列格式格式列列值域值域列列空值空值 元组值应满足的条件元组值应满足的条件实体完整性约束实体完整性约束参照完整性约束参照完整性约束函数依赖约束函数依赖约束统计约束统计约束 动动 态态 改变列定义改变列定义或列值或列值 元组新旧值之间应满足元组新旧值之间应满足的约束条件的约束条件 关系新旧状态间应关系新旧状态间应满足的约束条件满足的约束条件 刘安丰刘安丰()7数据库原理及应用数据库原理及应用10.2 10.2 完整性控制完整性控制nDBMS的完整性控制机制应具有以下三方面功能的完整性控制机制应具有以下三方面功能n定义功能：定义功能：提供定义完整性约束条件的机制。提供定义完整性约束条件的机制。n检查功能：检查功能：检查用户的操作请求是否违背了完整性约束条件。检查用户的操作请求是否违背了完整性约束条件。n立即执行的约束立即执行的约束(Immediate constraints)：语句执行完后立即语句执行完后立即检查是否违背完整性约束。如银行数据库中检查是否违背完整性约束。如银行数据库中“借贷总金额应平借贷总金额应平衡衡”的约束就应该是延迟执行的约束，从账号的约束就应该是延迟执行的约束，从账号A转一笔钱到账转一笔钱到账号号B为一个事务，从账号为一个事务，从账号A转出去钱后账就不平了，必须等转入转出去钱后账就不平了，必须等转入账号账号B后账才能重新平衡，这时才能进行完整性检查。后账才能重新平衡，这时才能进行完整性检查。n延迟执行的约束延迟执行的约束(Deferred constrainsts)：完整性检查延迟到完整性检查延迟到整个事务执行结束后进行。整个事务执行结束后进行。n违约反应：违约反应：若用户的请求使数据违背了完整性约束条件，则采若用户的请求使数据违背了完整性约束条件，则采取一定的动作来保证数据的完整性。取一定的动作来保证数据的完整性。刘安丰刘安丰()8数据库原理及应用数据库原理及应用完整性规则的五元组表示：完整性规则的五元组表示：(D,O,A,C,P)，其中：其中：D(Data)：约束作用的数据对象。约束作用的数据对象。O(Operation)：触发完整性检查的数据库操作，即当用户发出触发完整性检查的数据库操作，即当用户发出什么操作请求时需要检查该完整性规则，立即或延迟检查。什么操作请求时需要检查该完整性规则，立即或延迟检查。A(Assertion)：数据对象必须满足的断言，这是规则的主体。数据对象必须满足的断言，这是规则的主体。C(Condition)：选择选择A作用的数据对象值的谓词。作用的数据对象值的谓词。P(Procedure)：违反完整性规则时触发的过程。违反完整性规则时触发的过程。如在如在“学号不能为空学号不能为空”的约束中，的约束中，D为为Sno属性；属性；O为插入或修为插入或修改改Student 元组时；元组时；A为为Sno不能为空；不能为空；C无无(A可作用于所有记可作用于所有记录的录的Sno属性属性)；P为拒绝执行该操作。为拒绝执行该操作。又如在又如在“教授工资不得教授工资不得低于低于1000元元”的约束中，的约束中，D为工资为工资Sal属性；属性；O为插入或修改职为插入或修改职工元组时；工元组时；A为为Sal不能小于不能小于1000；C为职称为职称=教授教授(A仅作用仅作用于职称于职称=教授教授的记录的记录)；P为拒绝执行该操作。为拒绝执行该操作。刘安丰刘安丰()9数据库原理及应用数据库原理及应用关系系统三类完整性的实现：关系系统三类完整性的实现：关系数据库系统都提供了定义和关系数据库系统都提供了定义和检查实体完整性、参照完整性和用户定义完整性的功能。对于检查实体完整性、参照完整性和用户定义完整性的功能。对于违反实体完整性规则和用户定义完整性规则的操作一般采用拒违反实体完整性规则和用户定义完整性规则的操作一般采用拒绝执行的方式进行处理。而对于违反参照完整性的操作，并不绝执行的方式进行处理。而对于违反参照完整性的操作，并不都是简单地拒绝执行，有时要根据应用语义执行一些附加的操都是简单地拒绝执行，有时要根据应用语义执行一些附加的操作，以保证数据库的正确性。作，以保证数据库的正确性。参照完整性的实现：参照完整性的实现：如职工如职工部门数据库中包含职工表部门数据库中包含职工表EMP和部门表和部门表DEPT，DEPT的主码为部门号的主码为部门号Deptno，EMP的主码的主码为职工号为职工号Empno，外码为部门号外码为部门号Deptno，称称EMP为参照关系，为参照关系，DEPT为被参照关系或目标关系。为被参照关系或目标关系。RDBMS实现参照完整性时实现参照完整性时需要考虑以下四个方面的问题：需要考虑以下四个方面的问题：外码能否可以接受空值：外码能否可以接受空值：依赖于应用环境的语义，系统除应提依赖于应用环境的语义，系统除应提供定义外码机制外，还提供定义外码列允许为空否的机制供定义外码机制外，还提供定义外码列允许为空否的机制 刘安丰刘安丰()10数据库原理及应用数据库原理及应用 如职工如职工部门数据库中部门数据库中EMP表有外码表有外码Deptno，某元组的这一某元组的这一列若为空值，表示这个职工尚未分配到任何具体的部门工作，列若为空值，表示这个职工尚未分配到任何具体的部门工作，和应用环境的语义是相符的。但学生和应用环境的语义是相符的。但学生选课数据库中选课数据库中Student关系为被参照关系，其主码为关系为被参照关系，其主码为Sno；SC为参照关系，外码为为参照关系，外码为Sno。若若SC的的Sno为空值：表明尚不存在的某个学生，或者某为空值：表明尚不存在的某个学生，或者某个不知学号的学生，选修了某门课程，其成绩记录在个不知学号的学生，选修了某门课程，其成绩记录在Grade中，中，与学校的应用环境是不相符的，因此与学校的应用环境是不相符的，因此SC的的Sno列不能取空值。列不能取空值。在被参照关系中删除元组时的问题：在被参照关系中删除元组时的问题：当删除被参照关系的某个当删除被参照关系的某个元组元组，而参照关系有若干元组的外码值与被删除的被参照关系而参照关系有若干元组的外码值与被删除的被参照关系的主码值相同，这时有三种不同策略：的主码值相同，这时有三种不同策略：级联删除：级联删除：将参照关系中外码值与被参照关系中要删除元组主将参照关系中外码值与被参照关系中要删除元组主码值相对应的元组一起删除。码值相对应的元组一起删除。受限删除：受限删除：当参照关系中没有任何元组的外码值与要删除的被当参照关系中没有任何元组的外码值与要删除的被参照关系的元组的主码值相对应时，系统才执行删除操作，否参照关系的元组的主码值相对应时，系统才执行删除操作，否则拒绝此删除操作。则拒绝此删除操作。刘安丰刘安丰()11数据库原理及应用数据库原理及应用置空值删除：置空值删除：删除被参照关系的元组，并将参照关系中与被参删除被参照关系的元组，并将参照关系中与被参照关系中被删除元组主码值相等的外码值置为空值。照关系中被删除元组主码值相等的外码值置为空值。如要删除如要删除Student关系中关系中Sno=95001的元组，而的元组，而SC关系中有关系中有4个元组的个元组的Sno都等于都等于95001。级联删除：将。级联删除：将SC关系中所有关系中所有4个个Sno=95001的元组一起删除，如果参照关系同时又是另一个关的元组一起删除，如果参照关系同时又是另一个关系的被参照关系，则这种删除操作会继续级联下去；受限删除：系的被参照关系，则这种删除操作会继续级联下去；受限删除：系统将拒绝执行此删除操作；置空值删除：将系统将拒绝执行此删除操作；置空值删除：将SC关系中所有关系中所有Sno=95001的元组的的元组的Sno值置为空值。在学生选课数据库中，值置为空值。在学生选课数据库中，显然前两种方法都正确，第三种方法不符合应用环境语义。显然前两种方法都正确，第三种方法不符合应用环境语义。在参照关系中插入元组时的问题：在参照关系中插入元组时的问题：当参照关系插入某个元组，当参照关系插入某个元组，而被参照关系不存在相应的元组，其主码值与参照关系插入元而被参照关系不存在相应的元组，其主码值与参照关系插入元组的外码值相同，这时可以两种策略：组的外码值相同，这时可以两种策略：受限插入：受限插入：仅当被参照关系中存在相应的元组，其主码值与参仅当被参照关系中存在相应的元组，其主码值与参照关系插入元组的外码值相同时，系统才执行插入操作，否则照关系插入元组的外码值相同时，系统才执行插入操作，否则拒绝此操作。拒绝此操作。刘安丰刘安丰()12数据库原理及应用数据库原理及应用递归插入：递归插入：首先向被参照关系中插入相应的元组，其主码值等首先向被参照关系中插入相应的元组，其主码值等于参照关系插入元组的外码值，然后向参照关系插入元组。于参照关系插入元组的外码值，然后向参照关系插入元组。如向如向SC关系插入关系插入(99001,1,90)元组，而元组，而Student关系中尚没有关系中尚没有Sno=99001的学生。受限插入：系统将拒绝向的学生。受限插入：系统将拒绝向SC关系插入该关系插入该元组；递归插入：系统将首先向元组；递归插入：系统将首先向Student关系插入关系插入Sno=99001的元组，然后向的元组，然后向SC关系插入关系插入(99001,1,90)元组。元组。修改关系中主码的问题：修改关系中主码的问题：要修改被参照关系中某些元组的主码要修改被参照关系中某些元组的主码值，而参照关系中有些元组的外码值正好等于被参照关系要修值，而参照关系中有些元组的外码值正好等于被参照关系要修改的主码值；要修改参照关系中某些元组的主码值，而被参照改的主码值；要修改参照关系中某些元组的主码值，而被参照关系中没有任何元组的外码值等于被参照关系修改后的主码值。关系中没有任何元组的外码值等于被参照关系修改后的主码值。一般有两种策略：一般有两种策略：不允许修改主码：不允许修改主码：有的有的DBMS不允许修改关系的主码值，若需不允许修改关系的主码值，若需要修改主码值，只能先删除该元组，然后再把具有新主码值的要修改主码值，只能先删除该元组，然后再把具有新主码值的元组插入到关系中。元组插入到关系中。允许修改主码允许修改主码刘安丰刘安丰()13数据库原理及应用数据库原理及应用 若修改的是被参照关系，则与删除类似，有三种策略：若修改的是被参照关系，则与删除类似，有三种策略：级联修改：级联修改：修改被参照关系主码值的同时，修改参照关系中相修改被参照关系主码值的同时，修改参照关系中相应的外码值。应的外码值。受限修改：受限修改：拒绝此修改操作。只当参照关系中没有任何元组的拒绝此修改操作。只当参照关系中没有任何元组的外码值等于被参照关系中某个元组的主码值时，才能被修改。外码值等于被参照关系中某个元组的主码值时，才能被修改。置空值修改：置空值修改：修改被参照关系主码值的同时，将参照关系中相修改被参照关系主码值的同时，将参照关系中相应的外码值置为空值。应的外码值置为空值。如将如将Student表中表中Sno=95001元组的元组的Sno值改为值改为95123，而，而SC表中有表中有 4个元组的个元组的Sno=95001。级联修改：将级联修改：将SC表中表中4个个Sno=95001元组的元组的Sno值也改为值也改为95123，若参照关系同时又是，若参照关系同时又是另一个关系的被参照关系，则这种修改会继续级联下去；受限另一个关系的被参照关系，则这种修改会继续级联下去；受限修改：只有修改：只有SC表中没有任何元组的表中没有任何元组的Sno=95001时，才能将时，才能将Student表中表中Sno=95001元组的元组的Sno值改为值改为95123；置空值修；置空值修改：将改：将Student表中表中Sno=95001元组的元组的Sno值改为值改为95123，而，而将将SC表中所有表中所有Sno=95001元组的元组的Sno值置为空值。值置为空值。刘安丰刘安丰()14数据库原理及应用数据库原理及应用 显然在学生选课数据库中只有第一种方法是正确的。若修改的是显然在学生选课数据库中只有第一种方法是正确的。若修改的是参照关系，则与插入类似，有受限修改、递归修改两种策略。参照关系，则与插入类似，有受限修改、递归修改两种策略。参照完整性的实现：参照完整性的实现：RDBMS在实现参照完整性时，除了要向用在实现参照完整性时，除了要向用户提供定义主码、外码的机制，还需要向用户提供不同的策略供户提供定义主码、外码的机制，还需要向用户提供不同的策略供用户选择。选择哪种策略，都要根据应用环境的要求确定。用户选择。选择哪种策略，都要根据应用环境的要求确定。刘安丰刘安丰()15数据库原理及应用数据库原理及应用10.3 SQL Server的完整性技术的完整性技术SQL Server的实体完整性：的实体完整性：SQL Server在在CREATE TABLE语语句中提供了句中提供了PRIMARY KEY子句，供用户在建表时指定关系的子句，供用户在建表时指定关系的主码。主码。例例1在学生选课数据库中，定义在学生选课数据库中，定义Student表的表的Sno属性为主码。属性为主码。CREATE TABLE Student (Sno CHAR(5)PRIMARY KEY,Sname CHAR(6),Sage INT);/可在可在PRIMARY前加前加自定义约束名自定义约束名例例2在在SC表中定义表中定义(Sno,Cno)为主码。为主码。CREATE TABLE SC(Sno CHAR(5),Cno CHAR(2),Grade INT,CONSTRAINT PK_SC PRIMARY KEY(Sno,Cno);SQL Server的参照完整性：的参照完整性：SQL Server的的CREATE TABLE语语句也可以定义参照完整性规则。即用句也可以定义参照完整性规则。即用FOREIGN KEY子句定义子句定义哪些列为外码，用哪些列为外码，用REFERENCES子句指明外码相应于哪个表子句指明外码相应于哪个表的主码。的主码。刘安丰刘安丰()16数据库原理及应用数据库原理及应用例例3 建立仓库和职工表：建立仓库和职工表：CREATE TABLE 仓库仓库 (仓库号仓库号 NUMERIC(4)PRIMARY KEY,城市城市 VARCHAR(10),面积面积 INT)CREATE TABLE 职工职工(职工号职工号 NUMERIC(4)PRIMARY KEY,工资工资 INT,仓库号仓库号 NUMERIC(4)FOREIGN KEY REFERENCES 仓库仓库(仓库号仓库号)SQL Server的自定义完整性的自定义完整性使用使用CREATE TABLE语句定义三类用户完整性约束：语句定义三类用户完整性约束：列值非空列值非空(NOT NULL)、列值唯一列值唯一(UNIQUE)、检查列值是否满足一个布尔表达式检查列值是否满足一个布尔表达式(CHECK子句子句)、默、默认值认值(DEFAULT)。例例4建立订购单建立订购单 CREATE TABLE 订购单订购单(订购单号订购单号 CHAR(5)PRIMARY KEY,职工号职工号 CHAR(5)NULL FOREIGN KEY REFERENCES 职工职工,供应商号供应商号 CHAR(5)NULL FOREIGN KEY REFERENCES 供应商供应商,订购日期订购日期 DATETIME DEFAULT getdate(),订购数量订购数量 INT CHECK(订购数量订购数量=10 AND订购数量订购数量=ALL(SELECT COUNT(SC.S#)FROM S,SC WHERE S.S#=SC.S#AND SEX=M GROUP BY C#)刘安丰刘安丰()18数据库原理及应用数据库原理及应用使用触发器定义用户完整性规则使用触发器定义用户完整性规则例例7建立触发器：拒绝一名职工对应多个仓库的情况，保证了语建立触发器：拒绝一名职工对应多个仓库的情况，保证了语义义“一名职工仅在一个仓库工作一名职工仅在一个仓库工作”，即保证了数据的正确性，即保证了数据的正确性。CREATE TRIGGER wh_emp ON 管理管理 FOR INSERT,UPDATE AS DECLARE emp CHAR(6),wh CHAR(6)SELECT wh=仓库号仓库号,emp=职工号职工号 FROM inserted IF EXISTS(SELECT*FROM 管理管理 WHERE 职工号职工号=emp AND 仓库号仓库号wh)BEGIN RAISERRROR(该职工已经属于其他仓库该职工已经属于其他仓库!,16,1)ROLLBACK TRANSACTION END 刘安丰刘安丰()19数据库原理及应用数据库原理及应用小结小结n完整性约束条件完整性约束条件n完整性控制完整性控制nSQL Server的完整性技术的完整性技术刘安丰刘安丰()20数据库原理及应用数据库原理及应用作业作业nP313第第1、3、5、6题。题。n预习预习12.112.5。刘安丰刘安丰()21数据库原理及应用数据库原理及应用 下课了。下课了。休息。休息。