第6章 索引和数据完整性

6.1索引

6.1.1索引的分类

1.聚集索引

聚集索引将数据行的键值在表内排序并存储对应的数据记录，使得数据表的物理顺序与索引循序一致。（BTREE）每个表只会有一个聚集索引。

2.非聚集索引

非聚集索引完全独立与数据行的结构，也按B树方式组织。在非聚集索引内，从索引行指向数据行的指针称为行定位器。行定位器的结构取决于数据页的存储方式是堆集还是聚集。对于堆集，行定位器是指向行的指针。对于有聚集索引的表，行定位器是聚集索引键。

堆集：一个表没有创建索引，则数据行不按任何特定的顺序存储，这种结构成堆集。

6.1.2索引的创建：CREATE INDEX

语法结构：

CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX 索引名

ON 表或视图名

(列 [ASC | DESC])

WHERE 子句

……

说明(1)UNIQUE:表示为表或视图创建唯一索引(即不允许存在索引值相同的两行),对于视图创建的聚集索引必须是UNIQUE索引,如果对已存在的数据的表创建唯一索引,则必须保证索引项对应的值无重复值.

(2) CLUSTERED | NONCLUSTERED:指定创建聚集索引还是非聚集索引,前者表示聚集索引,一个表或视图只有一个聚集索引,并且必须先为表或视图创建唯一聚集索引,然后才能创建非聚索引.默认为NONCLUSTERED.

(3)索引名:索引名在表或者示图中必须唯一,但在数据库中不必唯一;参数”表或视图名”用于指定包含索引字段的表名或视图名,指定表名或视图名时可包含数据库和所属架构.

(4)列:指定建立索引的字段,可以为索引指定多个字段.指定索引字段时,注意表或视图索引字段的类型不能为ntext、text或image。通过指定多个索引字段可创建组合索引,但组合索引的所有字段必须取自同一表.

ASC表示索引文件按升序建立,DESC按降序建立,默认ASC.

(5)WHERE子句:通过指定索引中要包含哪些行来创建筛选索引.

示例1:为kcb表的”课程名”列创建索引,为”课程号”列创建唯一聚集索引.

CREATE INDEX kc_name

ON kcb(课程名)

CREATE UNIQUE CLUSTERED INDEX kc_id

ON kcb(课程号)

注意:在最初创建kcb时,定义了课程号为kcb的主键,所以kcb已经存在了一个聚集索引,要创建以上聚集索引,首先要将kcb的主键删除.

示例2:根据cjb表的”学号”列和课程列创建复合索引。

CREATE INDEX cjb_ind

ON cjb(学号,课程号)

WITH(DROP_EXSITING= ON)

注意:如果不存在名为cjb_ind的索引,可能会提示错误,需要将WITH子句去除.

示例3:根据xsb表中的”学号”列创建唯一聚集索引.如果输入了重复的键,将忽略该INSERT或UPDATE语句.

CREATE UNIQUE CLUSTERED INDEX xs_ind

ON xsb(学号)

WITH IGNORE_DUP_KEY

说明:如果表中已经存在一个聚集索引,则需要删除原来的索引,才能创建新索引.

6.1.3重建索引:ALTER INDEX

ALTER INDEX {索引名 |ALL }

ON 表或视图

{REBULLD

……

}

示例:重建kcb表上的所有索引:

ALTER INDEX ALL ON kcb REBULLD

示例:重建kcb表上的kcb_name索引

ALTER INDEX kc_name ON kcb REBULLD

6.1.4索引的删除:DROP INDEX

DROP INDEX 索引名

ON 表或视图名

……

DROP INDEX语句可以一次删除一个或多个索引.这个语句不适合删除通过定义PRIMARY KEY 或UNIQUE约束创建的索引,若要删除PRIMARY KEY 或UNIQUE约束创建的索引,必须通过删除约束实现.另外在系统表的索引上不能进行DROP INDEX操作.

示例:删除pxscj数据库中表kcb的一个索引名为kc_name的索引.

IF EXISTS (SELECT name FROM sysindexes WHERE name = ‘kc_name’)

DROP INDEX kcb.kc_name

创建索引以后，在系统表sysindexes中的name列会保存该索引的名称，通过搜索该名称可以判断该索引是否存在。

 

 

6.2数据完整性

6.2.1数据完整性分类

数据完整性一般包括实体完整性，域完整性和参照完整性

1. 实体完整性

实体完整性又称为行的完整性，要求表中有一个主键，其值不能为空且能唯一地标识对应的记录。通过索引、UNIQUE约束、PRIMARY KEY 约束或IDENTITY属性可实现数据实体的完整性。

