第9章 数据库完整性概要

第9章数据库完整性
关键词：
完整性约束默认值规则绑定规则
学习要求：
本章主要阐述了SQL Server 2000提供的数据完整性机制。

并且全面地、系统地介绍了SQL Server 2000提供的各种约束；设置默认值的方法；创建、解除和删除规则的方法。

重点分析了SQL Server 2000提供的数据完整性机制。

学习和掌握本章，是对SQL Server 2000数据库的灵活运用。

数据库的完整性:是指数据的正确性、有效性和相容性。

说明：完整性是为了防止数据库中存在不符合语义的数据，防止错误信息的输入和输出。

数据库完整性约束条件：加在数据库数据之上的语义约束条件。

完整性检查：在DBMS中检查数据是否满足完整性条件的机制。

DBMS的完整性控制机制应具有三个方面的功能：
（1）定义功能：提供定义完整性约束条件的机制。

（2）检查功能：检查用户发出的操作请求是否违背了完整性约束条件。

（3）防范功能：如果发现用户的操作请求使数据违背了完整性约束条件，采取一定的动作来保证数据的完整性。

9.1 约束
引言
约束分类：
● 列级约束：只能应用在一列上。

● 表级约束：可以应用在一个表中的多列上。

注意：数据完整性约束条件是在创建表结构的时候指定。

例：CREATE TABLE book (
bid int NOT NULL，――列级约束
bname char(8) NOT NULL，――列级约束
authorid char(10)
)
9.1.1 PRIMARY KEY约束
PRIMARY KEY约束也称为主键约束。

主键能够惟一地确定表中的每一条记录，主键不能取空值。

主键约束可以保证实体的完整性。

PRIMARY KEY约束可以在下面情况下使用：
● 作为表定义的一部分在创建表时创建。

● 添加到尚没有PRIMARY KEY约束的表中(一个表只能有一个PRIMARY KEY约束)。

● 如果己有PRIMARY KEY约束，则可对其进行修改或删除。

● 主键约束格式：PRIMARY KEY (<列名表>)
例：CREATE TABLE department /*部门表*/
( dno int PRIMARY KEY，/*部门号，为主键*/
dname char(20)， /*部门名*/
)
注意：当约束用于表级时，应按下列形式设置：
CONSTRAINT constraint_name
各种约束形式
constraint_name：约束名。

例：CREATE TABLE sc (
sno char(8)，
cno char(4)，
grade int，
CONSTRAINT pk_sc primary key(sno,cno)
)
9.1.2 FOREIGN KEY 约束
FOREIGN KEY约束也称为外键约束。

主要用来维护两个表之间数据的一致性，实现表之间的参照完整性。

１、外键--如果一个关系中的属性或属性组并非该关系的关键字，但它们是另外一个关系的关键字，则称其为该关系的外关键字。

course表的tno是一个外键。

２、参照完整性规则：外键属性上的值或者取空值；或者等于它所参照的表中某个元组的主键值。

上例中：course表的tno取值：
①空值(未给该课程指定教师)
②非空值(必须是teacher表中某个元组的tno值)。

３、FOREIGN KEY约束可以在下面情况下使用：
● 作为表定义的一部分在创建表时创建。

● 如果FOREIGN KEY约束与另一个表(或同一表)已有的PRIMARY KEY约束或UNIQUE约束相关联，则可向现有表添加FOREIGN KEY约束。

一个表可以有多个FOREIGN KEY约束。

(例如：在score(sno，cno，degree)表中有两个FOREIGN KEY约束，一个是sno，参照student表的主键sno；另一个是cno，参照course表的主键cno)。

● 对己有的FOREIGN KEY约束进行修改或删除。

４、外键约束格式：
FOREIGN KEY [(<列名表>)]
REFERENCE <目标表>[(<列名表>)]
[ON DELETE <NO ACTION | CASCADE>]
[ON UPDATE <NO ACTION | CASCADE>]
５、删除主键所在表要考虑的问题：
（1）NO ACTION: 对外键所在的参照表无影响。

