SQL Server 实用教程（第2版）第06章

第6章索引与数据完整性6.1索引6.1索引索引是根据表中一列或若干列按照一定顺序建立的列值与记录行之间的对应关系表。在数据库系统中建立索引主要有以下作用：快速存取数据；保证数据记录的唯一性；实现表与表之间的参照完整性；在使用ORDERBY、GROUPBY子句进行数据检索时，利用索引可以减少排序和分组的时间。6.1.2索引的创建在XSCJ数据库中，经常要对XS、KC、XS_KC三个表查询和更新，为了提高查询和更新速度，可以考虑对三个表建立如下索引：对于XS表，按学号建立主键索引（PRIMARYKEY约束），索引组织方式为聚集索引；对于XS表，按姓名建立唯一索引（UNIQUE约束），索引组织方式为非聚集索引；对于KC表，按课程号建立主键索引或者唯一索引，索引组织方式为聚集索引；对于XS_KC表，按学号+课程号建立唯一索引，索引组织方式为聚集索引。在SQLServer2000中，可利用企业管理器创建上述索引，也可以利用SQL命令，通过查询分析器建立索引。下面以XS表中按学号建立索引为例，介绍索引的创建方法。通过企业管理器建立索引第1步进入企业管理器，打开XSCJ数据库；6.1.2索引的创建第2步选择选择XS表的图标，右击，出现如图6.1所示的快捷菜单，选择“设计表”菜单项，出现如图6.2所示XS的表设计器界面；6.1.2索引的创建第3步在XS表设计器界面上右击，出现如图6.3所示的快捷菜单，快捷菜单中选择“索引/键”菜单项，出现如图6.4所示XS的属性界面；6.1.2索引的创建第4步在“索引／键”属性页选择“新建”命令，然后，确定新索引的名称（用系统缺省的名或重新取名）；在“列名”下选择要创建索引的列，在此选择“学号”这一列，最多可选择16列。6.1.2索引的创建2．利用SQL命令建立索引语法格式：CREATE[UNIQUE] /*是否为唯一索引*/[CLUSTERED|NONCLUSTERED] /*索引的组织方式*/INDEXindex_name /*索引名称*/

ON{table|view}(column[ASC|DESC][,...n])/*索引定义的依据*/[WITH<index_option>[,...n]] /*索引选项*/[ONfilegroup]/*指定索引文件所在的文件组*/

<index_option>::=

{PAD_INDEX|

FILLFACTOR=

fillfactor

|

IGNORE_DUP_KEY|

DROP_EXISTING|

STATISTICS_NORECOMPUTE|

SORT_IN_TEMPDB

}6.1.2索引的创建【例6.1】为KC表的课程名列创建索引。/*使用简单索引*/USEXSCJIFEXISTS(SELECTnameFROMsysindexesWHEREname='kc_name_ind')DROPINDEXKC.kc_name_indGOCREATEINDEXkc_name_indONKC(课程名)GO【例6.2】根据KC表的课程号列创建唯一聚集索引，因为指定了CLUSTERED子句，所以该索引将对磁盘上的数据进行物理排序。

/*使用唯一聚集索引*/USEXSCJIFEXISTS(SELECTnameFROMsysindexesWHEREname='kc_id_ind')DROPINDEXKC.kc_id_indGOCREATEUNIQUECLUSTEREDINDEXkc_id_indONKC(课程号)GO6.1.2索引的创建【例6.3】根据XS_KC表的学号列和课程号列创建复合索引。/*使用简单组合索引*/USEXSCJIFEXISTS(SELECTnameFROMsysindexesWHEREname='xs_kc_ind')DROPINDEXxs_kc_indGOCREATEINDEXxs_kc_indONXS_KC(学号,课程号)GO【例6.4】根据XS表中的总学分列创建索引，例中使用了FILLFACTOR子句。USEXSCJIFEXISTS(SELECTnameFROMsysindexesWHEREname='score_ind')DROPINDEXscore_indGOCREATENONCLUSTEREDINDEXscore_indONXS(总学分)WITHFILLFACTOR=60GO6.1.2索引的创建【例6.5】根据XS表中学号列创建唯一聚集索引。如果输入了重复的键，将忽略该INSERT或UPDATE语句。/*使用IGNORE_DUP_KEY*/USEXSCJIFEXISTS(SELECTnameFROMsysindexesWHEREname='xs_ind') DROPINDEXXS.xs_indCREATEUNIQUECLUSTEREDINDEXxs_indONXS(学号) WITHIGNORE_DUP_KEY创建索引有如下几点要说明：（1）在计算列上创建索引（2）在视图上创建索引（3）权限 （4）SET选项设置6.1.2索引的创建【例6.6】创建一个视图，并为该视图创建索引。USEXSCJGO--定义视图，如下例子中，由于使用了WITHSCHEMABINDING子句，因此，定--义视图时，SELECT子句中表名必须为：所有者名.表名的形式。CREATEVIEWView1WITHSCHEMABINDINGASSELECT姓名,成绩

