【SQL server速成之路】索引与数据完整性

🎉个人主页：这个昵称我想了20分钟
✨往期专栏：【速成之路】jQuery

---

🎖️专栏：【速成之路】SQL server
🔓往期回顾：
【SQL server速成之路】数据库基础
【SQL server速成之路】数据库和表（一）
【SQL server速成之路】数据库和表（二）
【SQL server速成之路】数据库的查询
【SQL server速成之路】数据库的视图和游标
【SQL server速成之路】T-SQL语言（一）
【SQL server速成之路】T-SQL语言（二）
【SQL server速成之路】函数

索引

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

• 快速存取数据；
• 保证数据记录的唯一性；
• 实现表与表之间的参照完整性；
• 在使用ORDER BY、GROUP BY子句进行数据检索时，利用索引可以减少排序和分组的时间。

注：

1. Sql server支持在表中任何列（包括计算列）上定义索引；
2. 唯一索引：不会有两行记录的索引键值相同；
3. 不唯一索引：多个行共享同一索引键值；
4. 复合索引：多列组合创建的索引。

一.索引的分类

  1. 聚集索引
  聚集索引将数据行的键值在表内排序并存储对应的数据记录，使得数据表物理顺序与索引顺序一致。SQL Server 2012是按B树（BTREE）方式组织聚集索引的，B树方式构建为包含了多个节点的一棵树。顶部的节点构成了索引的开始点，叫做根。每个节点中含有索引列的几个值，一个节点中的每个值又都指向另一个节点或者指向表中的一行，一个节点中的值必须是有序排列的。指向一行的一个节点叫做叶子页。叶子页本身也是相互连接的，一个叶子页有一个指针指向下一组。

注：表中的顺序和索引顺序一致。

  2. 非聚集索引

  非聚集索引完全独立于数据行的结构。SQL Server 2012也是按B树组织非聚集索引的，与聚集索引不同之处在于：非聚集索引B树的叶节点不存放数据页信息，而是存放非聚集索引的键值，并且每个键值项都有指针指向包含该键值的数据行。

  对于非聚集索引，表中的数据行不按非聚集键的次序存储。
  在非聚集索引内，从索引行指向数据行的指针称为行定位器。行定位器的结构取决于数据页的存储方式是堆集还是聚集。对于堆集，行定位器是指向行的指针。对于有聚集索引的表，行定位器是聚集索引键，只有在表上创建聚集索引时，表内的行才按特定顺序存储。这些行按聚集索引键顺序存储。如果一个表只有非聚集索引，它的数据行将按无序的堆集方式存储。


注：

• 一个表中最多只能有一个聚集索引，可以有多个非聚集索引；
• 创建索引时先创建聚集索引，然后再创建非聚集索引。

二. 系统表sysindexes

系统表sysindexes的主要字段如下表所示。

用户创建数据库时，系统自动创建系统表，用户创建的每个索引均将在系统表中登记。

三、索引的创建

  在xsbook数据库中，经常要对xs、book和jy这3个表查询和更新，为了提高查询和更新速度，可以考虑对3个表建立如下索引：

（1）对于xs表，按借书证号建立主键索引（PRIMARY KEY约束），索引组织方式为聚集索引；按姓名建立非唯一索引，索引组织方式为非聚集索引；
（2）对于book表，按ISBN建立主键索引或者唯一索引，索引组织方式为聚集索引；
（3）对于jy表，按借书证号+ISBN建立唯一索引，索引组织方式为聚集索引。

  1．界面方式创建索引

  在“对象资源管理器”中展开“数据库xsbook”→展开“表”中的“dbo.xs”→右击其中的“索引”项，在弹出的快捷菜单上选择“新建索引”菜单项的“非聚集索引”。这时，用户可以在弹出的“新建索引”窗口中输入索引名称（索引名在表中必须唯一），如ck_xs，（如果是唯一索引，需要勾选“唯一”复选框）。单击新建索引窗口的“添加”按钮→在弹出选择列窗口（如图所示）中选择要添加的列→添加完毕后，单击“确定”按钮。

  除了使用上面的方法创建索引之外，还可以直接在表设计器窗口创建索引。在表设计器窗口创建索引的方法如下：

