四 、索引和完整性
索引
索引的分类
聚焦索引
聚焦索引使得数据表物理顺序与索引顺序不一致。
不论聚集索引里有表的哪个（或哪些）字段，这些字段都会按顺序保存在表中
由于存在这种排序，所以每个表只会有一个聚焦索引。
由于数据记录按聚集索引键的次序存储，所以聚集索引对查找记录很有效
非聚焦索引
在非聚集索引内，从索引指向数据行的指针称为定位器。
行定位器的结构取决于数据页的存储方式是堆集还是聚集。
对于堆集，行定位器是指向行的指针。
对于有聚集索引的表，行定位器是聚集索引键。
如果一个表只有非聚集索引，则他数据行价格按无序的堆集方式存储。
一个表可以有一个或多个非聚集索引。
当在一个表中既要创建聚集索引，又要创建非聚集索引时，应先创建聚集索引，然后再创建非聚集索引，因为创建聚集索引时改变数据记录的物理存放顺序。
索引的创建
关键字
--示例
UNIQUE :表示为表或视图创建唯一索引（即不允许存在索引值相同的两行）
CLUSTERED | NONCLUSTERED :CLUSTERED表示创建聚集索引，NONCLUSTERED表示创建非聚集索引。
--索引名：索引名在在表或视图中必须唯一，但在数据库中不必唯一；
--表或视图名：用于指定包含索引字段的表名或视图名，指定表名、视图名时可包含数据库和所述架构。
--列：指定建立索引的字段，可以为索引指定多个字段。指定索引字段时，注意表或视图索引字段的类型不能为 ntext 、text或image.
WHERE 子句：通过指定索引中要包含哪些行来创建筛选索引。创建索引
--示例 在xscj数据库中为kcb表的“课程名”列创建索引，为“课程号”列创建唯一聚焦索引。
USE xscj
GO
CREATE INDEX kc_name
ON kcb(课程名)
CREATE UNIQUE CLUSTERED INDEX kc_id
ON kcb(课程号)
/*因为指定了CLUSTERED,所以该索引将对磁盘上的数据进行物理排序*/
/*--------------------------------------------------------------------------------*/
--根据cjb表的学号列和课程号列创建复合索引
CREATE INDEX cjb_ind
ON cjb(学号，课程号)
WITH(DROP _ EXISTING=ON)
/*
其中DROP_EXISTING指定删除已存在的同名聚集索引。
设置ON表示删除并重新生成现有索引；
设置OFF表示如果指定的索引已存在则显示一条错误信息；
默认值为OFF
*/
/*-------------------------------------------------------------------------------*/
--根据xsb表中的学号列创建唯一聚集索引。如果输入了重复键值，将忽略该 INSERT 或 UPDATE 语句
CREATE UNIQUE CLUSTERED INDEX xs_ind
ON xsb(学号)
WITH IGNORE_DUE_KEY
--其中IGNORE_DUE_KEY指定对索引列插入操作时出现重复键值的错误响应。在计算列和视图上创建索引
在计算列上创建索引
条件：
对于UNIQUE 或PRIMARY KEY 索引，只要满足索引条件，就可以包含计算列，但计算列必须具有确定性，必须精确。
若计算列中带有函数，则要求该函数有相同的参数输入，输出的结果也一定要相同。
例如：xscj数据库的xsb表中的“年龄”列时计算列，可以创建索引。
在视图上创建索引
可以在视图上定义索引。
索引视图是一种在数据库中存储视图结果集的方法，可减少动态生成结果集的开销。
索引视图还能自动反映出创建索引后对基数表数据所做的修改。
--示例
--创建xscj数据库的一个视图，并为该视图创建索引
CREATE VIEW dbo.vxs1 WITH SCHEMABINDING
AS
SELECT 学号,姓名
FROM dbo.xsb
GO
/*在视图上创建索引*/
CREATE UNIQUE CLUSTERED INDEX inx1
ON dbo.vxs1(学号)
GO索引的重建
--示例 索引使用一段时间后，可能需要重建。这是，可以使用 ALTER INDEX 语句来重新生成原来的索引。
--重建kcb表上所有的索引
USE xscj
ALTER INDEX ALL ON kcb REBUILD
--重建kcb表上的kc_name索引
ALTER INDEX kc_name ON REBUILD索引的删除
--示例 从当前数据库中删除一个或多个索引
--语法格式：
DROP INDEX 索引名 --索引名是要删除的索引的名称
ON 表或视图名 --要删除的索引所在的表名或视图名
--注意：该语句可以一次删除一个或多个索引。该语句不适合删除通过定义 PRIMARY KEY 或 UNIQUE 约束创建的索引，若要删除这两类必须通过删除约束实现。
/*---------------------------------------------------------*/
--删除xscj数据库中表kcb的一个索引名为kc_name的索引
IF EXISTS (
SELECT name
FROM sysindexes
WHERE name='kc_name'
)
DROP INDEX kcb.kc_name数据完整性
分类
实体完整性
PRIMARY KEY约束
表中应有一个列或列的组合，其值能唯一地标识表中的每一行，选择这样的一列或多列作为主键，可实现表的实体完整性。
通过定义PRIMARY KEY 约束来创建主键。
一个表只能有一个PRIMARY KEY约束，而且PRIMANY KEY约束中的列不能取空值。
由于PRIMARY KEY约束能确保数据的唯一性，所以经常用来定义标识列。
当为表定义PRIMARY KEY 约束时，SQL SERVER 2016 为主键列创建唯一索引，实现数据的唯一性。
在查询中使用主键时，该索引可用来对数据进行快速访问。
UNIQUE约束
如果要确保一个表中的非主键列不输入重复值，则应在该列上定义唯一约束（UNIQUE约束）。
例如，对于xscj数据库中的xsb表，“学号”列是主键，在xsb表中增加一列“身份证号码”，可以定义一个UNIQUE约束来要求表中的“身份证号码”列的取值是唯一的。
区别
（1）一个数据表只能创建一个PRIMARY KEY约束，但一个表中可根据需要对表中不同的列创建若干个UNIQUE约束。
（2）PRIMARY KEY 字段的值不允许为NULL，而UNIQUE字段的值可取NULL。
（3）一般创建PRIMARY KEY约束时，系统会自动产生索引，索引的默认类型为簇索引。
创建UNIQUE约束时，系统会自动产生一个UNIQUE索引，索引默认类型为非簇索引。
域完整性
域完整性又称为列完整性，指给定列输入有效性。
实现域完整性的方法有：通过 CHECK约束，数据类型，DEFALUT定义，NOT NULL定义和规则等。
CHECK约束通过输入到列中的值来实现域完整性；
DEFAULT定义后，如果列中没有输入值，则填充默认值来实现域完整性；
通过定义列为NOT NULL限制输入的值不能为空，也能实现域完整性。
参照完整性
参照完整性的实现是通过定义外键与主键之间或者外键与唯一键之间的对应关系来实现的。
参照完整性确保值在所有表中一致。
码：即前面所说的关键字，又称键，是能唯一标识表中记录的字段或字段组合。
如果一个表多个码，可选择一个作为主键（主码），其余的称为候选键。
外码：如果一个表中的一个字段或若干个字段的组合是另一个表的码，则称该字段或字段组合为该表的外码（外键）。
如果定义了两个表之间的参照完整性，则要求：
1，从表不能引用不存在的键值。例如，cjb表中行记录出现的学号必须是xsb表中已存在的学号。
2，如果主表中的键值更改了，那么在整个数据库中，对从表中该键值的所有引用都要进行一致的更改。
例如如果对xsb表中的某一学号进行修改，则对cjb表中所对应的学号也要进行相应的修改。
3，如果主表中没有关联的记录，则不能将记录添加到从表。如果要删除主表中的某一记录，则应先删除从表中与该记录匹配的相关记录。
创建实体完整性
--示例 在创建表的同时创建主键约束或唯一性约束。
--语法格式：
CREATE TABLE 表名
（
{<列定义>,<列约束>}[]
[<表约束>][,....]
）
/*-------------------------------------------------------------------------*/
--在xscj数据库中创建xsb3表，并对“学号”字段创建主键约束，对姓名字段定义唯一性约束。
USE xscj
GO
CREATE TABLE xsb3
(
学号 char(6) NOT NULL CONSTRAINT xh_pk PRIMARY KEY/*CONSTRAINT 约束名 ：为约束命名*/
姓名 char(8) NOT NULL CONSTRAINT xm_uk UNIQUE/*PRIMARK KEY、UNIQUE,定义约束的索引类型(聚集/非聚集)*/
性别 bit NOT NULL DEFAULT 1
出生时间 date NOT NULL
专业 char(12) NULL
总学分 int NULL
备注 varchar(500) NULL
)--示例 通过修改表创建主键约束或唯一性约束 ALTER TABLE语句中的ADD子句
--修改xsb3表，向其中添加一个“身份证号码”字段，对该字段定义唯一性约束。对表中“出生时间”字段定义唯一性约束
ALTER TABLE xsb3
ADD 身份证号码 char(20)
CONSTRAINT sf_uk UNIQUE NONCLUSTERED(身份证号码)
GO
ALTER TABLE xsb3
ADD
CONSTRAINT cssj_uk UNIQUE NONCLUSTERED(出生时间)
GO--示例 删除主键约束或唯一性约束
--删除主键约束或唯一性约束需要使用 ALTER TABLE的DROP子句
--语法格式：
ALTER TABLE 表名
DROP CONSTRAINT 约束名[,...]
/*-------------------------------------------------------------------------*/
--删除xsb3创建的sf_uk,xm_uk唯一性约束
ALTER TABLE xsb3
DROP CONSTRAINT sf_uk,xm_uk
GO创建域完整性
CHECK 约束的定义和删除
--示例 创建表时利用 CHECK 约束表达式来定义 CHECK 约束 关键字：CHECK
--语法格式
CHECK [NOT FOR REPLICATION](逻辑表达式)
/*----------------------------------------------------------------------*/
--在xscj数据库中创建一个表xsb1，其结构与xsb表相同，但要求：出生时间必须大于1994年1月1日
USE xscj
GO
CREATE TABLE xsb1
(
学号 char(6) NOT NULL PRIMARY KEY,/* 定义主键*/
姓名 char(8) NOT NULL,
性别 bit NULL DEFAULT 1,
出生时间 date NULL CHECK (出生时间>'1994-01-01'),/*添加约束*/
专业 char(12) NULL DEFAULT '计算机',
总学分 int NULL DEFAULT 0,
备注 varchar(500) NULL,
CONSTRAINT cxh_zy CHECK
(
LEFT(学号,2) = '19' AND 专业='计算机'
OR
LEFT(学号,2) = '22' AND 专业='通信工程'
)
)--示例 使用 ALTER TABLE 修改表时来定义 CHECK 约束 关键字：CHECK
--语法格式
ALTER TABLE 表名
[WITH {CHECK | NOCHECK }] ADD
[<列定义>]
[CONSTRAINT 约束名] CHECK (逻辑表达式)
/*-------------------------------------------------------------------*/
--通过修改xscj数据库的cjb表，增加“成绩”字段的 CHECK 约束
USE xscj
GO
ALTER TABLE cjb
ADD CONSTRAINT cj_constraint CHECK(成绩>=0 AND 成绩<=100)--示例 利用SQL语句删除CHECK约束
--语法格式
ALTER TABLE 表名
DROP CONSTRAINT 约束名
--删除cjb表“成绩”字段的 CHECK 约束
ALTER TABLE cjb
DROP CONSTRAINT cj_constraint规则对象的定义、使用与删除
定义
--示例 规则对象的定义
--语法格式：
CREATE RULE[架构名.]规则名
AS 条件表达式注：规则名：定义的新规则名，规则名必须符合标识符规则。
条件表达式：规则的条件表达式，该表达式可为WHERE子句中任何有效的表达式，但规则表达式中不能包含列或其他数据库对象， 可以包含不引用数据库对象的内置函数
创建的规则对先前已存在于数据库中的数据无效
在单个批处理中，CREATE RULE 语句不能与其他T-SQL语句组合使用
规则表达式的类型必须与列的数据类型兼容，不能将绑定到text、image或timestamp列
用户自定义数据类型，在该类型的数据列中插入值或更新该类型的数据列时，绑定到该类型的规则才会激活
如果列同时有默认值和规则与之相连，则默认值必须满足规则的定义，与规则冲突的默认值不能插入列
使用
--示例 将规则对象绑定到用户定义数据类型或列
--使用系统存储过程sp_bindrule
--语法规则：
sp_bindrule [@rulename=]'规则名'
[@objname=]'对象名' /*绑定的列或用户定义的数据类型*/
[,[@futureonly=]'futuronly标志']/*仅当将规则绑定到用户定义的数据类型时才使用*/
/*---------------------------------------------------------------------------------------*/
--创建一个规则，并绑定到表kcb的课程号列，用于限制课程号的输入范围
USE xscj
GO
CREATE RULE kc_rule
AS @range like '[1-5][0-9][0-9]'
GO
EXEC sp_bindrule 'kc_rule','kcb.课程号' /*执行存储过程使用EXEC命令*/
GO
--程序如果正确执行则提示：“已将规则绑定到表的列”
/*---------------------------------------------------------------------------------------*/
--创建一个规则，用以限制输入到该规则绑定的列中的值只能是该规则中列出的值
CREATE RULE list_rule
AS @list IN('C语言','离散数学','微机原理')
GO
EXEC sp_bindrule 'list_rule','kcb.课程名'
GO删除
--示例
--1，解除绑定
sp_unbindrule[@objname=]'对象名'
[,[@futureonly=]'futuronly标志']
--2，删除规则对象
DROP RULE {[架构名.]规则名}[,...][;]
/*---------------------------------------------------------------------------------------*/
--解除规则kc_rule与列用户定义类型的绑定关系，并删除规则对象kc_rule
EXEC sp_unbindrule 'kcb.课程号'
exec sp_unbindrule 'course_num'
GO
DROP RULE kc_rule创建参照完整性
两表没有创建任何索引，不能创建参照完整性
创建
--示例 在创建表的同时定义外键约束
--创建stu表，要求stu表中的所有的学生学号都必须出现在xsb表中，假设已经使用学号列作为主键创建了xsb
USE xscj
GO
CREATE TABLE stu
(
学号 char(6) NOT NULL FOREIGN KEY(学号) REFERENCES xsb(学号)，
姓名 char(8) NOT NULL, /*FOREIGN KEY定义的外键与参照表名指定的主表中的主键或唯一外键对应*/
出生时间 datetime NULL /*主表中主键或唯一键字段有参数“参照列”指定*/
) /*主键的数据类型和外键的数据类型必须一致*/
/*---------------------------------------------------------------------------------------*/
--创建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 /*指定参照动作，DELETE表示删除，UPDATE表示更新操作*/
)--示例 通过修改表定义外键外键约束
--假设kcb表为主表，kcb的课程号字段已定义为主键。cjb表为从表，如下示例用于将cjb表的课程号字段定义为外键。
ALTER TABLE cjb
ADD CONSTRAINT kc_foreign
FOREIGN KEY(课程号)
REFERENCES kcb(课程号)删除
--示例 删除上例对cjb表的“课程号”字段定义的外键约束
ALTER TABLE cjb
DROP CONSTRAINT kc_foreign
