第6章实现数据完整性

第六章实现SQLserver2000数据完整性 讲解提纲 6 1完整性的概念6 2约束的类型6 3约束的创建6 4查看约束的定义6 5删除约束6 6使用规则6 7使用默认 本章要点数据正确性 数据一致性的概念创建约束 默认值和规则的企业管理器及查询分析器的操作方法教学目的解决由于数据的添加 删除 修改操作造成数据的破坏或相关数据不一致的问题 防止存在不符合规定的数据 防止错误信息的输入与输出 数据完整性就是指存储在数据库中的数据正确性和相关数据具有一致性 例如在学生表中学号要具有唯一性 数据库的完整关系到否能真实反映现实世界 因此十分重要 数据完整性可分实体完整性 域完整性 参照完整性和用户定义完整性 4种 类型 6 1完整性的概念 1 实体完整性实体是指表中的纪录 一个实体就是表中的一条纪录 完整性要求在表中不能存在完全相同的纪录 而且每条记录都要具有一个非空且不重复的主键值 这样就能保证事物的唯一性 实体完整性将行定义为特定表的唯一实体 实体完整性强制表的标识符列或主键的完整性 通过惟一索引 惟一 UNIQUE 约束 主键 PRIMARYKEY 约束和IDENTITY属性 2 域完整性域完整性也可称为列完整性 是指给定列的输入有效性的验证限制 强制域有效性的方法有 限制类型 通过数据类型 格式 通过CHECK约束和规则 或有效数据的范围 通过FOREIGNKEY 外键 约束 CHECK约束 DEFAULT定义 NOTNULL定义和规则 3 引用 参照 完整性参照完整性又称为引用完整性 参照完整性是指作用于有关联的两个或两个以上的表 通过使用主键和外键或主键和唯一键之间的关系 使表中的键值在相关表中保持一致 在输入或删除记录时 引用完整性保持表之间已定义的关系 引用完整性基于外键与主键之间或外键与唯一键之间的关系 通过FOREIGNKEY和CHECK约束 引用完整性确保键值在所有表中一致 这样的一致性要求不能引用不存在的值 如果键值更改了 那么在整个数据库中 对该键值的所有引用要进行一致的更改 4 用户定义完整性用户定义完整性是应用领域需要遵守的约束条件 允许用户定义不属于其它任何完整性分类的特定业务规则 所有的完整性类型都支持用户定义完整性 CREATETABLE中的所有列级和表级约束 存储过程和触发器 6 2约束的类型约束是SQLServer提供的自动强制数据完整性的一种方法 它是通过定义列的取值规则来维护数据的完整性 约束定义关于列中允许值的规则 是强制完整性的标准机制 使用约束优先于使用触发器 规则和默认值 常用的约束有主键 UNIQUE 惟一 CHECK 检查 FOREIGNKEY 外键 DEFAULT 默认 约束 1 PRIMARYKEY 主键 约束表中经常有一个列或列的组合 其值能唯一地标识表中的每一行 这样的一列或多列称为表的主键 通过它可强制表的实体完整性 当创建或更改表时可通过定义PRIMARYKEY约束来创建主键 一个表只能有一个PRIMARYKEY约束 而且PRIMARYKEY约束中的列不能接受空值 由于PRIMARYKEY约束确保唯一数据 所以经常用来定义标识列 当为表指定PRIMARYKEY约束时 SQL通过为主键列创建唯一索引强制数据的唯一性 当在查询中使用主键时 该索引还可用来对数据进行快速访问 如果PRIMARYKEY约束定义在不止一列上 则一列中的值可以重复 但PRIMARYKEY约束定义中的所有列的组合的值必须唯一 2 UNIQUE 惟一 约束可使用UNIQUE约束确保在非主键列中不允许输入重复值 尽管UNIQUE约束和PRIMARYKEY约束都强制唯一性 但在强制下面的唯一性时应使用UNIQUE约束而不是PRIMARYKEY约束 特点 1 非主键的一列或多列组合 一个表可以定义多个UNIQUE约束 而只能定义一个PRIMARYKEY约束 2 允许空值的列 允许空值的列上可以定义UNIQUE约束 而不能定义PRIMARYKEY约束 FOREIGNKEY约束也可引用UNIQUE约束 3 CHECK 检查 约束CHECK约束通过限制输入到列中的值来强制域的完整性 这与FOREIGNKEY约束控制列中数值相似 区别在于它们如何判断哪些值有效 FOREIGNKEY约束从另一个表中获得有效数值列表 CHECK约束从逻辑表达式判断而非基于其它列的数据 例如 通过创建CHECK约束可将salary列的取值范围限制在 15 000至 100 000之间 从而防止输入的薪金值超出正常的薪金范围 逻辑表达式为 salary 15000ANDsalary 100000 4 DEFAULT 默认 约束记录中的每一列均必须有值 即使它是NULL 可能会有这种情况 当向表中装载新行时可能不知道某一列的值 或该值尚不存在 如果该列允许空值 就可以将该行赋予空值 由于有时不希望有可为空的列 因此如果合适 更好的解决办法可能是为该列定义DEFAULT定义 例如 通常将数字型列的默认值指定为零 将字符串列的默认值指定为暂缺 如果列不允许空值且没有DEFAULT定义 就必须明确地指定列值 否则SQLServer会返回错误信息 指出该列不允许空值 5 FOREIGNKEY 外键 约束外键 FK 是用于建立和加强两个表数据之间的链接的一列或多列 通过将保存表中主键值的一列或多列添加到另一个表中 可创建两个表之间的链接 这个列就成为第二个表的外键 当创建或更改表时可通过定义FOREIGNKEY约束来创建外键 6 3约束的创建6 3 1创建和修改PRIMARYKEY约束一个PRIMARYKEY约束可以 作为表定义的一部分在创建表时创建 添加到尚没有PRIMARYKEY约束的表中 一个表只能有一个PRIMARYKEY约束 如果已有PRIMARYKEY约束 则可对其进行修改或删除 定义了PRIMARYKEY约束的列的列宽不能更改 当向表中的现有列添加PRIMARYKEY约束时 SQL检查列中现有的数据以确保现有数据遵从主键的规则 无空值 无重复值如果PRIMARYKEY约束添加到具有空值或重复值的列上 SQLServer不执行该操作并返回错误信息 不能添加违背上述规定的PRIMARYKEY约束 创建 删除和修改约束约束是SQLServer提供的自动保持数据库完整性的一种方法 列级约束 列级约束是行定义的一部分 只能够应用在一列上 表级约束 表级约束的定义独立于列的定义 可以应用在一个表中的多列上 主键能够唯一地确定表中的每一条记录 主键不能取空值 主键约束可以保证实体的完整性 1 通过企业管理器完成添加 删除和修改主键的操作2 使用Transact SQL语句设置主键约束CONSTRAINTconstraint namePRIMARYKEY CLUSTERED NONCLUSTERED column name n 使用sql语句创建主键 有两种方法 1 使用CREATETABLE 2 使用ALTERTABLE 为已存在的表 修改表添加约束的语法格式 ALTERTABELEtable nameADDCONSTRAINTconstraint namePRIMARYKEY CLUSTERED NONCLUSTERED 说明 constraint name为主键约束名CLUSTERED表示在该列上建立聚集索引NONCLUSTERED表示在该列上建立非聚集索引 例如 在学生表中把学号作为主键 Usestudentgoaltertable学生表addconstraintjw041primarykeyclustered 学号 go 1 通过企业管理器完成创建和修改唯一性约束的操作2 使用Transact SQL语句完成唯一性约束的操作CONSTRAINTconstraint nameUNIQUE CLUSTERED NONCLUSTERED column name n 6 3 2创建惟一约束UNIQUE约束可以 作为表定义的一部分在创建表时创建 如果组成UNIQUE约束的列或列组合只包含唯一值或NULL值 则可向现有表添加UNIQUE约束 一个表可含有多个UNIQUE约束 例 向表中添加具有UNIQUE约束的新列 ALTERTABLE学生ADD备注VARCHAR 20 NULLCONSTRAINTaaUNIQUE 6 3 3创建检查约束可以对输入列的值设置检查条件 限制输入值 维护数据域完整性 表和列可以包含多个CHECK约束 列可以有任意多个CHECK约束 并且约束条件中可以包含用AND和OR组合起来的多个逻辑表达式 搜索条件必须取值为布尔表达式 1 使用企业管理器创建检查约束2 用Transact SQL语句创建检查约束 语法格式 ALTERTABELEtable nameADDCONSTRAINTconstraint namecheck logical expression 说明 constraint name为唯一约束名 logical expression是检查约束的条件表达式 下例显示对输入到jobs表中的min列和max列的值的限制 这两个约束都未命名 CHECK min 10 与CHECK max 250 例 创建表时建立检查约束 内容为系部代码在0101到1111之间 usestudentgocreatetable专业 专业代码char 2 constraintpk bjdmprimarykey 专业名称varchar 20 notnull 系部代码char 2 notnullconstraintchk aaCHECK 系部代码BETWEEN0101and1111 go 1 使用企业管理器创建默认约束2 使用Transact SQL语句创建默认约束 6 3 4创建DEFAULT默认约束DEFAULT定义可用于为表中现有行的新列提供值 如果列已有默认值 必须除去旧默认值后才能添加新默认值 除了常量以外 DEFAULT定义还可以包含函数 使用下例获取输入项的当前日期 DEFAULT getdate 语法格式 ALTERTABELEtable nameADDCONSTRAINTconstraint nameDEFAULTconstant expression FORcolumn name 说明 constraint name为惟一约束名 constant expression是默认值 例 创建表时带有默认约束 createtable专业 专业代码char 2 constraintpk bjdmprimarykey 专业名称varchar 20 notnull 授课语言char 10 NULLDEFAULT 汉语 go 例 在学生表的授课语言列上建立一个默认约束 内容为汉语 altertable学生表addconstraintdf aadefault 汉语 for 授课语言 一个表中最多可以有31个外键约束 在临时表中 不能使用外键约束 主键和外键的数据类型必须严格匹配 1 在企业管理器中添加外键约束2 使用Transact SQL语句设置外键约束CONSTRAINTconstraint nameFOREIGNKEY column name n REFERENCESref table ref column n 6 3 5创建和修改FOREIGNKEY外键约束 例 在班级表上的专业代码字段上建立外键 altertable班级表addconstraintfk aaforeignkey 专业代码 references专业 专业代码 go 课堂练习 1 建立课程表 包含两个主键 2 假如课程表已建好 但没有主键 请把课程号作为主键 课堂练习 向课程表中加上主键约束和UNIQUE约束 例 在学生表上得系部代码列上建立一个检查约束 内容为系部代码在0101到1111之间 altertable学生表addconstraintchk aaCHECK 系部代码BETWEEN0101and1111 课堂练习 1 建课程表 把课程号加上检查约束 内容是课程号在01到40 2 课程表已建好 给课程号加上检查约束 内容是课程号在01到40 课堂练习在学生表中建立默认约束df aa 放在id上 默认值为0 代码如下 ALTERTABLE学生表ADDconstraintdf aaDEFAULT 0 foridGO 课堂练习 1 建立 教师表 2 假设教师表没有外键 加上外键 6 4查看约束的定义语法格式 execsp helptext约束名称如 查看学生表中的ck aa约束usestudentgoexecsp helptextck aago 上一节内容 介绍了数据的完整性和约束的类型 约束的创建和查看 本节内容 删除约束 对规则和默认的创建 绑定 删除 重点 规则和默认的作用 难点 规则和默认的SQL语言 6 5删除约束语法格式 ALTERTABLEtable nameDROPCONSTRAINTconstraint name n 例 删除学生表的fk aa约束altertable学生表dropconstraintfk aago 课堂练习 1 学生表有学号 姓名 性别 出生日期 备注 现在把五种约束尽可能的加在学生表里 最后把约束删除掉 2 创建一个人事信息表 表中包含职工号 职工姓名 性别 出生日期 部门 联系电话 在创建时根据需要定义约束 规则是一种数据库对象 是一个向后兼容的功能 用于执行与CHECK约束相同的功能 CHECK约束是用来限制列值的首选标准方法 CHECK约束比规则更简明 一个列只能应用一个规则 但是却可以应用多个CHECK约束 CHECK约束作为CREATETABLE语句的一部分进行指定 而规则以单独的对象创建 然后绑定到列上 6 6使用规则 注 要创建与原有规则同名的新规则 必须在创建新规则之前将原有规则除去 而在除去规则之前 必须首先解除绑定 一个规则定义一次就可以被多次应用 语法格式 CREATERULErule nameAScondition expression其中 rule name指规则对象的名称 condition expression条件表达式包含一个变量 每个局部变量的前面都有一个 符号 该表达式引用通过UPDATE或INSERT语句输入的值 在创建规则时 可以使用任何名称或符号表示值 但第一个字符必须是 符号 规则对象与默认值对象的使用方法步骤 1 定义规则对象或默认值 2 将规则对象或默认值对象绑定到相应列或用户自定义类型 例如 创建一个规则 用以限制插入该规则所绑定列的整数范围 CREATERULErange ruleAS range 1000AND range 20000 sp bindrule将规则绑定到列或用户定义的数据类型 语法格式 executesp bindrule 规则名称 表名 字段名 例如 创建一个规则 并绑定到表kc的课程号列 用以限制课程号的输入范围 Usestudentcreaterulekc ruleas rangelike 1 5 0 9 0 9 gousestudentexecsp bindrule kc rule kc 课程号 go 例 创建一个规则 绑到学生表的学号列上 CREATERULEid chkAS idBETWEEN0and10000GOCREATETABLE学生表 学号intnotnull GOsp bindruleid chk 学生表 学号 GO 例 在student数据库中建立一个规则 其名称为 专业规则 Usestudentgocreaterule专业规则as guizein 电子商务 计算机网络 信息管理 go 查看规则定义sp helptext专业规则例 将 专业规则 绑定到 学生 表的 专业 字段上 可以限制录入或修改记录时提供的专业名称必须是 电子商务 计算机网络 信息管理 Usestudentgoexecsp bindrule 专业规则 学生 专业 go 3 sp unbindrule在当前数据库中为列或用户定义数据类型解除规则绑定 语法格式 executesp unbindrule 表名 字段名 例 为学生表的专业列解除规则绑定 EXECsp unbindrule 学生表 专业 4 DROPRULE从当前数据库中删除一个或多个用户定义的规则 语法格式 DROPRULE rule n 注意 删除规则对象前 首先解除绑定关系 课堂练习 创建一个规则是id在00001到10000范围内 绑到学生表的学号列上 再解除绑定 最后删除规则 分析代码如下 CREATERULEid chkAS idBETWEEN00001and10000GOCREATETABLE学生表 学号intnotnull GOsp bindruleid chk 学生表 学号 GOEXECsp unbindrule 学生表 学号 DROPRULEid chk 6 7使用默认创建称为默认值的对象 当绑定到列或用户定义数据类型时 如果插入时没有明确提供值 默认值便指定一个值 并将其插入到对象所绑定的列中 或者在用户定义数据类型的情况下 插入到所有列中 默认值是一个向后兼容的功能 它执行一些与使用ALTER或CREATETABLE语句的DEFAULT关键字创建的默认值定义相同的功能 默认值定义是限制列数据的首选并且标准的方法 因为定义和表存