（1）右击表名，在弹出的快捷菜单中选择“设计”菜单项。
（2）在“表设计器”窗口中，选择需要创建索引的属性列，右击鼠标，在弹出的快捷菜单中选择“索引/键”菜单项。

  2．使用SQL命令创建索引

  使用CREATE INDEX语句可以为表创建索引。语法格式：

CREATE [ UNIQUE ] 			/*指定索引是否唯一*/
    [ CLUSTERED | NONCLUSTERED ] 	/*索引的组织方式*/
    INDEX <索引名> 			/*索引名称*/
    ON {[ <数据库名>. [ <架构名> ] . | <架构名>. ] <表名或视图名>}
	 ( <列名> [ ASC | DESC ] [ ,...n ] ) 	/*索引定义的依据*/
    [ WITH ( <relational_index_option> [ ,...n ] ) ]	/*索引选项*/
    [ ON {   <分区方案名> (<列名>) 		/*指定分区方案*/
         		| <文件组名> 		/*指定索引文件所在的文件组*/
         	    }
    ]
 [ ; ]

其中：

<relational_index_option> ::=
{
  PAD_INDEX  = { ON | OFF }
 | FILLFACTOR = fillfactor
 | SORT_IN_TEMPDB = { ON | OFF }
 | IGNORE_DUP_KEY = { ON | OFF }
 | STATISTICS_NORECOMPUTE = { ON | OFF }
 | DROP_EXISTING = { ON | OFF }
 | ONLINE = { ON | OFF }
 | ALLOW_ROW_LOCKS = { ON | OFF }
 | ALLOW_PAGE_LOCKS = { ON | OFF }
 | MAXDOP = max_degree_of_parallelism
}

说明：

（1）UNIQUE：表示为表或视图创建唯一索引（即不允许存在索引值相同的两行）。
（2）CLUSTERED | NONCLUSTERED：用于指定创建聚集索引还是非聚集索引，前者表示创建聚集索引，后者表示创建非聚集索引。
（3）<列名>：用于指定建立索引的字段，参数n表示可以为索引指定多个字段。
（4）WITH子句：用于指定定义的索引选项。

【例1】 对于jy表，按 借书证号+ISBN 创建索引（组合索引）。

/*创建简单索引*/
IF EXISTS (SELECT name FROM sysindexes WHERE name = 'jy_num_ind ')
	DROP INDEX jy.jy_num_ind
GO
CREATE INDEX jy_num_ind 
	ON jy (借书证号,ISBN)

【例2】 根据book表的ISBN列创建唯一聚集索引，因为指定了 CLUSTERED 子句，所以该索引将对磁盘上的数据进行物理排序。

/*创建唯一聚集索引*/
CREATE UNIQUE CLUSTERED INDEX book_id_ind  
	ON  book(ISBN)

  注：在创建索引之前，如果已经创建主键，系统会自动将主键列为聚集索引，如果未删除主键，则无法再创建新的聚集索引。

【例3】 根据xs表中借书证号字段创建唯一聚集索引。如果输入了重复键值，将忽略该INSERT或UPDATE语句。

CREATE UNIQUE CLUSTERED INDEX xs_ind 
	ON xs(借书证号)
	WITH IGNORE_DUP_KEY

【例4】 创建一个视图，并为该视图创建索引。

/*定义视图，如下例子中，由于使用了WITH  SCHEMABINDING子句，因此，定义视图时，SELECT子句中表名必须为：架构名名.视图名的形式。*/
CREATE  VIEW  VIEW1  WITH  SCHEMABINDING 
AS 
	SELECT 	索书号,书名,姓名
	FROM  dbo.jy, dbo.book, dbo.xs
	WHERE  jy.ISBN = book.ISBN AND xs.借书证号 = jy.借书证号
/*在视图VIEW1上定义索引*/
CREATE  UNIQUE CLUSTERED INDEX Ind1 
	ON dbo.VIEW1(索书号 ASC)

注：