FROMdbo.XS,dbo.XS_KCWHEREdbo.XS.学号=dbo.XS_KC.学号

GO--设置选项SETNUMERIC_ROUNDABORTOFFSETANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL,ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLSON--在视图上创建索引CREATEUNIQUECLUSTEREDINDEXInx1ONView1(姓名)GO6.1.3索引的删除1．通过企业管理器界面删除索引第1步进入企业管理器，并与指定的数据库相连接；第2步为要删除索引的表打开表设计器，在表设计器中右击，然后从快捷菜单中选择“属性”命令；第3步选择“索引/键”选项卡，从“选定的索引”列表中选择要删除的索引，选择“删除”按钮。2．通过SQL命令删除索引语法格式：DROPINDEX'table.index|view.index'[,...n]参数含义：table|view：索引列所在的表或索引视图。Index：要删除的索引名称。n：表示可以指定多个要删除的索引。功能：从当前数据库中删除一个或多个索引。【例6.7】删除XSCJ数据库中表XS的一个索引名为st_id_ind

的索引。USEXSCJIFEXISTS(SELECTnameFROMsysindexesWHEREname='st_id_ind') DROPINDEXXS.au_id_indGO6.2.1在表中定义及删除默认值约束1．默认值约束的定义语法格式：CREATETABLEtable_name /*指定表名*/(column_name

datatypeNOTNULL|NULL[DEFAULTconstraint_expression] /*缺省值约束表达式*/[,…n])/*定义列名、数据类型、标识列、是否空值及定义缺省值约束*/【例6.8】在定义表时定义一个字段的默认值约束。USEXSCJ CREATETABLEXS( 学号char(6)NOTNULL,

姓名char(8)NOTNULL,

专业名char(10)NULL,

性别bitNOTNULL,

出生时间smalldatetimeNOTNULL,

总学分tinyintNULL,

备注textNULL,

入学日期datetimedefaultgetdate() /*定义默认值约束*/ )GO6.2.1在表中定义及删除默认值约束【例6.9】在修改表时定义一个字段的默认值约束。USEXSCJALTERTABLEXSADDAddDate

smalldatetimeNULLCONSTRAINTAddDateDflt /*默认值约束名*/DEFAULTgetdate()WITHVALUES2．默认值约束的删除默认值约束可在企业管理器中删除。【例6.10】删除上例定义的默认值约束。USEXSCJALTERTABLEXSDROPCONSTRAINTAddDateDfltGO6.2.2默认值对象的定义、使用与删除1.通过企业管理器定义和绑定DEFAULT默认值对象(1)通过企业管理器定义DEFAULT默认值对象第１步进入企业管理器，并连接到XSCJ数据库；展开XSCJ数据库文件夹，选择“默认”图标右击，出现快捷菜单，选择“新建默认”，进入如图6.6所示的默认属性定义窗口；6.2.2默认值对象的定义、使用与删除第2步在窗口中输入默认值对象名“zxf_default”及默认值“0”，选择“确定”后即定义了一个默认值对象。

