第章数据库完整性ppt课件.ppt

第七章 数据库完整性 学习要点 数据库完整性概述SQL2005完整性实现触发器概述创建 修改 查看 删除使用说明AFTER触发器INSTEADOF触发器 数据库完整性概述 数据完整性 DataIntegrity 定义指数据的准确性和一致性 目的防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息分类实体完整性 用于保证数据库中数据表的每一个特定实体的记录都是唯一的 域完整性 用于保证指定列的数据具有正确的数据类型 格式和有效的数据范围 参照完整性 确保同一键值在所有相关联表之间都一致 用户定义完整性 由用户自定义的不属于其他任何完整性分类的特定业务规则 数据库完整性概述 数据完整性 DataIntegrity 为维护数据库的完整性 DBMS必须 提供定义完整性约束条件的机制提供完整性检查的方法违约处理 学习要点 完整性实现 SQLServer2005完整性实现DEFAULT约束CHECK约束PRIMARYKEY约束UNIQUE约束FOREIGNKEY约束NOTNULL约束标识列默认值规则 学习要点 约束 DEFAULT约束定义在用户未输入某些列的数据时 数据库系统为用户提供的默认值 默认值必须与所约束列的数据类型相一致 是可以避免不允许为空值的数据错误可以加快用户的输入速度创建SSMS方式T SQL方式 约束列表 使用SSMS工具 DEFAULT约束 DEFAULT约束 CREATETABELBookInfo b IDVARCHAR 16 b DetailVARCHAR 100 DEFAULT 暂无描述 使用T SQL定义 一 DEFAULT约束 CREATETABEL数据表名 列名数据类型 CONSTRAIN约束名 DEFAULT默认值 列级约束 示例 NEXT CREATETABELBookInfo b DetailVARCHAR 100 CONSTRAINTde DetailDEFAULT 暂无描述 FORb Detail 使用T SQL定义 二 DEFAULT约束 NEXT CREATETABEL数据表名 列名1数据类型 CONSTRAIN约束名 DEFAULT默认值FOR 列名 n 表级约束 示例 使用T SQL定义 三 DEFAULT约束 ALTERTABEL数据表名 ADD CONSTRAIN约束名 DEFAULT默认值FOR 列名 n 修改表 添加约束 ALTERTABELBookInfo ADDCONSTRAINTde DetailDEFAULT 暂无描述 FORb Detail 示例 返回 约束 CHECK约束定义限制用户输入数据的取值范围 格式等 一个列可以定义多个CHECK约束 当往表中插入记录或修改相应列的值时 系统检查约束条件是否满足 若不满足则拒绝执行数据操作 创建SSMS方式T SQL方式 约束列表 使用SSMS工具 CHEECK约束 CHECK约束 CREATETABEL数据表名 列名数据类型 CONSTRAIN约束名 CHECK 逻辑表达式 CREATETABELBookInfo b QuantitySMALLINTCHECK b Quantity 1ANDb Quantity 100 使用T SQL定义 一 CHECK约束 列级约束 示例 NEXT 使用T SQL定义 二 三 CHECK约束 返回 CREATETABEL数据表名 列名1数据类型 CONSTRAIN约束名 CHECK 逻辑表达式 表级约束 ALTERTABEL数据表名 ADD CONSTRAIN约束名 CHECK 逻辑表达式 修改表 添加约束 约束 PRIMARYKEY约束定义若是单列作为主键 可以在列级定义或表级定义若是列集合作为主键 必须在表级定义一个表只能有一个PRIMARYKEY约束 受约束的列或列集合的值不能为空和重复 必须能唯一标识表中的行 定义了PRIMARYKEY约束的列的列宽不能更改 创建SSMS方式T SQL方式 约束列表 使用SSMS工具 返回 PRIMARY约束 使用T SQL定义 一 NEXT PRIMARY约束 CREATETABEL数据表名 列名数据类型 CONSTRAIN约束名 PRIMARYKEY CREATETABELBookInfo b IDVARCHAR 16 PRIMARYKEY 列级约束 示例 默认的约束名为 PK BookInfo 使用T SQL定义 二 PRIMARY约束 CREATETABEL数据表名 列名1数据类型 CONSTRAIN约束名 PRIMARYKEY 列名1 n CREATETABELBorrowReturn s IDCHAR 8 NOTNULL r IDCHAR 8 NOTNULL PRIMARYKEY s ID r ID 表级约束 示例 组合键必须在表级定义默认的约束名为 PK BorrowReturn NEXT 使用T SQL定义 三 返回 PRIMARY约束 ALTERTABEL数据表名 ADD CONSTRAIN约束名 PRIMARYKEY 列名 ALTERTABLEBorrowReturnADDCONSTRAINTPK BorrowReturnPRIMARYKEY s ID r ID 示例 修改表 添加约束 约束 UNIQUE约束定义也称唯一值约束 确保在非主键列中不输入重复值 一个表可以定义多个UNIQUE约束 在允许空值的列上保证唯一性时 应使用UNIQUE约束 当往表中插入记录或修改相应列的值时 系统检查是否有重复值 若有则拒绝执行数据操作 创建SSMS方式T SQL方式 约束列表 使用SSMS工具 返回 UNIQUE约束 使用T SQL定义 一 NEXT UNIQUE约束 CREATETABEL数据表名 列名数据类型 CONSTRAIN约束名 UNIQUE CREATETABELReaderType rt NameVARCHAR 10 UNIQUE 列级约束 示例 使用T SQL定义 二 三 UNIQUE约束 返回 CREATETABEL数据表名 列名1数据类型 CONSTRAIN约束名 UNIQUE 列名1 n 表级约束 ALTERTABEL数据表名 ADD CONSTRAIN约束名 UNIQUE 列名1 n 修改表 添加约束 约束 FOREIGNKEY约束定义也称为外键约束 用于强制实现参照完整性 建立两个表之间的关系 可以规定表中的某列参照同一个表或另外一个表中已有的PRIMARYKEY约束或UNIQUE约束的列 一个表可以有多个FOREIGNKEY约束 创建SSMS方式T SQL方式 约束列表 使用SSMS工具Step1 NEXT FOREIGNKEY约束 使用SSMS工具Step2 NEXT FOREIGNKEY约束 使用SSMS工具Step3 NEXT FOREIGNKEY约束 使用SSMS工具Step4 返回 FOREIGNKEY约束 使用T SQL定义 一 示例 CREATETABEL数据表名 列名数据类型 CONSTRAIN约束名 FOREIGNKEY REFERENCES参照主键表 参照列 ONDELETECASCADE ONUPDATECASCADE 列级约束 FOREIGNKEY约束 NOACITON 默认选项 将引发错误 撤销对父表的删除 更新 操作CASCADE 当在主表删除 更新 一行 则自动将子表中的对应行删除 更新 SETNULL 删除 更新 父表的一行 子表相应行的外键值被置为NULLSETDEFAULT 删除 更新 父表的一行 子表相应行的外键值被置为外键的默认值 使用T SQL定义 一 FOREIGNKEY约束 CREATETABELBookInfo b IDVARCHAR 16 PRIMARYKEY b NameVARCHAR 50 NOTNULL bt IDCHAR 10 REFERENCESBookType bt ID 示例 NEXT 使用T SQL定义 二 示例 CREATETABEL数据表名 列名1数据类型 CONSTRAIN约束名 FOREIGNKEY 列名 REFERENCES参照主键表 参照列 ONDELETECASCADE ONUPDATECASCADE 表级约束 FOREIGNKEY约束 使用T SQL定义 二 NEXT FOREIGNKEY约束 CREATETABLEBorrowReturn br IDCHAR 6 PRIMARYKEY s IDCHAR 8 REFERENCESNOTNULL r IDCHAR 8 REFERENCESNOTNULL FOREIGNKEY s ID REFERENCESBookStore s ID FOREIGNKEY r ID REFERENCESReaderInfo r ID 示例 使用T SQL定义 三 UNIQUE约束 返回 ALTERTABEL数据表名 ADD CONSTRAIN约束名 FOREIGNKEY 列名 REFERENCES参照主键表 参照列 ONDELETECASCADE ONUPDATECASCADE 修改表 添加约束 ALTERTABELBookInfo ADDFOREIGNKEY bt ID REFERENCESBookType bt ID ONDELETECASCADEONUPDATECASCADE 示例 约束 NOTNULL约束实质表中列可以定义为允许或不允许空值 默认情况下 创建表列允许空值 一个列中出现NULL值 意味着用户还没有为该列输入值 若设置为NOTNULL 用户没有输入该列值 则引发错误 示例SSMS方式T SQL方式 约束列表 使用SSMS工具 返回 NOTNULL约束 使用T SQL定义 NOTNULL约束 CREATETABEL数据表名 列名数据类型 CONSTRAIN约束名 NULL NOTNULL 表级约束 CREATETABELBookInfo b IDvarchar 16 PRIMARYKEY b Namevarchar 50 UNIQUENOTNULL bt IDchar 10 REFERENCESBookType bt ID b Authorvarchar 20 NOTNULL b Translatorvarchar 20 NULL 示例 返回 约束 标识列 IDENTITY 概述当用户向表中插入新的数据行时 系统自动为该行的IDENTITY列赋值 并保证其值在表中的唯一性 常与PRIMARYKEY约束一起使用 每个表中只能有一个IDENTITY列 其列值不能由用户更新 也不允许绑定默认值或建立DEFAULT约束标识列的有效数据类型可以是任何整数数据类型分类的数据类型 bit数据类型除外 也可以是decimal数据类型 但不允许出现小数 创建SSMS方式T SQL方式 约束列表 使用SSMS工具 返回 标识列 IDENTITY 使用T SQL定义 返回 CREATETABEL数据表名 列名数据类型IDENTITY 种子 增量 CREATETABELBorrowReturn br IDINTIDENTITY 1 1 PRIMARYKEY 语法 示例 标识列 IDENTITY DEFAULT 默认值 概述 默认值 是一种数据库对象 可以被绑定到列 如果绑定的列或者数据类型没有明确提供值 则将默认指定的数据插入 默认值 的执行与DEFAULT约束功能相同 但是DEFAULT约束定义和表存储在一起 当除去表时 将自动除去它 当在多个列中 特别是不同表中的列中多次使用同一默认值时 适合采用 默认 技术 管理 管理 默认值 创建绑定解绑 示例 CREATEDEFAULT默认名称AS常数表达式 sp bindefault 默认名称 数据表名 列名 sp unbindefault 数据表名 列名 DEFAULT 默认值 示例 创建绑定解绑 返回 sp bindefault d Ssex Student Ssex sp unbindefault Student Ssex USEStudyGOCREATEDEFAULTd SsexAS 女 DEFAULT 默认值 RULE 规则 概述 规则 是一种数据库对象 可以绑定到列 定义之后可以反复使用 执行 规则 功能与CHECK约束相同 每个列只能有一个绑定的规则 但列可以同时具有规则和多个CHECK约束 约束列表 RULE 规则 管理 规则 创建绑定解绑 示例 CREATERULE规则名称AS条件表达式 sp bindrule 规则名 数据表名 列名 sp unbindrule 数据表名 列名 RULE 规则 示例 创建绑定解绑 返回 sp bindrule r Ssex Student Ssex sp unbindrule Student Ssex USEStudyGOCREATERULEr SsexAS sex 女 OR sex 男 触发器 概述触发器触发器是特殊类型的存储过程 主要由操作事件 INSERT UPDATE DELETE 触发而被自动执行 与表相关联 必须定义在表上 不能直接调用 也不能传递或接受参数触发器可以实现比约束更复杂的数据完整性 经常用于加强数据的完整性约束和业务规则 触发器本身是一个特殊的事务单位 触发器 概述触发器的类型按触发时刻分AFTER触发器执行触发操作和处理完约束后激发 只能定义于表上 同一个表上可定义多个 INSTEAD触发器由触发器的程序代替触发语句的执行 在处理约束之前激发 可在表或视图上创建 同一个表或视图只可定义一个 按触发语句分INSERT触发器UPDATE触发器 DELETE触发器 触发器 Inserted表和deleted表触发器触发时 系统自动在内存创建deleted表或inserted表 它们是只读的 不允许修改 触发器执行完成后 自动删除 inserted表临时保存了插入或更新后的记录行可以从inserted表中检查插入的数据是否满足业务需求 如果不满足 则向用户报告错误消息 并回滚插入操作 deleted表临时保存了删除或更新前的记录行可以从deleted表中检查被删除的数据是否满足业务需求 如果不满足 则向用户报告错误消息 并回滚插入操作 触发器 触发器使用的限制CREATETRIGGER必须是批处理中的第一条语句 在同一个表中约束的执行优先于触发器 若在操作中触发器与约束发生冲突 触发器将不执行 不能在临时表上建立触发器 但是在触发器定义中可以引用临时表 触发器中不允许以下T SQL语句 触发器 Inserted表和deleted表inserted表和deleted表存放的信息 触发器 创建 SSMS 触发器 创建 SSMS 创建触发器的模板 只需修改相应的参数就可创建触发器 触发器 创建 T SQL 基本语句格式 CREATETRIGGER触发器名ON表名或视图名 FOR AFTER INSTEADOF INSERT UPDATE DELETE AS IFUPDATE 列名1 AND OR UPDATE 列名2 n SQL语句 触发器 创建 T SQL 基本语句格式 CREATETRIGGER触发器名ON表名或视图名 FOR AFTER INSTEADOF INSERT UPDATE DELETE AS IFUPDATE 列名1 AND OR UPDATE 列名2 n SQL语句 触发器 创建 T SQL 参数说明 AFTER 执行指定操作语句和处理完约束后激发触发器 FOR 如果仅指定FOR关键字 则AFTER是默认设置 INSTEADOF 指定语句激发触发器并被代替执行 DELETE INSERT UPDATE 指定激活触发器的数据修改语句 必须至少指定一个选项 若指定的选项多于一个 需用逗号分隔这些选项 AS 指定触发器要执行的操作 IFUPDATE 列名1 AND OR UPDATE 列名2 指定何列数据修改时激发触发器 不能用于DELETE语句 触发器 创建 T SQL 示例1 CREATETRIGGERtr addbookONBookInfoFORINSERT UPDATEASBEGINDE