数据库技术与应用课件

第9章数据完整性1第9章数据完整性1本章内容9.1数据完整性9.2约束9.3规则9.4默认值2本章内容9.1数据完整性29.1数据完整性数据完整性是指数据库中存储数据的一致性和正确性，确保各个文件或表中的数据值的关系一致，确保数据库中的数据可以成功和正确地更新。数据库设计的一项重要内容是确定如何加强数据的完整性。39.1数据完整性39.1数据完整性关系数据完整性关系数据完整性包括一下内容：(1)实体完整性实体完整性就是把表中每一条记录看作一个实体，要求所有行都具有唯一标识，即主键，且主键的值非空，又称为行完整性。(2)域完整性域完整性是关于数据列取值有效性的限制。域完整性通常用有效性检查来实现，也可以通过限制数据类型、格式或者可能的取值范围来实现。例如，对于teaching数据库中s_c表中，GRADE字段的取值只能是从0到100的整数值，而不能为其他数值。49.1数据完整性关系数据完整性49.1数据完整性(3)参照完整性参照完整性是对外键取值有效性的限制，以确保数据在另一个参照表的取值范围内。参照完整性要求外键的取值只能取参照表中的有效值或空值。如果在参考表中某一记录的主键被依赖表中的外部键参考，那么这一记录既不能删除，也不能修改其主键值，以确保关键字的一致性。(4)用户定义完整性用户定义完整性允许特定的不属于上述类别规则的完整性定义，前面的3个完整性类型都支持用户定义完整性。实现用户定义完整性，可以有两种方法，即声明数据完整性和过程数据完整性。59.1数据完整性(3)参照完整性59.1数据完整性声明数据完整性声明数据完整性是通过在对象定义中定义的标准来实现数据完整性，是由系统本身的自动强制来实现的，它包括使用各种约束、缺省的规则。过程数据完整性过程数据完整性是通过在脚本语言中定义的完整性标准来实现的，当执行这些脚本时，就可以强制完整性的实现。过程数据完整性的方式包括使用触发器和存储过程等。69.1数据完整性声明数据完整性69.1数据完整性SQLServer2005中的数据完整性

