数据库原理电子教案：第七章-索引与数据完整性.doc

第7章 索引与数据完整性
课题：索引与数据完整性
目的要求：
掌握数据库表索引的用法
掌握完整性约束的一般内容
掌握主键完整性、唯一性、外键、检查完整性和默认完整性约束的使用方法
重点难点：
主键完整性和检查完整性约束的设计
课外作业布置：
复习本节课内容，完成本章习题
索引与数据完整性
复习提问
1、说明系统数据类型与自定义数据类型的异同点。
2、说明就是@@VAR1与@VAR2的区别。
3、要声明变量V，并赋值，其语句如下，指出其错误：
declare V int(3)
V＝(select * from class_info )
4、指出下面函数的运行结果：
(1) left(‘school’,3) ‘social’+space(3)+’work’
(2) DATENAME(month,getdate()) CEILING (15.2)
5、删除自定义数据类型mytype和删除自定义函数myfun的SQL命令分别是什么？
7.1 索 引
7.1.1 索引的分类
索引是依赖于表建立的，它提供了数据库中排列表中数据的内部方法。一个表的存储由两部分组成：一部分用来存放表的数据页面，另一部分存放索引页面。SQL Server索引分为聚集、非聚集和惟一索引三种类型。
1．聚集索引是指数据行的物理存储顺序与索引顺序完全相同。每个表只能包括一个聚集索引，但是聚集索引可以包含多个列（最多16个），此时称为复合索引。只有当表包含聚集索引时，表内的数据行才按一定的排列顺序存储。
2．非聚集索引：具有完全独立于数据行的结构，索引不改变表中数据行的物理存储顺序。非聚集索引的最低行包含非聚集索引的键值，并且每个键值项都有指针指向包含该键值的数据行。数据行不按基于非聚集键的次序存储。
3．惟一索引：可以确保所有数据行中任意两行的被索引列不包括NULL在内的重复值。如果是复合惟一索引，则此索引可以确保索引列中每个组合都是惟一的。
7.1.2 索引的创建
1．使用CREATE INDEX创建聚集索引
（1）创建索引的完整语法
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ]
INDEX index_name
ON { table | view } ( column [ ASC | DESC ] [ ,…n ] ) }
（2）参数说明
① UNIQUE：为表或视图创建惟一索引。
② CLUSTERED：创建一个聚集索引。
③ NONCLUSTERED：创建一个非聚集索引。
④ index_name：是索引名。索引名必须遵循标识符规则。
⑤ table：包含要创建索引的列的表。可以选择指定数据库和表所有者。
⑥ view：要建立索引的视图的名称。
⑦ column：应用索引的列。
⑧ [ASC | DESC]：升序或降序，默认设置为ASC。索引字段可以有多个，排序方向可以不同。
【例7-1】对STUDENT数据库中的学生表按照学号降序建立一个聚集索引,索引名为idx_xh。
create clustered index idx_xh
on student_info(student_id desc)
2．使用企业管理器来创建非聚集索引
【例7-2】对STUDENT中的学生表，按照学生姓名升序建立一个非聚集索引idx_xm。
方法：选择表，右击，所有任务，管理索引
3．使用企业管理器中的向导建立索引
方法：工具－向导－数据库－创建索引向导。