（2）CASCADE(级联)：删除(修改)主键所在被参照表元组时，同时删除(修改)参照表中对应的元组。

例：CREATE TABLE authors (
authorid int NOT NULL PRIMARY KEY，
authorname char(20)，
address char(30)
)
CREATE TABLE book (
bid int NOT NULL PRIMARY KEY，
bname char(8) NOT NULL，
authorid int FOREIGN KEY REFERENCES authors(authorid)
)
例：
USE test
GO
CREATE 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) /*地址*/
)
GO
9.1.3 UNIQUE 约束
UNIQUE 约束也称为惟一约束。

惟一约束用于指定一个或多个列的组合值具有惟一性，以防止在列中输入重复的值。

应用场合：每个表中只能有一个主键，因此当表中已经有一个主键时，如果还要保证其他的标识符惟一时，就可以使用惟一性约束。

例：CREATE TABLE book (
bid int NOT NULL PRIMARY KEY，
bname char(8) UNIQUE，
authorid char(10)
)
USE test
CREATE TABLE table5
( cl int UNIQUE，
c2 int
)
INSERT table5 VALUES(1，100)
执行上列程序后如果再插入一行：
INSERT table5 VALUES(1，200)
会出现什么问题？
会出现如下的错误：
服务器: 消息 2627，级别 14，状态 2，行 1
违反了 UNIQUE KEY 约束’UQ__table5__4BAC3F29’。

不能在对象’table5’中插入重复键。

语句已终止。

9.1.4 CHECK 约束
CHECK 约束也称为检查约束。

检查约束对输入列或者整个表中的值设置检查条件，以限制输入值。

检查约束格式：CHECK (<条件表达式>)
例：CREATE TABLE student (
sid int NOT NULL PRIMARY KEY，
sname char(8) UNIQUE，
sex char(2)，
CONSTRAINT chk_sex CHECK (sex in (‘男’，’女’))
)
9.2 默认值
引言
如果在插入行时没有指定列的值，则默认值指定列中所使用的值。

默认值可以是任何取值为常量的对象。

9.2.1 在创建表时指定默认值
１、使用企业管理器
在使用企业管理器创建表时，可以在输入字段名称后，设定该字段的默认值，例如：将T2表中的“性别”字段的默认值设置为“男”，如下图9-1所示。

图9-1设计表“T2”
２、使用Transact-SQL语句
例：USE test
CREATE TABLE table8
( c1 int，
c2 datetime DEFAULT(getdate())-- 默认值为系统日期
)
然后执行如下语句插入一行数据并显示记录：
USE test
INSERT table8(c1) VALUES(1)
SELECT * FROM table8
执行结果如下：
c1 c2
-------- --------------------------
1 2006-12-01 08:47:06.413
例：使用ALTER TABLE语句给表的字段加上默认值。

USE test
DROP TABLE table8 /*删除table8表*/
CREATE TABLE table8 /*重建没有默认值的表table8*/
(
c1 int，
c2 datetime
)
ALTER TABLE table8 /*通过ALTER命令给c2字段加上默认值*/ ADD CONSTRAINT con1 DEFAULT getdate() FOR c2
INSERT table8(c1) VALUES(1) /*插入一个记录*/
SELECT * FROM table8 /*显示记录*/
其中con1表示DEFAULT约束的名字。

执行结果与上例一样。

9.2.2 使用默认对象
默认对象是单独存储的，删除表的时候，DEFAULT约束会自动删除，但是默认对象不会被删除。

另外，创建默认对象后，需要将其绑定到某列或者用户自定义的数据类型上。

１、创建默认对象
（1）使用企业管理器
下面在test数据库中创建一个默认对象。

操作步骤如下：
1）打开企业管理器，展开服务器组，并展开相应的服务器。