各类数据完整性和对应实现完整性的SQLServer组件如表9.1所示。数据完整性对应的SQLServer组件实体完整性PRIMARYKEY(主键)约束、UNIQUE(唯一)约束UNIQUEINDEX(唯一索引)、IDENTITYCOLUMN(标识列)域完整性DEFAULT(默认值)、CHECK(检查)约束、RULE(规则)FOREIGNKEY(外键)约束、DATATYPE(数据类型)参照完整性FOREIGNKEY(外键)约束、CHECK(检查)约束TRIGGER(触发器)、STOREDPROCEDURE(存储过程)用户定义完整性RULE(规则)、TRIGGER(触发器)、STOREDPROCEDURE(存储过程)79.1数据完整性SQLServer2005中的数据9.1数据完整性(1)空值数据表中的列可以接受空值，也可以拒绝空值。在数据库中，NULL是一个特殊值，表示未知值的概念。NULL不同于空字符或0。空字符是一个有效的字符，0是一个有效的数字。NULL只是表示此值未知这一概念。NULL也不同于零长度字符串(空串)。如果列定义中包含NOTNULL子句，则不能为该列输入NULL值。如果列定义中仅包含NULL关键字，则接受NULL值89.1数据完整性(1)空值89.1数据完整性(2)约束约束定义关于列中允许值的规则，是强制实施完整性的标准机制。使用约束优先于使用DML触发器、规则和默认值。约束类型SQLServer2005有下列约束类型：NOTNULL约束：非空约束，指定列不接受NULL值.CHECK约束：检查约束，通过限制可输入到列中的值来强制实施域完整性。UNIQUE约束：唯一约束，强制实施列取值集合中值的唯一性。主键强制实施唯一性，但主键不允许NULL作为一个唯一值。99.1数据完整性(2)约束99.1数据完整性d.PRIMARYKEY约束：主键约束，标识具有唯一标识表中行的值的列或列集。e.FOREIGNKEY约束：外键约束，标识并强制实施表之间的关系。列约束和表约束列约束指定为列定义的一部分，并且只应用于该列。表约束的声明与列定义无关，可以应用于表中多个列。当一个约束中必须包含多个列时，应使用表约束。109.1数据完整性d.PRIMARYKEY约束：主键约9.1数据完整性(3)规则规则用于执行一些与CHECK约束相同的功能。使用CHECK约束是限制列值的首选标准方法。CHECK约束还比规则更简明。一个列只能应用一个规则，但可以应用多个CHECK约束。CHECK约束被指定为CREATETABLE语句的一部分，而规则是作为单独的对象创建，然后绑定到列上。119.1数据完整性(3)规则119.1数据完整性(4)默认值如果插入行时没有为列指定值，默认值则指定列中默认取值。默认值可以是计算结果为常量的任何值，例如常量、内置函数或数学表达式。若要应用默认值，可以通过在CREATETABLE中使用DEFAULT关键字来创建默认值定义。这将为每一列分配一个常量表达式作为默认值，也可以作为单独的对象创建，然后绑定到列上。129.1数据完整性(4)默认值129.2约束约束是通过限制字段中数据、记录中数据和表之间的数据来保证数据完整性。约束独立于表结构，创建约束有两种方法：创建表时在CREATETABLE命令中声明；在不改变表结构的基础上，通过修改表结构命令ALTERTABLE添加或删除。当表被删除时，表所带的所有约束定义也随之被删除。139.2约束139.2约束表9.2所有可用的约束类型完整性类型约束类型域完整性DEFAULT(默认值)、CHECK(检查)约束实体完整性PRIMARYKEY(主键)约束、UNIQUE(唯一)约束参照完整性FOREIGNKEY(外键)约束149.2约束表9.2所有可用的约束类型完整性类型约束9.2约束主键约束在数据表中经常有一列或多列的组合，其值能唯一地标识表中的每一行。这样的一列或多列称为表的主键。通过它可强制表的实体完整性，以确保数据表中数据的唯一性。当创建或更改表时，可通过定义PRIMARYKEY(主键)约束来创建主键。159.2约束主键约束159.2约束对于主键约束，需满足以下规则。一个表只能包含一个PRIMARYKEY约束。由PRIMARYKEY约束生成的索引不会使表中的非聚集索引超过249个，聚集索引超过1个。如果没有为PRIMARYKEY约束指定CLUSTERED或NONCLUSTERED，并且没有为UNIQUE约束指定聚集索引，则将对该PRIMARYKEY约束使用CLUSTERED。在PRIMARYKEY约束中定义的所有列都必须定义为NOTNULL。如果没有指定为空性，则加入PRIMARYKEY约束的所有列的为空性都将设置为NOTNULL。169.2约束对于主键约束，需满足以下规则。169.2约束(1)创建表时声明主键约束如果表的主键由单列组成，则该主键约束可以定义为该列的列约束。如果主键由两个以上的列组成，则该主键约束必须定义为表约束。①定义列级主键约束语法格式如下：[CONSTRAINTconstraint_name]PRIMARYKEY[CLUSTERED|NONCLUSTERED]179.2约束(1)创建表时声明主键约束179.2约束CONSTRAINT：可选关键字，表示PRIMARYKEY、NOTNULL、UNIQUE、FOREIGNKEY或CHECK约束定义的开始。constraint_name：约束的名称。约束名称必须在表所属的架构中唯一。PRIMARYKEY：是通过唯一索引对给定的一列或多列强制实体完整性的约束。每个表只能创建一个PRIMARYKEY约束。CLUSTERED|NONCLUSTERED：指示为PRIMARYKEY约束创建聚集索引还是非聚集索引。PRIMARYKEY约束默认为CLUSTERED。189.2约束CONSTRAINT：可选关键字，表示PRI9.2约束②定义表级主键约束语法格式如下。[CONSTRAINTconstraint_name]PRIMARYKEY[CLUSTERED|NONCLUSTERED](column_name[,...n])其中，column_name[,...n]指定组成主键的列名，n最大值为16。199.2约束②定义表级主键约束199.2约束(2)修改表时创建主键约束语法格式如下。ALTERTABLEtable_nameADDCONSTRAINTConstraint_namePRIMARYKEY[CLUSTERED|NONCLUSTERED](column_name[,…n])