1. 只有在视图上定义了WITH SCHEMABINDING ，才可以创建索引；
2. 为视图创建的聚集索引必须是unique索引。

四、索引的删除

  1．通过界面方式删除索引
  通过界面方式删除索引的主要步骤如下：启动“SQL Server Management Studio”→在“对象资源管理器”中展开数据库“xsbook”→“表”→“dbo.xs”→“索引”，选择其中要删除的索引，单击鼠标右键，在弹出的快捷菜单上选择“删除”菜单项。在打开的“删除对象”窗口单击“确定”按钮即可。

  2．通过SQL命令删除索引
  语法格式：

DROP INDEX
{    <索引名> ON  <表名或视图名> [ ,...n ] 
	| table_or_view_name.index_name [ ,...n ]
}

注：DROP INDEX语句可以一次删除一个或多个索引；
不适合删除通过定义primary key或unique约束创建的索引；删除通过定义primary key或unique约束创建的索引须通过删除约束实现；
系统表上的索引不能使用drop语句。

数据完整性

一、数据完整性的分类

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

  【例5】对于数据库xsbook的xs表，如果允许读者当前的在借图书量最多为20本，为了对读者当前的在借图书量进行限制，可以在定义xs表时，规定：“0≤借书量≤20”的约束条件达到目的。定义表xs的同时定义借书量字段的约束条件：

USE xsbook
GO
CREATE TABLE xs
( 	
	借书证号 	char(8) 	NOT NULL  PRIMARY KEY,
  	姓名 	char(8) 	NOT NULL,
  	性别 	bit 	NOT NULL  DEFAULT 1,
  	出生时间 	date 	NOT NULL ,
	/*如下语句定义字段的同时定义约束条件*/
  	专业 	char(12) 	NOT NULL,
	借书量 	int 	CHECK (借书量 >=0 AND 借书量<=20)  NOT NULL,
  	照片 	varbinary(MAX)  NULL
)

  2．实体完整性

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

  例如，对于xsbook数据库中xs表，借书证号作为主键，每一个读者的借书证号能唯一的标识该读者对应的行记录信息，那么在输入数据时，则不能有相同借书证号的行记录，通过对借书证号这一字段建立主键约束可实现表xs的实体完整性。

  3．参照完整性

  参照完整性又称引用完整性。保证主表数据与从表数据的一致性。SQL Server 2012中，参照完整性的实现是通过定义外键（外码）与主键（主码）之间或外键与唯一键之间的对应关系实现的。

码：即前面所说的关键字，又称为“键”，是能唯一标识表中记录的字段或字段组合。如果一个表有多个码，可选其中一个作为主码（主键），其余的称为后选码。
外键：如果一个表中的一个字段或若干个字段的组合是另一个表的键则称该字段或字段组合为该表的外键。xs和jy表的对应关系如图所示。

二、域完整性的实现

Sql server通过数据类型、check约束、default定义、not null定义实现域完整性。
CHECK约束实际上是字段输入内容的验证规则，表示一个字段的输入内容必须满足CHECK约束的条件，若不满足，则数据无法正常输入。对于timestamp和identity两种类型字段不能定义CHECK约束。

（1）通过界面方式创建与删除CHECK约束。
对于xsbook数据库的xs表，要求读者的借书证号必须由6个数字字符构成，并且不能为“000000”。

① 启动“SQL Server Management Studio”→在“对象资源管理器”中展开“数据库”→“xsbook”→“表”→选择“dbo.xs”，右击鼠标选择“设计”菜单项。
② 在打开的“表设计器”窗口中选择“借书证号”属性列，右击鼠标选择“CHECK约束”菜单项。
③ 在打开的 “CHECK约束”窗口（如图所示）中，单击“添加”按钮，添加一个“CHECK约束”。

  （2）使用SQL语句在创建表时创建CHECK约束
  利用T-SQL命令可以使用两种方式定义约束：作为列的约束或作为表的约束。
  语法格式：