1. 域完整性

域完整性又称为列完整性，指给定列输入的有效性。实现域完整性的方法有限制类型（通过数据格式）、格式（通过CHECK约束和规则）或可能的取值范围（通过CHECK约束、DEFALUT定义、NOT NULL定义和规则）等。

示例：建立表kcb2，同时定义学分的约束条件为1-6.

CREATE TABLE kcb2

  (

课程号 char(6) NOT NULL,

课程名 char(8) NOT NULL,

学分 tinyint CHECK (学分>=1 AND 学分<=6) NULL

)

1. 参照完整性

参照完整性又称为引用完整性.参照完整性保证主表中的数据与从表(被参照表)中的数据的一致性.在SQL2012中,参照完整性的实现是通过定义外键与主键之间或外键与唯一键之间的对应关系来实现.参照完整性确保键值在所有表中一致.

例如对于pxscj数据库中xsb表的每一个学号,在cjb表中都有相关的课程成绩记录,将xsb作为主表,学号字段定义为主键,cjb作为从表,表中的学号字段定义为外键,从而建立主表与从表之间的联系,实现参照完整性.

 

6.2.2实体完整性PRIMARY KEY/UNIQUE

通过命令方式创建及删除主键约束或唯一性约束

1. 在创建表的同时创建主键约束或唯一性约束

CREATE TABLE 表名

(

{<列定义><列约束>}[,…]

[<表约束>][,…]

)

其中,<列约束>的格式如下:

<列约束>::=            /*定义列的约束*/

[CONSTRAINT 约束名]

{

{PRIMARY KEY | UNIQUE} /*定义主键与UNIQUE键*/

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

[WITH(<索引选项>[,…])]

[ON {分区架构名(分区列名)| 文件组 | “default”}]

|[FOREIGN KEY] <参照定义>     /*定义外键*/

| CHECK [NOT FOR REPLICATION] (逻辑表达式) /*定义CHECK约束*/

}

<表约束>的格式如下:

<表约束>::=            /*定义表的约束*/

[CONSTRAINT 约束名]

{

{PRIMARY KEY | UNIQUE} /*定义主键与UNIQUE键*/

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

(列[ASC | DESC ] [,…]) /*定义表的约束时需要指定的列*/

[WITH(<索引选项>[,…])]

[ON {分区架构名(分区列名)| 文件组 | “default”}]

|FOREIGN KEY (列 [,…]) <参照定义>     /*定义外键*/

| CHECK [NOT FOR REPLICATION] (逻辑表达式) /*定义CHECK约束*/

}

说明:

(1)CONSTRAINT约束名:为约束命名,”约束名”为要指定的名称.如果没有给出,则系统自动创建一个名称.

(2) PRIMARY KEY | UNIQUE:定义约束的关键字,PRIMARY KEY 为主键,UNIQUE为唯一键.

(3) CLUSTERED | NONCLUSTERED,定义约束的索引类型

(4)FOREIGN KEY :用于定义一个外键

(5)CHECK:用于定义一个CHECK约束,外键和CHECK约束都可以作为列的约束或表的约束来定义.

(6)<表约束>:定义表的约束与定义列的约束基本相同,只不过在定义表的约束时需要指定约束的列.

示例:创建xsb3,并对学号字段创建主键约束,对姓名字段定义唯一性约束.

CREATE TABLE xsb3

(

学号 char(6) NOT NULL CONSTRAINT xh_pk PRIMARY KEY,

姓名 char(8) NOT NULL CONSTRAINT xm_uk UNIQUE,

性别 bit     NOT NULL DEFAULT,

出生时间 date NOT NULL,

专业 char(12) NULL,

总学分 int    NULL,

备注 varchar(500) NULL

)

说明:xh_pk PRIMARY KEY:学号字段主键约束,名称为xh_pk

 xm_uk UNIQUE:姓名字段定义唯一性约束,名称为xm_uk.

注意:当表中的主键为复合主键时,只能定义为一个表的约束.

2)通过修改表创建主键约束或唯一性约束

示例:修改xsb3表,向其中添加一个身份证号码的字段,对该字段定义唯一性约束,对表中出生时间字段定义唯一性约束.

ALTER TABLE xsb3

ADD 身份证号码 char(20)

CONSTRAINT sf_uk UNIQUE NONCLUSTERED (身份证号码)

GO

ALTER TABLE xsb3

ADD CONSTRAINT cssj_uk UNIQUE NONCLUSTERED(出生时间)

3)删除主键约束或唯一性约束

ALTER TABLE 表名

DROP CONSTRAINT 约束名 [,…]

