第9章数据库完整性.docx

1、第9章 数据库完整性第9章 数据库完整性数据库完整性就是确保数据库中的数据的一致性和正确性。SQL Server提供了相应的组件以实现数据库的完整性，例如实体完整性通过索引、UNIQUE约束、PRIMARY KEY约束和IDENTITY属性等实现；域完整性通过FOREIGN KEY约束、CHECK约束、DEFAULT定义、NOT NULL定义和规则等实现；参照完整性通过FOREIGN KEY、CHECK约束和触发器等实现；用户定义完整性通过CREATE TABLE中的所有列级和表级约束、存储过程和触发器等实现。本章主要讨论约束、默认和规则等内容，有关存储过程和触发器的内容分别在后面两章中介绍。

2、9.1约束设计表时需要识别列的有效值并决定如何强制实现列中数据的完整性。SQL Server 2000提供多种强制数据完整性的机制： PRIMARY KEY约束 FOREIGN KEY约束 UNIQUE约束 CHECK约束 NOT NULL(为空性)上述约束是SQL Server 2000自动强制数据完整性的方式，它们定义关于列中允许值的规则，是强制完整性的标准机制。使用约束优先于使用触发器、规则和默认值。查询优化器也使用约束定义生成高性能的查询执行计划。其中NOT NULL前面己经使用过，下面介绍其他四种约束。9.1.1 PRIMARY KEY约束PRIMARY KEY约束标识列或列集，这些

3、列或列集的值惟一标识表中的行。一个PRIMARY KEY约束可以： 作为表定义的一部分在创建表时创建。 添加到尚没有PRIMARY KEY约束的表中(一个表只能有一个PRIMARY KEY约束)。 如果己有PRIMARY KEY约束，则可对其进行修改或删除。例如，可以使表的PRIMARY KEY约束引用其他列，更改列的顺序、索引名、聚集选项或PRIMARY KEY约束的填充因子。定义了PRIMARY KEY约束的列的列宽不能更改。在一个表中，不能有两行包含相同的主键值。不能在主键内的任何列中输入NULL值。在数据库中NULL是特殊值，代表不同于空白和0值的未知值。建议使用一个小的整数列作为主键

4、。每个表都应有一个主键。例如，下面的SQL语句在test数据库中创建一个名为department的表，其中指定dno为主键：USE testGOCREATE TABLE department /*部门表*/( dno int PRIMARY KEY, /*部门号,为主键*/ dname char(20), /*部门名*/)GO注意：若要使用Transact-SQL修改PRIMARY KEY，必须先删除现有的PRIMARY KEY约束，然后再用新定义重新创建。如果在创建表时指定一个主键，则SQL Server会自动创建一个名为“PK_”且后跟表名的主键索引。这个惟一索引只能在删除与它保持联系的表

5、或者主键约束时才能删除掉。如果不指定索引类型，缺省时创建一个聚集索引。9.1.2 FOREIGN KEY约束FOREIGN KEY约束标识表之间的关系，用于强制参照完整性，为表中一列或者多列数据提供参照完整性。FOREIGN KEY约束也可以参照自身表中的其他列，这种参照称为自参照。FOREIGN KEY约束可以在下面情况下使用： 作为表定义的一部分在创建表时创建。 如果FOREIGN KEY约束与另一个表(或同一表)已有的PRIMARY KEY约束或UNIQUE约束相关联，则可向现有表添加FOREIGN KEY约束。一个表可以有多个FOREIGN KEY约束。 对己有的FOREIGN KEY

6、约束进行修改或删除。例如，要使一个表的FOREIGN KEY约束引用其他列。定义了FOREIGN KEY约束列的列宽不能更改。下面就是一个使用FOREIGN KEY约束的例子：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) /*地址*/)G