CREATE TABLE table_name      			/*指定表名*/
(	<列名>  <数据类型>
       {
	 NOT NULL | NULL 			                               /*指定为空性*/
	| [ DEFAULT <默认值表达式> ]  	                              /*指定默认值*/
	| [ CONSTRAINT <约束名> ] CHECK ( logical_expression )] /*CHECK约束表达式*/
	}[,…n]
	[ CONSTRAINT constraint_name ] CHECK ( logical_expression )][,…n]
) 

  （3）使用SQL语句在修改表时创建CHECK约束
  语法格式：

ALTER TABLE <表名>     
	ADD [<列的定义>]
	[CONSTRAINT <约束名>] CHECK (logical_expression)

  （4）使用SQL语句删除CHECK约束
  CHECK约束的删除可在对象资源管理器中通过界面删除，读者可以自己试一试，在此介绍如何利用SQL命令删除。
使用ALTER TABLE语句的DROP子句可以删除CHECK约束。语法格式：

ALTER TABLE <表名>     
	DROP CONSTRAINT <约束名>  

三、实体完整性的实现

  表中应有一个列或列的组合，其值能唯一标识表中每一行，选择这样的列或列的组合可以作为主键实现实体完整性。通过定义PRIMARY KEY约束创建主键。
  PRIMARY KEY约束与UNIQUE约束的主要区别如下：

• 一个数据表只能创建一个PRIMARY KEY约束，但一个表中可根据需要对不同的列创建若干个UNIQUE约束；
• PRIMARY KEY字段的值不允许为NULL，而UNIQUE字段的值可取NULL；
• 一般创建PRIMARY KEY约束时，系统会自动产生索引，索引的默认类型为簇索引。创建UNIQUE约束时，系统会自动产生一个UNIQUE索引，索引的默认类型为非簇索引。

  1．使用界面方式创建和删除PRIMARY KEY约束

（1）创建PRIMARY KEY约束
当创建主键时，系统将自动创建一个名称以“PK_”为前缀、后跟表名的主键索引，系统自动按聚集索引方式组织主键索引。
（2）删除PRIMARY KEY约束
如果要删除对表xs中对借书证号字段建立的PRIMARY KEY约束，按如下步骤进行：在“对象资源管理器”中选择dbo.xs表图标，右击鼠标，在弹出的快捷菜单中选择“设计”菜单项，进入“表设计器”窗口。选中“xs表设计器”窗口中主键所对应的行，右击鼠标，在弹出的快捷菜单中选择“删除主键”菜单项即可。

  2．使用界面方式创建和删除UNIQUE约束

（1）创建UNIQUE约束
如果要对xs表中的“姓名”列创建UNIQUE约束，以保证该列取值的唯一性，可按以下步骤进行：
进入xs表的“表设计器”窗口，选择“姓名”属性列并右击鼠标，在弹出的快捷菜单中选择“索引/键”菜单项，打开“索引/键”窗口。
在窗口中单击“添加”按钮，并在右边的“标识”属性区域的“名称”一栏中输入唯一键的名称（用系统默认的名或重新取名）。在常规属性区域的“类型”一栏中选择类型为“唯一键”。
（2）删除UNIQUE约束
打开“姓名”属性列的“索引/键”窗口，选择要删除的UNIQUE约束，单击左下方的“删除”按钮，单击“关闭”按钮，保存表的修改即可。

  3．使用SQL命令创建及删除PRIMARY KEY约束或UNIQUE约束

（1）创建表的同时创建PRIMARY KEY约束或UNIQUE约束
语法格式：

CREATE TABLE <表名>          		/*指定表名*/
( 	<列名>  <数据类型>            		/*定义字段*/
	[ CONSTRAINT <约束名> ]      	/*约束名*/
	{ PRIMARY KEY | UNIQUE }         	/*定义约束类型*/
	[ CLUSTERED | NONCLUSTERED ]  /*定义约束的索引类型*/
	[, …n] 
)

（2）修改表时创建PRIMARY KEY约束或UNIQUE约束
创建PRIMARY KEY约束

ALTER TABLE <表名>     
	ADD  [ CONSTRAINT <约束名> ]  { PRIMARY KEY | UNIQUE }
	           [ CLUSTERED | NONCLUSTERED]
	           ( column [ ,...n ] )