2）打开test数据库，选择“默认”选项，然后右击鼠标，执行“新建默认”命令。

3）在打开的“新建默认对象”对话框中输入默认对象的名称con2，默认值为“汉族”，如下图9-2所示。

图9-2默认属性1
4）单击“确定”按钮，即可创建名为con2的默认对象。

（2）使用CREATE DEFAULT语句
格式：CREATE DEFAULT default
AS constant_expression
各参数含义如下：
default：默认值的名称。

默认值名称必须符合标识符的规则。

可以选择是否指定默认值所有者名称。

constant_expression：只包含常量值的表达式(不能包含任何列或其他数据库对象的名称)。

可以使用任何常量、内置函数或数学表达式。

字符和日期常量用单引号(’)引起来；货币、整数和浮点常量不需要使用引号。

二进制数据必须以0x开头，货币数据必须以美元符号($)开头。

默认值必须与列数据类型兼容。

例：USE test
GO
CREATE DEFAULT con3 AS 10 /*默认值名称为con3、默认值设为10*/
GO
２、绑定默认对象
默认对象创建后，必须绑定到某列或用户定义的数据类型上才能使用。

（1）使用企业管理器
使用企业管理器绑定一个默认对象的操作步骤如下：
1）打开企业管理器，展开服务器组，并展开相应的服务器。

2）打开“数据库”文件夹，选择test数据库。

3）单击“默认”文件夹，然后在右侧详细信息窗格中选择要绑定的默认对象，这里选择con3默认对象。

右击鼠标，执行“属性”命令。

4）在打开的“默认属性”对话框中，其中的“值”文本框可以设置默认值，“绑定UDT”按钮可将默认对象绑定到用户自定义数据类型，“绑定列”按钮可将默认对象绑定到列，如下图9-3所示。

图9-3默认属性2
5）单击“绑定列”按钮，打开“将默认值绑定到列”对话框，在“表”下拉列表框中选择列所在的表table8，然后在“未绑定的列”列表框中选择要绑定到的列，然后单击“添加”按钮，将其添加到“绑定列”列表框中。

这里将con3默认对象绑定到table8表的c1列上，如下图9-4所示。

图9-4将默认值绑定到列
6）单击“确定”按钮，即可将con3默认对象绑定到table8表的c1列上。

7）如果要将默认对象绑定到用户自定义数据类型上，则可以在“默认属性”对话框中，单击“绑定UDT”按钮，可打开“将默认值绑定到用户定义的数据类型”对话框，可以选择要绑定到的用户定义数据类型。

选择方法是选中“绑定”栏下的复选框，然后单击“确定”按钮即可，如下图9-5所示。

图9-5将默认值绑定到用户定义的数据类型
（2）使用sp_bindefault存储过程
格式：sp_bindefault [@defname = ] ’default’，
[@objname = ] ’object_name’
[，[@futureonly = ] ’futureonly_flag’]
主要参数含义如下：
‘default’：由CREATE DEFAULT语句创建的默认名称。

‘object_name’：要绑定默认值的表和列名称或用户定义的数据类型。

例：将con3默认对象绑定到test数据库的table8表的c1列上。

USE test
GO
EXEC sp_bindefault ’con3’，’table8.c1’
GO
３、重命名默认对象
使用企业管理器。

只需选择要重命名的默认对象，然后右击鼠标，执行“重命名”命令，输入新的默认对象名称后按Enter键即可。

使用sp_rename存储过程
例： sp_rename ’con2’，’con3’
４、解除默认对象的绑定
格式：sp_unbindefault [@objname = ] ’object_name’
[，@futureonly = ] ’futureonly_flag’]
例：解除test数据库中table8表c1列上的默认值绑定。

USE test
EXEC sp_unbindefault ’table8.c1’
５、删除默认对象
格式：DROP DEFAULT {default} [，…n]
例：判断是否存在con3默认对象，如果存在，则删除该默认对象。

