SQL Server教程（第6版）(2008版) 课件 第5章 索引和数据完整性

第5章

索引和数据完整性——索

引01索引分类聚集索引非聚集索引索引分类1．聚集索引聚集索引将数据行的键值在表内排序并存储对应的数据记录，使得数据表的物理顺序与索引顺序一致。SQLServer是按B树（BTREE）方式组织聚集索引的，不论聚集索引里有表的哪个（或哪些）列，这些列都会按顺序保存在表中。由于存在这种排序，所以每个表只会有一个聚集索引。2．非聚集索引非聚集索引完全独立于数据行的结构，它也按B树方式组织。在非聚集索引内，从索引行指向数据行的指针称为行定位器。行定位器的结构取决于数据页的存储方式是堆集还是聚集。对于堆集，行定位器是指向行的指针。对于有聚集索引的表，行定位器是聚集索引键。只有在表上创建聚集索引时，表内的行才按特定顺序存储，这些行按聚集索引键顺序存储。如果一个表只有非聚集索引，则它的数据行将按无序的堆集方式存储。一个表中可有一个或多个非聚集索引。当在SQLServer上创建索引时，可指定是按升序还是按降序存储键。02索引的创建创建索引复合索引在计算列和视图上创建索引索引的创建使用CREATEINDEX语句可以为表创建索引。语法格式如下。CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]INDEX索引名 ON

表或视图名 (列名[ASC|DESC]) WITH索引选项说明：（1）UNIQUE：表示为表或视图创建唯一索引（即不允许存在索引值相同的两行）。例如，对于xsb表，根据学号创建唯一索引，即不允许有两个相同的学号出现。（2）CLUSTERED|NONCLUSTERED：指定创建聚集索引还是非聚集索引，前者表示创建聚集索引，后者表示创建非聚集索引。（3）索引名：索引名在表或视图中必须唯一，但在数据库中不必唯一；参数“表或视图名”用于指定包含索引列的表名或视图名，指定表名、视图名时可包含数据库和所属架构。（4）列名：指定建立索引的列，可以为索引指定多个列。指定索引列时，注意表或视图索引列的类型不能为ntext、text或image。（5）WITH索引选项：设定索引记录数据的有效性规则。索引的创建1.创建索引【例5.1】先为kcb1表的“课程名”列创建索引。再为“课程号”列创建唯一聚集索引，如果输入了重复课程号，将忽略该INSERT或UPDATE语句。1）在xscj创建一个kcb1表USExscjCREATETABLEkcb1(

课程号 char(3) NOTNULL,

课程名 char(16) NOTNULL,

学分 tinyint)2）为kcb1表创建索引CREATEINDEXkc_name ONkcb1(课程名)CREATEUNIQUECLUSTEREDINDEXkc_id ONkcb1(课程号) WITHIGNORE_DUP_KEY索引的创建3）插入记录INSERTINTOkcb1VALUES('401','就业指导',1)INSERTINTOkcb1VALUES('401','就业指导',1)显示信息如图。

索引的创建2.复合索引由多列构成的索引称为复合索引。【例5.2】根据cjb表的“学号”列和“课程号”列创建复合索引。语句如下：CREATEINDEXcjb_ind ONcjb(学号,课程号)说明：如果替换已经存在的cjb_ind索引需要加WITH子句。CREATEINDEXcjb_ind ONcjb(学号,课程号) WITH(DROP_EXISTING=ON)索引的创建3．在计算列和视图上创建索引（1）在计算列上创建索引。对于UNIQUE或PRIMARYKEY索引，只要满足索引条件，就可以包含计算列，但计算列必须具有确定性和精确性。若计算列中带有函数，则使用该函数时有相同的参数输入，输出的结果也一定相同时，该计算列是确定的。而对于有些函数，如GETDATE()，每次调用时都输出不同的结果，这时就不能在计算列上定义索引。（2）在视图上创建索引。可以在视图上定义索引。索引视图是一种在数据库中存储视图结果集的方法，可减少动态生成结果集的开销。索引视图还能自动反映出创建索引后对基本表数据所做的修改。索引的创建【例5.3】基于xsb表创建一个视图，并为该视图创建索引。语句如下：USExscjGOCREATEVIEWxsv1WITHSCHEMABINDING AS SELECT学号,姓名 FROMdbo.xsbGOCREATEUNIQUECLUSTEREDINDEXinx1 ONxsv1(学号)GO03重建索引重建索引索引使用一段时间后，可能需要重新生成原来的索引。语法格式如下。ALTERINDEX索引名|ALL ON表或视图名REBUILD【例5.4】先重建kcb1表上的kc_name索引，然后重建kcb1表上的所有索引。语句如下。ALTERINDEXkc_nameONkcb1REBUILDALTERINDEXALLONkcb1REBUILD04索引的删除索引的删除从当前数据库中删除一个或多个索引。语法格式如下。DROPINDEX索引名 ON

