数据库技术与应用第10章数据完整性.ppt

第10章 数据完整性,10.1 数据完整性概述10.2 使用约束实施数据的完整性10.3 规 则10.4 默 认10.5 使用自动编号IDENTITY,数据的完整性指数据库中数据的一致性与正确性。在SQL Server 2000中，可以通过约束、默认、规则、触发器等来达到保证数据完整性的目的。本章介绍如何通过约束、默认与规则实现数据的完整性。,10.1 数据完整性概述,数据完整性的含义包括以下内容：（1）数值的完整性，指数据类型与取值的正确性。（2）表内数据不相互矛盾。（3）表间数据不相互矛盾，指数据的关联性不被破坏。,数据完整性有不同的分类方法。（1）按照数据完整性的功能可以将其分为四类：实体完整性，要求表中每一条记录（每一行数据）是惟一的，即它必须至少拥有一个惟一标识以区分不同的数据行。实现方法有：主键约束PRIMARY KEY、惟一性约束UNIQUE、惟一索引UNIQUE INDEX、标识IDENTITY等。,值域完整性，限定表中输入数据的数据类型与取值范围。实现方法：默认值约束DEFAULT或默认对象、核查约束CHECK、外键约束FOREIGN KEY、规则RULE、数据类型、非空性约束NOT NULL等。,引用完整性，指对数据库进行添加、删除、修改数据时，要维护表间数据的一致性。实现方法：外键约束FOREIGN KEY、核查约束CHECK、触发器TRIGGER、存储过程PROCEDURE。用户定义的完整性，用于实现用户特殊要求的数据规则或格式。实现方法：默认值DEFAULT、核查约束CHECK、规则RULE 等。,（2）按照在数据库中实现数据完整性的作用级别，可以将其分为对象级与总体级。对象级：作用范围是数据库的某个表对象，是在定义表的同时定义的，删除表则失去作用，如约束。,总体级：是作为数据库的对象单独定义的，因此单独存在于数据库中，需要时可以绑定到数据库的表或用户定义的数据类型中，如默认对象、规则。（3）按照数据完整性的实施方法包括：约束、默认、规则、触发器、存储过程等。,10.2 使用约束实施数据的完整性,约束用于实现表内数据的完整性，属于对象级。SQL Server 支持的约束有如下几种：非空约束NOT NULL，主键约束PRIMARY KEY、惟一约束UNIQUE、核查约束CHECK、外键约束 FOREIGN KEY、默认值约束DEFAULT。,约束可以在创建表时定义，也可以在修改表时定义（即向已有的表中添加约束），但同一个数据库中不同约束的名称不能相同。按约束的作用范围可以分为两类，在定义列时定义的约束，仅作用于本列，称之为列级约束；表中单独定义的约束，称之为表级约束。表级约束的声明与列的定义无关，当一个约束作用于一个以上的列时，必须使用表级约束。,10.2.1 主键约束PRIMARY KEY通常在表中将一个列或列组合的数据设置成具有各不相同的值，以便能惟一地标识表中的每一行。这样的一列或多列称为表的主键，通过它可强制实现表的实体完整性，消除表的冗余数据,主键具有如下特性：（1）不重复性。（2）非空性。（3）惟一性。,1创建表时定义主键约束创建或更改表时可通过定义 PRIMARY KEY 约束来创建主键。（1）定义列级主键的语法命令格式：,CREATE TABLE table_name(column_name data_type DEFAULT default_expression|IDENTITY(seed，increment)CONSTRAINT constraint_name PRIMARY KEY CLUSTERED|NONCLUSTERED,.n）,命令说明：DEFAULT为默认值约束的关键字，用于指定其后的default_expression为默认值表达式。（默认值约束见本章第10.2.5节）IDENTITY(seed,increment）表示该列为标识列或称自动编号列（见本章10.5节）。,CONSTRAINT constraint_name 为可选项，关键字CONSTRAINT用于指定其后面的约束名称constraint_name。如省略本选项，则系统自动给出一个约束名。建议选择约束名以便于识别。PRIMARY KEY表示该列具有主键约束。,CLUSTERED|NONCLUSTERED表示建立聚簇索引或非聚簇索引，省略此项则系统默认为聚簇索引。如果没有特别指定本选项，且没有为其他 UNIQUE 惟一约束指定聚簇索引，则默认对该 PRIMARY KEY 约束使用 CLUSTERED。,（2）定义表级主键。表级主键一般位于表定义中所有列定义之后，与列定义可以用逗号相隔，语法如下。,命令格式：CREATE TABLE table_name(column_name data_type,.n CONSTRAINT constraint_name PRIMARY KEY CLUSTERED|NONCLUSTERED(column_name,.n),命令说明：(column_name,.n)表示该表级主键可以作用于组合在一起的多列所构成的列组合。,2更改表的主键约束（1）在现有表中添加一列，同时将其设置为主键,要求表中原先没有主键，语法如下。,命令格式：ALTER TABLE table_nameADD column_name data_type DEFAULT default_expression|IDENTITY(seed,increment)CONSTRAINT constraint_name PRIMARY KEY CLUSTERED|NONCLUSTERED,命令说明：ALTER TABLE 只允许添加可包含空值或指定了 DEFAULT 定义的列。因为主键不能包含空值，所以需要指定 DEFAULT 定义，或指定IDENTITY。其他说明与创建主键约束类同。,（2）使表中现有的一列（或列组合）成为主键，要求表中原先没有主键。且备选主键列中的已有数据不得重复或为空，语法如下。,命令格式：ALTER TABLE table_name WITH CHECK|WITH NONCHECK ADD CONSTRAINT constraint_name PRIMARY KEY CLUSTERED|NONCLUSTERED(column_name,.n),命令说明：（1）WITH CHECK为默认选项，该选项表示将使用新的主键约束来检查表中已有数据是否符合主键条件；如果使用了WITH NOCHECK选项，则不进行检查。（2）ADD指定要添加的约束。,（3）将表的主键由当前列换到另一列。一般先删除主键，然后在另一列上添加主键。（4）删除主键约束语法,命令格式：ALTER TABLE table_nameDROP CONSTRAINT primarykey_name,命令说明：primarykey_name表示要删除的主键名称，该名称是建立主键时定义的。如果建立主键时没有定义名称，则这里必须输入建立主键时系统自动给出的随机名称。3使用企业管理器管理主键,10.2.2 惟一约束UNIQUE一个表只能有一个主键，若有多列或多个列组合需要实施数据惟一性，则可采用惟一约束。惟一约束与主键约束的主要区别在于：惟一约束用于非主键列，使之满足数据惟一性要求；惟一约束允许NULL值，而主键不允许；惟一约束可以在多列或多个列组合上分别设置，而主键只能在一列或一个列组合上设置。,1创建表时定义惟一约束（1）定义列级惟一约束的语法命令格式：CREATE TABLE table_name(column_name data_type DEFAULT default_expression|IDENTITY(seed,increment)CONSTRAINT constraint_name UNIQUE CLUSTERED|NONCLUSTERED,.n）,显然，惟一约束与主键约束的定义十分相似，区别仅在于惟一约束采用关键字UNIQUE，而主键约束采用关键字PRIMARY KEY。（2）定义表级惟一约束。,2更改现有表的惟一约束 3使用企业管理器管理惟一约束,10.2.3 核查约束CHECK核查约束用于检查输入数据的取值是否正确，只有符合核查约束条件的数据才能输入。在一个表中可以建立多个核查约束，在一列上也可以建立多个核查约束，只要它们不相互矛盾。,1创建表时定义核查约束创建表时定义核查约束语法如下。命令格式：CREATE TABLE table_name(column_name data_type CONSTRAINT constraint_name CHECK NOT FOR REPLICATION（check_criterial）,.n,.n）,命令说明：（1）NOT FOR REPLICATION：该选项表示在复制表时禁用核查约束。（2）CHECK：该关键字表示定义的约束为核查约束。（3）check_criterial为核查准则，一般是条件表达式，与WHERE子句中的查询条件表达式类似。,（4）（check_criterial）,.n：表示在一列上可以设置多个核查约束，只要它们不相互矛盾。（5）NOT FOR REPLICATION：用于在复制与分发过程中使核查约束暂时失效，而仅对用户的修改（而不是对复制进程）强加约束。,2更改表的核查约束（1）在现有表中添加新的一列，该列具有核查约束。命令格式：ALTER TABLE table_nameADD column_name data_type CONSTRAINT constraint_name CHECK NOT FOR REPLICATION（check_criterial）,.n,（2）为表中现有的一列或几列添加核查约束。命令格式：ALTER TABLE table_name WITH CHECK|WITH NOCHECK ADD CONSTRAINT constraint_name CHECK NOT FOR REPLICATION（check_criterial）,.n,命令说明：WITH CHECK|WITH NOCHECK：WITH CHECK为默认选项，表示将使用新的核查约束检查表中已有数据是否符合核查条件；若使用WITH NOCHECK，则不进行核查。,10.2.4 外键约束FOREIGN KEY外键是最能体现关系型数据库引用完整性特点的约束。将一个表的一列（或列组合）定义为引用其他表的主键或惟一约束列，则引用表中的这个列（或列组合）就称为外键。被引用的表称为主键约束（或惟一约束）表；引用表称为外键约束表。,1创建表时定义外键约束创建表时定义外键约束的语法如下。命令格式：CREATE TABLE table_name(column_name data_type CONSTRAINT constraint_name FOREIGN KEY REFERENCES ref_table(ref_column),ON DELETE CASCADE|NO ACTION ON UPDATE CASCADE|NO ACTION NOT FOR REPLICATION,.n CONSTRAINT constraint_name,FOREIGN KEY(column_name,.n)REFERENCES ref_table(ref_column,.n)ON DELETE CASCADE|NO ACTION ON UPDATE CASCADE|NO ACTION NOT FOR REPLICATION,.n),命令说明：（1）第1个外层方括号 表示外键作用于单列（列级约束）时的选项，第2个外层方括号 表示外键作用于列组合时的选项。（2）对于列级外键约束，关键字FOREIGN KEY REFERENCES用以指明其后的ref_table（ref_column）为引用表名称及其列名，该列名所指定的列在引用表中必须为主键或惟一约束列。,（3）对于列组合型外键约束，FOREIGN KEY后面的（column_name,.n）表示本表中的外键列组合，ref_table（ref_column,.n）表示所引用的主键表的表名ref_table及其主键或惟一约束列组合ref_column,.n。两表中外键与主键的列名顺序及其数据类型一定要相同。,（4）ON DELETE CASCADE|NO ACTION 表示在主键表中删除数据行时，级联删除外键表中外键所对应的数据行（CASCADE）或不做任何操作（NO ACTION）。对ON UPDATE则为级联修改或否。（5）NOT FOR REPLICATION选项表示在复制表时禁用外键。,2更改现有表的外键约束（1）在现有表中添加新的一列及其外键约束的语法如下。命令格式：ALTER TABLE table_name(ADD column_name data_type CONSTRAINT constraint_name,FOREIGN KEY REFERENCES ref_table(ref_column)ON DELETE CASCADE|NO ACTION ON UPDATE CASCADE|NO ACTION NOT FOR REPLICATION,.n CONSTRAINT constraint_name,FOREIGN KEY(column_name,.n)REFERENCES ref_table(ref_column,.n)ON DELETE CASCADE|NO ACTION ON UPDATE CASCADE|NO ACTION NOT FOR REPLICATION,.n),（2）对表中的某一列（或列组合）添加外键约束的语法如下。命令格式：ALTER TABLE table_nameWITH CHECK|WITH NOCHECK ADD CONSTRAINT constraint_name FOREIGN KEY(column_name,.n)REFERENCES ref_table(ref_column,.n)ON DELETE CASCADE|NO ACTION ON UPDATE CASCADE|NO ACTION NOT FOR REPLICATION,.n,（3）将表中某列设置为外键及删除外键约束 3利用企业管理器管理外键10.2.5 默认值约束DEFAULT当向表中插入一行数据时，若没有指定其中某一列字段的数值，则该字段的数值按如下次序取值：,若该字段定义有默认值，则系统将默认值插入字段。若该字段定义没有默认值，但允许空，则插入空值。若该字段定义没有默认值，又不允许空，则报错。,显然，对于非空字段，默认值极为重要。默认值有两种设置方式，默认值约束与默认对象。默认值约束是对象级的，其作用范围仅限于一个表，删除表则删除默认值约束。默认值对象是数据库级的，作为数据库对象单独存在于数据库中，需要时可以绑定到任何一个表中。,1创建表时定义默认值约束命令格式：CREATE TABLE table_name(column_name data_type CONSTRAINT constraint_name DEFAULT constant_expression),命令说明：（1）DEFAULT关键字用以指明其后的常数表达式constant_expression为该列的默认值。常数表达式中除了允许使用常数、NULL值和表达式作为默认值外，还可以使用SQL Server系统函数如current_user、system_user、user、getdate（）等的值作为默认值。默认值的数据类型必须与列定义的数据类型一致，且不能与CHECK约束矛盾。,（2）DEFAULT约束定义的默认值仅在执行INSERT操作插入数据时生效。（3）一列至多有一个默认值，其中包括NULL值。（4）具有IDENTITY属性或TIMESTAMP数据类型属性的列不能使用默认值，text与image类型的列只能以NULL为默认值。,2更改表的默认约束（1）在现有表中添加新的一列，该列具有默认值约束。命令格式：ALTER TABLE table_name(ADD column_name data_type CONSTRAINT constraint_name DEFAULT constant_expression),（2）为表中现有列添加默认约束命令格式：ALTER TABLE table_nameADD CONSTRAINT constraint_name DEFAULT constant_expression FOR column_name,命令说明：默认表达式的值constant_expression用于关键字FOR所指定的列column_name。（3）删除默认值约束的语法同删除主键。3利用企业管理器管理默认约束,10.3 规 则,约束是作用于表的数据库对象，离开了表，约束将不再存在。除了约束外，SQL Server还提供了两个独立于表的数据库对象用以维护数据库的完整性，即默认和规则。规则的作用类似于核查约束，若将一个规则绑定到指定列上，则可以检查该列的数据是否符合规则的要求。,规则与核查约束的主要区别在于一列只能绑定一个规则，但却可以设置多个核查约束。规则的优点是仅创建一次就可以绑定到数据库的多个表的列上，使同一数据库中所有表的不同列共享。规则还可以绑定到同一数据库中一个以上的用户定义的数据类型上。,10.3.1 创建规则创建规则的语法如下。命令格式：CREATE RULE rule_name AS rule_expression,命令说明：（1）rule_name为要建立的规则的名称。（2）rule_expression为规则表达式，它与WHERE子句中的条件表达式类似，可以使用比较表达式、逻辑表达式、LIKE子句等，所不同的是需要将WHERE子句中的列名改为一个以开头并符合SQL Server命名规则的参数。将规则绑定到列上时，该参数代表INSERT语句或UPDATE语句所输入的数据，因此其参数名字可以随意。注意rule_expression不能引用表中的列或其他数据库对象。,10.3.2 绑定规则规则创建后，它仅仅是一个数据库对象，与其他数据库对象并没有联系。要将规则应用于一个表（或用户定义的数据类型）中，必须将其与表中的指定列（或用户定义的数据类型）相结合，称之为将规则绑定（bind）到列（或用户定义的数据类型），或称绑定规则。绑定规则需要使用系统存储过程sp_bindrule，语法如下。,命令格式：sp_bindrule rule_name,table_name.column_name或 sp_bindrule rule_name,user_defined_datatype,futureonly_ flag,命令说明：（1）table_name.column_name指明将rule_name所表示的规则绑定到指定表的指定列上，列名前面必须有表名项，如果仅有列名而无表名，则系统视它为用户定义的数据类型。（2）user_defined_datatype指明将规则绑定到一个用户定义的数据类型上。,（3）futureonly_ flag为一标志变量，它仅在将规则或默认绑定到用户定义的数据类型时使用。当表中的某一列具有尚未绑定规则的用户定义数据类型属性时，此时如果再将一个新规则绑定到上述用户定义数据类型，则futureonly_ flag的作用为：,futureonly_ flag取值为FUTUREONLY时，在将规则绑定到数据类型时，该绑定规则的用户定义数据类型仅对新表和表中新列起作用，对于此次绑定之前的那些具有该用户定义数据类型属性的列无效，即具有该用户定义数据类型属性的现有列不会继承新规则。,futureonly_ flag取值 NULL（此为默认选项）时，新规则将绑定到具有用户定义数据类型属性的每一列，包括现有列和新建列。可以多次将不同规则绑定到同一列，但只有最近一次绑定的规则生效。亦即对同一列，每绑定一次新规则，以前所绑定的旧规则将自动失效，一列只有一个规则生效。,10.3.3 解除绑定当表中不再需要规则时，可以将规则与列分离，称为解除绑定。解除绑定需要使用系统存储过程sp_unbindrule，语法如下。,命令格式：sp_unbindrule table_name.column_name或 sp_unbindrule user_defined_datatype,futureonly_ flag,命令说明：futureonly_flag的默认值为NULL，当它取值为FUTUREONLY时，现有使用该数据类型的列值不会失去规则，仅将来输入的数据不采纳规则。解除绑定后规则仍然存在于数据库系统中，只不过它与表的联系没有了。,10.3.4 删除规则删除规则的前提是解除数据库中所有表的绑定。删除规则语法如下。命令格式：DROP RULE rule_name10.3.5 使用企业管理器管理规则,10.4 默 认,与规则类似，默认值对象（简称默认）的优点也是仅创建一次就可以绑定到数据库的多个表的列或用户自定义数据类型中，使它们共享默认。,10.4.1 创建默认创建默认的语法如下。命令格式：CREATE DEFAULT default_name AS default_expression,命令说明：（1）default_name是符合SQL Server标识符规则的默认值名称。（2）default_expression是常量，用以指出默认值的具体数值或字符串。,10.4.2 绑定默认绑定默认的语法如下。命令格式：sp_bindefault default_name,table_name.column_name 或 sp_bindefault default_name,user_defined_datatype,futureonly_ flag,命令说明：（1）default_name 为默认值数据库对象的名称。（2）其他语法项目的用法与规则类似。10.4.3 解除绑定解除默认绑定的语法如下。,命令格式：sp_unbindefault table_name.column_name或sp_unbindefault user_defined_datatype,futureonly_ flag,10.4.4 删除默认删除默认之前要先解除默认绑定。删除默认的语法如下。命令格式：DROP DEFAULT default_name,10.4.5 使用企业管理器管理默认值对象与管理规则类似，在企业管理器中展开指定数据库节点，单击“默认”，在窗口右边可以看到当前数据库中的所有默认值对象。右击“默认”，选择“新建默认”，在弹出的窗口输入默认名称及其取值。或右击某个现有的默认值对象，选择“属性”，可以查看、修改或绑定该默认值对象。注意在修改之前需要解除绑定。,10.5 使用自动编号IDENTITY,SQL Server为自动进行顺序编号而引入了自动编号的IDENTITY属性。当需要对某一列输入的数据进行有规律的顺序编号且保证每个编号惟一时，最好是采用IDENTITY，这样既方便准确，速度又快。具有IDENTITY属性的列称为标识列，其取值称为标识值，具有如下特点：,（1）IDENTITY列的数据类型只能为tinyint、smallint、int、bigint、numeric、decimal。当为numeric、decimal类型时，不允许有小数位。（2）当用户向表中插入新的一行记录时，不必也不能向具有IDENTITY属性的列输入数据，系统将自动在该列添加一个按规定间隔递增（或递减）的数据。,（3）每个表至多有一列具有IDENTITY属性，该列不能为空、不允许具有默认值、不能由用户更新。因此，IDENTITY列常可以作为主键使用。,（4）使用IDENTITY时，可以设置种子（seed）与增量（increment）。“种子”表示系统为表中第一条记录添加的自动编号数字。“增量”表示相邻两条记录之间后一个自动编号数字减去前一个自动编号数字的数值差，正值表示后一数据大于前一数据，反之后一数据小于前一数据。必须同时指定种子和增量，或者二者都不指定。如果二者都未指定，则取默认值（1,1）。当对表中数据进行删除操作后，在标识值之间可能会产生数量不等的差值。,设置IDENTITY属性的语法如下。建立表时定义标识列。命令格式：CREATE TABLE table_name(column_name data_type IDENTITY(seed，increment)NOT NULL,.n),