(2)通过企业管理器将一个字段绑定到一个DEFAULT默认值对象上面已定义了一个“zxf_default”默认值对象，下面将XS表中的总分字段绑定到该对象上，步骤如下：第1步在企业管理器中打开XS表设计器；第2步将光标移至总学分字段，从默认值对象下拉表中选择其对应的默认值对象即可，如图6.7所示。6.2.2默认值对象的定义、使用与删除2.通过SQL语句定义和绑定DEFAULT默认值对象通过SQL命令定义DEFAULT默认值对象语法格式：CREATEDEFAULTdefaultASconstant_expression

(2)通过系统存储过程绑定DEFAULT默认值对象语法格式：sp_bindefault[@defname=]'default',[@objname=]'object_name'[,[@futureonly=]'futureonly_flag'](3)应用举例6.2.2默认值对象的定义、使用与删除【例6.12】首先在XSCJ数据库中定义表book及名为today的默认值，然后将其绑定到book表的hiredate列。--定义表CREATETABLEbook(

book_idchar(6) namevarchar(20)NOTNULL,

hire_date

datetimeNOTNULL)GO--创建默认值对象CREATEDEFAULTtodayASgetdate()GO--绑定默认值对象USEbookEXECsp_bindefault'today','book.[hire_date]'6.2.2默认值对象的定义、使用与删除【例6.11】对于如前所述的XSCJ数据库中XS表的总学分字段，可用如下程序段实现初始值设置为0。CREATEDEFAULTzxf_defaultAS0/*定义语句应为第一条件语句或紧跟在GO之后*/USEXSCJEXECsp_bindefault‘zxf_default’,’XS.总学分’GO【例6.13】在XSCJ数据库中定义名为birthday_date

的数据类型，定义默认值对象day并将其绑定到用户定义的数据类型birthday_date

中。

--定义数据类型birthday_date USEXSCJ EXECsp_addtype

birthday_date,'datetime','NULL'Go--定义day缺省值对象CREATEDEFAULTdayAS‘1960,00,00’--将缺省值对象day绑定到birthday_date数据类型USEXSCJEXECsp_bindefault'day','birthday_date'6.2.2默认值对象的定义、使用与删除3）默认值对象的删除（1）利用sp_unbindefault解除绑定关系语法格式：sp_unbindefault[@objname=]'object_name'[,[@futureonly=]'futureonly_flag']（2）删除默认值对象语法格式：DROPDEFAULT{default}[,...n]【例6.14】解除默认值对象day与XSCJ库中用户定义类型birthday_date的绑定关系，然后删除名为day的默认值对象。USEXSCJIFEXISTS(SELECTnameFROMsysobjects

WHEREname='day'ANDtype='D')BEGIN EXECsp_unbindefault'birthday_date' DROPDEFAULTdayENDGO6.3.1数据完整性的分类1．域完整性域完整性又称为列完整性，指给定列输入的有效性。【例6.15】定义表KC的同时定义学分的约束条件。USEXSCJCREATETABLEKC( 课程号char(6)NOTNULL,

课程名char(8)NOTNULL,

学分tinyintCHECK(学分>=0AND学分<=10)NUL/*通过CHECK子句定义约束条件*/

备注textNULL)GO2．实体完整性实体完整性又称为行的完整性，要求表中有一个主键，其值不能为空且能唯一地标识对应的记录。3．参照完整性参照完整性又称为引用完整性。6.3.1数据完整性的分类XS和XS_KC表的对应关系如表6.2、6.3所示。(1)从表不能引用不存在的键值。如果主表中的键值更改了，那么在整个数据库中，对从表中键值的所有引用要进行一致的更改。如果主表中没有关联的记录，则不能将记录添加到从表。6.3.2域完整性的实现1．CHECK约束的定义与删除对于TimeStamp和Identity两种类型字段不能定义CHECK约束。