7、O如果一个外键值没有主键，则不能插入带该值(NULL除外)的行。如果尝试删除现有外键指向的行，ON DELETE子句将控制所采取的操作。ON DELETE子句有两个选项： NO ACTION 指定删除因错误而失败。 CASCADE 指定还将删除包含指向己删除行的外键的所有行。如果尝试更新现有外键指向的候选键值，ON UPDATE子句将定义所采取的操作。它也支持NO ACTION和CASCADE选项。使用FOREIGN KEY约束，还应注意以下几个问题： 一个表中最多可以有253个可以参照的表，因此每个表最多可以有253个FOREIGN KEY约束。 FOREIGN KEY约束中，只能参照同一个

8、数据库中的表，而不能参照其他数据库中的表。 FOREIGN KEY子句中的列数目和每个列指定的数据类型必须和REFERENCE子句中的列相同。 FOREIGN KEY约束不能自动创建索引。 参照同一个表中的列时，必须只使用REFERENCE子句，而不能使用FOREIGN KEY子句。 在临时表中，不能使用FOREIGN KEY约束。9.1.3 UNIQUE约束UNIQUE约束在列集内强制执行值的惟一性。对于UNIQUE约束中的列，表中不允许有两行包含相同的非空值。主键也强制执行惟一性，但主键不允许空值，而且每个表中主键只能有一个，但是UNIQUE列却可以有多个。UNIQUE约束优先于惟一索引。

9、在向表中的现有列添加UNIQUE约束时，默认情况下SQL Server 2000检查列中的现有数据，确保除NULL外的所有值均惟一。如果对有重复值的列添加UNIQUE约束，SQL Server将返回错误信息并不添加约束。SQL Server自动创建UNIQUE索引来强制UNIQUE约束的惟一性要求。因此，如果试图插入重复行，SQL Server将返回错误信息，说明该操作违反了UNIQUE约束并不将该行添加到表中。除非明确指定了聚集索引，否则，默认情况下创建惟一的非聚集索引以强制UNIQUE约束。例如，下面的SQL语句在test数据库中创建了一个table5表，其中指定了c1字段不能包含重复的值

10、：USE testGOCREATE TABLE table5(cl int UNIQUE, c2 int)GOINSERT table5 VALUES(1,100)GO如果再插入一行：INSERT table5 VALUES(1,200)则会出现如下的错误：服务器: 消息 2627，级别 14，状态 2，行 1违反了 UNIQUE KEY 约束 UQ_table5_4BAC3F29。不能在对象 table5 中插入重复键。语句已终止。注意：删除UNIQUE约束，以删除对约束中所包含列或列组合输入值的惟一性要求。如果相关列是表的全文健，则不能删除UNIQUE约束。9.1.4 CHECK约束CHE

