第6章 索引与数据完整性6.1 索引 6.1 索引 索引是根据表中一列或若干列按照一定顺序建立的列值与记录行之间的对应关系表。在数据库系统中
建立索引主要有以下作用： 快速存取数据； 保证数据记录的唯一性； 实现表与表之间的参照完整性；在使用ORDER BY、GRO
UP BY子句进行数据检索时，利用索引可以减少排序和分组的时间。6.1.2 索引的创建 在SQL Server中可以通过对象资源
管理器和T .SQL语句创建索引。1.使用对象资源管理器创建 下面以表Student为例来介绍创建索引的方法，具体步骤如下： ①
在对象资源管理器中找到要创建索引的数据库db_stu中表Student，右键单击，在弹出菜单中选择【设计】，打开表Student，
选中要设置索引的列，右击，在弹出菜单中选择【索引/键】或在菜单栏的【表设计器】中选择【索引/键】，如图6.1所示。 图6.1 选择
【索引/键】 6.1.2 索引的创建 图6.2 创建索引 ②在弹出的【索引/键】对话框中，点击 按
钮，为该列添加索引，在右边的【常规】项中可以设置该索引的属性。如图6.2所示。③设置完成后，点击 按钮，完成索
引的创建。 6.1.2 索引的创建2.使用T .SQL语句创建 在T .SQL语句中，可以使用CREATE INDEX语句来创建
，语法格式如下： CREATE [UNIQUE][CLUSTERED|NONCLUSTERED] INDEX index_name
ON table_name(column_name [ASC|DESC][,…n]) 其中各参数的含义如下： UNI
QUE：为表或视图建立唯一索引。 CLUSTERED|NONCLUSTERED：用于建立聚类或非聚类索引。
index_name：索引名称 table_name：指定建立索引的表名。 column_name：指定
建立索引列名。6.1.2 索引的创建【例6.1】为KC 表的课程名列创建索引。/使用简单索引/USE XSCJIF EXIS
TS (SELECT name FROM sysindexes WHERE name = '' kc_name_ind '') D
ROP INDEX KC. kc_name_indGOCREATE INDEX kc_name_ind ON KC (课程名)
GO【例6.2】根据KC表的课程号列创建唯一聚集索引，因为指定了 CLUSTERED 子句，所以该索引将对磁盘上的数据进行物理排序
。 /使用唯一聚集索引/USE XSCJIF EXISTS (SELECT name FROM sysindexes WHER
E name = ''kc_id_ind'') DROP INDEX KC. kc_id_indGOCREATE UNIQUE C
LUSTERED INDEX kc_id_ind ON KC (课程号)GO6.1.2 索引的创建【例6.3】根据XS_KC
表的学号列和课程号列创建复合索引。/使用简单组合索引/USE XSCJIF EXISTS (SELECT name FROM
sysindexes WHERE name = ''xs_kc_ind'') DROP INDEX xs_kc_indG
O CREATE INDEX xs_kc_ind ON XS_KC (学号, 课程号)GO【例6.4】根据XS表中的总学
分列创建索引，例中使用了FILLFACTOR 子句。USE XSCJIF EXISTS (SELECT name FROM sys
indexes WHERE name = ''score_ind'') DROP INDEX score_indGOC
REATE NONCLUSTERED INDEX score_ind ON XS (总学分) WITH FILLF
ACTOR = 60GO6.1.2 索引的创建【例6.5】根据XS表中学号列创建唯一聚集索引。如果输入了重复的键，将忽略该INS
ERT或UPDATE语句。/使用 IGNORE_DUP_KEY /USE XSCJIF EXISTS (SELECT name
FROM sysindexes WHERE name = ''xs_ind'') DROP INDEX XS.xs_indC
REATE UNIQUE CLUSTERED INDEX xs_ind ON XS(学号) WITH IGNORE_DUP_
KEY创建索引有如下几点要说明：（1）在计算列上创建索引（2）在视图上创建索引（3）权限 （4）SET选项设置6.1.2 索引的
创建【例6.6】 创建一个视图，并为该视图创建索引。 USE XSCJGO--定义视图，如下例子中，由于使用了WITH
SCHEMABINDING子句，因此，定--义视图时，SELECT子句中表名必须为：所有者名.表名 的形式。CREATE VIE
W View1 WITH SCHEMABINDING AS SELECT 姓名,成绩 FROM dbo.XS,dbo.
XS_KC WHERE dbo.XS.学号=dbo. XS_KC.学号 GO--设置选项SET NUMERIC_ROUN
DABORT OFF SET ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL
,ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLS ON--在视图上创建索引CREATE UNIQU
E CLUSTERED INDEX Inx1 ON View1(姓名)GO6.1.3 索引的删除 1．使用对象资源管理器删除索引①
在对象资源管理器中找到要删除索引的表，在该表的折叠项中找到【索引】，展开【索引】，选中要删除的索引，点击右键，在弹出的菜单中选择【
删除】命令。 ②在弹出的【删除对象】对话框中，点击 按钮，就可以删除索引。 2．通过SQL命令删除索引在
T .SQL语句中，可以使用DROP INDEX语句删除数据库中相应表的索引，其语法格式 DROP INDEX ''table.in
dex | view.index'' [ ,...n ]参数含义：table | view：索引列所在的表或索引视图。Index：要
删除的索引名称。n：表示可以指定多个要删除的索引。功能：从当前数据库中删除一个或多个索引。 【例6.7】删除 XSCJ数据库中表X
S的一个索引名为 st_id_ind 的索引。USE XSCJ IF EXISTS (SELECT name FROM sysin
dexes WHERE name = ''st_id_ind'') DROP INDEX XS.au_id_indGO6.2.1
在表中定义及删除默认值约束1．使用T .SQL语句定义默认值约束语法格式：CREATE TABLE table_name
/指定表名/(column_name datatype NOT NULL | NULL [DEFAULT c
onstraint_expression] /缺省值约束表达式/[,…n]) /定义列名、数据类型、标识列、是否空值及
定义缺省值约束/【例6.8】在定义表时定义一个字段的默认值约束。USE XSCJ CREATE TABLE XS( 学号 ch
ar(6) NOT NULL, 姓名 char(8) NOT NULL, 专业名 char(10) NULL, 性别
bit NOT NULL, 出生时间 smalldatetime NOT NULL, 总学分 tinyint NULL,
备注 text NULL, 入学日期 datetime default getdate() /定义默认值约束
/ ) GO6.2.1 在表中定义及删除默认值约束【例6.9】在修改表时定义一个字段的默认值约束。USE XSCJALTER
TABLE XS ADD AddDate smalldatetime NULL CONSTRAI
NT AddDateDflt /默认值约束名/ DEFAULT getdate() WITH
VALUES 默认值约束的删除 默认值约束可以通过SQL
Server管理控制台中删除。(省略) 【例6.10】删除上例定义的默认值约束。 USE
XSCJ ALTER TABLE XS DROP CONSTRAINT AddDateDf
lt GO 6.2.1 在表中定义及删除默认值约束2.使用SQL Server管理控制台定义和绑定DEFAU
LT默认值约束(1)使用SQL Server管理控制台定义DEFAULT默认值约束具体操作步骤如下：①在对象资源管理器中，右键单击
要更改其小数位数的列所在的表，再单击“设计”。此时，将在表设计器中打开该表。②选择要为其指定默认值的列。③在“列属性”选项卡中，在
“默认值或绑定”属性中输入新的默认值。如下图6.3所示。 6.2.2 默认值对象的定义、使用与删除默认值对象的定义和使用可用T
.SQL语句来实现。 1.通过T .SQL语句定义和绑定DEFAULT默认值对象通过T .SQL语句定义DEFAULT默认值对象
的语法格式如下： CREATE DEFAULT [ schema_name .] default_name AS constant
_expression [ ; ] 通过系统存储过程绑定DEFAULT默认值对象，可以使用sp_bindefault语句来实现，语
法格式如下：sp_bindefault [ @defname = ] ''default'' , [ @objname = ] ''ob
ject_name'' [ , [ @futureonly = ] ''futureonly_flag'' ] 6.2.2 默认值对
象的定义、使用与删除2.默认值对象的删除默认值对象的删除可以在对象资源管理器中也可以用T .SQL语句删除，注意，要删除默认值对象
首先要解除它与表字段的绑定关系。 （1）利用sp_unbindefault解除绑定关系 其语法格式如下： sp_unbindefa
ult [ @objname = ] ''object_name'' [ , [ @futureonly = ] ''futureonl
y_flag'' ] （2）删除默认值对象可以用DROP语句删除，语法格式如下： DROP DEFAULT {default}[,…
n] 6.2.2 默认值对象的定义、使用与删除【例6.12】首先在XSCJ数据库中定义表book及名为 today 的默认值，
然后将其绑定到 book表的 hire date 列。--定义表CREATE TABLE book( book_id c
har(6) name varchar(20) NOT NULL, hire_date
datetime NOT NULL )GO--创建默认值对象CREATE DEFAULT today AS getdat
e()GO--绑定默认值对象USE bookEXEC sp_bindefault ''today'', ''book.[hire_ da
te]''6.2.2 默认值对象的定义、使用与删除【例6.11】对于如前所述的XSCJ数据库中XS表的总学分字段，可用如下程序段实
现初始值设置为0。CREATE DEFAULT zxf_default AS 0/ 定义语句应为第一条件语句或紧跟在GO之后/
USE XSCJEXEC sp_bindefault ‘zxf_default’ ,’XS.总学分’GO【例6.13】在XSCJ数
据库中定义名为 birthday_date 的数据类型，定义默认值对象day并将其绑定到用户定义的数据类型birthday_dat
e 中。 --定义数据类型birthday_date USE XSCJ EXEC sp_addtype birthday_
date, ''datetime'', ''NULL'' Go--定义day缺省值对象CREATE DEFAULT day AS
‘1960,00,00’-- 将缺省值对象day绑定到birthday_date数据类型USE XSCJEXEC sp_bind
efault '' day '', '' birthday_date ''6.3.1 数据完整性的分类 1．域完整性域完整性又称为列完整
性，指给定列输入的有效性。 【例6.15】定义表KC的同时定义学分的约束条件。USE XSCJCREATE TABLE KC( 课
程号 char(6) NOT NULL, 课程名 char(8) NOT NULL, 学分 tinyint CHECK (学
分 >=0 AND学分<=10) NUL / 通过CHECK子句
定义约束条件 / 备注 text NULL)GO2．实体完整性实体完整性又称为行的完整性，要求表中有一个主键，其值不能为空且能
唯一地标识对应的记录。 3．参照完整性参照完整性又称为引用完整性。 6.3.1 数据完整性的分类XS和 XS_KC表的对应关系如
表6.2、6.3所示。(1) 从表不能引用不存在的键值。 如果主表中的键值更改了，那么在整个数据库中，对从表中键值的所有引用要进
行一致的更改。 如果主表中没有关联的记录，则不能将记录添加到从表。 6.3.2 域完整性的实现 1．CHECK约束的定义与删
除对于TimeStamp和Identity两种类型字段不能定义CHECK约束。 (1)使用SQL Server管理控制台创建与删除
CHECK约束CHECK约束用于规范列的取值,列值必须满足约束的范围，范围外的数据无法输入。CHECK约束的定义和删除可以通过SQ
L Server管理控制台来实现.下面通过这两种方法来定义学生课程表学分(Ccredit)列的取值范围为0～20。具体操作步骤如下
：①在对象资源管理器中打开表Course的表设计器窗口，在列名中选择“Ccredit”列，单击鼠标右键，在弹出的快捷菜单中选择【C
HECK约束】，如图6.4所示。 6.3.2 域完整性的实现 图6.4
选择【CHCKE约束】命令 6.3.2 域完整性的实现 ②在打开的CHECK约束对话框中单击 按钮，
在“名称”中输入要定义约束的列的名字，在“表达式”中直接输入条件表达式。如图6.5所示，或点击 按钮，在弹出的约束表达式对话框
中输入条件表达式，然后点击 按钮，完成表达式的输入，如图6.6所示。最后单击
按钮，完成“Ccredit”列的CHECK约束定义。 图6.5 输入条件表达式和列名称 6.3.2 域完整性的实现
图6.6 约束表达式对话框 6.3.2 域完整性的实现 ③如果要删除约束，只需在CHECK约束对话框中选中要删除的约
束，单击 就可以删除约束。如图6.7所示。 图6.7 删除CHECK约束 6.3.2 域完整性的实
现2.利用SQL语句在创建表时创建CHECK约束语法格式：CREATE TABLE table_name
/指定表名/(column_name datatype NOT NULL | NULL [DEFAULT con
straint_expression] /缺省值约束表达式/[[check_name ] CHECK
?( logical_expression )] /CHECK约束表达式/ [,…n]) / 定义列名
、数据类型、标识列、是否空值及定义缺省值约束、CHECK 约束/【例6.16】在 XSCJ 数据库中创建表books，其中包含所
有的约束定义。USE XSCJCREATE TABLE books( book_id smallint,
/书号/ book_name varchar(50) NOT NULL
/书名/ book _desc varchar(50) NOT NULL /书的简要说明
/ DEFAULT ''New book - '', max_lvl tinyint NOT NULL
CHECK (max_lvl <= 250) /书允许的最高价CHECK约束/)6.3
.2 域完整性的实现 (3) 利用SQL语句在修改表时创建CHECK约束 语法格式：ALTER TABLE table_n
ame ADD CONSTRAINT check_name CHECK ? (logical_expression)【例
6.17】通过修改XSCJ数据库的XS_KC表，增加成绩字段的CHECK约束。USE XSCJALTER TABLE XS_KC
ADD CONSTRAINT cj_constraint CHECK ?(成绩>=0 and 成绩<
=100) (4) 利用SQL语句删除CHECK约束CHECK约束的删除可在企业管理器中通过界面删除。语法格式：ALTER TAB
LE table_name DROP CONSTRAINT check_name 【例6.18】删除XSCJ数据库中XS
_KC表成绩字段的CHECK约束。USE XSCJALTER TABLE XS_KC DROP CONSTR
AINT cj_constraint GO6.3.3 实体完整性的实现 PRIMARY KEY约束与UNIQUE约束的主要区别
如下：(1) 一个数据表只能创建一个PRIMARY KEY约束，但一个表中可根据需要对不同的列创建若干个UNIQUE约束；(2)
PRIMARY KEY字段的值不允许为NULL，而UNIQUE字段的值可取NULL；(3) 一般创建PRIMARY KEY约束时，
系统会自动产生索引，索引的缺省类型为簇索引。 PRIMARY KEY约束与UNIQUE约束的相同点在于：二者均不允许表中对应字段存
在重复值。 6.3.3 实体完整性的实现2．利用企业管理器创建和删除UNIQUE约束UNIQUE约束可以用以下两种方法定义：使用
SQL Server管理控制台定义和修改UNIQUE约束 具体步骤如下： ①在对象资源管理器中，右键单击要为其添加唯一约束的Cou
rse表，再单击“设计”,将在表设计器中打开该表。②在菜单栏上单击”表设计器”选项，在下拉菜单中单击“索引/键”，如图6.8所示，
或在将要定义或修改UNIQUE约束的列名上点右键在下拉菜单中单击“索引/键”，如图6.9所示，弹出“索引/键”对话框，如图6.10
所示。③在“索引/键”对话框中，单击“添加”。在网格中单击“类型”，再从属性右侧的下拉列表框中选择“唯一键”，而后单击“列”， 如
图6.11所示。④再点击属性右侧的按钮，从弹出的“索引列”对话框中选择将要添加约束的列名，如图6.12所示。当保存表时，即会在数据
库中创建该唯一约束。如果要改变该列的约束，可以在第三步中从下拉列表框中选择其他约束。 6.3.3 实体完整性的实现 图6.8
选择“表设计器” 图6.9 右击选择“索引/键” 6.3.3 实体完整性的实现图6.10 “索引/键”对话框 6.3.3 实
体完整性的实现图6.11 定义UNIQUE约束 6.3.3 实体完整性的实现图6.12 “索引列”对话框 6.3.3 实体完整
性的实现使用SQL Server管理控制台删除UNIQUE约束的具体步骤如下： ①在对象资源管理器中，右键单击具有唯一约束的表，然
后单击“设计”。此时，将在表设计器中打开该表。②在表设计器菜单上，单击“索引/键”。③在“索引/键”对话框中，从“选定的主键/唯一
键和索引”列表中选择唯一键。 ④单击“删除”。如下图所示。在保存表时，将从数据库中移除该约束。 6.3.3 实体完整性的实现
3．利用SQL命令创建及删除PRIMARY KEY约束或UNIQUE约束（1）创建表的同时创建PRIMARY KEY约束或UNIQ
UE约束语法格式：CREATE TABLE table_name
/指定表名/( column_name datatype
/定义字段/[ CONSTRAINT constraint_name ] /约束名
/?NOT NULL PRIMARY KEY ｜ UNIQUE
/定义约束类型/[CLUSTERED | NONCLUSTERED] /定义约束的索引类型/ [,?…n
]??????) /n表示可定义多个字段/(2) 通过修改表创建PRIMARY KEY约
束或UNIQUE约束创建PRIMARY KEY约束语法格式：ALTER TABLE table_name ADD??[ C
ONSTRAINT constraint_name ]??PRIMARY KEY CLUSTERED | NONCLUSTERED
( column [ ,...n ] ) 6.3.3 实体完整性的实现【例6.24】 对XSCJ数据库中XS表的学号字段创建P
RIMARY KEY约束，对身份证号码字段定义UNIQUE约束。USE XSCJCREATE TABLE XS(
学号 char(6) NOT NULLCONSTRAINT XH_PK PRIMARY KEY, 姓名 ch
ar(8) NOT NULL, 身份证号码 char(20) CONSTR
AINT SH_UK UNIQUE, 专业名 char(10) NULL, 性别 bit
NOT NULL, 出生时间 smalldatetime NOT NULL, 总学分 ti
nyint NULL, 备注 text NULL, 入学日期 datetime CONST
RAINT datedfltdefault getdate() /定义默认值约束/) 6.3.3
实体完整性的实现【例6.25】先在XSCJ数据库中创建表XS，然后通过修改表，对学号字段创建PRIMARY KEY约束，对身份
证号码字段定义UNIQUE约束。 USE XSCJCREATE TABLE XS( 学号 char(6) NOT NULL,
姓名 char(8) NOT NULL, 身份证号码 char(20) ,
专业名 char(10) NULL, 性别 bit NOT NULL,
出生时间 smalldatetime NOT NULL, 总学分 tinyint NULL,
备注 text NULL, 入学日期 datetime CONSTRAINT d
atedflt default getdate() /定义默认值约束/
) GOALTER TABLE XS ADD????CONSTRAINT XS_PK
PRIMARY KEY CLUSTERED (学号)GOALTER TABLE XS ADD???CO
NSTRAINT XS_UK ??UNIQUE NONCLUSTEREDGO6.3.3 实体完整性的实现（3）删除PRIMARY
KEY约束或UNIQUE约束语法格式：ALTER TABLE table_name DROP CONSTRAINT co
nstraint_name [,…n]【例6.26】 删除前面例中创建的PRIMARY KEY约束和UNIQUE约束。ALTER
TABLE XS DROP????CONSTRAINT XS_PK, XS_UKGO6.3.4 参照完整性的实现 1．使用SQ
L Server管理控制台定义表间的参照关系操作步骤如下：①在对象资源管理器中，右键单击将位于关系的外键方的表，再单击“设计”。
②在表设计器菜单上，单击“关系”， 如图6.14所示。 图6.14 选择【关系】命令 6.3.4 参照完整性的实现 ③
在“外键关系”对话框中，单击按钮。“选定的关系”列表中将显示关系以及系统提供的名称，格式为 FK_
_，其中 tablename 是外键表的名称。如下图6.15所示。 单击该按钮6.3.4 参照完整性的实
现 ④在“选定的关系”列表中单击该关系。单击右侧网格中的“表和列规范”，再单击该属性右侧的省略号 (…)。如下图6.16
所示。 6.3.4 参照完整性的实现 ⑤在“表和列”对话框中，从“主键”下拉列表中选择要位于关系主键方的表。如下图6.1
7所示。 6.3.4 参照完整性的实现 ⑥在下方的网格中，选择要分配给表的主键的列。在每列左侧的相临网格单元格中，选择外
键表的相应外键列。表设计器将为此关系提供一个建议名称。若要更改此名称，请编辑“关系名”文本框的内容。如下图6.18所示。 6.3.
4 参照完整性的实现⑦选择“确定”即可创建关系。 ⑧如果要修改关系，重新进行第5，6步的操作。 ⑨如果要删除关系，在“外键关系”
对话框中选中要删除的关系，而后单击 按钮就可以删除，如下图6.19所示。 6.3.4 参照完整性的实现2．利用S
QL命令定义表间的参照关系前面已介绍了创建主码（PRMARY KEY约束）及唯一码（UNIQUE约束）的方法，在此将介绍通过SQL
命令创建外码的方法。（1）创建表的同时定义外码约束语法格式： CREATE TABLE table_name
/指定表名/ (column_name datatype [FOREIGN KEY ] REFERENCES
ref_table ( ref_column ) [,?…n]??????)
/n表示可定义多个字段/（2）通过修改表定义外码约束语法格式：ALTER TABLE table_name
ADD???[?CONSTRAINT constraint_name]FOREIGN KEY???( column [ ,...n ] )REFERENCES ref_table ( ref_column [ ,...n ] )?????6.3.4 参照完整性的实现【例6.27】在XSCJ数据库中创建主表XS，XS.学号为主键，然后定义从表XS_KC，XS_KC.学号为外码。USE XSCJCREATE TABLE XS( 学号 char(6) NOT NULL CONSTRAINT XH_PK PRIMARY KEY, 姓名 char(8) NOT NULL, 专业名 char(10) NULL, 性别 bit NOT NULL, 出生时间 smalldatetime NOT NULL, 总学分 tinyint NULL, 备注 text NULL) GOCREATE TABLE XS_KC( 学号 char(6) NOT NULL FOREIGN KEY ? REFERENCES XS (学号), 课程号 char(3) NOT NULL, 成绩 smallint, 学分 smallint) GO6.3.4 参照完整性的实现【例6.28】假设XSCJ数据库中KC表为主表，KC.课程号字段已定义为主键。XS_KC表为从表，如下示例用于将XS_KC.课程号字段定义为外码。USE XSCJALTER TABLE XS_KC ADD???CONSTRAINT kc_foreign? FOREIGN KEY???(课程号)????????? REFERENCES KC(课程号 )???GO3．利用SQL命令删除表间的参照关系删除表间的参照关系，实际上删除从表的外码约束即可。语法格式与前面其它约束删除的格式同。【例6.29】删除上例对XS_KC.课程号字段定义的外码约束。USE XSCJ ALTER TABLE XS_KC DROP CONSTRAINT kc_foreignGO |
|
