第10章数据库完整性精.ppt
第第1010章数据库完整性章数据库完整性第1页，本讲稿共21页学习目标学习目标n掌握数据库的完整性和安全性的区别和联系掌握数据库的完整性和安全性的区别和联系n理解完整性约束条件理解完整性约束条件n掌握完整性控制掌握完整性控制n掌握掌握SQL Server的完整性技术的完整性技术第2页，本讲稿共21页10.1 完整性约束条件完整性约束条件数据库的完整性：数据库的完整性：指数据的正确性、有效性和相容性。如学生的年龄必指数据的正确性、有效性和相容性。如学生的年龄必须是整数，取值范围为须是整数，取值范围为1825；学生的性别只能是男或女；学生的；学生的性别只能是男或女；学生的学号必须唯一；学生所在的系必须是学校开设的系等。完整性不学号必须唯一；学生所在的系必须是学校开设的系等。完整性不同于安全性，前者是为了防止数据库中存在不符合语义的数据，同于安全性，前者是为了防止数据库中存在不符合语义的数据，防止错误信息的输入和输出；后者是为了防止非法用户和非法操防止错误信息的输入和输出；后者是为了防止非法用户和非法操作。作。完整性约束条件：完整性约束条件：是数据模型组成部分中施加在数据库数据之上的语义约是数据模型组成部分中施加在数据库数据之上的语义约束条件，是完整性控制的核心，束条件，是完整性控制的核心，DBMS应提供定义数据库完整性约束条件，应提供定义数据库完整性约束条件，并把它们作为模式的一部分存入数据库中。其作用的对象可以是列、元组、并把它们作为模式的一部分存入数据库中。其作用的对象可以是列、元组、关系。列约束主要是列的类型、取值范围、精度、排序等约束条件；元组约关系。列约束主要是列的类型、取值范围、精度、排序等约束条件；元组约束是元组中各个字段间联系的约束；关系的约束是若干元组间、关系集合上束是元组中各个字段间联系的约束；关系的约束是若干元组间、关系集合上以及关系之间的联系的约束。完整性约束条件涉及上述三类对象，其状态可以及关系之间的联系的约束。完整性约束条件涉及上述三类对象，其状态可以静态的，也可以是动态的。以静态的，也可以是动态的。第3页，本讲稿共21页静态约束：静态约束：指数据库每一确定状态时的数据对象所应满足的约束条件，反指数据库每一确定状态时的数据对象所应满足的约束条件，反映了数据库状态合理性的约束，是最重要的一类完整性约束。映了数据库状态合理性的约束，是最重要的一类完整性约束。动态约束：动态约束：指数据库从一种状态转变为另一种状态时，新、旧值之间指数据库从一种状态转变为另一种状态时，新、旧值之间所应满足的约束条件，反映了数据库状态变迁的约束。所应满足的约束条件，反映了数据库状态变迁的约束。六类完整性约束条件六类完整性约束条件静态列级约束：静态列级约束：对一个列的取值域的说明，是最常用也最容易实现的一类对一个列的取值域的说明，是最常用也最容易实现的一类完整性约束。它包括以下几个方面：完整性约束。它包括以下几个方面：对数据类型的约束对数据类型的约束(包括数据的类型、长度、单位、精度等包括数据的类型、长度、单位、精度等)。对数据格式的约束。如出生日期的格式为对数据格式的约束。如出生日期的格式为YY.MM.DD。对取值范围或取值集合的约束。如性别的取值范围是对取值范围或取值集合的约束。如性别的取值范围是男男,女女。对空值的约束。如学号不能为空，成绩可以为空。对空值的约束。如学号不能为空，成绩可以为空。其他约束。如关于列的排序。其他约束。如关于列的排序。第4页，本讲稿共21页静态元组约束：静态元组约束：一个元组由若干列组成，它规定元组的各个列之间的一个元组由若干列组成，它规定元组的各个列之间的约束关系。如规定订货关系中满足发货量约束关系。如规定订货关系中满足发货量=2000元。元。静态关系约束：静态关系约束：在一个关系的各个元组之间或者若干关系之间常常在一个关系的各个元组之间或者若干关系之间常常存在各种联系或约束。常见的静态关系约束有：存在各种联系或约束。常见的静态关系约束有：实体完整性约束。实体完整性约束。参照完整性约束。参照完整性约束。实体完整性和参照完整性约束称为关系的两个不变性。实体完整性和参照完整性约束称为关系的两个不变性。函数依赖约束。函数依赖约束。统计约束。如规定部门经理的工资不得低于该部门职工平均工资的统计约束。如规定部门经理的工资不得低于该部门职工平均工资的2倍，不得高于该部门职工平均工资的倍，不得高于该部门职工平均工资的5倍。倍。动态列级约束：动态列级约束：修改列定义或列值时应满足的约束条件。如允许为空修修改列定义或列值时应满足的约束条件。如允许为空修改为不允许为空、学生年龄只能增长。改为不允许为空、学生年龄只能增长。第5页，本讲稿共21页n动态元组约束：动态元组约束：修改元组值时元组中各个字段应满足的约束条件。如修改元组值时元组中各个字段应满足的约束条件。如职工工资调整时新工资不得低于原工资职工工资调整时新工资不得低于原工资+工龄工龄*1.5。n动态关系约束：动态关系约束：加在关系变化前后状态上的限制条件。如事务的一致性、加在关系变化前后状态上的限制条件。如事务的一致性、原子性等约束条件。原子性等约束条件。粒度状态列级元组级关系级静态列定义类型格式值域空值元组值应满足的条件实体完整性约束参照完整性约束函数依赖约束统计约束动态改变列定义或列值元组新旧值之间应满足的约束条件关系新旧状态间应满足的约束条件第6页，本讲稿共21页10.2 10.2 完整性控制完整性控制nDBMS的完整性控制机制应具有以下三方面功能的完整性控制机制应具有以下三方面功能n定义功能：定义功能：提供定义完整性约束条件的机制。提供定义完整性约束条件的机制。n检查功能：检查功能：检查用户的操作请求是否违背了完整性约束条件。检查用户的操作请求是否违背了完整性约束条件。n立即执行的约束立即执行的约束(Immediate constraints)：语句执行完后立即检查是否语句执行完后立即检查是否违背完整性约束。如银行数据库中违背完整性约束。如银行数据库中“借贷总金额应平衡借贷总金额应平衡”的约束就应该的约束就应该是延迟执行的约束，从账号是延迟执行的约束，从账号A转一笔钱到账号转一笔钱到账号B为一个事务，从账号为一个事务，从账号A转出转出去钱后账就不平了，必须等转入账号去钱后账就不平了，必须等转入账号B后账才能重新平衡，这时才能进后账才能重新平衡，这时才能进行完整性检查。行完整性检查。n延迟执行的约束延迟执行的约束(Deferred constrainsts)：完整性检查延迟到整个完整性检查延迟到整个事务执行结束后进行。事务执行结束后进行。n违约反应：违约反应：若用户的请求使数据违背了完整性约束条件，则采取一定若用户的请求使数据违背了完整性约束条件，则采取一定的动作来保证数据的完整性。的动作来保证数据的完整性。第7页，本讲稿共21页完整性规则的五元组表示：完整性规则的五元组表示：(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为拒绝为拒绝执行该操作。执行该操作。第8页，本讲稿共21页关系系统三类完整性的实现：关系系统三类完整性的实现：关系数据库系统都提供了定义和检查实体完关系数据库系统都提供了定义和检查实体完整性、参照完整性和用户定义完整性的功能。对于违反实体完整性规则和整性、参照完整性和用户定义完整性的功能。对于违反实体完整性规则和用户定义完整性规则的操作一般采用拒绝执行的方式进行处理。而对于违用户定义完整性规则的操作一般采用拒绝执行的方式进行处理。而对于违反参照完整性的操作，并不都是简单地拒绝执行，有时要根据应用语义执反参照完整性的操作，并不都是简单地拒绝执行，有时要根据应用语义执行一些附加的操作，以保证数据库的正确性。行一些附加的操作，以保证数据库的正确性。参照完整性的实现：参照完整性的实现：如职工如职工部门数据库中包含职工表部门数据库中包含职工表EMP和部门和部门表表DEPT，DEPT的主码为部门号的主码为部门号Deptno，EMP的主码为职工号的主码为职工号Empno，外码为部门号，外码为部门号Deptno，称，称EMP为参照关系，为参照关系，DEPT为被参为被参照关系或目标关系。照关系或目标关系。RDBMS实现参照完整性时需要考虑以下四个实现参照完整性时需要考虑以下四个方面的问题：方面的问题：外码能否可以接受空值：外码能否可以接受空值：依赖于应用环境的语义，系统除了应提供定依赖于应用环境的语义，系统除了应提供定义外码的机制，还应提供定义外码列是否允许空值的机制。义外码的机制，还应提供定义外码列是否允许空值的机制。第9页，本讲稿共21页 如职工如职工部门数据库中部门数据库中EMP表有外码表有外码Deptno，某元组的这一列若为空，某元组的这一列若为空值，表示这个职工尚未分配到任何具体的部门工作，和应用环境值，表示这个职工尚未分配到任何具体的部门工作，和应用环境的语义是相符的。但学生的语义是相符的。但学生选课数据库中选课数据库中Student关系为被参照关系，关系为被参照关系，其主码为其主码为Sno；SC为参照关系，外码为为参照关系，外码为Sno。若。若SC的的Sno为空值：表为空值：表明尚不存在的某个学生，或者某个不知学号的学生，选修了某门明尚不存在的某个学生，或者某个不知学号的学生，选修了某门课程，其成绩记录在课程，其成绩记录在Grade中，与学校的应用环境是不相符的，中，与学校的应用环境是不相符的，因此因此SC的的Sno列不能取空值。列不能取空值。在被参照关系中删除元组时的问题：在被参照关系中删除元组时的问题：当删除被参照关系的某个元组，当删除被参照关系的某个元组，而参照关系有若干元组的外码值与被删除的被参照关系的主码值相同，而参照关系有若干元组的外码值与被删除的被参照关系的主码值相同，这时有三种不同策略：这时有三种不同策略：级联删除：级联删除：将参照关系中外码值与被参照关系中要删除元组主码值相对将参照关系中外码值与被参照关系中要删除元组主码值相对应的元组一起删除。应的元组一起删除。受限删除：受限删除：当参照关系中没有任何元组的外码值与要删除的被参照关系当参照关系中没有任何元组的外码值与要删除的被参照关系的元组的主码值相对应时，系统才执行删除操作，否则拒绝此删除操作。的元组的主码值相对应时，系统才执行删除操作，否则拒绝此删除操作。第10页，本讲稿共21页置空值删除：置空值删除：删除被参照关系的元组，并将参照关系中与被参照关系中被删除被参照关系的元组，并将参照关系中与被参照关系中被删除元组主码值相等的外码值置为空值。删除元组主码值相等的外码值置为空值。如要删除如要删除Student关系中关系中Sno=95001的元组，而的元组，而SC关系中有关系中有4个元组个元组的的Sno都等于都等于95001。级联删除：将。级联删除：将SC关系中所有关系中所有4个个Sno=95001的元的元组一起删除，如果参照关系同时又是另一个关系的被参照关系，则这种删除组一起删除，如果参照关系同时又是另一个关系的被参照关系，则这种删除操作会继续级联下去；受限删除：系统将拒绝执行此删除操作；置空值删除：操作会继续级联下去；受限删除：系统将拒绝执行此删除操作；置空值删除：将将SC关系中所有关系中所有Sno=95001的元组的的元组的Sno值置为空值。在学生选课数值置为空值。在学生选课数据库中，显然前两种方法都正确，第三种方法不符合应用环境语义。据库中，显然前两种方法都正确，第三种方法不符合应用环境语义。在参照关系中插入元组时的问题：在参照关系中插入元组时的问题：当参照关系插入某个元组，而被参照当参照关系插入某个元组，而被参照关系不存在相应的元组，其主码值与参照关系插入元组的外码值相同，关系不存在相应的元组，其主码值与参照关系插入元组的外码值相同，这时可以两种策略：这时可以两种策略：受限插入：受限插入：仅当被参照关系中存在相应的元组，其主码值与参照关仅当被参照关系中存在相应的元组，其主码值与参照关系插入元组的外码值相同时，系统才执行插入操作，否则拒绝此操作。系插入元组的外码值相同时，系统才执行插入操作，否则拒绝此操作。第11页，本讲稿共21页递归插入：递归插入：首先向被参照关系中插入相应的元组，其主码值等于参照关系首先向被参照关系中插入相应的元组，其主码值等于参照关系插入元组的外码值，然后向参照关系插入元组。插入元组的外码值，然后向参照关系插入元组。如向如向SC关系插入关系插入(99001,1,90)元组，而元组，而Student关系中尚没有关系中尚没有Sno=99001的学生。受限插入：系统将拒绝向的学生。受限插入：系统将拒绝向SC关系插入该元组；关系插入该元组；递归插入：系统将首先向递归插入：系统将首先向Student关系插入关系插入Sno=99001的元组，然后向的元组，然后向SC关系插入关系插入(99001,1,90)元组。元组。修改关系中主码的问题：修改关系中主码的问题：要修改被参照关系中某些元组的主码值，而要修改被参照关系中某些元组的主码值，而参照关系中有些元组的外码值正好等于被参照关系要修改的主码值；参照关系中有些元组的外码值正好等于被参照关系要修改的主码值；要修改参照关系中某些元组的主码值，而被参照关系中没有任何元组要修改参照关系中某些元组的主码值，而被参照关系中没有任何元组的外码值等于被参照关系修改后的主码值。一般有两种策略：的外码值等于被参照关系修改后的主码值。一般有两种策略：不允许修改主码：不允许修改主码：有的有的DBMS不允许修改关系的主码值，若需要修改不允许修改关系的主码值，若需要修改主码值，只能先删除该元组，然后再把具有新主码值的元组插入主码值，只能先删除该元组，然后再把具有新主码值的元组插入到关系中。到关系中。允许修改主码允许修改主码第12页，本讲稿共21页 若修改的是被参照关系，则与删除类似，有三种策略：若修改的是被参照关系，则与删除类似，有三种策略：n级联修改：级联修改：修改被参照关系主码值的同时，修改参照关系中相应的外码修改被参照关系主码值的同时，修改参照关系中相应的外码值。值。n受限修改：受限修改：拒绝此修改操作。只当参照关系中没有任何元组的外码拒绝此修改操作。只当参照关系中没有任何元组的外码值等于被参照关系中某个元组的主码值时，才能被修改。值等于被参照关系中某个元组的主码值时，才能被修改。n置空值修改：置空值修改：修改被参照关系主码值的同时，将参照关系中相应的外修改被参照关系主码值的同时，将参照关系中相应的外码值置为空值。码值置为空值。如将如将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值置为空值。值置为空值。第13页，本讲稿共21页 显然在学生选课数据库中只有第一种方法是正确的。若修改的是参显然在学生选课数据库中只有第一种方法是正确的。若修改的是参照关系，则与插入类似，有受限修改、递归修改两种策略。照关系，则与插入类似，有受限修改、递归修改两种策略。参照完整性的实现：参照完整性的实现：RDBMS在实现参照完整性时，除了要向用户提供定义在实现参照完整性时，除了要向用户提供定义主码、外码的机制，还需要向用户提供不同的策略供用户选择。选择哪种策主码、外码的机制，还需要向用户提供不同的策略供用户选择。选择哪种策略，都要根据应用环境的要求确定。略，都要根据应用环境的要求确定。第14页，本讲稿共21页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子句指明外码相应于哪个表的主码。子句指明外码相应于哪个表的主码。第15页，本讲稿共21页例例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订购数量订购数量=10 and range=20 规则是独立的数据库对象，在规则是独立的数据库对象，在SQL Server中要通过中要通过sp_bindrule系统存储过程把规则绑定系统存储过程把规则绑定在数据列上，其格式是在数据列上，其格式是:sp_bindrule rule_name,obj_name 其中，其中，obj_name指明要绑定的表和列或用户定义的数据类型。如将规则指明要绑定的表和列或用户定义的数据类型。如将规则range_rule绑定在职绑定在职工表的工资列上，可使用语句：工表的工资列上，可使用语句：sp_bindrule range_rule,职工职工.工资工资，取消绑定使用系统存储过程取消绑定使用系统存储过程sp_unbindrule，如如sp_unbindrule 职工职工.工资工资第17页，本讲稿共21页使用触发器定义灵活、复杂的用户完整性规则使用触发器定义灵活、复杂的用户完整性规则例例6建立触发器：建立触发器：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 建立上述触发器后，将会拒绝一名职工对应多个仓库的情况，从而建立上述触发器后，将会拒绝一名职工对应多个仓库的情况，从而保证了语义保证了语义“一名职工仅在一个仓库工作一名职工仅在一个仓库工作”，即保证了数据的正，即保证了数据的正确性。确性。第18页，本讲稿共21页小结小结n完整性约束条件完整性约束条件n完整性控制完整性控制nSQL Server的完整性技术的完整性技术第19页，本讲稿共21页作业作业nP313第第1、3、5、6题。题。n预习预习12.112.5。第20页，本讲稿共21页 下课了。下课了。休息。第21页，本讲稿共21页
