数据库原理及应用(13)第13章 数据库完整性

1、第第13章数据库完整性章数据库完整性数据库完整性就是确保数据库中的数据的一致性和正确性。数据库完整性就是确保数据库中的数据的一致性和正确性。本章主要讨论约束、默认和规则等内容本章主要讨论约束、默认和规则等内容 13.1 约约 束束设计表时需要识别列的有效值并决定如何强制实现列中设计表时需要识别列的有效值并决定如何强制实现列中数据的完整性。数据的完整性。SQL Server 2005提供多种强制数据完整性的提供多种强制数据完整性的机制：机制：l PRIMARY KEY约束约束l FOREIGN KEY约束约束l UNIQUE约束约束l CHECK约束约束l NOT NULL（非空性）（非空性）1

2、3.1.1 PRIMARY KEY约束约束PRIMARY KEY约束标识列或列集，这些列或列集的值唯约束标识列或列集，这些列或列集的值唯一标识表中的行。一个一标识表中的行。一个PRIMARY KEY约束可以：约束可以：l 作为表定义的一部分在创建表时创建。作为表定义的一部分在创建表时创建。l 添加到还没有添加到还没有PRIMARY KEY约束的表中（一个表只能约束的表中（一个表只能有一个有一个PRIMARY KEY约束）。约束）。l 如果已有如果已有PRIMARY KEY约束，则可对其进行修改或删约束，则可对其进行修改或删除。例如，可以使表的除。例如，可以使表的PRIMARY KEY约束引用其

3、他列，约束引用其他列，更改列的顺序、索引名、聚集选项或更改列的顺序、索引名、聚集选项或PRIMARY KEY约约束的填充因子。定义了束的填充因子。定义了PRIMARY KEY约束的列的列宽约束的列的列宽不能更改。不能更改。【例【例13.1】 给出以下程序的功能。给出以下程序的功能。USE testGOCREATE TABLE department/*部门表部门表*/(dno int PRIMARY KEY, /*部门号部门号,为主键为主键*/dname char(20),/*部门名部门名*/)GO解：解：本程序在本程序在test数据库中创建一个名为数据库中创建一个名为department的表，

4、的表，其中指定其中指定dno为主键。为主键。 注意：若要使用注意：若要使用T-SQL修改修改PRIMARY KEY，必须先，必须先删除现有的删除现有的PRIMARY KEY约束，然后再用新定义重新创约束，然后再用新定义重新创建。建。13.1.2 FOREIGN KEY约束约束FOREIGN KEY约束称为外键约束，用于标识表之间的约束称为外键约束，用于标识表之间的关系，以强制参照完整性，即为表中一列或者多列数据提供关系，以强制参照完整性，即为表中一列或者多列数据提供参照完整性。参照完整性。FOREIGN KEY约束也可以参照自身表中的其约束也可以参照自身表中的其他列，这种参照称为自参照。他列，

5、这种参照称为自参照。FOREIGN KEY约束可以在下面情况下使用：约束可以在下面情况下使用：l 作为表定义的一部分在创建表时创建。作为表定义的一部分在创建表时创建。l 如果如果FOREIGN KEY约束与另一个表（或同一表）已约束与另一个表（或同一表）已有的有的PRIMARY KEY约束或约束或UNIQUE约束相关联，则约束相关联，则可向现有表添加可向现有表添加FOREIGN KEY约束。一个表可以有约束。一个表可以有多个多个FOREIGN KEY约束。约束。l 对已有的对已有的FOREIGN KEY约束进行修改或删除。例如，约束进行修改或删除。例如，要使一个表的要使一个表的FOREIGN

6、KEY约束引用其他列。定义约束引用其他列。定义了了FOREIGN KEY约束列的列宽不能更改。约束列的列宽不能更改。【例【例13.2】 给出以下程序的功能。给出以下程序的功能。USE testGOCREATE TABLE worker/*职工表职工表*/(no int PRIMARY KEY, /*编号编号,为主键为主键*/name char(8),/*姓名姓名*/sex char(2),/*性别性别*/dno int /*部门号部门号*/FOREIGN KEY REFERENCES department(dno)ON DELETE NO ACTION,address char(30)/*地址

7、地址*/)GO解：解：该程序使用该程序使用FOREIGN KEY子句在子句在worker表中建表中建立了一个删除约束，即立了一个删除约束，即worker表的表的dno列（是一个外键）与列（是一个外键）与department表的表的dno列关联。列关联。使用使用FOREIGN KEY约束，还应注意以下几个问题：约束，还应注意以下几个问题：l 一个表中最多可以有一个表中最多可以有253个可以参照的表，因此每个表最多个可以参照的表，因此每个表最多可以有可以有253个个FOREIGN KEY约束。约束。l 在在FOREIGN KEY约束中，只能参照同一个数据库中的表，约束中，只能参照同一个数据库中的表