表或视图名说明：DROPINDEX语句可以一次删除一个或多个索引。【例5.5】删除cjb表cjb_ind索引。语句如下。IFEXISTS(SELECTnameFROMsysindexesWHEREname='cjb_ind') DROPINDEXcjb.cjb_ind第5章

索引和数据完整性——数据完整性01数据完整性分类实体完整性域完整性参照完整性数据完整性分类1．实体完整性实体完整性又称为行的完整性，要求表中有一个主键，其值不能为空且能唯一地标识对应的记录。通过索引、UNIQUE约束、PRIMARYKEY约束或IDENTITY属性可实现数据的实体完整性。例如，对于xscj数据库中的xsb表，“学号”作为主键，每一个学生的学号能唯一地标识该学生对应的行记录信息，那么在输入数据时，就不能有相同学号的行记录。通过对“学号”这一列建立主键约束可实现表xsb的实体完整性。2．域完整性域完整性又称为列完整性，指给定列输入的有效性。实现域完整性的方法有限制类型（通过数据类型）、格式（通过CHECK约束和规则）或可能的取值范围（通过CHECK约束、DEFALUT定义、NOTNULL定义和规则）等。数据完整性分类3．参照完整性在SQLServer中，参照完整性的实现是通过定义外键与主键之间或外键与唯一键之间的对应关系来实现的。参照完整性确保键值在所有表中一致。如果定义了两个表（例如，xsb表和cjb表）之间的参照完整性，则需要满足下列要求：（1）从表不能引用不存在的键值。例如，cjb表中行记录出现的学号必须是xsb表中已存在的学号。（2）如果主表中的键值被更改，那么对从表中该键值的所有引用要进行一致更改。例如，如果对xsb表中的某一学号进行修改，则cjb表中所有对应学号也要进行相应的修改。（3）如果主表中没有关联的记录，则不能将记录添加到从表中。例如，xsb表中没有学号的记录在cjb表中不能添加。（4）如果要删除主表中的某一记录，则应先删除从表中与该记录匹配的相关记录。例如，要删除xsb表中某学号的记录，必须先删除cjb表中所有该学号学生的记录。02实体完整性PRIMARYKEY约束和UNIQUE约束PRIMARYKEY约束和UNIQUE约束创建实体完整性1.PRIMARYKEY约束和UNIQUE约束如果要确保一个表中的非主键列不输入重复值，则应在该列上定义唯一约束（UNIQUE约束）。例如，对于xscj数据库中的xsb表，“学号”列是主键，在xsb表中增加一列“身份证号码”，可以定义一个UNIQUE约束来要求表中“身份证号码”列的取值是唯一的。PRIMARYKEY约束与UNIQUE约束的主要区别如下。（1）一个数据表只能创建一个PRIMARYKEY约束，但可根据需要对一个表中不同的列创建若干个UNIQUE约束。（2）PRIMARYKEY列的值不允许为NULL，而UNIQUE列的值可取NULL。（3）一般创建PRIMARYKEY约束时，系统会自动产生索引，该索引的默认类型为簇索引。创建UNIQUE约束时，系统会自动产生一个UNIQUE索引，默认类型为非簇索引。PRIMARYKEY约束与UNIQUE约束的相同点在于：二者均不允许表中对应列存在重复值。实体完整性2.PRIMARYKEY约束和UNIQUE约束创建利用T-SQL命令可以使用两种方式定义约束：作为列的约束或作为表的约束。可以在创建表或修改表时定义。1）在创建表的同时创建主键约束或唯一性约束其语法格式如下。CREATETABLE表名(

列定义 [CONSTRAINT约束名]PRIMARYKEY|UNIQUE, … [CONSTRAINT约束名]PRIMARYKEY|UNIQUE(列名,…))说明：（1）CONSTRAINT约束名：为约束命名，“约束名”为要指定约束的名称，在表所属的数据库中必须唯一。（2）PRIMARYKEY|UNIQUE：定义约束的关键字，PRIMARYKEY为主键，UNIQUE为唯一键。实体完整性【例5.6】在xscj数据库中创建xsb1表，并对学号列创建主键约束，对姓名列定义唯一性约束。语句如下：USExscjCREATETABLExsb1(

学号 char(6) NOTNULL CONSTRAINTxh_pkPRIMARYKEY,

姓名 char(8) NOTNULL CONSTRAINTxm_ukUNIQUE,

性别 bit NOTNULL DEFAULT1,

出生日期 date NOTNULL,

专业 varchar(12) NULL,

总学分 int NULL,

备注 varchar(500) NULL)说明：（1）对表建立PRIMARYKEY约束创建主键索引，以“pk”为后缀、后跟表名，系统自动按聚集索引方式组织主键索引。（2）当表中的主键为复合主键时，只能定义为一个表的约束。实体完整性2）通过修改表结构创建主键约束或唯一性约束可以修改表结构为表中已存在的列或新列定义约束。语法格式如下。ALTERTABLE表名 ADDCONSTRAINT约束名PRIMARYKEY|UNIQUE(列名,…)【例5.7】修改xsb1表结构，向其中添加一个身份证号列，并定义唯一性约束。语句如下：ALTERTABLExsb1ADD

