第6章 实现数据完整约束---11.ppt

数据库原理与应用 高等院校计算机教材系列 第6章实现数据完整性约束 6 1数据完整性基本概念6 2实现声明完整性6 3实现过程完整性 6 1数据完整性基本概念 数据的完整性是为了防止数据库中存在不符合语义的数据 这些加在数据库数据之上的语义约束条件就是数据完整性约束条件 这些约束条件作为表定义的一部分存储在数据库中 DBMS检查数据是否满足完整性条件的机制就称为完整性检查 6 1 1完整性约束条件的作用对象 列级约束对数据类型的约束对数据格式的约束对取值范围或取值集合的约束对空值的约束元组约束元组中各个字段之间的联系的约束 如 开始日期小于结束日期 关系约束是若干元组之间 关系之间的联系的约束 6 1 2实现数据完整性的方法 一种是在定义表时声明数据完整性 称为声明完整性 另一种是在服务器端编写触发器来实现 称为过程完整性 在执行对数据的增 删 改操作时 数据库管理系统自动检查用户定义的完整性约束条件 6 2实现声明完整性 1 主码约束每个表只能有一个PRIMARYKEY约束 用PRIMARYKEY约束的列取值不能有重复 而且不允许有空值 添加主码约束的语法格式 ALTERTABLE表名ADD CONSTRAINT约束名 PRIMARYKEY n 例 对雇员表和工作表添加主码约束 ALTERTABLE雇员表ADDCONSTRAINTPK EMPPRIMARYKEY 雇员编号 ALTERTABLE工作表ADDCONSTRAINTPK JOBPRIMARYKEY 工作编号 2 UNIQUE约束 用于限制在一个列中不能有重复的值 用在事实上具有惟一性的属性列上 比如每个人的身份证号码 驾驶证号码等均不能有重复值 注意 允许有一个空值 在一个表中可以定义多个UNIQUE约束 可以在一个列或多个列上定义UNIQUE约束 添加UNIQUE约束 添加UNIQUE约束的语法格式为 ALTERTABLE表名ADD CONSTRAINT约束名 UNIQUE n 例 为雇员表的 电话 列添加UNIQUE约束 ALTERTABLE雇员表ADDCONSTRAINTUK SIDUNIQUE 电话 3 外码约束 实现引用完整性 外码所引用的列必须是有PRIMARYKEY约束或UNIQUE约束的列 添加FOREIGNKEY约束的语法格式为 ALTERTABLE表名ADD CONSTRAINT约束名 FOREIGNKEY REFERENCES引用表名 示例 例 为雇员表的工作编号添加外码引用约束 ALTERTABLE雇员ADDCONSTRAINTFK job idFOREIGNKEY 工作编号 REFERENCES工作表 工作编号 4 DEFAULT约束 用于提供列的默认值 只有在向表中插入数据时才检查DEFAULT约束 添加DEFAULT约束的语法格式为 ALTERTABLE表名ADD CONSTRAINT约束名 DEFAULT默认值FOR列名 4 DEFAULT约束 例 定义雇员表的工资的默认值为1000 ALTERTABLE雇员ADDCONSTRAINTDF SALARYDEFAULT1000FOR工资 CHECK约束 用于限制列的取值在指定的范围内 使数据库中存放的值都是有意义的 系统在执行INSERT语句和UPDATE语句时自动检查CHECK约束 CHECK约束可约束同一个表中多个列之间的取值关系 添加CHECK约束的语法格式为 ALTERTABLE表名ADD CONSTRAINT约束名 CHECK 逻辑表达式 示例 例1 限制雇员的工资必须大于等于200 ALTERTABLE雇员ADDCONSTRAINTCHK SalaryCHECK 工资 200 例2 限制工资表的最低工资小于等于最高工资 ALTERTABLE工作ADDCONSTRAINTCHK Job SalaryCHECK 最低工资 最高工资 6 3实现过程完整性 过程完整性是指在服务器端通过编写实现约束的一段代码来实现数据完整性约束 这段代码就称为触发器 触发器是用编程的方法实现复杂的商业规则 它可以实现一般的数据完整性约束实现不了的复杂的完整性约束 6 3 1事务基本概念 事务 Transaction 是作为完整的工作单元执行的一系列操作 如果一个事务中的所有操作都成功 则事务成功 其对数据库的更改都会成为永久性的更改 如果事务中的任何一个操作失败 则整个事务失败 其中所完成的操作均被取消 所有对数据的更改均无效 事务的三种类型 自动提交事务每一条对数据的增 删 改语句都自动地构成了一个事务 显式事务是用户定义的事务 有显式的开始 BEGINTRANSACTION 和结束标记 COMMIT 正常结束 和ROLLBACK 异常结束 隐式事务事务的开始是隐式的 以前一个事务结束后的第一个SQL语句作为下一个事务的开始 但每个事务必须有显式的结束标记 SQLServer支持的是显式事务 6 3 2触发器 是一种特殊的存储过程 不需要由用户调用执行 而是当用户对表中的数据进行UPDATE INSERT或DELETE操作时自动触发执行的 触发器通常用于保证业务规则和数据完整性 其主要优点是用户可以用编程的方法来实现复杂的处理逻辑和商业规则 增强了数据完整性约束的功能 触发器的优点 完成比CHECK约束更复杂的数据约束 为保证数据库性能而维护的非规范化数据 可实现复杂的商业规则 触发器也可以评估数据修改前后的表状态 并根据其差异采取对策 创建触发器 语法格式 CREATETRIGGER触发器名称ON表名 FOR AFTER INSTEADOF INSERT DELETE UPDATE ASSQL语句 n 两个逻辑工作表 DELETED表存储DELETE和UPDATE语句所影响的行的副本 INSERTED表存储INSERT和UPDATE语句所影响的行的副本 示例 创建限制最低工资必须大于等于400的触发器 CREATETRIGGERtri job salary1ON工作表FORINSERT UPDATEASIFEXISTS SELECT FROMINSERTEDWHERE最低工资 400 BEGINPRINT 最低工资必须大于等于400 ROLLBACKEND 示例 创建实现限制最低工资必须小于最高工资的触发器 CREATETRIGGERtri job salary2ON工作表FORINSERT UPDATEASIFEXISTS SELECT FROMINSERTEDWHERE最低工资 最高工资 BEGINPRINT 最低工资必须小于最高工资 ROLLBACKEND 示例 创建实现限制雇员的工资必须在工作表的相应工作的最低工资和最高工资之间 CREATETRIGGERtri emp salaryON雇员表FORINSERT UPDATEASIFEXISTS SELECT FROMINSERTEDaJOIN工作表bONa 工作编号 b 工作编号WHERE工资NOTBETWEEN最低工资AND最高工资 ROLLBACK 限制更新数据的触发器 限制将SC表中不及格学生的成绩改为及格 CREATETRIGGERtri gradeONSCFORUPDATEASIFUPDATE Grade IFEXISTS SELECT FROMINSERTEDJOINDELETEDONINSERTED Sno DELETED SnoWHEREINSERTED Grade 60ANDDELETED Grade 60 ROLLBACK 限制删除的触发器 限制删除SC表中成绩不及格学生的修课记录 CREATETRIGGERtri del gradeONSCFORDELETEASIFEXISTS SELECT FROMDELETEDWHEREGrade 60 ROLLBACK 修改触发器定义 语法格式为 ALTERTRIGGER触发器名称ON表名 FOR AFTER INSTEADOF INSERT DELETE UPDATE ASSQL语句 n 删除触发器 语法格式为 DROPTRIGGER触发器名 n 例 删除tri grade触发器 DROPTRIGGERtri grade 小结 实现数据完整性的方法 对于简单的约束可以使用声明完整性方法实现 对于复杂的业务规则和约束可以使用过程完整性实现 即编写实现完整性约束的代码段 触发器 触发器是由对数据的操作自动引发执行的代码 声明完整性和过程完整性各有各的优势 触发器的主要好处在于它可以包含使用SQL代码的复杂处理逻辑 因此 触发器可以支持完整性约束的所有功能 但它在性能上不如一般的数据完整性好 小结 实体完整性通过PRIMARYKEY约束实现 引用完整性通过FOREIGNKEY约束实现 域完整性一般通过CHECK约束实现 当约束所支持的功能无法满足应用程序的约束要求时 触发器就极为有用 例如 CHECK约束只能对同一个表中的一个列或者多个列的取值进行约束 如果是涉及多张表之间的列的相互取值约束 则必须使用触发器实现 简言之 触发器用于实现使用声明完整性约束实现不了的复杂的约束条件和业务规则 对能用声明完整性实现的约束条件 尽量用声明完整性约束实现 因为这样的执行效率比较高 小结 在实际的应用系统中 数据库端和客户端都可以提供数据完整性约束 在实施企业规则时需遵循如下准则 集中在数据库中实施企业规则减少多数据库请求的次数 尽量减少返回结果 我们在实施企业规则时 应将所有的企业规则定义在数据字典中 应用程序在向数据库提交SQL语句之前尽量减少由用户引起数据库错误的可能性 使应用程序运行顺畅 这样的应用程序对用户友好 并且系统开销很少 函数练习 例1 创建计算立方体的体积