8、，而不能参照其他数据库中的表。而不能参照其他数据库中的表。l FOREIGN KEY子句中的列数目和每个列指定的数据类型子句中的列数目和每个列指定的数据类型必须和必须和REFERENCE子句中的列相同。子句中的列相同。l FOREIGN KEY约束不能自动创建索引。约束不能自动创建索引。l 参照同一个表中的列时，必须只使用参照同一个表中的列时，必须只使用REFERENCE子句，子句，而不能使用而不能使用FOREIGN KEY子句。子句。l 在临时表中，不能使用在临时表中，不能使用FOREIGN KEY约束。约束。13.1.3 UNIQUE约束约束UNIQUE约束在列集内强制执行值的唯一性。对于

9、约束在列集内强制执行值的唯一性。对于UNIQUE约束中的列，表中不允许有两行包含相同的非空值。约束中的列，表中不允许有两行包含相同的非空值。主键也强制执行唯一性，但主键不允许空值，而且每个表中主主键也强制执行唯一性，但主键不允许空值，而且每个表中主键只能有一个，但是键只能有一个，但是UNIQUE列却可以有多个。列却可以有多个。UNIQUE约束优先于唯一索引。约束优先于唯一索引。【例【例13.3】 给出一个示例说明给出一个示例说明UNIQUE约束的使用方法。约束的使用方法。解：解：以下程序在以下程序在test数据库中创建了一个数据库中创建了一个table5表，其中指表，其中指定了定了c1列不能包

10、含重复的值：列不能包含重复的值：USE testGOCREATE TABLE table5(cl int UNIQUE,c2 int)GOINSERT table5 VALUES(1,100)GO如果再插入一行：如果再插入一行：INSERT table5 VALUES(1,200)则会出现如图则会出现如图13.1所示的错误消息。所示的错误消息。13.1.4 CHECK约束约束CHECK约束通过限制用户输入的值来加强域完整性。它约束通过限制用户输入的值来加强域完整性。它指定应用于列中输入的所有值的布尔（取值为指定应用于列中输入的所有值的布尔（取值为TRUE或或FALSE）搜索条件，拒绝所有不取值

