第9章数据完整性.ppt

,*,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,*,第9章 数据完整性,1,本章内容,9.1 数据完整性,9.2 约束,9.3 规则,9.4 默认值,2,9.1 数据完整性,数据完整性是指数据库中存储数据的一致性和正确性，确保各个文件或表中的数据值的关系一致，确保数据库中的数据可以成功和正确地更新。,数据库设计的一项重要内容是确定如何加强数据的完整性。,3,9.1 数据完整性,关系数据完整性,关系数据完整性包括一下内容：,(1)实体完整性,实体完整性就是把表中每一条记录看作一个实体，要求所有行都具有唯一标识，即主键，且主键的值非空，又称为行完整性。,(2)域完整性,域完整性是关于数据列取值有效性的限制。域完整性通常用有效性检查来实现，也可以通过限制数据类型、格式或者可能的取值范围来实现。,例如，对于teaching数据库中s_c表中，GRADE字段的取值只能是从0到100的整数值，而不能为其他数值。,4,9.1 数据完整性,(3)参照完整性,参照完整性是对外键取值有效性的限制，以确保数据在另一个参照表的取值范围内。参照完整性要求外键的取值只能取参照表中的有效值或空值。如果在参考表中某一记录的主键被依赖表中的外部键参考，那么这一记录既不能删除，也不能修改其主键值，以确保关键字的一致性。,(4)用户定义完整性,用户定义完整性允许特定的不属于上述类别规则的完整性定义，前面的3个完整性类型都支持用户定义完整性。,实现用户定义完整性，可以有两种方法，即声明数据完整性和过程数据完整性。,5,9.1 数据完整性,(1)空值,数据表中的列可以接受空值，也可以拒绝空值。在数据库中，NULL是一个特殊值，表示未知值的概念。,NULL不同于空字符或0。空字符是一个有效的字符，0是一个有效的数字。NULL只是表示此值未知这一概念。NULL也不同于零长度字符串(空串)。,如果列定义中包含NOT NULL子句，则不能为该列输入NULL值。如果列定义中仅包含NULL关键字，则接受NULL值,8,9.1 数据完整性,(2)约束,约束定义关于列中允许值的规则，是强制实施完整性的标准机制。使用约束优先于使用,DML触发器,、,规则,和,默认值,。,约束类型,SQL Server 2005有下列约束类型：,NOT NULL,约束：非空约束，指定列不接受,NULL,值,.,CHECK,约束：检查约束，通过限制可输入到列中的值来强制实施域完整性。,UNIQUE,约束：唯一约束，强制实施列取值集合中值的唯一性。主键强制实施唯一性，但主键不允许,NULL,作为一个唯一值。,9,9.1 数据完整性,d.PRIMARY KEY约束：主键约束，标识具有唯一标识表中行的值的列或列集。,e.FOREIGN KEY约束：外键约束，标识并强制实施表之间的关系。,列约束和表约束,列约束指定为列定义的一部分，并且只应用于该列。表约束的声明与列定义无关，可以应用于表中多个列。当一个约束中必须包含多个列时，应使用表约束。,10,9.1 数据完整性,(3)规则,规则用于执行一些与CHECK约束相同的功能。使用CHECK约束是限制列值的首选标准方法。CHECK约束还比规则更简明。一个列只能应用一个规则，但可以应用多个CHECK约束。CHECK约束被指定为CREATE TABLE语句的一部分，而规则是作为单独的对象创建，然后绑定到列上。,11,9.1 数据完整性,(4)默认值,如果插入行时没有为列指定值，默认值则指定列中默认取值。默认值可以是计算结果为常量的任何值，例如常量、内置函数或数学表达式。,若要应用默认值，可以通过在CREATE TABLE中使用DEFAULT关键字来创建默认值定义。这将为每一列分配一个常量表达式作为默认值，也可以作为单独的对象创建，然后绑定到列上。,12,9.2 约束,约束是通过限制字段中数据、记录中数据和表之间的数据来保证数据完整性。,约束独立于表结构，创建约束有两种方法：,创建表时在CREATE TABLE命令中声明；,在不改变表结构的基础上，通过修改表结构命令ALTER TABLE添加或删除。,当表被删除时，表所带的所有约束定义也随之被删除。,13,9.2 约束,表9.2 所有可用的约束类型,完整性类型,约束类型,域完整性,DEFAULT(默认值)、CHECK(检查)约束,实体完整性,PRIMARY KEY(主键)约束、UNIQUE(唯一)约束,参照完整性,FOREIGN KEY(外键)约束,14,9.2 约束,主键约束,在数据表中经常有一列或多列的组合，其值能唯一地标识表中的每一行。这样的一列或多列称为表的主键。,通过它可强制表的实体完整性，以确保数据表中数据的唯一性。,当创建或更改表时，可通过定义PRIMARY KEY(主键)约束来创建主键。,15,9.2 约束,对于主键约束，需满足以下规则。,一个表只能包含一个,PRIMARY KEY,约束。,由,PRIMARY KEY,约束生成的索引不会使表中的非聚集索引超过,249,个，聚集索引超过,1,个。,如果没有为,PRIMARY KEY,约束指定,CLUSTERED,或,NONCLUSTERED,，并且没有为,UNIQUE,约束指定聚集索引，则将对该,PRIMARY KEY,约束使用,CLUSTERED,。,在,PRIMARY KEY,约束中定义的所有列都必须定义为,NOT NULL,。如果没有指定为空性，则加入,PRIMARY KEY,约束的所有列的为空性都将设置为,NOT NULL,。,16,9.2 约束,(1),创建表时声明主键约束,如果表的主键由单列组成，则该主键约束可以定义为该列的列约束。如果主键由两个以上的列组成，则该主键约束必须定义为表约束。,定义列级主键约束,语法格式如下：,CONSTRAINT constraint_name,PRIMARY KEY CLUSTERED|NONCLUSTERED,17,9.2 约束,CONSTRAINT,：可选关键字，表示,PRIMARY KEY,、,NOT NULL,、,UNIQUE,、,FOREIGN KEY,或,CHECK,约束定义的开始。,constraint_name,：约束的名称。约束名称必须在表所属的架构中唯一。,PRIMARY KEY,：是通过唯一索引对给定的一列或多列强制实体完整性的约束。每个表只能创建一个,PRIMARY KEY,约束。,CLUSTERED|NONCLUSTERED,：指示为,PRIMARY KEY,约束创建聚集索引还是非聚集索引。,PRIMARY KEY,约束默认为,CLUSTERED,。,18,9.2 约束,定义表级主键约束,语法格式如下。,CONSTRAINT constraint_name,PRIMARY KEY CLUSTERED|NONCLUSTERED,(column_name ,.n ),其中，column_name ,.n 指定组成主键的列名，n最大值为16。,19,9.2 约束,(2)修改表时创建主键约束,语法格式如下。,ALTER TABLE table_name,ADD CONSTRAINT Constraint_name,PRIMARY KEY CLUSTERED|NONCLUSTERED (column_name,n),20,9.2 约束,例如：假设在teaching数据库中的s_c表中没有设置主键约束，以下示例通过ALTER TABLE命令添加主键约束。,USE teaching,GO,ALTER TABLE s_c,ADD CONSTRAINT PK_sc PRIMARY KEY(SNO,CNO),GO,通过ALTER TABLE命令也可以删除不使用的主键约束，命令格式如下。,ALTER TABLE table_name DROP CONSTRAINT constraint_name,21,9.2 约束,外键约束,通过将用于保存表中主键值的一列或多列添加到另一个表中，可创建两个表之间的链接。这个列就成为第二个表的外键。,外键用于建立和加强两个表数据之间的链接的一列或多列。,当创建或更改表时，可以通过定义FOREIGN KEY约束来创建外键。,22,9.2 约束,对于外键约束，需要满足以下规则。,如果在,FOREIGN KEY,约束的列中输入非,NULL,值，则此值必须在被引用列中存在；否则，将返回违反外键约束的错误信息。,FOREIGN KEY,约束仅能引用位于同一服务器上的同一数据库中的表。跨数据库的参照完整性必须通过触发器实现。,FOREIGN KEY,约束可引用同一表中的其他列，此行为称为自引用。,列级,FOREIGN KEY,约束的,REFERENCES,子句只能列出一个引用列。此列的数据类型必须与定义约束的列的数据类型相同。,表级,FOREIGN KEY,约束的,REFERENCES,子句中引用列的数目必须与约束列列表中的列数相同。每个引用列的数据类型也必须与列表中相应列的数据类型相同。,FOREIGN KEY,约束只能引用所参照的表的,PRIMARY KEY,或,UNIQUE,约束中的列或所引用的表上,UNIQUE INDEX,中的列。,23,9.2 约束,(1)创建表时声明外键约束,语法格式如下。,CONSTRAINT constraint_name,FOREIGN KEY,REFERENCES schema_name.referenced_table_name (ref_column),ON DELETE NO ACTION|CASCADE|SET NULL|SET DEFAULT ,ON UPDATE NO ACTION|CASCADE|SET NULL|SET DEFAULT ,24,9.2 约束,FOREIGN KEY REFERENCES,：为列中的数据提供参照完整性的约束。,FOREIGN KEY,约束要求列中的每个值在所引参照的表中对应的被引用列中都存在。,schema_name,.,referenced_table_name,：是,FOREIGN KEY,约束参照的表的名称，以及该表所属架构的名称。,(,ref_column,.n ),：是,FOREIGN KEY,约束所引用的表中的一列或多列。,ON DELETE NO ACTION|CASCADE|SET NULL|SET DEFAULT,：指定如果已创建表中的行具有引用关系，并且被引用行已从父表中删除，则对这些行采取的操作。默认值为,NO ACTION,。,25,9.2 约束,NO ACTION,：数据库引擎将引发错误，并回滚对父表中相应行的删除操作。,CASCADE,：如果从父表中删除一行，则将从引用表中删除相应行。,SET NULL,：如果父表中对应的行被删除，则组成外键的所有值都将设置为,NULL,。若要执行此约束，外键列必须可为空值。,SET DEFAULT,：如果父表中对应的行被删除，则组成外键的所有值都将设置为默认值。若要执行此约束，所有外键列都必须有默认定义。如果某列为空值，并且未设置显式的默认值，则将使用,NULL,作为该列的隐式默认值。,26,9.2 约束,ON UPDATE NO ACTION|CASCADE|SET NULL|SET DEFAULT,：指定在发生更改的表中，如果行有引用关系且引用的行在父表中被更新，则对这些行采取什么操作。默认值为,NO ACTION,。,27,9.2 约束,外键约束不仅可以与一张表上的主键约束建立联系，也可以与另一张表上的UNIQUE约束建立联系。当一行新的数据被加入到表格中，或表格中已经存在的外键上的数据进行修改时，新的数据必须存在于另一张表的主键上，或者为NULL。,在外键约束上允许存在为NULL的值。,当主键所在表的数据被另一张表的外键所引用时，用户将无法对主键里的数据进行修改或删除，除非事先删除或修改引用的数据。,28,9.2 约束,(2)修改表时创建外键约束,语法格式如下。,ALTER TABLE table_name,ADD,CONSTRAINT constraint_name,FOREIGN KEY(column ,n),REFERENCES ref_table(ref_column,n),29,9.2 约束,例如：假设在teaching数据库中的s_c表中没有设置外键约束，以下示例通过ALTER TABLE命令添加外键约束。,USE teaching,GO,ALTER TABLE s_c,ADD CONSTRAINT FK_sc_sno,FOREIGN KEY(SNO)REFERENCES student(SNO),GO,ALTER TABLE s_c,ADD CONSTRAINT FK_sc_cno,FOREIGN KEY(CNO)REFERENCES course(CNO),GO,通过ALTER TABLE命令也可以删除不使用的外键约束，命令格式如下。,ALTER TABLE table_name DROP CONSTRAINT constraint_name,n,30,9.2 约束,UNIQUE(唯一)约束,对于数据表中非主键列的指定列，唯一约束确保不会输入重复的值。每个UNIQUE约束建立一个唯一索引。每个表中只能有一个主键，但是可以有多个UNIQUE列。唯一约束指定的列可以有NULL值。表中的主键也强制执行唯一性，但主键不允许为NULL，主键约束强度大于唯一约束。,唯一约束需满足以下规则。,如果没有为,UNIQUE,约束指定,CLUSTERED,或,NONCLUSTERED,，则默认使用,NONCLUSTERED,。,每个,UNIQUE,约束都生成一个索引。,UNIQUE,约束的数目不会使表中的非聚集索引超过,249,个，聚集索引超过,1,个。,31,9.2 约束,(1)创建表时声明唯一约束,如果表的唯一约束由单列组成，则该唯一约束可以定义为该列的列约束。如果唯一约束由两个以上的列组成，则该唯一约束必须定义为表约束。,定义列级主键约束,语法格式如下。,CONSTRAINT constraint_name,UNIQUE CLUSTERED|NONCLUSTERED,UNIQUE：唯一约束。,CLUSTERED|NONCLUSTERED：指示为UNIQUE约束创建聚集索引还是非聚集索引。,32,9.2 约束,定义表级唯一约束,语法格式如下。,CONSTRAINT constraint_name,UNIQUE CLUSTERED|NONCLUSTERED,(column_name ,.n ),(2)修改表时创建唯一约束,语法格式如下。,ALTER TABLE table_name,ADD,CONSTRAINT constraint_name,UNIQUE(column ,n),33,9.2 约束,例如：以下示例将teaching数据库中的student表中的姓名列设置为唯一约束。,USE teaching,GO,ALTER TABLE student,ADD,CONSTRAINT UQ_student,UNIQUE(SNAME),通过ALTER TABLE命令也可以删除不使用的唯一约束，命令格式如下。,ALTER TABLE table_name DROP CONSTRAINT constraint_name,n,34,9.2 约束,检查约束,检查(CHECK)约束通过检查输入表列的数据的值来维护值域的完整性，它可用来指定某列可取值的清单或可取值的集合，也可指定某列可取值的范围。,可以在一列上设置多个检查约束，也可以将一个检查约束应用于多列。当一列受多个检查约束控制时，所有的约束按照创建的顺序，依次进行数据有效性的检查。,根据检查约束是作用于单列还是多列，可分为列级检查约束和表级检查约束。,35,9.2 约束,对于检查约束，需满足以下规则。,列可以有任意多个,CHECK,约束，并且约束条件中可以包含用,AND,和,OR,组合起来的多个逻辑表达式。列上的多个,CHECK,约束按创建顺序进行验证。,搜索条件必须取值为布尔表达式，并且不能引用其他表。,列级,CHECK,约束只能引用被约束的列，表级,CHECK,约束只能引用同一表中的列。,当执行,INSERT,和,DELETE,语句时，,CHECK,约束和规则具有相同的数据验证功能。,当列上存在规则和一个或多个,CHECK,约束时，将验证所有限制。,不能在,text,、,ntext,或,image,列上定义,CHECK,约束。,36,9.2 约束,(1)创建表时声明唯一约束,语法格式如下。,CONSTRAINT constraint_name,CHECK(logical_expression),各选项含义如下：,CHECK：检查约束。该约束通过限制可输入一列或多列中的可能值来强制实现域完整性。,logical_expression：返回TRUE或FALSE的逻辑表达式。别名数据类型不能作为表达式的一部分。,37,9.2 约束,(2)修改表时创建检查约束,语法格式如下。,ALTER TABLE table_name,ADD,CONSTRAINT constraint_name,CHECK(logical_expression),38,9.2 约束,例如：假设在teaching数据库中的s_c表中没有设置对成绩列的检查约束，以下示例通过ALTER TABLE命令添加检查约束。,USE teaching,GO,ALTER TABLE s_c,ADD,CONSTRAINT CK_sc CHECK(GRADE=0 AND GRADE=0 and Grade=100,GO,45,9.3 规则,查看规则,可以使用系统存储过程sp_helptext查看已经创建的规则，语法格式如下。,sp_helptext objname=object_name,其中：,objname=object_name：指定对象的名称。,例如：以下示例查看已经创建的规则grade_rule。,USE teaching,GO,EXEC sp_helptext grade_rule,GO 执行结果如图9.1所示。,46,9.3 规则,绑定与解除规则,1.绑定规则,创建好一个规则后，必须通过绑定才能使用规则，一般情况下，规则可以绑定在用户自定义数据类型或是数据列中。,绑定规则可以使用存储过程sp_bindrule，语法格式如下:,sp_bindrule rulename=rule,objname=object_name,rulename=rule：指定规则名称。,objname=object_name：指定规则绑定的对象。,47,9.3 规则,如果规则绑定的对象是表的列，则object_name的格式是table.column，否则认为是用户定义数据类型。,例如：以下示例将例9.7创建的规则grade_rule绑定到teaching数据库中的s_c表中的GRADE列上。,USE teaching,GO,EXEC sp_bindrule grade_rule,s_c.GRADE,GO,48,9.3 规则,2.解除规则,系统存储过程sp_unbindrule用于当前数据库中为列或用户定义数据类型解除规则绑定。,语法格式如下。,sp_unbindrule objname=object_name,例如：解除绑定在teaching数据库中的s_c表中GRADE列上的规则。,USE teaching,GO,EXEC sp_unbindrule s_c.GRADE,GO,49,9.3 规则,删除规则,从数据库中删除一个规则值时，可以分为以下两种情况来处理。,如果这个规则尚未绑定到表或用户定义数据类型上，可以使用DROP RULE语句来删除。,如果已经将这个规则绑定到表或用户定义数据类型上，必须首先使用系统存储过程sp_unbindrule来解除该规则在表列或用户定义数据类型上的绑定，然后使用DROP RULE语句删除该规则。,50,9.3 规则,可以使用Transact-SQL命令的DROP RULE语句从数据库删除一个或多个规则。,其语法格式如下。,DROP RULE rule_name,n,例如：删除teaching数据库中的规则grade_rule。,USE teaching,GO,DROP RULE grade_rule,GO,51,9.4 默认值,默认值就是当用户未指定时由SQL Server自动指派的数据值，它可以是常量、内置函数或表达式。,使用默认值有两种方式：,在CREATE TABLE语句中对列定义一个DEFAULT约束,使用CREATE DEFAULT语句在数据库中创建一个默认值对象，然后使用sp_binddefault系统存储过程将该对象绑定到表列上。,52,9.4 默认值,创建默认值,1.创建默认值对象可以使用Transact-SQL命令CREATE DEFAULT语句来完成。,语法格式如下。,CREATE DEFAULT default_name AS constant_expression,其中：,constant_expression可以是常量表达式、任何常量、内置函数或数学表达式，但不能包含任何列或其他数据库对象的名称。,53,9.4 默认值,例如：在teaching数据库中创建一个名为grade_default的默认值，并以0作为其值。,USE teaching,GO,CREATE DEFAULT grade_default AS 0,GO,54,9.4 默认值,2.可以使用系统存储过程sp_helptext查看默认值定义，语法格式如下。,sp_helptext objname=name,name为用户定义的对象名称。仅当指定限定对象时才需要引号。对象必须在当前数据库中。,例如：查看已经创建的默认值grade_default。,USE teaching,GO,EXEC sp_helptext grade_default,GO 执行结果如图9.2所示,55,9.4 默认值,绑定与解除默认值,1.绑定默认值,在数据库中创建一个默认值后，还必须把该默认值绑定到列或用户定义数据类型上才能让它发挥作用。,可以用系统存储过程sp_binddefault来完成，其语法格式如下：,sp_bindefault defname=default,objname=object_name,56,9.4 默认值,例如：以下示例将例9.14创建的默认值grade_default绑定到teaching数据库中的s_c表中GRADE列上。,USE teaching,GO,EXEC sp_bindefault grade_default,s_c.GRADE,GO,57,9.4 默认值,2.解除默认值,系统存储过程sp_unbindefault用于当前数据库中为列或用户定义数据类型解除默认值绑定，语法格式如下。,sp_unbindefault objname=object_name,例如：解除绑定在teaching数据库中的s_c表中GRADE列上的默认值。,USE teaching,GO,EXEC sp_unbindefault s_c.GRADE,GO,58,9.4 默认值,删除默认值,从数据库中删除一个默认值时，可以分为以下两种情况来处理。,如果这个默认值尚未绑定到表或用户定义数据类型上，可以使用DROP DEFAULT语句来删除。,如果已经将这个默认值绑定到表或用户定义数据类型上，必须首先使用系统存储过程sp_unbindefault来解除该默认值在表列或用户定义数据类型上的绑定，然后使用DROP DEFAULT语句删除该默认值。,59,9.4 默认值,DROP DEFAULT语句用于从数据库删除一个或多个默认值，其语法格式如下。,DROP DEFAULT default_name,n,例如：删除teaching数据库中的默认值grade_default,USE teaching,GO,DROP DEFAULT grade_default,GO,60,本章小结,(1)数据完整性有4种类型。实体完整性、域完整性、参照完整性和用户定义的完整性。在SQL Server 2005中可以通过各种约束、默认、规则和触发器等数据库对象来保证数据的完整性。,(2)规则实施数据的完整性。规则是数据库中对存储在表的列或用户定义数据类型中的值的规定和限制。可以通过Transact-SQL语句来创建、删除、查看规则以及规则的绑定与松绑。,(3)默认值实施数据完整性。默认值是用户输入记录时没有指定具体数据的列中自动插入的数据。默认值对象可以用于多个列或用户定义数据类型，它的管理与应用同规则有许多相似之处。表的一列或一个用户定义数据类型也只能与一个默认值相绑定。在SQL Server中使用Transact-SQL语句实现默认值的创建、查看、删除以及默认值的绑定与松绑。,(4)使用约束实施数据完整性。约束是SQL Server提供的自动保持数据库完整性的一种方法，定义了可输入表或表的单个列中的数据的限制条件。在SQL Server中有6种约束：非空值约束、主键约束、外键约束、唯一性约束、检查约束和默认约束。,61,