11、CK约束通过限制用户输入的值来加强域完整性。它指定应用于列中输入的所有值 的布尔(取值为TRUE或FALSE)搜索条件，拒绝所有不取值为TRUE的值。可以为每列指定多个CHECK约束。 例如，下面的SQL语句在test数据库中创建一个table6表，其中使用CHECK约束来限定f2列只能为0-100分：USE testGOCREATE TABLE table6( f1 int, f2 int NOT NULL CHECK(f2=0 AND f2=100)GO当执行如下语句：INSERT table6 VALUES(1,120)则会出现如下的错误：服务器: 消息 547，级别 16，状态 1，行

12、 1INSERT 语句与 COLUMN CHECK 约束 CK_table6_f2_4D94879B 冲突。该冲突发生于数据库 test，表 table6, column f2。语句已终止。9.1.5列约束和表约束约束可以是列约束或表约束： 列约束被指定为列定义的一部分，并且仅适用于那个列(前面的score表中的约束就是列约束)。 表约束的声明与列的定义无关，可以适用于表中一个以上的列。 当一个约束中必须包含一个以上的列时，必须使用表约束。例如，如果一个表的主键内有两个或两个以上的列，则必须使用表约束将这两列加入主键内。例如，以下SQL语句在test数据库中创建table7表，它的主键为c1和

13、c2：USE test GOCREATE TABLE table7( c1 int, c2 int, c3 char(5), c4 char(10), CONSTRAINT c1 PRIMARY KEY(c1,c2)GO执行以下语句：USE testGOINSERT table7 VALUES(1,2,ABC1,XYZ1)INSERT table7 VALUES(1,2,ABC2,XYZ2)GOSELECT * FROM table7GO执行结果如下：服务器: 消息 2627，级别 14，状态 1，行 1违反了 PRIMARY KEY 约束 c1。不能在对象 table7 中插入重复键。语句已

14、终止。c1 c2 c3 c4 - - - - 1 2 ABC1 XYZ1 从中看到，第二个INSERT语句由于主键约束而没有成功执行。9.2默认值 如果在插入行时没有指定列的值，则默认值指定列中所使用的值。默认值可以是任何取值为常量的对象。在SQL Server中，有两种使用默认值的方法： 在创建表时，指定默认值。如果使用企业管理器，则可以在设计表时指定默认值。如果使用Transact-SQL语言，则在CREATE TABLE语句中使用DEFAULT子句。这是首选的方法，也是定义默认值比较简洁的方法。 使用CREATE DEFAULT语句创建默认对象，然后使用存储过程sp_bindefault

15、将该默认对象绑定到列上。这是向前兼容的方法。9.2.1在创建表时指定默认值在使用企业管理器创建表时，可以在输入字段名称后，设定该字段的默认值，如图9.1所示，将“性别”字段的默认值设置为“男”。图9.1设定默认值如果使用SQL-Transact语言，则可以使用DEFAULT子句。这样在使用INSERT和UPDATE语句时，如果没有提供值，则默认值会提供值。例如，下面在test数据库中创建一个table8表，其中c2指定默认值为当前日期：USE testGOCREATE TABLE table8( c1 int, c2 datetime DEFAULT(getdate()GO然后执行如下语句插入

16、一行数据并显示记录：USE testGOINSERT table8(c1) VALUES(1)SELECT * FROM table8GO执行结果如下：c1 c2 - - 1 2004-11-01 08:47:06.413 从上述结果看到，插入数据中，只给定了cl字段的值，c2自动使用默认值，这里默认值是使用getdate()函数来获取当前日期。同样，可以通过ALTER TABLE语句给表的字段加上默认值，例如，以下语句的功能与前面的相同：USE testGODROP TABLE table8 /*删除table8表*/CREATE TABLE table8 /*重建没有默认值的表table8

17、*/( c1 int, c2 datetime)GOALTER TABLE table8 /*通过ALTER命令给c2字段加上默认值*/ADD CONSTRAINT con1 DEFAULT getdate() FOR c2GOINSERT table8(c1) VALUES(1) /*插入一个记录*/SELECT * FROM table8 /*显示记录*/GO其中con1表示DEFAULT约束的名字。9.2.2使用默认对象默认对象是单独存储的，删除表的时候，DEFAULT约束会自动删除，但是默认对象不会被删除。另外，创建默认对象后，需要将其绑定到某列或者用户自定义的数据类型上。1创建默认对

18、象 创建默认对象可以使用CREATE DEFAULT语句，也可以使用企业管理器。(1) 使用企业管理器下面在test数据库中创建一个默认对象。操作步骤如下：1) 打开企业管理器，展开服务器组，并展开相应的服务器。2) 打开test数据库，选择“默认”选项，然后右击鼠标，执行“新建默认”命令。3) 此时会打开新建默认对象对话框，如图9.2所示。输入默认对象的名称con2，默认值为“汉族”。图9.2新建默认对象对话框4) 单击“确定”按钮，即可创建名为con2的默认对象。(2) 使用CREATE DEFAULT语句CREATE DEFAULT语句的语法格式如下：CREATE DEFAULT def