11、为搜索条件，拒绝所有不取值为TRUE的值。可以为每列指定多的值。可以为每列指定多个个CHECK约束。约束。【例【例13.4】 给出一个示例说明给出一个示例说明CHECK约束的使用方法。约束的使用方法。解：解：以下程序在以下程序在test数据库中创建一个数据库中创建一个table6表，其中使用表，其中使用CHECK约束来限定约束来限定f2列只能为列只能为0100分：分：USE testGOCREATE TABLE table6(f1 int,f2 int NOT NULL CHECK(f2=0 AND f2=100)GO当执行如下语句：当执行如下语句：INSERT table6 VALUES(1

12、,120)则会出现如图则会出现如图13.2所示的错误消息。所示的错误消息。13.1.5 列约束和表约束列约束和表约束约束可以是列约束或表约束：约束可以是列约束或表约束：l 列约束被指定为列定义的一部分，并且仅适用于那个列列约束被指定为列定义的一部分，并且仅适用于那个列（前面的（前面的score表中的约束就是列约束）。表中的约束就是列约束）。l 表约束的声明与列的定义无关，可以适用于表中一个以表约束的声明与列的定义无关，可以适用于表中一个以上的列。上的列。l 当一个约束中必须包含一个以上的列时，必须使用表约当一个约束中必须包含一个以上的列时，必须使用表约束。例如，如果一个表的主键内有两个或两个以

13、上的列，束。例如，如果一个表的主键内有两个或两个以上的列，则必须使用表约束将这两列加入主键内。则必须使用表约束将这两列加入主键内。【例【例13.5】 给出以下程序的执行结果。给出以下程序的执行结果。USE testGOCREATE TABLE table7(c1 int,c2 int,c3 char(5),c4 char(10),CONSTRAINT c1 PRIMARY KEY(c1,c2)GOUSE testGOINSERT table7 VALUES(1,2,ABC1,XYZ1)INSERT table7 VALUES(1,2,ABC2,XYZ2)GOSELECT * FROM tabl

14、e7GO解：解：该程序在该程序在test数据库中创建数据库中创建table7表，它的主键为表，它的主键为c1和和c2。然后将其中插入两个记录（它们的。然后将其中插入两个记录（它们的c1和和c2列值相同），最列值相同），最后输出这些记录。执行时错误消息如图后输出这些记录。执行时错误消息如图13.3所示。所示。图图13.3 错误消息错误消息 在图在图13.3中单击中单击“结果结果”选项卡，看到如图选项卡，看到如图13.4所示的执所示的执行结果，从中看到，第行结果，从中看到，第2个个INSERT语句由于主键约束而没有语句由于主键约束而没有成功执行。成功执行。图图13.4 程序执的结果程序执的结果 1

15、3.2 默默 认认 值值如果在插入行时没有指定列的值，则默认值指定列中所如果在插入行时没有指定列的值，则默认值指定列中所使用的值。默认值可以是任何取值为常量的对象。使用的值。默认值可以是任何取值为常量的对象。在在SQL Server中，有两种使用默认值的方法：中，有两种使用默认值的方法：l 在创建表时，指定默认值。如果使用在创建表时，指定默认值。如果使用SQL Server管管理控制器，则可以在设计表时指定默认值。如果使理控制器，则可以在设计表时指定默认值。如果使用用T-SQL语言，则在语言，则在CREATE TABLE语句中使用语句中使用DEFAULT子句。这是首选的方法，也是定义默认子句。

16、这是首选的方法，也是定义默认值比较简洁的方法。值比较简洁的方法。l 使用使用CREATE DEFAULT语句创建默认对象，然后语句创建默认对象，然后使用存储过程使用存储过程sp_bindefault将该默认对象绑定到列将该默认对象绑定到列上。这是向前兼容的方法。上。这是向前兼容的方法。13.2.1 在创建表时指定默认值在创建表时指定默认值在使用在使用SQL Server管理控制器创建表时，可以为列指定默管理控制器创建表时，可以为列指定默认值，默认值可以是计算结果为常量的任何值，例如常量、内认值，默认值可以是计算结果为常量的任何值，例如常量、内置函数或数学表达式。置函数或数学表达式。在创建表时，

17、输入列名称后，设定该列的默认值，如图在创建表时，输入列名称后，设定该列的默认值，如图13.5所示，将所示，将student表性别列的默认值设置为表性别列的默认值设置为“男男”。【例【例13.6】 给出以下程序的执行结果。给出以下程序的执行结果。USE testGOCREATE TABLE table8(c1 int,c2 int DEFAULT 2*5,c3 datetime DEFAULT getdate()GO-如下语句插入一行数据并显示记录。如下语句插入一行数据并显示记录。USE testGOINSERT table8(c1) VALUES(1)SELECT * FROM table8G

18、O13.2.2 使用默认对象使用默认对象默认对象是单独存储的，删除表的时候，默认对象是单独存储的，删除表的时候，DEFAULT约束约束会自动删除，但是默认对象不会被删除。另外，创建默认对会自动删除，但是默认对象不会被删除。另外，创建默认对象后，需要将其绑定到某列或者用户自定义的数据类型上。象后，需要将其绑定到某列或者用户自定义的数据类型上。1创建默认对象创建默认对象可以使用可以使用CREATE DEFAULT语句创建默认对象。其语法语句创建默认对象。其语法格式如下：格式如下：CREATE DEFAULT default AS constant_exprion例如，使用下面的例如，使用下面的SQ

19、L语句创建语句创建con3默认对象：默认对象：USE testGOCREATE DEFAULT con3 AS 10 /*默认值设为默认值设为10*/GO2绑定默认对象绑定默认对象默认对象创建后不能使用，必须首先将其绑定到某列或默认对象创建后不能使用，必须首先将其绑定到某列或者用户自定义的数据类型上。绑定过程可以使用者用户自定义的数据类型上。绑定过程可以使用sp_bindefault存储过程来完成。其使用语法格式如下：存储过程来完成。其使用语法格式如下：sp_bindefault defname = default, objname = object_name , futureonly = f

20、utureonly_flag例如，上面将例如，上面将con3默认对象绑定到默认对象绑定到test数据库的数据库的table8表的表的c1列上的操作过程可以使用下面的列上的操作过程可以使用下面的T-SQL语句来完成：语句来完成：USE test GO EXEC sp_bindefault con3,table8.c1 GO3重命名默认对象重命名默认对象和其他的数据库对象一样，也可以重命名默认对象。重和其他的数据库对象一样，也可以重命名默认对象。重命名默认对象也是使用命名默认对象也是使用sp_rename存储过程来完成的。例如，存储过程来完成的。例如，以下以下T-SQL语句将默认对象语句将默认对象

21、con3的名称改为的名称改为con4：USE test GO EXEC sp_rename con3,con4 GO4解除默认对象的绑定解除默认对象的绑定可以使用可以使用sp_unbindefault存储过程来解除绑定，其语法格存储过程来解除绑定，其语法格式如下：式如下：sp_unbindefault objname = object_name ,futureonly = futureonly_flag例如，下面的例如，下面的SQL语句解除语句解除test数据库中数据库中table8表表c1列上的列上的默认值绑定：默认值绑定：USE test GO EXEC sp_unbindefault t

22、able8.c1 GO对应的消息如下：对应的消息如下：已解除了表列与其默认值之间的绑定。已解除了表列与其默认值之间的绑定。5删除默认对象删除默认对象在删除默认对象之前，首先要确认默认对象已经解除绑在删除默认对象之前，首先要确认默认对象已经解除绑定。删除默认对象使用定。删除默认对象使用DROP DEFAULT语句，其语法格式语句，其语法格式如下：如下：DROP DEFAULT default ,n其中，其中，“default”是现有默认值的名称。若要查看现有默认是现有默认值的名称。若要查看现有默认值的列表，可以执行值的列表，可以执行sp_help存储过程。例如，以下存储过程。例如，以下T-SQL

23、语语句用于删除默认对象句用于删除默认对象con4：USE test GO DROP DEFAULT con4 GO13.3 规规 则则规则限制了可以存储在表中或者用户定义数据类型的值，规则限制了可以存储在表中或者用户定义数据类型的值，它可以使用多种方式来完成对数据值的检验，可以使用函数它可以使用多种方式来完成对数据值的检验，可以使用函数返回验证信息，也可以使用关键字返回验证信息，也可以使用关键字BETWEEN、LIKE和和IN完成对输入数据的检查。完成对输入数据的检查。当将规则绑定到列或者用户定义数据类型时，规则将指当将规则绑定到列或者用户定义数据类型时，规则将指定可以插入到列中的可接受的值。

24、规则是作为一个独立的数定可以插入到列中的可接受的值。规则是作为一个独立的数据库对象存在，表中每列或者每个用户定义数据类型只能和据库对象存在，表中每列或者每个用户定义数据类型只能和一个规则绑定。一个规则绑定。13.3.1 创建规则创建规则创建规则使用创建规则使用CREATE RULE语句，其语法格式如下：语句，其语法格式如下：CREATE RULE 规则名规则名 AS condition_exprion【例【例13.8】 给出以下程序的功能。给出以下程序的功能。USE test GO CREATE RULE rule1 AS c1 BETWEEN 0 and 10 GO解：解：该程序创建一个名为该程序创建一个名为rule1的规则，限定输入的值必的规则，限定输入的值必须在须在010之间。之间。13.3.2 绑定规则绑定规则要使用规则，必须首先将其和列或者用户定义数据类型绑要使用规则，必须首先将其和列或者用户定义数据类型绑定。可以使用定。可以使用sp_bindrule存储过程，也可以使用存储过程，也可以使用SQL Server管管理控制器。理控制器。使用使用SQL Server管理控制器绑定规则的操作步骤和绑定默管理控制器绑定规则的操作步骤和绑定默认对