(1)通过企业管理器创建与删除CHECK约束第1步在XS_KC表设计器界面右击鼠标，出现如图6.8所示的快捷菜单；6.3.2域完整性的实现第2步选择CHECK约束菜单项进入如图6.9所示的CHECK选项卡属性窗口；第3步选择“新建”，进入CHECK约束的输入窗口，输入约束表达式“成绩>=0and成绩<=100”，如图6.10所示，选择“关闭”。6.3.2域完整性的实现(2)利用SQL语句在创建表时创建CHECK约束语法格式：CREATETABLEtable_name /*指定表名*/(column_name

datatypeNOTNULL|NULL[DEFAULTconstraint_expression] /*缺省值约束表达式*/[[check_name]CHECK

(logical_expression)]/*CHECK约束表达式*/ [,…n])/*定义列名、数据类型、标识列、是否空值及定义缺省值约束、CHECK约束*/【例6.16】在XSCJ数据库中创建表books，其中包含所有的约束定义。USEXSCJCREATETABLEbooks(

book_id

smallint, /*书号*/

book_namevarchar(50)NOTNULL/*书名*/book_descvarchar(50)NOTNULL /*书的简要说明*/ DEFAULT'Newbook-',

max_lvl

tinyintNOTNULLCHECK(max_lvl<=250) /*书允许的最高价CHECK约束*/)6.3.2域完整性的实现

(3)利用SQL语句在修改表时创建CHECK约束

语法格式：ALTERTABLEtable_name

ADDCONSTRAINTcheck_nameCHECK

(logical_expression)【例6.17】通过修改XSCJ数据库的XS_KC表，增加成绩字段的CHECK约束。USEXSCJALTERTABLEXS_KCADDCONSTRAINTcj_constraintCHECK

(成绩>=0and成绩<=100)(4)利用SQL语句删除CHECK约束CHECK约束的删除可在企业管理器中通过界面删除。语法格式：ALTERTABLEtable_name

DROPCONSTRAINTcheck_name

【例6.18】删除XSCJ数据库中XS_KC表成绩字段的CHECK约束。USEXSCJALTERTABLEXS_KCDROPCONSTRAINTcj_constraint

GO6.3.2域完整性的实现2.规则对象的定义、使用与删除规则对象的使用方法与缺省值对象的使用步骤类似：(1)定义规则对象；(2)将规则对象绑定到列或用户自定义类型；规则对象的定义可利用企业管理器，通过界面定义，也可以利用查询分析器，通过执行SQL语句定义。1）利用企业管理器定义规则对象并绑定到自定义类型或列第1步进入企业管理器，展开控制台目录树中的XSCJ数据库图标，选中“规则”图标右击，出现如图6.11所示的快捷菜单；6.3.2域完整性的实现第2步选择“新建规则”，进入规则创建窗口，如图6.12所示，在窗口中输入规则名及规则表达式，本例的规则名为kc_rule，规则表达式为：@numlike‘[1-5][0-9][0-9]’，其中@num为任意输入的局部变量，但必须以@开头。第3步选择“确定”按钮，则新建规则创建成功，出现如图6.13的图标。6.3.2域完整性的实现第4步在规则图标下选中刚定义的规则对象kc_rule对象双击，出现如图6.13所示的规则属性界面；第5步在规则属性窗口中有两个按钮：一个是“绑定UDT”，即绑定到用户自定义类型；另一个按钮是“绑定列”，对于本例选中“绑定列”，进入如图6.14所示将规则对象绑定到列的界面；6.3.2域完整性的实现第6步选择表[dbo].[kc]，在未绑定规则字段中选择“课程号”字段并添加到“绑定列”，如图所示，然后选择确定。2）利用SQL命令定义规则对象并绑定到自定义类型或列（1）规则对象的定义语法格式：CREATERULErule

AScondition_expression6.3.2域完整性的实现另外有如下几点需说明：创建的规则对先前已存在于数据库中的数据无效。单个批处理中，CREATERULE语句不能与其它T-SQL语句组合使用。规则表达式的类型必须与列的数据类型兼容，不能将规则绑定到text、image或timestamp

列。对于用户定义数据类型，当在该类型的数据列中插入值，或更新该类型的数据列时，绑定到该类型的规则才会激活。如果列同时有默认值和规则与之关联，则默认值必须满足规则的定义，与规则冲突的默认值不能插入列。（2）将规则对象绑定到自定义类型或列语法格式：sp_bindrule[@rulename=]'rule',[@objname=]'object_name'[,[@futureonly=]'futureonly_flag']（3）应用举例6.3.2域完整性的实现【例6.19】如下程序创建一个规则，并绑定到表KC的课程号列，用于限制课程号的输入范围。