7.1.2 索引的删除
1．使用DROP INDEX删除聚集索引
DROP INDEX命令可以删除一个或多个当前数据库中的索引，不能删除由CREATE TABLE或ALTER TABLE命令创建的PRIMARY KEY 或UNIQUE约束索引，也不能删除系统表中的索引。
删除索引的完整语法
DROP INDEX 表名.索引名 | 视图名.索引名
【例7-3】删除Student数据库中的学生表中的“idx_xm”索引。
命令：drop index student_info.idx_xm
2．使用企业管理器删除索引
（1）选择表；
（2）右击表，选择“所有任务→管理索引”，在出现如图所示的对话框中，选择“删除”按钮。选择“是”按钮，即完成索引的删除。
7.2 数据完整性
7.2.1 数据完整性的分类
数据完整性是指数据的精确性和可靠性，它是为了防止数据库中存在不符合语义规定的数据和因错误信息的输入、输出造成无效操作或错误信息而提出的，数据完整性分为三类实体完整性：
实体完整性；
参照完整性；
用户自定义完整性。
7.2.1 数据完整性的分类
1．实体完整性的实现
实体完整性将行定义为特定表的惟一实体。在关系数据库中一个关系对应现实世界的一个实体集，关系中的每一个元组对应一个实体。在关系中用主关键字来唯一标识一个实体，实体具有独立性，关系中的这种约束条件称为实体完整性。
2．参照完整性的实现
参照完整性是指两个表的主关键字和外关键字的数据应相互一致。参照完整性是建立在外关键字和主关键字之间或外关键字和惟一性关键字之间的关系上，作用是：
① 禁止在从表中插入包含主表中不存在的关键字的数据行；
② 禁止会导致从表中的相应值的主表中的外关键字值改变；
③ 禁止删除在从表中的有对应记录的主表记录。
3．用户自定义完整性
用户自定义完整性是指用户针对某一具体关系数据库的约束条件，反映某一具体应用所涉及的数据必须满足的语义要求。
①类型限制，通过数据类型来限制。
②条件限制，通过CHECK 约束和默认值来限制。
7.2.2 PRIMARY KEY约束
一个表只能有一个PRIMARY KEY约束，而且PRIMARY KEY约束中的列不能接受空值。由于PRIMARY KEY约束确保惟一数据，经常用来定义标识列。
当为表指定PRIMARY KEY约束时，SQL Server 2005通过为主键列创建惟一索引，强制数据的惟一性。当在查询中使用主键时，该索引还可用来对数据进行快速访问。如果PRIMARY KEY约束定义在不止一列上，则一列中的值可以重复，但PRIMARY KEY约束定义中的所有列的组合的值必须惟一。
在查询分析器中创建PRIMARY KEY约束的3种方法。
方法1：PRIMARY KEY 约束直接写在列名及其类型之后。
【例7-1】用SQL语句完成创建student_Info表。
CREATE TABLE student_Info
( student_ID INT not null PRIMARY KEY,
student_Name CHAR(10) NULL,
student_Sex CHAR(2) NOT NULL,
born_Date DATETIME,
class_NO INT,
tele_Number CHAR(10),

ru_Date DATETIME,
address VARCHAR(50),
comment VARCHAR(200)
)
方法2：按照语法在相应列名及类型后单独列出：
CONSTRAINT <约束名> PRIMARY KEY(列名)
方法3： 在CREATE TABLE语句列出基本表的所有列之后，再附加一个声明：
PRIMARY KEY(<列名1>[，<列名2>，…])
要求：建立一个工人信息表，包括编号、姓名、职务，要求主键为编号，姓名和编号组合起来具有唯一性。
Create table 职工信息表
(编号 char(10),
姓名 char(8)
职务 char(12),
primary key(编号,姓名)
)
【例7-4】 在创建课程基本表时，将课程号定义为主关键字，并指定PK_KCH为PRIMARY KEY约束名（列级约束）。
create table 课程
(课程号 char(6) constraint pk_kch primary key,
课称名 char(20),
学分 smallint,
周学时 smallint);
7.2.3 FOREIGN KEY约束
外键是用于建立和加强两个表数据之间的链接的一列或多列。
通过将保存表中主键值的一列或多列添加到另一个表中，可创建两个表之间的链接。这个列就成为第二个表的外键。
当创建或更改表时可通过定义FOREIGN KEY约束来创建外键。
当一行新的数据被加入到表格中，或对表格中已经存在的外部关键字上的数据进行修改时，新数据必须存在于另一基本表的主关键字上，或者为NULL。
1．语法
如果外部关键字只有一列，可在它的列名和类型后面直接用“REFERENCES”说明它参照了某个基本表的某些列（必须是主关键字），其语法格式为：
REFERENCES 表名 (列名)
l 在属性列表后面增加一个或几个外部关键字说明，格式为：
FOREIGN KEY(列名) REFERENCES 表名(列名)
l 在相应列名及其类型后单独列出，并指定约束名，格式为：
CONSTRAINT 约束名 FOREIGN KEY(列名) REFERENCES 表名(列名)
2．参数
① 第一种语法格式中的<表名> （<列名>）为参照某个表名和列名。
② 第二种语法格式中第一个“列名”是外部关键字；第二个“列名”是被参照基本表中的列名。
③ FOREIGN KEY…REFERENCES：是为列中的数据提供引用完整性的约束。FOREIGN KEY 约束要求列中的每个值在被引用表中对应的被引用列中都存在。FOREIGN KEY 约束只能引用被引用表中为PRIMARY KEY 或 UNIQUE 约束的列或被引用表中在 UNIQUE INDEX 内引用的列。
【例7-5】在创建考试表时，定义相应外部关键字。
create table 考试
(学号 char(6),
课程号 char(6),
成绩 smallint,
constraint pk_ks1 primary key (学号,课程号),
constraint pk_ks2 foreign key(课程号)
references 课程(课程号)
)
7.2.4 UNIQUE约束
可使用 UNIQUE 约束确保在非主键列中不输入重复值。
非主键的一列或列组合：一个表可以定义多个 UNIQUE 约束，而只能定义一个 PRIMARY KEY 约束。
允许空值的列：允许空值的列上可以定义 UNIQUE 约束，而不能定义 PRIMARY KEY 约束。FOREIGN KEY 约束也可引用 UNIQUE 约束。
1．语法
列级UNIQUE约束
CONSTRAINT 约束名 UNIQUE
表级UNIQUE约束
CONSTRAINT 约束名 UNIQUE(列名1[,列名2,…])
2．参数
UNIQUE：是通过惟一索引为给定的一列或多列提供
实体完整性的约束。一个表可以有多个 UNIQUE 约束。
【例7-6】在创建学生基本表时，创建一个表级UNIQUE约束。
create table 课程1
(课程号 char(6),
课程名 char(20),
学分 smallint,
周学时 smallint,
constraint un_one unique(课程名,学分)
)
7.2.5 CHECK约束
CHECK约束通过限制输入到列中的值来强制域的完整性。
这与FOREIGN KEY约束控制列中数值相似。区别在于它们如何判断哪些值有效，FOREIGN KEY约束从另一个表中获得有效数值列表，CHECK约束从逻辑表达式判断而非基于其他列的数据。
CHECK约束通过检查输入表列的数据的值来维护值域的完整性，它就像一个门卫，依次检查每一个要进入数据库的数据，只有符合条件的数据才允许通过。
对单独一列可使用多个CHECK约束，按约束创建的顺序对其取值。通过在表一级上创建CHECK约束，可以将该约束应用到多列上。
1．语法
CONSTRAINT 约束名 CHECK(检查条件)
2．参数
① CHECK：是通过限制可输入到一列或多列中的可能值强制域完整性的约束。
② 条件：是返回 TRUE 或 FALSE 的逻辑表达式。
【例7-7】在创建学生基本表时，创建一个CHECK约束，使学生的年龄在20岁到65岁之间。
create table 学生
(学号 char(6) primary key,
姓名 char (10),
性别 char (2),
年龄 smallint,
constraint ch_nl check (年龄>=20 and 年龄<=65),
班级号 char (5)
);
7.2.6 默认值约束
默认约束使用户能够定义一个值，每当用户没有在某一列中输入值时，则将所定义的值提供给这一列。
在数据库关系图中，可以将默认约束定义为表的一个列属性。通过在标准视图下的表内指定默认值，为列定义这种类型的约束。
默认值对象与ALTER TABLE或CREATE TABLE命令使用的“DEFAULT”功能选项相似，但默认值对象可以用于多个列或用户自定义数据类型，且表的一列或一个用户自定义数据类型也只能与一个默认值相绑定。
语法：CREATE DEFAULT 默认名 AS 常量表达式
参数：①默认名：默认值的名称。默认值名称必须符合标识符的规则。可以选择是否指定默认值所有者名称。
②常量表达式：只包含常量值的表达式（不能包含任何列或其他数据库对象的名称）。
【例7-8】创建一个默认实例，默认电话号码为8888888。
create default phone_num as 8888888
创建好默认值对象后，需要将默认值绑定到列或用户定义数据类型时。
在企业管理器中，展开服务器，接着展开数据库。
在展开的内容中选择“默认”，在右边便出现用SQL建立的默认对象。
在右边选择刚建立好的“默认”，单击鼠标右键，选择“属性”，单击“绑定列”。
弹出的绑定到列对话框，在“表”的下拉框中选择绑定列所在的表，然后选择要绑定的列，确定后，单击中间的“添加”按钮，最后单击“确定”即可。
第24教案 实训12：索引与数据完整性
实训12：索引与数据完整性
（一实训目的
通过实训掌握数据库表索引的建立和用法
通过实训掌握完整性约束的一般使用内容
通过实训掌握主键完整性、唯一性、外键、检查完整性和默认完整性约束的使用方法
（二）、实训内容
1、索引的建立和用法
例1：对STUDENT数据库中的学生表按照学号降序建立一个聚集索引,索引名为idx_xh。
create clustered index idx_xh
on student_info(student_id desc)
例2：删除Student数据库中的学生表中的“idx_xm”索引。
命令：drop index student_info.idx_xm
2、PRIMARY KEY约束
例3：建立一个工人信息表，包括编号、姓名、职务，要求主键为编号，姓名具有唯一性
Create table 职工信息表
(编号 char(10),
姓名 char(8),
职务 char(12),
Constraint pk_no primary key(编号),
Constraint pk_name unique(姓名)
)
3、FOREIGN KEY约束
例4:在创建考试表时，定义相应外部关键字。
create table 考试
(学号 char(6),
课程号 char(6),
成绩 smallint,
constraint pk_ks1 primary key (学号,课程号),
constraint pk_ks2 foreign key(课程号)
references 课程(课程号)
)
4、UNIQUE约束
例5：在创建学生基本表时，创建一个表级UNIQUE约束。
create table 课程1
(课程号 char(6),
课程名 char(20),
学分 smallint,
周学时 smallint,
constraint un_one unique(课程名,学分)
)
5、CHECK约束
例6:在创建学生基本表时，创建一个CHECK约束，使学生的年龄在20岁到65岁之间。
create table 学生
(学号 char(6) primary key,
姓名 char (10),
性别 char (2),
年龄 smallint,
constraint ch_nl check (年龄>=20 and 年龄<=65),
班级号 char (5)
)
6、默认值约束
例7:创建一个默认实例，默认电话号码为6666666并绑定到表student_info的列tele_number上。
create default phone_num as 8888888