示例:删除xsb3创建的主键约束和唯一性约束

ALTER TABLE xsb3

DROP CONSTRAINT xh_pk,xm_uk

GO

 

6.2.3域完整性CHECK/CREATE RULE

1.CHECK约束的定义与删除

CHECK(验证规则)约束实际上是字段输入内容的验证规则,表示一个字段的输入内容必须满足CHECK约束的条件,若不满足,则数据无法正常输入,对于timestamp类型字段和identity属性字段不能定义CHECK约束.

1)通过命令方式在创建表时创建CHECK约束

CHECK [NOT FOR REPLICATION] (逻辑表达式)

示例:创建一个表student,只考虑”学号”和”性别”两列,性别只能包含”男”或”女”

CREATE TABLE student

(

学号 char(6) NOT NULL,

性别 char(1) NOT NULL CHECK(性别 IN (‘男’,’女’))

)

2)修改表时创建CHECK约束

ALTER TABLE 表名

[ WITH {CHECK | NOCHECK}] ADD

[<列定义>]

[CONSTRAINT 约束名] CHECK (逻辑表达式)

说明:WITH 子句:指定表中的数据是否用新添加的或重新启用的FOREIGN KEY 或 CHECK约束进行验证.如果未指定,则默认为WITH CHECK,如果不想根据现有数据验证新的CHECK或FOREIGN KEY 约束,则使用 WITH NOCHECK.除极个别的情况外,建议不要这样操作.

CONSTRAINT关键字:为CHECK约束定义一个约束名.

示例:通过修改pxscj数据库中的cjb表,增加成绩字段的CHECK约束.

ALTER TABLE cjb

ADD CONSTRAINT cj_constraint CHECK (成绩>=0 AND 成绩<=100)

3)利用SQL语句删除CHECK约束

ALTER TABLE 表名

DROP CONSTRAINT 约束名

2.规则对象的定义使用和删除

规则是一组使用T-SQL语句组成的条件语句,规则提供了另外一种在数据库中实现域完整性与用户自定义完整性的方法.

1)规则对象的定义,语法格式:

CREATE RULE [架构名.]规则名

AS 条件表达式

说明：

(1)规则名:定义的新规则名,规则名必须符合标识符规则.

(2)条件表达式:规则的条件表达式,该表达式可为WHERE子句中任何有效的表达式,但规则表达式中不能包含列或其他数据库对象,可以包含不引用数据库对象的内置函数.在条件表达式中包含一个局部变量,每一个局部变量的前面都有一个”@”符号.使用UPDATE或INSERT语句修改或插入值时,该表达式用于对规则关联的列值进行约束.

创建规则时,一般使用局部变量表示UPDATE或INSERT语句输入的值.

A.创建的规则对先前已存在于数据库中的数据无效.

B.在单个批处理中,CREATE RULE不能与其他T-SQL语句组合使用.

C.规则表达式的类型必须与列的数据类型兼容,不能将规则绑定到text/iamge或timestamp列.要用单引号将字符和日期常量引起来,在十六进制常量前加0x.

D.对于用户定义数据类型,当在该类型的数据列中插入值或更新该类型的数据列时,绑定到该类型的规则才会激活.规则不检验变量,所以在向用户定义数据类型的变量赋值时,不能与列绑定的规则冲突.

E.如果列同时有默认值和规则与之关联,则默认值必须满足规则的定义,与规则冲突的默认值不能插入列.

2)将规则对象绑定到用户定义数据类型或列.

sp_bindrule [ @rulename = ] ‘规则名’,

[ @objname = ] ‘对象名’

[, [@futureonly = ] ‘futureonly标志’]

说明:

(1)规则名:为CREATE RULE语句创建的规则名,单引号引起来.

(2)对象名:为绑定到规则的列或用户定义的数据类型.如果”对象名”采用”表名.字段名”格式.则认为绑定到表的列,否则绑定到用户定义的数据类型.

(3)futureonly:仅当规则绑定到用户定义的数据类型时才使用.如果设置为”futureonly”,则用户定义数据类型的现有列不继承新规则;如果设置为NULL,则当被绑定的数据类型当前无规则时,新规则将绑定到用户定义数据类型的每一列,默认值为NULL.

示例:创建一个规则,并绑定到kcb的课程号列,用于限制课程号的输入范围.

CFREATE RULE kc_rule

AS @rang like ‘[1-5][0-9][0-9]’

GO

EXEC sp_bindrule ‘kc_rule’,’kcb.课程号’    /*执行存储过程使用EXEC命令*/

GO

3）规则对象的删除

sp_unbindrule [@objname = ] ‘对象名’

