SQLServer_第7章 索引与数据完整性

第7章 索引与数据完整性

7.1 索引

在数据库建立索引主要有以下作用：

（1）       快速存取数据；

（2）       保证数据记录的唯一性；

（3）       实现表与表之间的参照完整性；

（4）       在使用order by和group by子句进行数据检索时，利用索引可以提高排序和分组的效率。

7.1.1 索引的分类

1．聚集索引

聚集索引将数据行的键值在表内排序并存储对应的数据记录，使得数据表物理顺序与索引顺序一致。SqlServer2000是按B树组织聚集索引的，B树的叶节点存放数据页信息。对查找记录很有效。

一个表只能有一个聚集索引。

2．非聚集索引

非聚集索引是完全独立于数据行的结构，SqlServer2000是按B树组织聚集索引的，B树的叶节点存放非聚集索引的键值，并且每个键值项都有指针指向包含该键值的数据行。

如果在一个表中既要聚集索引又要非聚集索引，应该先创建聚集索引后创建非聚集索引。

7.1.2 索引的创建

1．通过企业管理器建立索引

2．通过SQL命令建立索引

语法格式：

Create [unique]  /*是否是唯一索引*/

[clustered  |  nonclustered]  /*索引的组织方式*/

Index index_name   /*索引名称*/

On { table |view} (column [asc |desc],…n)   /*索引定义的依据*/

[with <index_option>[,…n]]              /*索引选项*/

[on filegroup]                          /*指定索引文件所在的文件组*/

<index_option>::=

{

       PAD_INDEX

       | FILLFACTOR =fillfactor

       | IGNORE_DUP_KEY

       | DROP_EXISTING

       | STATISTICS_NORECOMPUTE

       | SORT_IN_TEMPDB

}

例子：/*为kc表的课程名列创建索引*/

if exists(select name from sysindexes where name='kc_name_ind')

       drop index kc.kc_name_ind

go

create index kc_name_ind on kc(kc_name)

go 

例子：/*依据课程号为kc表创建唯一聚集索引*/

create unique clustered index kc_id_ind on kc(kc_name)

7.1.3 索引的删除

7.2 默认值约束及默认值对象

7.2.1 在表中定义默认值约束

（1）入学日期  datetime Constraint datedflt default getdate(),Constraint datedflt可以省略。

（2）在修改表时定义一个字段的默认值约束

例子：

Alter  table xs

Add adddate1 smalldatetime null

Constraint AddDateDflt1 default getdate() with values

其中with values为表中的现有数据添加默认值

（3）删除默认值约束

Drop constraint Constraint_name

7.2.2 默认值对象的定义使用和删除

使用步骤：新建 绑定

（1）       使用企业管理器

（2）       使用SQL命令

l           定义default对象的命令：create  default default_name as constant_expression

l           通过系统存储过程sp_bindefault将其绑定到列或用户定义数据类型。

例子：/*为xs表中的总学分字段的初始值设为0*/

create default stu_total_credit_default_value as 0

exec sp_bindefault 'stu_total_credit_default_value' ,'xs.stu_total_credit'

例子：

/*为book表定义today的默认值，并将其绑定到book表中的hire_date列*/

alter table book

add hire_date datetime

 

create default today as getdate()

exec sp_bindefault 'today' ,'book.hire_date'

删除步骤：

解除绑定 sp_unbindefault à删除  drop default  default_name

7.3 数据完整性

7.3.1 域完整性的实现

1．Check约束的定义与删除

注意：对于timestamp和identity列不能使用check约束

1） 通过企业管理器添加和删除

2）SQL命令

定义表时：Check_name check (logical expression)

修改表时：add constraint Check_name check (logical expression)

例子：

alter table xs_kc

add constraint cj_constraint check (score<=100 and score >=0)

删除： drop constraint Check_name

例子：alter table xs_kc

drop constraint cj_constraint

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

（1）定义规则对象    Create  rule rule_name as condition_expression

（2）绑定规则对象     sp_bindrule

例子：/*创建一个规则，用于限制课程号的输入范围*/

create rule kc_rule  as @range like '[1-5][0-9][0-9]'

exec sp_bindrule 'kc_rule','kc.kc_id'

例子：/*创建一个规则，用以限制输入的值只能是该规则中列出的值*/

create rule list_rule   as @list in('C语言','离散数学','微机原理')

（3）       解除绑定unbindrule

（4）       删除规则对象 drop rule  rule_name

7.3.2 实体完整性的实现

通过定义primary key约束来实现实体完整性。

注意：primary key约束和unique约束的主要区别在于：

（1）       数量：一个表中只能有一个primary key 约束，但一个表中可以根据需要对不同的列建立unique约束；

（2）       取值：primary key字段的值不允许取null值，而unique字段的值可以取null值；

（3）       组织方式：primary key默认的索引类型是簇索引，而unique的默认索引类型是非簇索引。

1．  使用企业管理器建立和删除primary key约束和unique约束

2．  使用SQL命令

1）  创建表的同时创建primary或unique约束

Create table table_name

(column_name datatype

[constraint constraint_name]

Not null

Primary key  | unique

[clustered |nonclustered]

,…..n)

例子：

create table xs2

(id char(6) not null constraint id_pk primary key

,name char(8) not null

,id_card_num char(20) constraint sh_uk unique

,study_time smalldatetime constraint datedflt default getdate()

)

2）  通过修改表创建primary或unique约束

Add constraint constraint_name primary key| unique clustered|nonclustered  column[,…n]

3）  删除primary或unique约束

Drop constraint constraint_name

7.3.3 参照完整性的实现

利用foreign key定义外码，primary key或unique约束定义不允许为null值的主码，可以实现主表与从表之间的参照完整性。

1．  使用企业管理器

2．  使用SQL命令

1）  定义表的同时定义：

Column_name datatype foreign key references ref_table(ref_column)

2）  修改表定义外码约束

Add constraint constraint_name

Foreign key (column[,…n[])

References ref_table(ref_column[,…n[])

3）  删除外码

Drop constraint constraint_name