USEXSCJCREATERULEkc_ruleAS@rangelike‘[1-5][0-9][0-9]’GOUSEXSCJEXECsp_bindrule'kc_rule','KC.课程号'GO【例6.20】创建一个规则，用以限制输入到该规则所绑定的列中的值只能是该规则中列出的值。USEXSCJCREATERULElist_ruleAS@listIN('C语言','离散数学','微机原理')GOGOUSEXSCJEXECsp_bindrule'list_rule','KC.课程名'GO6.3.2域完整性的实现

【例6.21】如下程序定义一个用户数据类型course_num，然后将前面定义的规则“kc_rule”绑定到用户数据类型course_num上，最后定义表KC，其课程号的数据类型为course_num。USEXSCJ/*打开数据库*/EXECsp_addtype‘course_num’,’char(3)’,’notnull’/*调用存储过程*/EXECsp_bindrule'kc_rule','course_num'GOCREATETABLEKC1(课程号course_num /*将学号定义为student_num类型*/

课程名char(16)NOTNULL,

开课学期tinyint,

学时tinyint,

学分tinyint)GO（4）规则对象的删除sp_unbindrule[@objname=]'object_name'[,[@futureonly=]'futureonly_flag']6.3.2域完整性的实现【例6.22】解除课程号列与kc_rule之间的绑定关系，并删除规则对象kc_rule。USEXSCJIFEXISTS(SELECTnameFROMsysobjectsWHEREname='kc_rule'ANDtype='R')BEGINEXECsp_unbindrule'KC.课程号'DROPRULEkc_ruleENDGO【例6.23】解除自定义类型course_num与kc_rule之间的绑定关系，并删除规则对象kc_rule。USEXSCJIFEXISTS(SELECTnameFROMsysobjectsWHEREname='kc_rule'ANDtype='R')BEGINEXECsp_unbindrule'course_num'DROPRULEkc_ruleENDGO6.3.3实体完整性的实现PRIMARYKEY约束与UNIQUE约束的主要区别如下：(1)一个数据表只能创建一个PRIMARYKEY约束，但一个表中可根据需要对不同的列创建若干个UNIQUE约束；(2)PRIMARYKEY字段的值不允许为NULL，而UNIQUE字段的值可取NULL；(3)一般创建PRIMARYKEY约束时，系统会自动产生索引，索引的缺省类型为簇索引。PRIMARYKEY约束与UNIQUE约束的相同点在于：二者均不允许表中对应字段存在重复值。利用企业管理器创建和删除PRIMARYKEY约束（1）利用企业管理器创建PRIMARYKEY约束如果要对XS表按学号建立PRIMARYKEY约束，应如何操作呢？可以按下列步骤进行：6.3.3实体完整性的实现第1步选择XS表图标，右击，打开XS表的表设计器，进入如图6.16所示的表设计器界面。第2步选中“学号”对应的这一行，选择主键图标，这样在“学号”对应的这一行前面，将出现一主键图标。6.3.3实体完整性的实现（2）利用企业管理器删除PRIMARYKEY约束如果要删除对表XS中对学号字段建立的PRIMARYKEY约束，按如下步骤进行：第1步选择XS表图标，右击，打开XS表设计器，进入如图6.17所示的表设计器界面。第2步选中XS表设计器中主键对应的行，选择工具栏的“设置主键图标”，则取消了原来定义的主键。2．利用企业管理器创建和删除UNIQUE约束（1）利用企业管理器创建UNIQUE约束如果要对XS表中的“身份证号码”列创建UNIQUE约束，以保证该列取值的唯一性，可按以下步骤进行：第1步选择XS表图标，右击，打开XS表设计器，在表设计器中右击，出现如图6.18所示的快捷菜单。第2步选择“属性”菜单项，出现属性界面，在此界面中选择“索引/键”选项卡，如图6.19所示。6.3.3实体完整性的实现第3步在图6.19的属性界面中选择“新建”，输入新建索引的名字或使用系统缺省名，在“列名”下拉表中选择“身份证号码”，并设置索引顺序，将“创建UNIQUE约束”单选按钮设置为选中状态，如图6.20所示。6.3.3实体完整性的实现第3步选择“关闭”按钮。（2）利用企业管理器删除UNIQUE约束进入如图6.20所示的属性窗口，在“选定的索引”下拉框中选择要删除的UNIQUE约束的索引名，再点击“删除”按钮即删除了指定的UNIQUE约束。6.3.3实体完整性的实现3．利用SQL命令创建及删除PRIMARYKEY约束或UNIQUE约束（1）创建表的同时创建PRIMARYKEY约束或UNIQUE约束语法格式：CREATETABLEtable_name /*指定表名*/(column_name

datatype /*定义字段*/[CONSTRAINTconstraint_name] /*约束名*/

NOTNULLPRIMARYKEY｜UNIQUE /*定义约束类型*/[CLUSTERED|NONCLUSTERED] /*定义约束的索引类型*/ [,

…n]

) /*n表示可定义多个字段*/(2)通过修改表创建PRIMARYKEY约束或UNIQUE约束创建PRIMARYKEY约束语法格式：ALTERTABLEtable_name