IF EXISTS (SELECT name FROM sysobjects
WHERE name = ’con3’ AND type=’D’)
DROP DEFAULT con3
sysobjects是系统表。

在删除默认对象之前，首先要确认默认对象已经解除绑定。

9.3 规则
引言
规则限制了可以存储在表中或者用户定义数据类型的值。

一个列只能应用一个规则，规则以单独的对象创建，然后绑定到列上。

9.3.1 创建规则
１、使用CREATE RULE语句
格式：CREATE RULE rule
AS condition_expression
各参数含义如下：
rule：规则的名称。

condition_expression：定义规则的条件。

例1：创建一个名为rule1的规则，限定输入的值必须在0到10之间。

USE test
GO
CREATE RULE rule1AS@c1BETWEEN 0 and 10
GO
注意：规则的条件中要包含一个变量。

例2：创建的规则rule2将输入到该规则所绑定的列中的实际值限制为只能是该规则中列出的值。

USE test
GO
CREATE RULE rule2 AS @c1IN (’2’，’5’，’8’)
GO
例3：定义一个规则，使任意两个字符的后面跟一个连字符和任意多个字符(或没有字符)，并以1～6之间的整数结尾。

USE test
GO
CREATE RULE rule3 AS @value LIKE ’_ _\ _%[1-6]’ESCAPE ‘\’
GO
匹配串中的[]：指定范围或集合中的任意单个字符。

ESCAPE后跟的是转义字符。

２、使用企业管理器
通过企业管理器创建规则的操作步骤如下：
（1）打开企业管理器，展开服务器组，并展开相应的服务器。

（2）打开“数据库”文件夹，在“规则”选项上右击鼠标，执行“新建规则”命令，打开“规则属性”对话框。

（3）在“名称”文本框中输入规则的名称，然后在“文本”列表框中输入规则定义的描述后，单击“确定”按钮即可创建一个规则，如下图9-6所示。

图9-6规则属性界面9.3.2 绑定规则
１、使用企业管理器
与默认对象的绑定操作步骤相同。

２、使用sp_bindrule存储过程
格式：sp_bindrule [@rulename = ] ’rule’，
[@objname = ] ’object_name’
[，[@futureonly = ] ’futureon1y_flag’ ] 例：将rule1规则绑定到test数据库中table8表的c1列上。

USE test
GO
EXEC sp_bindrule ’rule1’，’table8.c1’
GO
9.3.3 解除和删除规则
１、解除规则的绑定
格式：sp_unbinrule [@objname = ] ’object_name‘[，[@futureonly = ] ’futureonly_flag’]
例：解除绑定到table8表的c1列上的规则。

EXEC sp_unbinrule ’table8.c1’
２、删除规则
格式：DROP RULE {rule} [，…n]
例：删除规则rule1。

USE test
GO
DROP RULE rule1
GO
重点分析
本章主要讨论了关于SQL Server 2000提供的数据完整性机制，对约束的分类要清楚，对设置默认值的方法、创建、解除和删除规则的方法要熟练掌握。

１、约束分类：
（1）列级约束：只能应用在一列上。

（2）表级约束：可以应用在一个表中的多列上。

２、本章主要介绍的约束有四种：主键约束(PRIMARY KEY)、外键约束(FOREIGN KEY)、惟一约束(UNIQUE)、检查约束(CHECK)，这些约束的基本操作和使用方法应牢牢掌握。

３、默认值创建有用企业管理器和Transact-SQL语句两种。

对于较熟练的人来说引用Transact-SQL 语句创建的方法，优点在于操作时间短，效率高；对默认值的使用主要还是在于灵活应用。

４、规则的创建和绑定有用企业管理器和Transact-SQL语句两种。

对于较熟练的人来说引用Transact-SQL语句创建的方法，优点在于操作时间短，效率高；对于解除规则的绑定和删除规则应进行比较，观察两者的异同之处。