19、aultAS constant_expression各参数含义如下： default 默认值的名称。默认值名称必须符合标识符的规则。可以选择是否指定默认值所有者名称。 constant_expression 只包含常量值的表达式(不能包含任何列或其他数据库对象的名称)。可以使用任何常量、内置函数或数学表达式。字符和日期常量用单引号()引起来；货币、整数和浮点常量不需要使用引号。二进制数据必须以0x开头，货币数据必须以美元符号($)开头。默认值必须与列数据类型兼容。例如，使用下面的SQL语句创建con3默认对象：USE testGOCREATE DEFAULT con3 AS 10 /*默认值设

20、为10*/GO2绑定默认对象默认对象创建后不能使用，必须首先将其绑定到某列或者用户自定义的数据类型上。绑定过程可以使用企业管理器来完成，也可以使用sp_bindefault存储过程来完成。 (1) 使用企业管理器使用企业管理器绑定一个默认对象的操作步骤如下：1) 打开企业管理器，展开服务器组，并展开相应的服务器。2) 打开“数据库”文件夹，选择test数据库。3) 单击“默认”文件夹，然后在右侧详细信息窗格中选择要绑定的默认对象，这里选择con3默认对象。4) 右击鼠标，执行“属性”命令，打开“默认属性”对话框，如图9.3所示。其中的“值”文本框可以设置默认值，“绑定UDT”按钮可将默认对象绑

21、定到用户自定义数据类型，“绑定列”按钮可将默认对象绑定到列。图9.3“默认属性”对话框5) 单击“绑定列”按钮，打开“将默认值绑定到列”对话框，如图9.4所示。在“表”下拉列表框中选择列所在的表table8，然后在“未绑定的列”列表框中选择要绑定到的列，然后单击“添加”按钮，将其添加到“绑定列”列表框中。这里将con3默认对象绑定到table8表的c1列上。图9.4“将默认值绑定到列”对话框6) 单击“确定”按钮，即可将con3默认对象绑定到table8表的c1列上。7) 如果要将默认对象绑定到用户自定义数据类型上，则可以在“默认属性”对话框中，单击“绑定UDT”按钮，可打开“将默认值绑定到用

22、户定义的数据类型”对话框，如图9.5所示。可以选择要绑定到的用户定义数据类型。图9.5“将默认值绑定到用户定义的数据类型”对话框在此对话框中可以选择要绑定的用户定义数据类型，选择方法是选中“绑定”栏下的复选框，然后单击“确定”按钮即可。(2) 使用sp_bindefault存储过程sp_bindefault存储过程的语法格式如下：sp_bindefault defname = default,objname = object_name, futureonly = futureonly_flag各参数含义如下： defname = default 由CREATE DEFAULT语句创建的默认名称

23、。“default”的数据类型为nvarchar(776)，无默认值。 objname = object_name 要绑定默认值的表和列名称或用户定义的数据类型。“object_name”的数据类型为nvarchar(517)，无默认值。如果“object_name”没有采取table.column格式，则认为它属于用户定义数据类型。默认情况下，用户定义数据类型的现有列继承“default”，除非默认值直接绑定到列中。默认值无法绑定到timestamp数据类型的列、带IDENTITY属性的列或者已经有DEFAULT约束的列。 futureonly = futureonly_flag 仅在将默认

24、值绑定到用户定义的数据类型时才使用。“futureonly_flag”的数据类型为varchar(15)，默认值为NULL。将此参数设置为futureonly时，它会防止现有的属于此数据类型的列继承新的默认值。当将默认值绑定到列时不会使用此参数。如果“futureonly_flag”为NULL，那么新默认值将绑定到用户定义数据类型的任一列，条件是此数据类型当前无默认值或者使用用户定义数据类型的现有默认值。例如，上面将con3默认对象绑定到test数据库的table8表的c1列上的操作过程可以使用下面的SQL语句来完成：USE testGOEXEC sp_bindefault con3,tabl

25、e8.c1GO3重命名默认对象和其他的数据库对象一样，也可以重命名默认对象。重命名默认对象也是使用sp_rename存储过程来完成的，请参考前面的介绍。使用企业管理器也可以重命名默认对象，只需选择要重命名的默认对象，然后右击鼠标，执行“重命名”命令，输入新的默认对象名称后按Enter键即可。4解除默认对象的绑定解除绑定可以使用sp_unbindefault存储过程，其语法格式如下：sp_unbindefault objname = object_name,futureonly = futureonly_flag各参数含义如下： objname = object_name 是要解除默认值绑定的表

