第9章数据库完整性.docx
《第9章数据库完整性.docx》由会员分享，可在线阅读，更多相关《第9章数据库完整性.docx（21页珍藏版）》请在冰豆网上搜索。
第9章数据库完整性
第9章数据库完整性
数据库完整性就是确保数据库中的数据的一致性和正确性。
SQLServer提供了相应的组件以实现数据库的完整性，例如实体完整性通过索引、UNIQUE约束、PRIMARYKEY约束和IDENTITY属性等实现；域完整性通过FOREIGNKEY约束、CHECK约束、DEFAULT定义、NOTNULL定义和规则等实现；参照完整性通过FOREIGNKEY、CHECK约束和触发器等实现；用户定义完整性通过CREATETABLE中的所有列级和表级约束、存储过程和触发器等实现。
本章主要讨论约束、默认和规则等内容，有关存储过程和触发器的内容分别在后面两章中介绍。
9.1约束
设计表时需要识别列的有效值并决定如何强制实现列中数据的完整性。
SQLServer2000提供多种强制数据完整性的机制：
∙PRIMARYKEY约束
∙FOREIGNKEY约束
∙UNIQUE约束
∙CHECK约束
∙NOTNULL（为空性）
上述约束是SQLServer2000自动强制数据完整性的方式，它们定义关于列中允许值的规则，是强制完整性的标准机制。
使用约束优先于使用触发器、规则和默认值。
查询优化器也使用约束定义生成高性能的查询执行计划。
其中NOTNULL前面己经使用过，下面介绍其他四种约束。
9.1.1PRIMARYKEY约束
PRIMARYKEY约束标识列或列集，这些列或列集的值惟一标识表中的行。
一个PRIMARYKEY约束可以：
∙作为表定义的一部分在创建表时创建。
∙添加到尚没有PRIMARYKEY约束的表中（一个表只能有一个PRIMARYKEY约束）。
∙如果己有PRIMARYKEY约束，则可对其进行修改或删除。
例如，可以使表的PRIMARYKEY约束引用其他列，更改列的顺序、索引名、聚集选项或PRIMARYKEY约束的填充因子。
定义了PRIMARYKEY约束的列的列宽不能更改。
在一个表中，不能有两行包含相同的主键值。
不能在主键内的任何列中输入NULL值。
在数据库中NULL是特殊值，代表不同于空白和0值的未知值。
建议使用一个小的整数列作为主键。
每个表都应有一个主键。
例如，下面的SQL语句在test数据库中创建一个名为department的表，其中指定dno为主键：
USEtest
GO
CREATETABLEdepartment/*部门表*/
（dnointPRIMARYKEY,/*部门号,为主键*/
dnamechar（20）,/*部门名*/
）
GO
注意：
若要使用Transact-SQL修改PRIMARYKEY，必须先删除现有的PRIMARYKEY约束，然后再用新定义重新创建。
如果在创建表时指定一个主键，则SQLServer会自动创建一个名为“PK_”且后跟表名的主键索引。
这个惟一索引只能在删除与它保持联系的表或者主键约束时才能删除掉。
如果不指定索引类型，缺省时创建一个聚集索引。
9.1.2FOREIGNKEY约束
FOREIGNKEY约束标识表之间的关系，用于强制参照完整性，为表中一列或者多列数据提供参照完整性。
FOREIGNKEY约束也可以参照自身表中的其他列，这种参照称为自参照。
FOREIGNKEY约束可以在下面情况下使用：
∙作为表定义的一部分在创建表时创建。
∙如果FOREIGNKEY约束与另一个表（或同一表）已有的PRIMARYKEY约束或UNIQUE约束相关联，则可向现有表添加FOREIGNKEY约束。
一个表可以有多个FOREIGNKEY约束。
∙对己有的FOREIGNKEY约束进行修改或删除。
例如，要使一个表的FOREIGNKEY约束引用其他列。
定义了FOREIGNKEY约束列的列宽不能更改。
下面就是一个使用FOREIGNKEY约束的例子：
USEtest
GO
CREATETABLEworker/*职工表*/
（nointPRIMARYKEY,/*编号,为主键*/
namechar（8）,/*姓名*/
sexchar
（2）,/*性别*/
dnoint/*部门号*/
FOREIGNKEYREFERENCESdepartment（dno）
ONDELETENOACTION,
addresschar（30）/*地址*/
）
GO
如果一个外键值没有主键，则不能插入带该值（NULL除外）的行。
如果尝试删除现有外键指向的行，ONDELETE子句将控制所采取的操作。
ONDELETE子句有两个选项：
∙NOACTION指定删除因错误而失败。
∙CASCADE指定还将删除包含指向己删除行的外键的所有行。
如果尝试更新现有外键指向的候选键值，ONUPDATE子句将定义所采取的操作。
它也支持NOACTION和CASCADE选项。
使用FOREIGNKEY约束，还应注意以下几个问题：
∙一个表中最多可以有253个可以参照的表，因此每个表最多可以有253个FOREIGNKEY约束。
∙FOREIGNKEY约束中，只能参照同一个数据库中的表，而不能参照其他数据库中的表。
∙FOREIGNKEY子句中的列数目和每个列指定的数据类型必须和REFERENCE子句中的列相同。
∙FOREIGNKEY约束不能自动创建索引。
∙参照同一个表中的列时，必须只使用REFERENCE子句，而不能使用FOREIGNKEY子句。
∙在临时表中，不能使用FOREIGNKEY约束。
9.1.3UNIQUE约束
UNIQUE约束在列集内强制执行值的惟一性。
对于UNIQUE约束中的列，表中不允许有两行包含相同的非空值。
主键也强制执行惟一性，但主键不允许空值，而且每个表中主键只能有一个，但是UNIQUE列却可以有多个。
UNIQUE约束优先于惟一索引。
在向表中的现有列添加UNIQUE约束时，默认情况下SQLServer2000检查列中的现有数据，确保除NULL外的所有值均惟一。
如果对有重复值的列添加UNIQUE约束，SQLServer将返回错误信息并不添加约束。
SQLServer自动创建UNIQUE索引来强制UNIQUE约束的惟一性要求。
因此，如果试图插入重复行，SQLServer将返回错误信息，说明该操作违反了UNIQUE约束并不将该行添加到表中。
除非明确指定了聚集索引，否则，默认情况下创建惟一的非聚集索引以强制UNIQUE约束。
例如，下面的SQL语句在test数据库中创建了一个table5表，其中指定了c1字段不能包含重复的值：
USEtest
GO
CREATETABLEtable5
（clintUNIQUE,
c2int
）
GO
INSERTtable5VALUES（1,100）
GO
如果再插入一行：
INSERTtable5VALUES（1,200）
则会出现如下的错误：
服务器:
消息2627，级别14，状态2，行1
违反了UNIQUEKEY约束'UQ__table5__4BAC3F29'。
不能在对象'table5'中插入重复键。
语句已终止。
注意：
删除UNIQUE约束，以删除对约束中所包含列或列组合输入值的惟一性要求。
如果相关列是表的全文健，则不能删除UNIQUE约束。
9.1.4CHECK约束
CHECK约束通过限制用户输入的值来加强域完整性。
它指定应用于列中输入的所有值的布尔（取值为TRUE或FALSE）搜索条件，拒绝所有不取值为TRUE的值。
可以为每列指定多个CHECK约束。
例如，下面的SQL语句在test数据库中创建一个table6表，其中使用CHECK约束来限定f2列只能为0-100分：
USEtest
GO
CREATETABLEtable6
（f1int,
f2intNOTNULLCHECK（f2>=0ANDf2<=100）
）
GO
当执行如下语句：
INSERTtable6VALUES（1,120）
则会出现如下的错误：
服务器:
消息547，级别16，状态1，行1
INSERT语句与COLUMNCHECK约束'CK__table6__f2__4D94879B'冲突。
该冲突发生于数据库'test'，表'table6',column'f2'。
语句已终止。
9.1.5列约束和表约束
约束可以是列约束或表约束：
∙列约束被指定为列定义的一部分，并且仅适用于那个列（前面的score表中的约束就是列约束）。
∙表约束的声明与列的定义无关，可以适用于表中一个以上的列。
∙当一个约束中必须包含一个以上的列时，必须使用表约束。
例如，如果一个表的主键内有两个或两个以上的列，则必须使用表约束将这两列加入主键内。
例如，以下SQL语句在test数据库中创建table7表，它的主键为c1和c2：
USEtest
GO
CREATETABLEtable7
（c1int,
c2int,
c3char（5）,
c4char（10）,
CONSTRAINTc1PRIMARYKEY（c1,c2）
）
GO
执行以下语句：
USEtest
GO
INSERTtable7VALUES（1,2,'ABC1','XYZ1'）
INSERTtable7VALUES（1,2,'ABC2','XYZ2'）
GO
SELECT*FROMtable7
GO
执行结果如下：
服务器:
消息2627，级别14，状态1，行1
违反了PRIMARYKEY约束'c1'。
不能在对象'table7'中插入重复键。
语句已终止。
c1c2c3c4
-------------------------------------------
12ABC1XYZ1
从中看到，第二个INSERT语句由于主键约束而没有成功执行。
9.2默认值
如果在插入行时没有指定列的值，则默认值指定列中所使用的值。
默认值可以是任何
取值为常量的对象。
在SQLServer中，有两种使用默认值的方法：
∙在创建表时，指定默认值。
如果使用企业管理器，则可以在设计表时指定默认值。
如果使用Transact-SQL语言，则在CREATETABLE语句中使用DEFAULT子句。
这是首选的方法，也是定义默认值比较简洁的方法。
∙使用CREATEDEFAULT语句创建默认对象，然后使用存储过程sp_bindefault将该默认对象绑定到列上。
这是向前兼容的方法。
9.2.1在创建表时指定默认值
在使用企业管理器创建表时，可以在输入字段名称后，设定该字段的默认值，如图9.1所示，将“性别”字段的默认值设置为“男”。
图9.1设定默认值
如果使用SQL-Transact语言，则可以使用DEFAULT子句。
这样在使用INSERT和UPDATE语句时，如果没有提供值，则默认值会提供值。
例如，下面在test数据库中创建一个table8表，其中c2指定默认值为当前日期：
USEtest
GO
CREATETABLEtable8
（c1int,
c2datetimeDEFAULT（getdate（））
）
GO
然后执行如下语句插入一行数据并显示记录：
USEtest
GO
INSERTtable8（c1）VALUES
（1）
SELECT*FROMtable8
GO
执行结果如下：
c1c2
----------------------------------
12004-11-0108:
47:
06.413
从上述结果看到，插入数据中，只给定了cl字段的值，c2自动使用默认值，这里默认值是使用getdate（）函数来获取当前日期。
同样，可以通过ALTERTABLE语句给表的字段加上默认值，例如，以下语句的功能与前面的相同：
USEtest
GO
DROPTABLEtable8/*删除table8表*/
CREATETABLEtable8/*重建没有默认值的表table8*/
（
c1int,
c2datetime
）
GO
ALTERTABLEtable8/*通过ALTER命令给c2字段加上默认值*/
ADDCONSTRAINTcon1DEFAULTgetdate（）FORc2
GO
INSERTtable8（c1）VALUES
（1）/*插入一个记录*/
SELECT*FROMtable8/*显示记录*/
GO
其中con1表示DEFAULT约束的名字。
9.2.2使用默认对象
默认对象是单独存储的，删除表的时候，DEFAULT约束会自动删除，但是默认对象不会被删除。
另外，创建默认对象后，需要将其绑定到某列或者用户自定义的数据类型上。
1．创建默认对象
创建默认对象可以使用CREATEDEFAULT语句，也可以使用企业管理器。
（1）使用企业管理器
下面在test数据库中创建一个默认对象。
操作步骤如下：
1）打开企业管理器，展开服务器组，并展开相应的服务器。
2）打开test数据库，选择“默认”选项，然后右击鼠标，执行“新建默认”命令。
3）此时会打开新建默认对象对话框，如图9.2所示。
输入默认对象的名称con2，默认值为“汉族”。
图9.2新建默认对象对话框
4）单击“确定”按钮，即可创建名为con2的默认对象。
（2）使用CREATEDEFAULT语句
CREATEDEFAULT语句的语法格式如下：
CREATEDEFAULTdefault
ASconstant_expression
各参数含义如下：
∙default默认值的名称。
默认值名称必须符合标识符的规则。
可以选择是否指定默认值所有者名称。
∙constant_expression只包含常量值的表达式（不能包含任何列或其他数据库对象的名称）。
可以使用任何常量、内置函数或数学表达式。
字符和日期常量用单引号（'）引起来；货币、整数和浮点常量不需要使用引号。
二进制数据必须以0x开头，货币数据必须以美元符号（$）开头。
默认值必须与列数据类型兼容。
例如，使用下面的SQL语句创建con3默认对象：
USEtest
GO
CREATEDEFAULTcon3AS10/*默认值设为10*/
GO
2．绑定默认对象
默认对象创建后不能使用，必须首先将其绑定到某列或者用户自定义的数据类型上。
绑定过程可以使用企业管理器来完成，也可以使用sp_bindefault存储过程来完成。
（1）使用企业管理器
使用企业管理器绑定一个默认对象的操作步骤如下：
1）打开企业管理器，展开服务器组，并展开相应的服务器。
2）打开“数据库”文件夹，选择test数据库。
3）单击“默认”文件夹，然后在右侧详细信息窗格中选择要绑定的默认对象，这里选择con3默认对象。
4）右击鼠标，执行“属性”命令，打开“默认属性”对话框，如图9.3所示。
其中的“值”文本框可以设置默认值，“绑定UDT”按钮可将默认对象绑定到用户自定义数据类型，“绑定列”按钮可将默认对象绑定到列。
图9.3“默认属性”对话框
5）单击“绑定列”按钮，打开“将默认值绑定到列”对话框，如图9.4所示。
在“表”下拉列表框中选择列所在的表table8，然后在“未绑定的列”列表框中选择要绑定到的列，然后单击“添加”按钮，将其添加到“绑定列”列表框中。
这里将con3默认对象绑定到table8表的c1列上。
图9.4“将默认值绑定到列”对话框
6）单击“确定”按钮，即可将con3默认对象绑定到table8表的c1列上。
7）如果要将默认对象绑定到用户自定义数据类型上，则可以在“默认属性”对话框中，单击“绑定UDT”按钮，可打开“将默认值绑定到用户定义的数据类型”对话框，如图9.5所示。
可以选择要绑定到的用户定义数据类型。
图9.5“将默认值绑定到用户定义的数据类型”对话框
在此对话框中可以选择要绑定的用户定义数据类型，选择方法是选中“绑定”栏下的复选框，然后单击“确定”按钮即可。
（2）使用sp_bindefault存储过程
sp_bindefault存储过程的语法格式如下：
sp_bindefault[@defname=]'default',
[@objname=]'object_name'
[,[@futureonly=]'futureonly_flag']
各参数含义如下：
∙[@defname=]'default'由CREATEDEFAULT语句创建的默认名称。
“default”的数据类型为nvarchar（776），无默认值。
∙[@objname=]'object_name'要绑定默认值的表和列名称或用户定义的数据类型。
“object_name”的数据类型为nvarchar（517），无默认值。
如果“object_name”没有采取table.column格式，则认为它属于用户定义数据类型。
默认情况下，用户定义数据类型的现有列继承“default”，除非默认值直接绑定到列中。
默认值无法绑定到timestamp数据类型的列、带IDENTITY属性的列或者已经有DEFAULT约束的列。
∙[@futureonly=]'futureonly_flag'仅在将默认值绑定到用户定义的数据类型时才使用。
“futureonly_flag”的数据类型为varchar（15），默认值为NULL。
将此参数设置为futureonly时，它会防止现有的属于此数据类型的列继承新的默认值。
当将默认值绑定到列时不会使用此参数。
如果“futureonly_flag”为NULL，那么新默认值将绑定到用户定义数据类型的任一列，条件是此数据类型当前无默认值或者使用用户定义数据类型的现有默认值。
例如，上面将con3默认对象绑定到test数据库的table8表的c1列上的操作过程可以使用下面的SQL语句来完成：
USEtest
GO
EXECsp_bindefault'con3','table8.c1'
GO
3．重命名默认对象
和其他的数据库对象一样，也可以重命名默认对象。
重命名默认对象也是使用sp_rename存储过程来完成的，请参考前面的介绍。
使用企业管理器也可以重命名默认对象，只需选择要重命名的默认对象，然后右击鼠标，执行“重命名”命令，输入新的默认对象名称后按Enter键即可。
4．解除默认对象的绑定
解除绑定可以使用sp_unbindefault存储过程，其语法格式如下：
sp_unbindefault[@objname=]'object_name'
[,[@futureonly=]'futureonly_flag']
各参数含义如下：
∙[@objname=]'object_name'是要解除默认值绑定的表和列或者用户定义数据类型的名称。
当为用户定义数据类型解除默认值绑定时，所有属于该数据类型并具有相同默认值的列也同时解除默认值绑定。
对属于该数据类型的列，如果其默认值直接绑定到列上，则该列不受影响。
∙[@futureonly=]'futureonly_flag'仅用于解除用户定义数据类型默认值的绑定。
当参数“futureonly_flag”为futureonly时，现有的属于该数据类型的列不会失去指定默认值。
提示：
由于一列或者用户定义数据类型只能同时绑定一个默认对象，所以解除绑定时，不需要再指定默认对象的名称。
另外，如果要查看默认值的文本，可以以该默认对象的名称为参数执行存储过程sp_helptext。
例如，下面的SQL语句解除test数据库中table8表c1列上的默认值绑定：
USEtest
GO
EXECsp_unbindefault'table8.c1'
GO
提示信息如下：
已从表的列上解除了默认值的绑定。
5．删除默认对象
在删除默认对象之前，首先要确认默认对象已经解除绑定。
删除默认对象使用DROPDEFAULT语句，其语法格式如下：
DROPDEFAULT{default}[,…n]
其中，“default”是现有默认值的名称。
若要查看现有默认值的列表，可以执行sp_help存储过程。
n是表示可以指定多个默认值的占位符。
例如，下面的SQL语句判断是否存在con3默认对象，如果存在，则删除该默认对象：
USEtest
GO
IFEXISTS（SELECTnameFROMsysobjects
WHEREname='con3'ANDtype='D'）
DROPDEFAULTcon3
GO
注意：
DROPDEFAULT语句不适用于DEFAULT约束。
如果要除去DEFAULT约束，则应该使用ALTERTABLE语句。
9.3规则
规则限制了可以存储在表中或者用户定义数据类型的值，它可以使用多种方式来完成对数据值的检验，可以使用函数返回验证信息，也可以使用关键字BETWEEN、LIKE和IN完成对输入数据的检查。
当将规则绑定到列或者用户定义数据类型时，规则将指定可以插入到列中的可接受的值。
规则是作为一个独立的数据库对象存在，表中每列或者每个用户定义数据类型只能和一个规则绑定。
注意：
规则是一个向后兼容的功能，用于执行一些与CHECK约束相同的功能。
CHECK约束是用来限制列值的首选标准方法。
CHECK约束比规则更简明，一个列只能应用一个规则，但是却可以应用多个CHECK约束。
CHECK约束作为CREATETABLE语句的一部分进行指定，而规则以单独的对象创建，然后绑定到列上。
和默认对象类似，规则只有绑定到列或者用户定义数据类型上才能起作用。
如果要删除规则，则应确定规则已经解除绑定。
9.3.1创建规则
创建规则使用CREATERULE语句，其语法格式如下：
CREATERULErule
AScondition_expression
各参数含义如下：
∙rule是新规则的名称。
规则名称必须符合标识符规则。
可以选择是否指定规则所有者的名称。
∙condition_expression是定义规则的条件。
规则可以是WHERE子句中任何有效的表达式，并且可以包含诸如算术运算符、关系运算符和谓词（如IN、LIKE、BETWEEN）之类的元素。
规则不能引用列或其他数据库对象。
可以包含不引用数据库对象的内置函数。
“condition_expression”包含一个变量。
每个局部变量的前面都有一个＠符号。
该表达式引用通过UPDATE或INSERT语句输入的值。
在创建规则时，可以使用任何名称或符号表示值，但第一个字符必须是＠符号。
例如，下面的SQL语句创建一个名为rule1的规则，限定输入的值必须在0到10之间：
USEtest
GO
CREATERULErule1AS@c1BETWEEN0and10
GO
而下面创建的规则rule2将输入到该规则所绑定的列中的实际值限制为只能是该规则中列出的值：
USEtest
GO
CREATERULErul
升级会员 