身份证号char(20)CONSTRAINTsfz_uk

UNIQUENONCLUSTERED(身份证号)GO实体完整性3）删除主键约束或唯一性约束语法格式如下。ALTERTABLE表名 DROPCONSTRAINT约束名【例5.8】删除xsb1姓名列索引和身份证列索引。语句如下：ALTERTABLExsb1 DROP

CONSTRAINTsfz_uk,xm_uk03域完整性CHECK约束的创建规则对象的定义、使用与删除域完整性1．CHECK约束的创建1）在创建表时定义CHECK约束语法格式如下。[CONSTRAINT约束名]CHECK[NOTFORREPLICATION](逻辑表达式)关键字CHECK表示定义CHECK约束，如果指定NOTFORREPLICATION选项，则当复制代理执行插入、更新或删除操作时，将不会强制执行此约束。其后的“逻辑表达式”称为CHECK约束表达式，返回值为TRUE或FALSE，该表达式只能为标量表达式。【例5.9】创建一个表student，只考虑“学号”和“性别”两列，性别只能包含“男”或“女”。语句如下：USExscjCREATETABLEstudent(

学号char(6) NOTNULL,

性别char(2) NOTNULLCHECK(性别IN('男','女')))域完整性说明：（1）这里CHECK约束指定了性别列的约束，其列值只能为“男”或者“女”。（2）列的CHECK约束也可以写在表的约束中，但如果指定的一个CHECK约束中，要相互比较一个表的两个或多个列，那么该约束必须定义为表的约束。【例5.10】创建一个表student1，只考虑“学号”和“出生日期”两列，出生日期必须大于2004年1月1日，并命名CHECK约束。语句如下：CREATETABLEstudent1(

学号 char(6) NOTNULL,

出生日期 datetime NOTNULL,CONSTRAINTDF_student1_cjsjCHECK(出生日期>'2004-01-01'))域完整性2）修改表时创建CHECK约束在使用ALTERTABLE语句修改表时也能定义CHECK约束。修改表时创建CHECK约束的语法格式如下。ALTERTABLE表名 ADD[CONSTRAINT约束名]CHECK(逻辑表达式)【例5.11】修改student1表，增加出生日期列的CHECK约束。语句如下：ALTERTABLEstudent1 ADDCONSTRAINTDF_student1_cjsj1CHECK(出生日期<'2007-01-01')域完整性3）删除CHECK约束语法格式如下。ALTERTABLE表名 DROPCONSTRAINT约束名【例5.12】删除student1表出生日期列CHECK约束。语句如下。ALTERTABLEstudent1 DROPCONSTRAINTDF_student1_cjsj,DF_student1_cjsj1在对象资源管理器中，选择对应的表的约束项下约束名，右击，选择“删除”菜单，就可删除指定的约束。域完整性2．规则对象的定义、使用与删除规则是一组使用T-SQL语句组成的条件语句，它提供了另外一种在数据库中实现域完整性与用户定义完整性的方法。1）规则对象的定义语法格式如下。CREATERULE规则名 AS条件表达式说明：（1）规则名：定义的规则名，规则名必须符合标识符规则。（2）条件表达式：规则的条件表达式，该表达式可为WHERE子句中任何有效的表达式，但规则表达式中不能包含列或其他数据库对象，可以包含不引用数据库对象的内置函数。域完整性2）将规则对象绑定到用户定义数据类型或列将规则对象绑定到用户定义数据类型或列中，可以使用系统存储过程sp_bindrule。语法格式如下。sp_bindrule[@rulename=]'规则名', [@objname=]'对象名' [,[@futureonly=]'futureonly标志']说明：（1）规则名：为CREATERULE语句创建的规则名，要用单引号引起来。（2）对象名：为绑定到规则的用户定义数据类型或列。如果“对象名”采用“表名.列名”格式，则认为绑定到表的列，否则绑定到用户定义数据类型。（3）futureonly：仅当将规则绑定到用户定义的数据类型时才使用。域完整性【例5.13】创建两个规则，分别绑定到临时表kcb1的课程号和课程名列，用于限制输入范围。CREATERULEkc_rule1 AS@rangeLIKE'[1-5][0-9][0-9]'GOEXECsp_bindrule'kc_rule1','kcb1.课程号' /*执行存储过程使用EXEC命令*/GOCREATERULElist_rule1 AS@listIN('程序设计与语言','离散数学','数据结构')GOEXECsp_bindrule'list_rule1','kcb1.课程名'GO说明：EXEC前需要GO，也就是说，创建规则完成后才能绑定。程序如果正确执行将提示：“已将规则绑定到表的列”。域完整性在对象资源管理器kcb1表下右击“课程号”选项，在弹出的快捷菜单中选择“属性”命令，在kcb1表的“列属性-课程号”窗口中的“规则”选项中可以查看已经新建的规则。课程名规则同理显示。【例5.14】定义一个用户数据类型course_num，然后将前面定义的规则“kc_rule1”绑定到用户数据类型course_num上，最后创建表kcb2，其“课程号”的数据类型为course_num。语句如下：CREATETYPEcourse_num /*创建用户定义数据类型*/ FROMchar(3)NOTNULL GOEXECsp_bindrule'kc_rule1','course_num' /*将规则对象绑定到用户定义数据类型*/GOCREATETABLEkcb2(

课程号 course_num, /*将课程号定义为course_num类型*/

课程名 char(16)NOTNULL,

学分 tinyint)GO域完整性3）规则对象的删除在删除规则对象前，首先应使用系统存储过程sp_unbindrule解除被绑定对象与规则对象之间的绑定关系，其语法格式如下。sp_unbindrule[@objname=]'对象名' [,[@futureonly=]'futureonly标志']在解除列或自定义类型与规则对象之间的绑定关系后，即可删除规则对象。其语法格式如下。DROPRULE规则名域完整性【例5.15】解除规则kc_rule1与kcb1表课程号列和用户定义类型course_num的绑定关系，并删除规则对象kc_rule1。同理删除规则对象list_rule1。语句如下：EXECsp_unbindrule'kcb1.课程号'EXECsp_unbindrule'kcb1.课程名'EXECsp_unbindrule'course_num'GODROPRULEkc_rule1DROPRULElist_rule1说明：（1）只有在解除指定规则所有绑定关系后才能删除该规则。（2）当解除与用户定义数据类型course_num的关系后，系统自动解除使用course_num定义的列与规则的绑定关系。04参照完整性定义外键约束删除表间的参照关系参照完整性1.定义外键约束1）创建表的同时定义外键约束语法格式为。CREATETABLE表名(

列定义 [CONSTRAINT约束名]FOREIGNKEY(列名,…)参照定义, …… [CONSTRAINT约束名]FOREIGNKEY(列名,…)参照定义)说明：（1）和主键一样，外键也可以定义为列的约束或表的约束。如果定义为列的约束，则直接在列定义后面使用FOREIGNKEY关键字定义该列为外键。（2）REFERENCES后面是参照定义，其具体格式如下。REFERENCES参照表名(参照列名,…) [ONDELETE参照动作] [ONUPDATE参照动作]（3）定义外键时还可以指定参照动作：ONDELETE|ONUPDATE。可以为每个外键定义参照动作。参照完整性【例5.16】在xscj数据库中创建student2表，要求所有的学生学号都必须出现在student1表中。语句如下：USExscjALTERTABLEstudent1 ADDCONSTRAINTxh_pkPRIMARYKEY(学号)GOCREATETABLEstudent2(

学号