26、和列或者用户定义数据类型的名称。当为用户定义数据类型解除默认值绑定时，所有属于该数据类型并具有相同默认值的列也同时解除默认值绑定。对属于该数据类型的列，如果其默认值直接绑定到列上，则该列不受影响。 futureonly= futureonly_f lag 仅用于解除用户定义数据类型默认值的绑定。当参数“futureonly_flag”为futureonly时，现有的属于该数据类型的列不会失去指定默认值。提示：由于一列或者用户定义数据类型只能同时绑定一个默认对象，所以解除绑定时，不需要再指定默认对象的名称。另外，如果要查看默认值的文本，可以以该默认对象的名称为参数执行存储过程sp_helptex

27、t。例如，下面的SQL语句解除test数据库中table8表c1列上的默认值绑定：USE testGOEXEC sp_unbindefault table8.c1GO提示信息如下：已从表的列上解除了默认值的绑定。5删除默认对象在删除默认对象之前，首先要确认默认对象已经解除绑定。删除默认对象使用DROP DEFAULT语句，其语法格式如下：DROP DEFAULT default ,n其中，“default”是现有默认值的名称。若要查看现有默认值的列表，可以执行sp_help存储过程。n是表示可以指定多个默认值的占位符。例如，下面的SQL语句判断是否存在con3默认对象，如果存在，则删除该默认对

28、象：USE testGOIF EXISTS (SELECT name FROM sysobjects WHERE name = con3 AND type=D) DROP DEFAULT con3GO注意：DROP DEFAULT语句不适用于DEFAULT约束。如果要除去DEFAULT约束，则应该使用ALTER TABLE语句。9.3 规则规则限制了可以存储在表中或者用户定义数据类型的值，它可以使用多种方式来完成对数据值的检验，可以使用函数返回验证信息，也可以使用关键字BETWEEN、LIKE和IN完成对输入数据的检查。当将规则绑定到列或者用户定义数据类型时，规则将指定可以插入到列中的可接受的

29、值。规则是作为一个独立的数据库对象存在，表中每列或者每个用户定义数据类型只能和一个规则绑定。注意：规则是一个向后兼容的功能，用于执行一些与CHECK约束相同的功能。CHECK约束是用来限制列值的首选标准方法。CHECK约束比规则更简明，一个列只能应用一个规则，但是却可以应用多个CHECK约束。CHECK约束作为CREATE TABLE语句的一部分进行指定，而规则以单独的对象创建，然后绑定到列上。和默认对象类似，规则只有绑定到列或者用户定义数据类型上才能起作用。如果要删除规则，则应确定规则已经解除绑定。9.3.1创建规则创建规则使用CREATE RULE语句，其语法格式如下：CREATE RUL

30、E rule AS condition_expression各参数含义如下： rule 是新规则的名称。规则名称必须符合标识符规则。可以选择是否指定规则所有者的名称。 condition_expression 是定义规则的条件。规则可以是WHERE子句中任何有效的表达式，并且可以包含诸如算术运算符、关系运算符和谓词(如IN、LIKE、BETWEEN)之类的元素。规则不能引用列或其他数据库对象。可以包含不引用数据库对象的内置函数。“condition_expression”包含一个变量。每个局部变量的前面都有一个符号。该表达式引用通过UPDATE或INSERT语句输入的值。在创建规则时，可以使用任何名称或符号表示值，但第一个字符必须是符号。例如，下面的SQL语句创建一个名为rule1的规则，限定输入的值必须在0到10之间：USE testGOCREATE RULE rule1 AS c1 BETWEEN 0 and 10GO而下面创建的规则rule2将输入到该规则所绑定的列中的实际值限制为只能是该规则中列出的值：USE testGOCREATE RULE rul