（3）删除PRIMARY KEY约束或UNIQUE约束
删除PRIMARY KEY约束或UNIQUE约束需要使用ALTER TABLE的DROP子句。语法格式：

ALTER TABLE <表名>     
	DROP CONSTRAINT <约束名> [,…n]

四、参照完整性的实现

  对两个相互关联的表（主表与从表）进行数据插入和删除时，通过参照完整性保证它们之间的数据一致性。
  利用foreign key约束定义从表外键，利用primary key约束或unique约束定义主表中的主键或唯一键（不允许为空），可实现主表与从表之间的参照完整性。
  定义表间参照关系时，先定义主键（或唯一键），再对从表定义外键。

  1.使用界面方式定义表间的参照关系
  例如：在数据库xsbook中要建立xs表与jy表之间的参照完整性，操作步骤如下：

（1）按照前面所介绍的方法定义主表的主键。由于之前在创建表的时候已经定义xs表中的借书证号字段为主键，所以这里就不需要再定义主表的主键了。
（2）在“对象资源管理器”中展开“数据库”→“xsbook”→选择“数据库关系图”，右击鼠标，在出现的快捷菜单中选择“新建数据库关系图”菜单项，打开“添加表”窗口。
（3）在出现的“添加表”窗口中选择要添加的表，这里选择表xs和表jy。单击“添加”按钮完成表的添加，之后单击“关闭”按钮退出窗口。
（4）在“数据库关系图设计”窗口将鼠标指向主表的主键，并拖动到从表，即将xs表中的“借书证号”字段拖动到从表jy中的“借书证号”字段。
（5）在弹出的“表和列”窗口中输入关系名、设置主键表和列名，如图6.5所示，单击“表和列”窗口中的“确定”按钮，再单击“外键关系”窗口中的“确认”按钮，进入如图所示的界面。


（6）单击“保存”按钮，在弹出的“选择名称”对话框中输入关系图的名称。单击“确定”按钮，在弹出的“保存”对话框中单击“是”按钮，保存设置。

  2．使用界面方式删除表间的参照关系
  如果要删除前面建立的xs表与jy表之间的参照关系，可按以下步骤进行：

（1）在“xsbook”数据库的“数据库关系图”目录下选择要修改的“关系图”，如Diagram_0，右击鼠标，在弹出的快捷菜单中选择“修改”菜单项，打开“数据库关系图设计”窗口。
（2）在“数据库关系图设计”窗口中，选择已经建立的“关系”，单击鼠标右键，选择“从数据库中删除关系”，如图所示。在随后弹出的对话框中，单击“是”按钮，删除表之间的关系。

  3．使用SQL命令定义表间的参照关系
  （1）创建表的同时定义外键约束
  语法格式：

CREATE TABLE <表名>
(
	<列名> <数据类型>  	
	[ CONSTRAINT <约束名> ] 
	[ FOREIGN KEY ][ ( column [ ,...n ] )] 
	REFERENCES referenced_table_name [ ( ref_column [ ,...n ] ) ] 
	[ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] 
	[ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] 
)

说明：

• FOREIGN KEY定义的外键应与参数referenced_table_name指定的主表中的主键或唯一键对应，主表中主键或唯一键字段由参数ref_column指定。主键的数据类型和外键的数据类型必须相同。
• 定义外键时还可以指定参照动作：ON DELETE | ON UPDATE。可以为每个外键定义参照动作。
• 和主键一样，外键也可以定义为列的约束或表的约束。

（2）通过修改表定义外键约束
使用ALTER TABLE语句的ADD子句也可以定义外键约束，语法格式：

ALTER TABLE <表名>     
	ADD   [ CONSTRAINT <约束名>]
	[ FOREIGN KEY ][ ( column [ ,...n ] )] 
	REFERENCES referenced_table_name [ ( ref_column [ ,...n ] ) ] 
	[ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] 
	[ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]

  4．使用SQL命令删除表间的参照关系
  删除表间的参照关系，实际上删除从表的外键约束即可。