209.2约束(2)修改表时创建主键约束209.2约束例如：假设在teaching数据库中的s_c表中没有设置主键约束，以下示例通过ALTERTABLE命令添加主键约束。USEteachingGOALTERTABLEs_cADDCONSTRAINTPK_scPRIMARYKEY(SNO,CNO)GO通过ALTERTABLE命令也可以删除不使用的主键约束，命令格式如下。ALTERTABLEtable_nameDROPCONSTRAINTconstraint_name219.2约束例如：假设在teaching数据库中的s_c表9.2约束外键约束通过将用于保存表中主键值的一列或多列添加到另一个表中，可创建两个表之间的链接。这个列就成为第二个表的外键。外键用于建立和加强两个表数据之间的链接的一列或多列。当创建或更改表时，可以通过定义FOREIGNKEY约束来创建外键。229.2约束外键约束229.2约束对于外键约束，需要满足以下规则。如果在FOREIGNKEY约束的列中输入非NULL值，则此值必须在被引用列中存在；否则，将返回违反外键约束的错误信息。FOREIGNKEY约束仅能引用位于同一服务器上的同一数据库中的表。跨数据库的参照完整性必须通过触发器实现。FOREIGNKEY约束可引用同一表中的其他列，此行为称为自引用。列级FOREIGNKEY约束的REFERENCES子句只能列出一个引用列。此列的数据类型必须与定义约束的列的数据类型相同。表级FOREIGNKEY约束的REFERENCES子句中引用列的数目必须与约束列列表中的列数相同。每个引用列的数据类型也必须与列表中相应列的数据类型相同。FOREIGNKEY约束只能引用所参照的表的PRIMARYKEY或UNIQUE约束中的列或所引用的表上UNIQUEINDEX中的列。239.2约束对于外键约束，需要满足以下规则。239.2约束(1)创建表时声明外键约束语法格式如下。[CONSTRAINTconstraint_name]FOREIGNKEYREFERENCES[schema_name.]referenced_table_name[(ref_column)][ONDELETE{NOACTION|CASCADE|SETNULL|SETDEFAULT}][ONUPDATE{NOACTION|CASCADE|SETNULL|SETDEFAULT}]249.2约束(1)创建表时声明外键约束249.2约束FOREIGNKEYREFERENCES：为列中的数据提供参照完整性的约束。FOREIGNKEY约束要求列中的每个值在所引参照的表中对应的被引用列中都存在。[schema_name.]referenced_table_name：是FOREIGNKEY约束参照的表的名称，以及该表所属架构的名称。(ref_column[,...n])：是FOREIGNKEY约束所引用的表中的一列或多列。ONDELETE{NOACTION|CASCADE|SETNULL|SETDEFAULT}：指定如果已创建表中的行具有引用关系，并且被引用行已从父表中删除，则对这些行采取的操作。默认值为NOACTION。259.2约束FOREIGNKEYREFERENCES9.2约束NOACTION：数据库引擎将引发错误，并回滚对父表中相应行的删除操作。CASCADE：如果从父表中删除一行，则将从引用表中删除相应行。SETNULL：如果父表中对应的行被删除，则组成外键的所有值都将设置为NULL。若要执行此约束，外键列必须可为空值。SETDEFAULT：如果父表中对应的行被删除，则组成外键的所有值都将设置为默认值。若要执行此约束，所有外键列都必须有默认定义。如果某列为空值，并且未设置显式的默认值，则将使用NULL作为该列的隐式默认值。269.2约束NOACTION：数据库引擎将引发错误，并回9.2约束ONUPDATE{NOACTION|CASCADE|SETNULL|SETDEFAULT}：指定在发生更改的表中，如果行有引用关系且引用的行在父表中被更新，则对这些行采取什么操作。默认值为NOACTION。279.2约束ONUPDATE{NOACTION9.2约束外键约束不仅可以与一张表上的主键约束建立联系，也可以与另一张表上的UNIQUE约束建立联系。当一行新的数据被加入到表格中，或表格中已经存在的外键上的数据进行修改时，新的数据必须存在于另一张表的主键上，或者为NULL。在外键约束上允许存在为NULL的值。当主键所在表的数据被另一张表的外键所引用时，用户将无法对主键里的数据进行修改或删除，除非事先删除或修改引用的数据。289.2约束外键约束不仅可以与一张表上的主键约束建立联系，9.2约束(2)修改表时创建外键约束语法格式如下。ALTERTABLEtable_nameADDCONSTRAINTconstraint_nameFOREIGNKEY(column[,…n])REFERENCESref_table(ref_column[,…n])299.2约束(2)修改表时创建外键约束299.2约束例如：假设在teaching数据库中的s_c表中没有设置外键约束，以下示例通过ALTERTABLE命令添加外键约束。USEteachingGOALTERTABLEs_cADDCONSTRAINTFK_sc_snoFOREIGNKEY(SNO)REFERENCESstudent(SNO)GOALTERTABLEs_cADDCONSTRAINTFK_sc_cnoFOREIGNKEY(CNO)REFERENCEScourse(CNO)GO 通过ALTERTABLE命令也可以删除不使用的外键约束，命令格式如下。ALTERTABLEtable_nameDROPCONSTRAINTconstraint_name[,…n]309.2约束例如：假设在teaching数据库中的s_c表9.2约束UNIQUE(唯一)约束对于数据表中非主键列的指定列，唯一约束确保不会输入重复的值。每个UNIQUE约束建立一个唯一索引。每个表中只能有一个主键，但是可以有多个UNIQUE列。唯一约束指定的列可以有NULL值。表中的主键也强制执行唯一性，但主键不允许为NULL，主键约束强度大于唯一约束。唯一约束需满足以下规则。如果没有为UNIQUE约束指定CLUSTERED或NONCLUSTERED，则默认使用NONCLUSTERED。每个UNIQUE约束都生成一个索引。UNIQUE约束的数目不会使表中的非聚集索引超过249个，聚集索引超过1个。319.2约束UNIQUE(唯一)约束319.2约束(1)创建表时声明唯一约束如果表的唯一约束由单列组成，则该唯一约束可以定义为该列的列约束。如果唯一约束由两个以上的列组成，则该唯一约束必须定义为表约束。①定义列级主键约束语法格式如下。[CONSTRAINTconstraint_name]UNIQUE[CLUSTERED|NONCLUSTERED]UNIQUE：唯一约束。CLUSTERED|NONCLUSTERED：指示为UNIQUE约束创建聚集索引还是非聚集索引。329.2约束(1)创建表时声明唯一约束329.2约束②定义表级唯一约束语法格式如下。[CONSTRAINTconstraint_name]UNIQUE[CLUSTERED|NONCLUSTERED](column_name[,...n])

(2)修改表时创建唯一约束语法格式如下。ALTERTABLEtable_nameADDCONSTRAINTconstraint_nameUNIQUE(column[,…n])339.2约束②定义表级唯一约束339.2约束例如：以下示例将teaching数据库中的student表中的姓名列设置为唯一约束。USEteachingGOALTERTABLEstudentADDCONSTRAINTUQ_studentUNIQUE(SNAME)通过ALTERTABLE命令也可以删除不使用的唯一约束，命令格式如下。ALTERTABLEtable_nameDROPCONSTRAINTconstraint_name[,…n]349.2约束例如：以下示例将teaching数据库中的st9.2约束检查约束检查(CHECK)约束通过检查输入表列的数据的值来维护值域的完整性，它可用来指定某列可取值的清单或可取值的集合，也可指定某列可取值的范围。可以在一列上设置多个检查约束，也可以将一个检查约束应用于多列。当一列受多个检查约束控制时，所有的约束按照创建的顺序，依次进行数据有效性的检查。根据检查约束是作用于单列还是多列，可分为列级检查约束和表级检查约束。359.2约束检查约束359.2约束对于检查约束，需满足以下规则。列可以有任意多个CHECK约束，并且约束条件中可以包含用AND和OR组合起来的多个逻辑表达式。列上的多个CHECK约束按创建顺序进行验证。搜索条件必须取值为布尔表达式，并且不能引用其他表。列级CHECK约束只能引用被约束的列，表级CHECK约束只能引用同一表中的列。当执行INSERT和DELETE语句时，CHECK约束和规则具有相同的数据验证功能。当列上存在规则和一个或多个CHECK约束时，将验证所有限制。不能在text、ntext或image列上定义CHECK约束。369.2约束对于检查约束，需满足以下规则。369.2约束(1)创建表时声明唯一约束语法格式如下。[CONSTRAINTconstraint_name]CHECK(logical_expression)各选项含义如下：CHECK：检查约束。该约束通过限制可输入一列或多列中的可能值来强制实现域完整性。logical_expression：返回TRUE或FALSE的逻辑表达式。别名数据类型不能作为表达式的一部分。379.2约束(1)创建表时声明唯一约束379.2约束(2)修改表时创建检查约束语法格式如下。ALTERTABLEtable_nameADDCONSTRAINTconstraint_nameCHECK(logical_expression)389.2约束(2)修改表时创建检查约束389.2约束例如：假设在teaching数据库中的s_c表中没有设置对成绩列的检查约束，以下示例通过ALTERTABLE命令添加检查约束。USEteachingGOALTERTABLEs_cADDCONSTRAINTCK_scCHECK(GRADE>=0ANDGRADE<=100)GO通过ALTERTABLE命令也可以删除不使用的检查约束，命令格式如下。ALTERTABLEtable_nameDROPCONSTRAINTconstraint_name[,…n]399.2约束例如：假设在teaching数据库中的s_c表9.2约束默认约束DEFAULT(默认)约束能够定义一个值，每当用户没有在某一列中输入值时，则将所定义的值提供给这一列。默认约束需满足以下规则。一个表中只能有一列定义有DEFAULT(默认)约束。不能在数据类型为timestamp的列或具有IDENTITY属性的列中定义DEFAULT(默认)约束。409.2约束默认约束409.2约束(1)创建表时声明主键约束①建表时创建DEFAULT(默认)约束语法格式如下。[CONSTRAINTconstraint_name]DEFAULTconstant_expression各选项含义如下：DEFAULT：如果在插入过程中未显式提供值，则指定为列提供的值。constant_expression：是用作列的默认值的常量、NULL或系统函数。419.2约束(1)创建表时声明主键约束419.2约束(2)修改表时创建默认约束语法格式如下。ALTERTABLEtable_nameADDCONSTRAINTconstraint_nameDEFAULTconstant_expressionFORcolumn_name例如：以下示例为teaching数据库中的s_c表中的成绩列设置默认约束。USEteachingGOALTERTABLEs_cADDCONSTRAINTDF_gradeDEFAULT0FORGRADEGO通过ALTERTABLE命令也可以删除不使用的默认约束，命令格式如下。ALTERTABLEtable_nameDROPCONSTRAINTconstraint_name[,…n]429.2约束(2)修改表时创建默认约束429.3规则规则是一个数据库对象，当把它绑定到列或用户定义的数据类型时，用来指定列可以接受哪些数据，使用这种方式可以提供与CHECK约束相同的功能。CHECK约束是用来限制列值的首选标准方法。规则与CHECK约束相比较，CHECK约束比规则更简明(因为CHECK约束被创建之后可以直接使用，而规则在创建之后必须绑定到指定的列上才能够使用)。439.3规则439.3规则

创建规则语法格式如下。CREATERULErule_nameAScondition_expression各选项含义如下：rule_name：新规则的名称。规则名称必须符合标识符规则。根据需要，指定规则所有者名称。condition_expression：定义规则的条件。condition_expression包含一个变量。每个局部变量的前面都有一个@符号。该表达式引用通过UPDATE或INSERT语句输入的值。在创建规则时，可以使用任何名称或符号表示值，但第一个字符必须是@符号。449.3规则创建规则449.3规则例如：以下示例在teaching数据库中创建一个规则grade_rule，规则的条件是只能输入一个0～100之间的数字。USEteachingGOCREATERULEgrade_ruleAS@Grade>=0and@Grade<=100GO459.3规则例如：以下示例在teaching数据库中创建一9.3规则查看规则可以使用系统存储过程sp_helptext查看已经创建的规则，语法格式如下。sp_helptext[@objname=]'object_name‘其中：[@objname=]‘object_name’：指定对象的名称。例如：以下示例查看已经创建的规则grade_rule。USEteachingGOEXECsp_helptextgrade_ruleGO执行结果如图9.1所示。469.3规则查看规则469.3规则绑定与解除规则

1.绑定规则创建好一个规则后，必须通过绑定才能使用规则，一般情况下，规则可以绑定在用户自定义数据类型或是数据列中。绑定规则可以使用存储过程sp_bindrule，语法格式如下:sp_bindrule[@rulename=]'rule',[@objname=]'object_name‘[@rulename=]‘rule’：指定规则名称。[@objname=]‘object_name’：指定规则绑定的对象。479.3规则绑定与解除规则479.3规则如果规则绑定的对象是表的列，则object_name的格式是table.column，否则认为是用户定义数据类型。例如：以下示例将例9.7创建的规则grade_rule绑定到teaching数据库中的s_c表中的GRADE列上。USEteachingGOEXECsp_bindrule'grade_rule','s_c.GRADE'GO489.3规则如果规则绑定的对象是表的列，则object_n9.3规则2.解除规则系统存储过程sp_unbindrule用于当前数据库中为列或用户定义数据类型解除规则绑定。语法格式如下。sp_unbindrule[@objname=]'object_name‘例如：解除绑定在teaching数据库中的s_c表中GRADE列上的规则。USEteachingGOEXECsp_unbindrule's_c.GRADE'GO499.3规则2.解除规则499.3规则

删除规则从数据库中删除一个规则值时，可以分为以下两种情况来处理。如果这个规则尚未绑定到表或用户定义数据类型上，可以使用DROPRULE语句来删除。如果已经将这个规则绑定到表或用户定义数据类型上，必须首先使用系统存储过程sp_unbindrule来解除该规则在表列或用户定义数据类型上的绑定，然后使用DROPRULE语句删除该规则。509.3规则删除规则509.3规则可以使用Transact-SQL命令的DROPRULE语句从数据库删除一个或多个规则。其语法格式如下。DROPRULErule_name[,…n]

例如：删除teaching数据库中的规则grade_rule。USEteachingGODROPRULEgrade_ruleGO519.3规则可以使用Transact-SQL命令的DROP9.4默认值默认值就是当用户未指定时由SQLServer自动指派的数据值，它可以是常量、内置函数或表达式。使用默认值有两种方式：在CREATETABLE语句中对列定义一个DEFAULT约束使用CREATEDEFAULT语句在数据库中创建一个默认值对象，然后使用sp_binddefault系统存储过程将该对象绑定到表列上。529.4默认值默认值就是当用户未指定时由SQLServe9.4默认值创建默认值1.创建默认值对象可以使用Transact-SQL命令CREATEDEFAULT语句来完成。语法格式如下。CREATEDEFAULTdefault_nameASconstant_expression其中：constant_expression可以是常量表达式、任何常量、内置函数或数学表达式，但不能包含任何列或其他数据库对象的名称。539.4默认值创建默认值539.4默认值例如：在teaching数据库中创建一个名为grade_default的默认值，并以0作为其值。USEteachingGOCREATEDEFAULTgrade_defaultAS0GO549.4默认值例如：在teaching数据库中创建一个名为9.4默认值2.可以使用系统存储过程sp_helptext查看默认值定义，语法格式如下。sp_helptext[@objname=]'name‘name为用户定义的对象名称。仅当指定限定对象时才需要引号。对象必须在当前数据库中。例如：查看已经创建的默认值grade_default。USEteachingGOEXECsp_helptextgrade_defaultGO执行结果如图9.2所示559.4默认值2.可以使用系统存储过程sp_helpte9.4默认值绑定与解除默认值

1.绑定默认值在数据库中创建一个默认值后，还必须把该默认值绑定到列或用户定义数据类型上才能让它发挥作用。可以用系统存储过程sp_binddefault来完成