ADD

[CONSTRAINTconstraint_name]

PRIMARYKEYCLUSTERED|NONCLUSTERED(column[,...n])6.3.3实体完整性的实现【例6.24】对XSCJ数据库中XS表的学号字段创建PRIMARYKEY约束，对身份证号码字段定义UNIQUE约束。USEXSCJCREATETABLEXS(学号char(6)NOTNULLCONSTRAINTXH_PKPRIMARYKEY,

姓名char(8)NOTNULL,

身份证号码char(20)CONSTRAINTSH_UKUNIQUE,

专业名char(10)NULL,

性别bitNOTNULL,

出生时间smalldatetimeNOTNULL,

总学分tinyintNULL,

备注textNULL,

入学日期datetimeCONSTRAINTdatedfltdefaultgetdate() /*定义默认值约束*/)6.3.3实体完整性的实现【例6.25】先在XSCJ数据库中创建表XS，然后通过修改表，对学号字段创建PRIMARYKEY约束，对身份证号码字段定义UNIQUE约束。

USEXSCJCREATETABLEXS( 学号char(6)NOTNULL,

姓名char(8)NOTNULL,

身份证号码char(20),

专业名char(10)NULL,

性别bitNOTNULL,

出生时间smalldatetimeNOTNULL,

总学分tinyintNULL,

备注textNULL,

入学日期datetimeCONSTRAINTdatedfltdefaultgetdate() /*定义默认值约束*/)GOALTERTABLEXSADD

CONSTRAINTXS_PKPRIMARYKEYCLUSTERED(学号)GOALTERTABLEXSADD

CONSTRAINTXS_UK

UNIQUENONCLUSTEREDGO6.3.3实体完整性的实现（3）删除PRIMARYKEY约束或UNIQUE约束语法格式：ALTERTABLEtable_name

DROPCONSTRAINTconstraint_name[,…n]【例6.26】删除前面例中创建的PRIMARYKEY约束和UNIQUE约束。ALTERTABLEXSDROP

CONSTRAINTXS_PK,XS_UKGO6.3.4参照完整性的实现1．利用企业管理器定义表间的参照关系例如：要建立XS表与XS_KC表之间的参照完整性，操作步骤如下：第1步按照前面所介绍的方法定义主表的主码（或唯一码）。在此，定义XS表中的学号字段为主码。第2步选择企业管理器目录树中XSCJ数据库目录下的“关系图”图标右击，出现如图6.21所示的快捷菜单。第3步选择菜单项“新建数据库关系图”，进入如图6.22所示的欢迎界面。6.3.4参照完整性的实现第4步选择欢迎界面中的“下一步”按钮，进入如图6.23所示“创建关系图向导”的界面，从可用表中选择要添加到关系图中的表，本例中选择了XS表和XS_KC表。第5步点击“下一步”按钮，并按提示进行后继操作，进入如图6.24所示的关系图界面。6.3.4参照完整性的实现第6步在关系图上，将鼠标指向主表的主键并拖动到从表。对于本例：将XS表中的学号字段拖动到从表XS_KC，出现如图6.25所示的关系设置界面。第7步退出图6.26的关系图界