[,[@futureonly = ] ‘futureonly标志’]

DROP RULE { [架构名.]规则名 }[,…][;]

示例:解除规则kc_rule与列或用户定义类型的绑定关系,并删除规则对象kc_rule

EXEC sp_unbindrule ‘kcb.课程名’

EXEC sp_unbindrule ‘course_num’

GO

DROP RULE kc_rule

注意:后续版本中删除了规则对象的功能,相应的功能可以用CHECK约束实现.

 

6.2.4参照完整性: FOREIGN KEY

利用FOREIGN KEY 定义从表的外键,PRIMARY KEY或UNIQUE约束定义主表中主键或唯一键,可实现主表与从表之间的参照完成性.定义表间参照关系:先定义主表的主键,再对从表定义外键约束(根据查询的需要可先对从表的该列创建索引).

 

1.创建表的同时定义外键约束

CREATE TABLE 表名    /*指定表名*/

(

<列定义>

[CONSTRAINT 约束名]

[FOREIGN KEY] [(列 [,…] ) ] <参照定义>

)

说明:

和主键一样,外键也可以定义列的约束或表的约束.如果定义为列的约束,则直接在列定义后面使用FOREIGN KEY 关键字定义该字段为外键.如果定义为表的约束,则需要在FOREIGN KEY关键字后面指定由哪些字段名组成外键,”列”为字段名,可以是一个字段或多个字段的组合.

其中<参照定义>的具体格式如下:

<参照定义>::=

REFERENCES 参照表名 [(参照列 [,…])]

[ON DELETE { NO ACTION| CASCADE| SET NULL| SET DEFAULT } ]

[ON UPDATE { NO ACTION| CASCADE| SET NULL| SET DEFAULT } ]

[NOT FOR REPLICATION ]

(1)FOREIGN KEY 定义的外键应与参数”参照表名”指定的主表中的主键或唯一键对应,主表中主键或唯一字段由参数”参照列”指定.主键的数据类型和外键的数据类型必须相同.

(2)定义外键时还可以指定参照动作:ON DELETE |ON UPDATE.可以为每个外键定义参数动作.一个参照动作包含两部分.a.第一部分指定这个参照动作用哪条语句.这里有两条相关语句,即DELETE和UPDATE语句,即对表进行删除和更新操作.b.第二部分指定采取哪个动作.可能采取的动作是NO ACTION;CASCADE;SET NULL和SET DEFAULT

NO ACTION:意味着不采取动作,就是如果有一个相关的外键值在子表中,那么删除或更新父表中主要键值的企图不被允许.

CASCADE:从父表删除或更新行时自动删除或更新子表中匹配的行.

SET NULL:当从父表删除或更新行时,设置子表中与之对应的外键列为NULL.如果外键列没有指定NOT NULL限定词,这就是合法的.

SET DEFAULT:作用和SET NULL一样,只不过SET DEFAULT是指定子表中的外键列为默认值.如果没有指定动作,两个参照动作就会默认使用 NO ACTION.

(3)如果指定NOT FOR REPLICATION选项,则当复制代理执行插入,更新或删除操作时,将不会强制执行此约束.

示例1:创建stu表,要求stu表中所有的学生学号都必须出现在xsb表中,假设已经使用”学号”列作为主键创建了xsb表.

CREATE TABLE stu

(

学号 char(6) NOT NULL FOREIGN KEY (学号) REFERENCES xsb (学号),

姓名 char(8) NOT NULL,

出生时间 datetimeNULL

)

示例2:创建point表,要求表中所有的学号,课程号组合都必须出现在cjb表中.

CREATE TABLE point

(

学号 char(6) NOT NULL,

课程号 char(3) NOT NULL,

成绩 int NULL,

CONSTRAINT FK_point FOREIGN KEY (学号,课程号) REFERENCES cjb(学号,课程号)

ON DELETE NO ACTION

)

2.通过修改表定义外键约束

使用ALTER TABLE 语句的ADD子句也可以定义外键约束,语法格式与定义其他约束类似

示例:假设kcb表为主表,kcb的课程号字段已定义为主键.cjb表为从表,如下示例用于将cjb表的课程号字段定义为外键.

ALTER TABLE cjb

ADD CONSTRAINT kc_foreign

FOREIGN KEY (课程号)

REFERENCES kcb(课程号)

3.删除表间参照关系

删除表间参照关系,实际上删除从表的外键约束即可.语法格式与其他约束删除的格式类似.

示例 :删除上个示例中对cjb表的课程号字段定义的外键约束.

ALTER TABLE cjb

DROP CONSTRAINT kc_foreign