第10章数据库完整性精选文档.ppt

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