完整性约束定义.ppt

1 5 4数据完整性的概念与实施方法 5 4 1数据完整性概念数据完整性是指数据的正确性 完备性和一致性 是衡量数据库质量好坏的重要标准 在用INSERT DELETE UPDATE语句修改数据库内容时 数据的完整性可能会遭到破坏 可能会存在下列情况 无效的数据被添加到数据库的表中 如 将学生考试成绩输入成负数 SQLServer提供了对数据库中表 列实施数据完整性的方法 对表进行设计数据完整性有两个重要内容 标识列的有效值和确定如何强制列中的数据完整性 2 1 域完整性域完整性是指一个列的输入有效性 是否允许空值 强制域完整性的方法有 限制类型 通过设定列的数据类型 格式 通过CHECK约束和规则 或可能值的范围 通过FOREIGNKEY约束 CHECK约束 DEFAULT定义 NOTNULL定义和规则 2 实体完整性实体完整性是指保证表中所有的行唯一 实体完整性要求表中的所有行都有一个唯一标识符 这个唯一标识符可能是一列 也可能是几列的组合 称之为主键 也就是说 表中主键在所有行上必须取值唯一 强制实体完整性的方法有 索引 UNIQUE约束 PRIMARYKEY约束或IDENTITY属性 3 参照完整性参照完整性也叫引用完整性 参照完整性总是保证主关键字 被引用表 和外部关键字 引用表 之间的参照关系 它涉及两个或两个以上表数据的一致性维护 3 5 4 2数据完整性实施方法1 声明型数据完整性声明型数据完整性一般在对象创建时定义 由SQLServer强制实施 通常使用约束 缺省值和规则来实现 实现基本数据完整性的首选方法是使用声明型数据完整性 声明型数据完整性作为数据库对象说明的一部分在语法中实现 在CREATETABLE和ALTERTABLE定义中使用CONSTRAINT DEFAULT等语句限制表中的值 使用这种方法实现数据完整性简单且不易出错 系统直接将实现数据完整性的要求定义在表和列上 2 过程型数据完整性过程型数据完整性是指由某个过程引发而实施的数据完整性 一般先写出实施数据完整性的条件 再写出强制该条件所执行的用于保证数据完整性的脚本 通常由触发器和存储过程实现 过程型数据完整性也可以在客户机和服务器上使用其它编程语言和工具实现 4 5 4 3约束1 约束的定义和类型约束是SQLServer提供的自动保持数据库完整性的一种方法 列级约束 列级约束是行定义的一部分 只能够应用在一列上 表级约束 表级约束的定义独立于列的定义 可以应用在一个表中的多列上 约束有六种类型 非空约束 缺省约束 检查约束 主键约束 唯一约束 外键约束 参照约束 非空约束 NOTNULL 表中的某些列必须存在有效值 不允许有空值出现 这是最简单的数据完整性约束 可在建表时将该列声明为NOTNULL即可 缺省约束 DEFALUTCONSTRAINTS 当向数据库中的表插入数据时 如果用户没有明确给出某列的值 SQLServer自动为该列输入指定值 5 检查约束 CHECKCONSTRAINTS 限制插入列中的值的范围 主键约束 PRIMARYKEYCONSTRAINTS 要求主键的列上没有两行具有相同值 也没有空值 唯一约束 UNIQUECONSTRAINTS 要求表中所有行在指定的列上没有完全相同的列值 外键约束 FROEIGNKEYCONSTRAINTS 要求正被插入或更新的列 外键 的新值 必须在被参照表 主表 的相应列 主键 中已经存在 6 不同的约束强制不同类型的数据完整性 表中给出了两者的对应关系 7 2 使用T SQL语言创建 管理约束 1 使用CREATETABLE语句创建约束使用CREATETABLE语句创建约束的一般语法如下 CREATETABLEtable name column namedata type CONSTRAINTconstraint name PRIMARYKEY CLUSTERED NONCLUSTERED UNIQUE CLUSTERED NONCLUSTERED FOREIGNKEY REFERENCESref table ref column DEFAULTconstant expression CHECK logical expression n 8 其中 table name 创建约束所在的表的名称 column name 列名 data type 数据类型 constraint name 约束名 在创建 修改 实现约束时注意以下几点 可以在已有的表上创建 修改 删除约束 而不必删除并重建表 可以在应用程序中创建错误检查逻辑 测试是否违反约束 在给表添加约束时 SQL将验证表中已有数据是否满足正在添加的约束 9 缺省约束缺省约束是指当向数据库中的表插入数据时 如果用户没有明确给出某列的值时 SQLServer自动为该列输入的值 缺省约束用于实现域的完整性 创建DEFAULT约束时应考虑以下因素 DEFAULT约束只能用于INSERT语句不能用于具有IDENTITY属性的列每列只能有一个DEFAULT约束 例1 为数据库JWGL中表teacher的SEX 性别 字段创建一个缺省约束 缺省值为1 男 USEJWGLGOALTERTABLEteacherADDCONSTRAINTdefault sexDEFAULT1FORSEX 10 检查约束检查约束用来指定某列可取值的清单 可取值的集合或可取值的范围 检查约束主要用于实现域完整性 创建CHECK约束时应考虑以下因素 当向数据库中的表执行插入或更新操作时 检查插入的新列值是否满足CHECK约束条件不能在具有IDENTITY属性 或具有timestamp或uniqueidentifier数据类型的列上放置CHECK约束CHECK条件不能含有子查询 例2 为数据库JWGL中表student course的GRADE 成绩 字段创建一个检查约束 使得GRADE的值在0 100之间 USEJWGLGOALTERTABLEstudent courseADDCONSTRAINTcheck gradeCHECK grade 0andgrade 100 11 主键约束主键约束保证某一列或一组列值的组合相对于表中的每一行都是唯一的 主键约束创建在表的主键列上 它对实现实体完整性更加有用 主键约束的作用就是为表创建主键 创建PRIMARYKEY约束时应考虑以下因素 每个表只能有一个主键 并且必须有一个主键 不允许有空值 参照约束使用它作为维护参照完整性的参考点 创建主键时 在创建主键的列上创建了一个唯一索引 可以是聚簇索引 也可以是非聚簇索引 默认是聚簇索引 例3 将数据库JWGL中teacher表的teacher id 教师号 字段设为主键 USEJWGLGOALTERTABLEteacherADDCONSTRAINTPK teacher idPRIMARYKEYclustered teacher id 12 唯一约束唯一约束限制表中指定列上所有的非空值必须唯一 即表中任意两行在指定列上都不允许有相同的值 创建UNIQUE约束时应考虑以下因素 一个表可以放置多个UNIQUE约束允许有空值创建唯一索引时强制UNIQUE约束 例4 将数据库JWGL中teacher表的teacher id 教师号 字段设为唯一约束 USEJWGLGOALTERTABLEdepartmentADDCONSTRAINTunique department nameUNIQUEnonclustered department name 13 唯一约束和主键约束的区别 唯一约束与主键约束都为指定的列建立唯一索引 即不允许唯一索引的列上有相同的值 主键约束限制更严格 不但不允许有重复值 而且也不允许有空值 唯一约束与主键约束产生的索引可以是聚簇索引也可以是非聚簇索引 但在缺省情况下唯一约束产生非聚簇索引 主键约束产生聚簇索引 外键约束通过外键约束强制参照完整性 SQLServer提供了外键 主键值约束 即满足以下两点 存在外键时 被参照表中这一行不能删除 向子表插入记录或更新子表中外键值的前提是 必须保证这个外键值与主表中主键的某个值相等或者该外键值为空 否则不允许插入或修改外键值 14 创建FOREIGNKEY约束时应考虑以下因素 它提供一列或多列数据的参照完整性 FOREIGNKEY约束不自动创建索引 但如果考虑数据库的多表连接 建议为外键创建一个索引 以提高连接性能主键与外键的数据类型和长度必须一致 或系统可转换 例5 为表teacher创建外键department id 外键department id参考表department中的主键department id USEJWGLGOALTERTABLEteacherADDCONSTRAINTFK department idFOREIGNKEY department id REFERENCESdepartment department id GO 15 3 使用企业管理器管理约束创建缺省约束创建缺省约束的具体步骤如下 启动企业管理器 分别点击 数据库 JWGL 表 显示数据库JWGL中所有的表 右击要设置缺省的表 假设要设置缺省的表为teacher 在系统弹出的快捷菜单上点击 设计表 点击设置缺省的列 在下面列属性设置栏的默认值输入框中 输入对应缺省值即可 创建检查约束创建主键约束创建唯一性约束创建外键约束 16 5 4 4使用默认值1 默认值的定义默认值是数据库对象之一 它指定在向数据库中的表插入数据时 如果用户没有明确给出某列的值 SQLServer自动为该列使用此默认值 它是实现数据完整性的方法之一 在关系数据库中 每个列都必须包含有值 即使这个值是个空值 对于不接受空值的列 就必须为该列输入某个非空值 要么由用户明确输入 要么由SQLServer输入默认值 默认值可用于表中的列和用户定义数据类型 请注意 缺省约束中使用的缺省值可以由用户创建时输入 也可以通过绑定已创建的默认值来指定 在创建默认值时 请考虑以下几点 默认值需和它要绑定的列或用户定义数据类型具有相同的数据类型 默认值需符合该列的所有规则 默认值缺省还需符合所有CHECK约束 创建 管理默认值可以用T SQL语言也可以用企业管理器 17 2 使用T SQL语言创建默认值 创建默认值的语法CREATEDEFAULTdefault nameASconstant expression这里 default name 新建默认值的名称 它必须遵循SQLServer标识符的命名规则 constant expression 默认值default name的值 是一个常数表达式 在这个表达式中不能含有任何列名或其他数据库对象名 但可使用不涉及数据库对象的SQLServer内部函数 默认值创建后 可以使用系统存储过程sp helptext可以查看用于定义一个默认值的SQL脚本 其语法是 sp helptextdefault name 18 默认值的绑定默认值创建之后 只有将其绑定到某个列或用户自定义数据类型才能有效 使用系统存储过程sp bindefault可实现与表中的列及用户自定义数据类型的绑定 一个默认值可以绑定到多个列或用户自定义数据类型 绑定默认值的语法是 sp bindefaultdefault name object name 其中 default name 由CREATEDEFAULT语句创建的默认值名字 它将与指定的列或用户定义数据类型相绑定 object name 要与该默认值相绑定的列名或用户自定义数据类型名 如果指定的是表中的列 其格式为 table name column 否则被认为是用户自定义数据类型名 19 默认值绑定的几点考虑 绑定的默认值只适用于受INSERT语句影响的行 不能将默认值绑定到系统数据类型或timestamp列 若绑定了一个默认值到一用户定义数据类型 又给使用该数据类型的列绑定了一个不同的默认值或规则 则绑定到列的默认值和规则有效 默认值绑定的解除一个默认值被绑定到表中的列或用户自定义的数据类型之后 可使用系统存储过程sp unbinddefault解除其绑定 绑定解除后 作为数据库对象的默认值仍然存在于数据库中 解除默认值绑定的语法是 sp unbindefault object name object name 要解除和默认值绑定的表的列名或用户自定义数据类型名 如果指定的是表中的列 其格式为 table name column 否则被认为是用户定义数据类型名 20 默认值绑定的删除如果一个默认值不再使用了 可以用DROP语句将其删除 删除默认值的语法是 DROPDEFAULTdefault namedefault name 要删除默认值的名称 在删除默认值时 应注意以下几点 DROP将默认值从数据库中删除 如果要删除一个默认值 必须解除这个默认值的所有绑定 否则该默认值就不能被删除掉 在一个批中不能既定义默认值又同时使用默认值 只有默认值的创建者可以删除默认值 21 例6 为数据库JWGL创建一个名为profession default 值为 讲师 的默认值 并绑定到表teacher的profession列 然后解除这个绑定 绑定解除后将此默认值删除 USEJWGLGOCREATEDEFAULTprofession defaultAS 讲师 GOsp bindefaultprofession default teacher profession GOsp unbindefault teacher profession DROPDEFAULTprofession defaultGO 22 5 4 5规则1 规则的定义规则是数据库对象之一 它指定向表的某列 或使用与该规则绑定的用户定义数据类型的所有列 插入或更新数据时 限制输入新值的取值范围 一个规则可以是 值的清单或值的集合 值的范围 必须满足的单值条件 用like子句定义的编辑掩码 规则是实现域完整性的方法之一 规则用来验证一个数据库中的数据是否处于一个指定的值域范围内 是否与特定的格式相匹配 当数据库中数据值被更新或被插入时 就要检查新值是否遵循规则 如果不符合规则就拒绝执行此更新或插入的操作 23 规则可用于表中列或用户定义数据类型 规则在实现功能上等同于CHECK约束 用CREATERULE语句创建规则 然后用sp bindrule把它绑定至一列或用户定义的数据类型 使用系统存储过程sp helptext查看用于定义一个规则或缺省的SQL脚本 其语法是 sp helptextrule name在创建规则时 应考虑以下几点 缺省情况下 规则将检查创建和绑定规则之前输入的数据 规则可以绑定到一列 多列或数据库中的用户定义数据类型 规则要求一个值在一定范围内 并与特定格式相匹配 在一个列上至多有一个规则起作用 如果有多个规则与一列相绑定 那么只有最后绑定到该列的规则才是有效的 24 2 使用T SQL语言创建 管理规则 创建规则的语法是 CREATERULErule nameAScondition expressionrule name 创建规则的名称 应遵循SQLServer标识符的命名规范 condition expression 指明定义规则的条件 在这个条件表达式中不能包含列名或其他数据库对象名 但它带有一个 为前缀的参数 即参数的名字必须以 为第一个字符 也称空间标志符 spaceholder 意即这个规则被附加到这个空间标志符 它只在规则定义中引用 为数据项值在内存中保留空间 以便与规则作比较 规则的绑定规则创建之后 只有将其绑定到某个列或用户自定义数据类型才能有效 使用系统存储过程sp binderule可实现与表中的列及用户自定义数据类型的绑定 绑定规则的语法是 sp bindrulerule name object name 25 其中 rule name 由CREATERULE语句创建的规则名字 它将与指定的列或用户定义数据类型相捆绑 object name 指定要与该规则相绑定的列名或用户定义数据类型名 如果指定的是表中的列 其格式为 table column 否则被认为是用户定义数据类型名 绑定时应注意如下几点 绑定的缺省只适用于受INSERT语句影响的行 绑定的规则只适用于受INSERT和UPDATE语句影响的行 不能将缺省或规则绑定到系统数据类型或timestamp列 26 解除规则使用系统存储过程sp unbindrule可以解除由sp unbindrule绑定到列或用户定义数据类型的规则 但被解除的规则仍然存在于数据库中 解除规则的语法如下 sp unbindrulerule name object name其中 rule name 由CREATERULE语句创建的规则名字 它将与指定的列或用户定义数据类型相捆绑 object name 指定要解除与该规则相绑定的列名或用户定义数据类型名 如果指定的是表中的列 其格式为table column 否则被认为是用户定义数据类型名 27 删除规则使用DROPRULE可删除由CREATERULE命令创建的规则 删除规则的语法如下 DROPRULE owner rule name其中 rule name 要解