SQL server 2008 索引与数据完整性【苍松书苑】.ppt

《SQL server 2008 索引与数据完整性【苍松书苑】.ppt》由会员分享，可在线阅读，更多相关《SQL server 2008 索引与数据完整性【苍松书苑】.ppt（52页珍藏版）》请在三一文库上搜索。

1、第6章 索引与数据完整性,索引,数据完整性,综合应用训练,1,深层分析,6.1 索引,6.1.1 索引的分类 1. 聚集索引 聚集索引将数据行的键值在表内排序并存储对应的数据记录，使得数据表物理顺序与索引顺序一致。SQL Server 2008是按B树（BTREE）方式组织聚集索引的，B树方式构建为包含了多个节点的一棵树。顶部的节点构成了索引的开始点，叫做根。每个节点中含有索引列的几个值，一个节点中的每个值又都指向另一个节点或者指向表中的一行，一个节点中的值必须是有序排列的。指向一行的一个节点叫做叶子页。叶子页本身也是相互连接的，一个叶子页有一个指针指向下一组。这样，表中的每一行都会在索引中有

2、一个对应值。查询的时候就可以根据索引值直接找到所在的行。 聚集索引中B树的叶节点存放数据页信息。聚集索引在索引的叶级保存数据。这意味着不论聚集索引里有表的哪个（或哪些）字段，这些字段都会按顺序被保存在表中。由于存在这种排序，所以每个表只会有一个聚集索引。,2,深层分析,6.1.1 索引的分类,2. 非聚集索引 非聚集索引完全独立于数据行的结构。SQL Server 2008也是按B树组织非聚集索引的，与聚集索引不同之处在于：非聚集索引B树的叶节点不存放数据页信息，而是存放非聚集索引的键值，并且每个键值项都有指针指向包含该键值的数据行。 对于非聚集索引，表中的数据行不按非聚集键的次序存储。 在非

3、聚集索引内，从索引行指向数据行的指针称为行定位器。行定位器的结构取决于数据页的存储方式是堆集还是聚集。对于堆集，行定位器是指向行的指针。对于有聚集索引的表，行定位器是聚集索引键，只有在表上创建聚集索引时，表内的行才按特定顺序存储。这些行按聚集索引键顺序存储。如果一个表只有非聚集索引，它的数据行将按无序的堆集方式存储。,3,深层分析,6.1.2 系统表sysindexes,在此，将介绍另一系统表sysindexes。当用户创建数据库时，系统将自动创建系统表sysindexes，用户创建的每个索引均将在系统表sysindexes中登记，当创建一个索引时，如果该索引已存在，则系统将报错，因此，创建一

4、个索引前，应先查询sysindexes表，若待定义的索引已存在，则先删除之，然后再创建索引；当然，也可采用其它措施，如检测到待定义的索引已存在，则不创建该索引。系统表sysindexes的主要字段如表6.1所示。,表6-1 系统表sysindexes的主要字段,4,深层分析,6.1.3 索引的创建,1界面方式创建索引 下面以XS表中按借书证号建立主键索引及按姓名建立非惟一索引（索引组织方式为非聚集索引）为例，介绍索引的创建方法。 启动“SQL Server Management Studio”在“对象资源管理器”中展开“数据库XSBOOK”选择“表”中的“dbo.XS”右击其中的“索引”项，在

5、弹出的快捷菜单上选择“新建索引(N)”菜单项。,5,深层分析,6.1.3 索引的创建,这时，用户可以在弹出的“新建索引”窗口中输入索引名称（索引名在表中必须唯一），如PK_XS，选择索引类型为“聚集”、勾选“唯一”复选框单击新建索引窗口的“添加”按钮在弹出选择列窗口（如图6.1所示）中选择要添加的列添加完毕后，单击“确定”按钮，在主界面中为索引键列设置相关的属性单击“确定”按钮，即完成索引的创建工作。,图6.1 添加索引键列,6,深层分析,6.1.3 索引的创建,在表设计器窗口创建索引的方法如下： （1）右击XSBOOK数据库中的“dbo.XS”表，在弹出的快捷菜单中选择“设计”菜单项，打开“

6、表设计器”窗口。,（2）在“表设计器”窗口中，选择“借书证号”属性列，右击鼠标，在弹出的快捷菜单中选择“索引/键”菜单项。在打开的“索引/键”窗口中单击“添加”按钮，并在右边的“标识”属性区域的“名称”一栏中确定新索引的名称（用系统缺省的名或重新取名）。在右边的常规属性区域中的“列”一栏后面单击“ ”按钮，可以修改要创建索引的列。如果将“是唯一的”一栏设定为“是”则表示索引是唯一索引。在“表设计器”栏下的“创建为聚集的”选项中，可以设置是否创建为聚集索引，由于XS表中已经存在聚集索引，所以这里的这个选项不可修改，如图6.2所示。,图6.2 “索引/键”窗口,7,深层分析,6.1.3 索引的创建

7、,2使用SQL命令创建索引 使用CREATE INDEX语句可以为表创建索引。语法格式： CREATE UNIQUE /*指定索引是否唯一*/ CLUSTERED | NONCLUSTERED /*索引的组织方式*/ INDEX index_name /*索引名称*/ ON database_name. schema_name . | schema_name. table_or_view_name ( column ASC | DESC ,.n ) /*索引定义的依据*/ INCLUDE ( column_name ,.n ) WITH ( ,.n ) /*索引选项*/ ON partitio

8、n_scheme_name ( column_name ) /*指定分区方案*/ | filegroup_name /*指定索引文件所在的文件组*/ | default FILESTREAM_ON filestream_filegroup_name | partition_scheme_name | NULL /*指定FILESTREAM数据的位置*/ ; ,8,深层分析,6.1.3 索引的创建,其中： := PAD_INDEX = ON | OFF | FILLFACTOR = fillfactor | SORT_IN_TEMPDB = ON | OFF | IGNORE_DUP_KEY =

9、 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 说明：,9,深层分析,6.1.3 索引的创建,【例6.1】 对于JY表，按借书证号+ISBN创建索引。 /*创建简单索引*/ USE XSBOOK GO IF EXISTS (SELECT name FROM sysind

10、exes WHERE name = JY_num_ind ) DROP INDEX JY.JY_num_ind GO CREATE INDEX JY_num_ind ON JY (借书证号,ISBN) GO 【例6.2】 根据BOOK表的ISBN列创建惟一聚集索引，因为指定了 CLUSTERED 子句，所以该索引将对磁盘上的数据进行物理排序。 /*创建唯一聚集索引*/ CREATE UNIQUE CLUSTERED INDEX book_id_ind ON book(ISBN) GO,10,深层分析,6.1.3 索引的创建,【例6.3】 根据XS表中的借书量字段创建索引，例中使用了FILLFA

11、CTOR子句。 CREATE NONCLUSTERED INDEX num_ind ON XS (借书量) WITH FILLFACTOR = 60 【例6.4】 根据XS表中借书证号字段创建惟一聚集索引。如果输入了重复键值，将忽略该INSERT或UPDATE语句。 CREATE UNIQUE CLUSTERED INDEX xs_ind ON XS(借书证号) WITH IGNORE_DUP_KEY,11,深层分析,6.1.3 索引的创建,创建索引有如下几点要说明： （1）在计算列上创建索引。对于UNIQUE或PRIMARY KEY索引，只要满足索引条件，就可以包含计算列，但计算列必须具有确

12、定性、必须精确。若计算列中带有函数时，使用该函数时有相同的参数输入，输出的结果也一定相同时该计算列是确定的。而有些函数如getdate()每次调用时都输出不同的结果，这时就不能在计算列上定义索引。计算列为text、ntext或image列时也不能在该列上创建索引。 （2）在视图上创建索引。可以在视图上定义索引，索引视图能自动反映出创建索引后对基表数据所做的修改。,12,深层分析,6.1.3 索引的创建,【例6.5】 创建一个视图，并为该视图创建索引。 /*定义视图，如下例子中，由于使用了WITH SCHEMABINDING子句，因此，定义视图时，SELECT子句中表名必须为：架构名名.视图名的

13、形式。*/ CREATE VIEW VIEW1 WITH SCHEMABINDING AS SELECT 索书号,书名,姓名 FROM dbo.JY, dbo.BOOK, dbo.XS WHERE JY.ISBN=BOOK.ISBN AND XS.借书证号=JY.借书证号 GO /*在视图VIEW1上定义索引*/ CREATE UNIQUE CLUSTERED INDEX Ind1 ON dbo.VIEW1(索书号 ASC) GO,13,深层分析,6.1.4 索引的删除,1通过界面方式删除索引 通过界面方式删除索引的主要步骤如下：启动“SQL Server Management Studio”

14、在“对象资源管理器”中展开数据库“XSBOOK”“表”“dbo.XS”“索引”，选择其中要删除的索引，单击鼠标右键，在弹出的快捷菜单上选择“删除”菜单项。在打开的“删除对象”窗口单击“确定”按钮即可。 2通过SQL命令删除索引 语法格式： DROP INDEX index_name ON table_or_view_name ,.n | table_or_view_name.index_name ,.n 【例6.6】 删除例6.1为XSBOOK数据库中表JY创建的索引JY_num_ind。 USE XSBOOK GO IF EXISTS (SELECT name FROM sysindexes

15、 WHERE name = JY_num_ind) DROP INDEX JY. JY_num_ind GO,14,深层分析,6.2数据完整性,6.2.1 数据完整性的分类 1域完整性 域完整性又称为列完整性，指列数据输入的有效性。实现域完整性的方法有：限制类型（通过数据类型）、格式（通过CHECK约束和规则）或可能的取值范围（通过CHECK约束、DEFALUT定义、NOT NULL定义和规则）等。 CHECK约束通过显示输入到列中的值来实现域完整性；DEFAULT定义后，如果列中没有输入值则填充默认值来实现域完整性；通过定义列为NOT NULL限制输入的值不能为空也能实现域完整性。 例如：对

16、于数据库XSBOOK的XS表，如果允许读者当前的在借图书量最多为20本，为了对读者当前的在借图书量进行限制，可以在定义XS表时，规定：“0借书量20”的约束条件达到目的。,15,深层分析,6.2.1 数据完整性的分类,【例6.7】 定义表XS的同时定义借书量字段的约束条件。 USE XSBOOK GO CREATE TABLE XS ( 借书证号 char(8) NOT NULL PRIMARY KEY, 姓名 char(8) NOT NULL, 专业 char(12) NOT NULL, 性别 bit NOT NULL DEFAULT 1, 出生时间 date NOT NULL , /*如下

17、语句定义字段的同时定义约束条件*/ 借书量 int CHECK (借书量 =0 AND 借书量=20) NOT NULL, 照片 varbinary(MAX) NULL ) GO,16,深层分析,6.2.1 数据完整性的分类,2实体完整性 实体完整性又称为行的完整性，要求表中有一个主键，其值不能为空且能惟一地标识对应的记录。通过索引、UNIQUE 约束、PRIMARY KEY 约束或 IDENTITY 属性等可实现数据的实体完整性。例如，对于XSBOOK数据库中XS表，借书证号作为主键，每一个读者的借书证号能惟一的标识该读者对应的行记录信息，那么在输入数据时，则不能有相同借书证号的行记录，通过

18、对借书证号这一字段建立主键约束可实现表XS的实体完整性。 3参照完整性 参照完整性又称为引用完整性。参照完整性保证主表中的数据与从表中数据的一致性。SQL Server 2008中，参照完整性的实现是通过定义外键（外码）与主键（主码）之间或外键与惟一键之间的对应关系实现的。参照完整性确保键值在所有表中一致。,17,深层分析,6.2.1 数据完整性的分类,XS和JY表的对应关系如表6.2、和表6.3所示。,18,深层分析,6.2.1 数据完整性的分类,如果定义了两个表之间的参照完整性，则要求： （1）从表不能引用不存在的键值。例如：对于JY表中行记录出现的借书证号必须是XS表中已存在的。 （2）

19、如果主表中的键值更改了，那么在整个数据库中，对从表中该键值的所有引用要进行一致的更改。例如：如果对XS表中的某一借书证号修改，JY表中所有对应借书证号也要进行相应的修改。 （3）如果要删除主表中的某一记录，应先删除从表中与该记录匹配的相关记录。,19,深层分析,6.2.2 域完整性的实现,1CHECK约束的定义与删除 对于timestamp和identity两种类型字段不能定义CHECK约束。 （1）通过界面方式创建与删除CHECK约束。 对于XSBOOK数据库的XS表，若要求读者的借书证号必须由8个数字字符构成，并且不能为“00000000”，通过企业管理器对借书证号字段定义这一约束可按如下

20、步骤进行： 启动“SQL Server Management Studio”在“对象资源管理器”中展开“数据库”“XSBOOK”“表”选择“dbo.XS”，右击鼠标选择“设计”菜单项。 在打开的“表设计器”窗口中选择“借书证号”属性列，右击鼠标选择“CHECK约束”菜单项。,20,深层分析,6.2.2 域完整性的实现, 在打开的 “CHECK约束”窗口（如图6.3所示）中，单击“添加”按钮，添加一个“CHECK约束”。在常规属性区域中的“表达式”一栏后面单击“ ”按钮（或直接在文本框中输入内容），打开“CHECK约束表达式”窗口，并编辑相应的CHECK约束表达式为：“借书证号 LIKE 0-9

21、0-90-90-90-90-90-90-9 AND 借书证号00000000”。,图6.3 CHECK选项卡属性窗口,21,深层分析,6.2.2 域完整性的实现,（2）使用SQL语句在创建表时创建CHECK约束 利用T-SQL命令可以使用两种方式定义约束：作为列的约束或作为表的约束。 语法格式： CREATE TABLE table_name /*指定表名*/ (column_name datatype NOT NULL | NULL /*指定为空性*/ | DEFAULT constraint_expression /*指定默认值*/ | CONSTRAINT constraint_name

22、 CHECK ( logical_expression )/*CHECK约束表达式*/ ,n CONSTRAINT constraint_name CHECK ( logical_expression ),n ),22,深层分析,6.2.2 域完整性的实现,【例6.8】 对于数据库XSBOOK中的表BOOK，要求书的最高限价为250元，请重新定义BOOK表。 USE XSBOOK GO CREATE TABLE BOOK ( ISBN char(16)NOT NULL PRIMARY KEY, 书名 char(26) NOT NULL, 作者 char(8) NOT NULL, 出版社 cha

23、r(20) NOT NULL, 价格float NOT NULL CHECK (价格= 250), 复本量 int NOT NULL, 库存量int NOT NULL ),23,深层分析,6.2.2 域完整性的实现,【例6.9】 创建表student，有学号、最好成绩和平均成绩三列，要求最好成绩必须大于平均成绩。 CREATE TABLE student2 ( 学号 char(6) NOT NULL, 最好成绩 int NOT NULL, 平均成绩 int NOT NULL, CHECK(最好成绩平均成绩) ),24,深层分析,6.2.2 域完整性的实现,（3）使用SQL语句在修改表时创建CH

24、ECK约束 语法格式： ALTER TABLE table_name ADD CONSTRAINT constraint_name CHECK (logical_expression) 【例6.10】 通过修改XSBOOK数据库的XS表，增加借书证号字段的CHECK约束：要求借书证号必须全由8个数字字符构成，并且不等于00000000。 ALTER TABLE XS ADD CONSTRAINT card_constraint CHECK (借书证号 LIKE 0-90-90-90-90-90-90-90-9 AND 借书证号00000000) GO,25,深层分析,6.2.2 域完整性的实现

25、,（4）使用SQL语句删除CHECK约束 CHECK约束的删除可在对象资源管理器中通过界面删除，读者可以自己试一试，在此介绍如何利用SQL命令删除。 使用ALTER TABLE语句的DROP子句可以删除CHECK约束。语法格式： ALTER TABLE table_name DROP CONSTRAINT check_name 【例6.11】 删除XSBOOK数据库中XS表借书证号字段的CHECK约束。 IF EXISTS (SELECT name FROM sysobjects WHERE name = card_constraint AND type = C) BEGIN ALTER TA

26、BLE XS DROP CONSTRAINT card_constraint END,26,深层分析,6.2.2 域完整性的实现,2规则对象的定义、使用与删除 规则是一组使用T-SQL语句组成的条件语句，规则提供了另外一种在数据库中实现域完整性与用户定义完整性的方法。规则对象的使用方法如下： 定义规则对象； 将规则对象绑定到列或用户自定义类型； 在SQL Server 2008中规则对象的定义可以利用CREATE RULE语句来实现。 （1）规则对象的定义 语法格式： CREATE RULE schema_name. rule_name AS condition_expression,27,深

27、层分析,6.2.2 域完整性的实现,创建规则时，一般使用局部变量表示UPDATE 或 INSERT 语句输入的值。另外有如下几点需说明： 创建的规则对先前已存在于数据库中的数据无效。 单个批处理中，CREATE RULE语句不能与其它T-SQL语句组合使用。 规则表达式的类型必须与列的数据类型兼容，不能将规则绑定到text、image或 timestamp列。要用单引号（）将字符和日期常量引起来，在十六进制常量前加0 x。 对于用户定义数据类型，当在该类型的数据列中插入值，或更新该类型的数据列时，绑定到该类型的规则才会激活。规则不检验变量，所以在向用户定义数据类型的变量赋值时，不能与列绑定的规

28、则冲突。 如果列同时有默认值和规则与之关联，则默认值必须满足规则的定义，与规则冲突的默认值不能关联到列。,28,深层分析,6.2.2 域完整性的实现,（2）将规则对象绑定到自定义类型或列 语法格式： sp_bindrule rulename = rule , objname = object_name , futureonly = futureonly_flag 说明：参数rule为 CREATE RULE 语句创建的规则名，要用单引号括起来。参数object_name为绑定到规则的列或用户定义的数据类型，如果object_name采用 表名.字段名 格式，则认为绑定到表的列，否则绑定到用户定

29、义数据类型；参数futureonly_flag仅当将规则绑定到用户定义的数据类型时才使用，如果futureonly_flag设置为futureonly ，用户定义数据类型的现有列不继承新规则。如果 futureonly_flag 为 NULL，当被绑定的数据类型当前无规则时，新规则将绑定到用户定义数据类型的每一列。,29,深层分析,6.2.2 域完整性的实现,（3）应用举例 【例6.12】 如下程序创建一个规则，并绑定到XSBOOK数据库XS表的借书证号字段。 USE XSBOOK GO CREATE RULE num_rule AS num like 1-61-61-9 1-9 1-9 1-

30、9 0-9 0-9 0-9 GO EXEC sp_bindrule num_rule, XS.借书证号/*执行存储过程使用EXEC命令*/ GO 【例6.13】 创建一个规则，用以限制输入到该规则所绑定的列的值只能是该规则中列出的值。 CREATE RULE list_rule AS list IN (机械工业出版社, 电子工业出版社, 邮电出版社, 高等教育出版社,科学出版社,清华大学出版社) GO EXEC sp_bindrule list_rule, BOOK.出版社 GO,30,深层分析,6.2.2 域完整性的实现,【例6.14】 定义一个用户数据类型telphone，及规则“tel_

31、rule”，然后将规则“tel_rule”绑定到用户数据类型telphone上，最后定义表XS3，其电话号码字段的数据类型为telphone。 CREATE TYPE telphone FROM char(12) NULL/*创建用户定义数据类型*/ GO CREATE RULE tel_rule/*创建规则*/ AS tel LIKE 0-90-90-9-0-90-90-90-90-90-90-90-9 GO EXEC sp_bindrule tel_rule, telphone/*将规则对象绑定到用户定义数据类型*/ GO CREATE TABLE XS3 ( 借书证号 char(8) N

32、OT NULL, 姓名 char(8) NOT NULL, 专业 char(12) NOT NULL, 性别 bit NOT NULL, 出生时间 date NOT NULL, 电话 telphone,/*将电话定义为telphone类型*/ 借书量 tinyint CHECK (借书量 =0 AND 借书量=20) NULL, 照片 varbinary(MAX) NULL ) GO,31,深层分析,6.2.2 域完整性的实现,（4）规则对象的删除 删除规则对象前，首先应使用系统存储过程sp_unbindrule解除被绑定对象与规则对象之间的绑定关系。 语法格式： sp_unbindrule

33、objname = object_name , futureonly = futureonly_flag 在解除列或自定义类型与规则对象之间的绑定关系后，就可以删除规则对象了。 语法格式： DROP RULE rule ,.n 【例6.15】 解除规则对象list_rule与XSBOOK数据库BOOK表的出版社字段的绑定关系，然后删除规则对象list_rule。 IF EXISTS (SELECT name FROM sysobjects WHERE name = list_rule AND type = R) BEGIN EXEC sp_unbindrule BOOK.出版社 DROP RU

34、LE list_rule END GO,32,深层分析,6.2.3 实体完整性的实现,1使用界面方式创建和删除PRIMARY KEY约束 （1）创建PRIMARY KEY约束 如果要对XS表按借书证号建立PRIMARY KEY约束，可以按第3章中创建表的第3步中所介绍的设置主键的相关步骤进行。 当创建主键时，系统将自动创建一个名称以“PK_”为前缀、后跟表名的主键索引，系统自动按聚集索引方式组织主键索引。 （2）删除PRIMARY KEY约束 如果要删除对表XS中对借书证号字段建立的PRIMARY KEY约束，按如下步骤进行：在“对象资源管理器”中选择dbo.XS表图标，右击鼠标，在弹出的快捷

35、菜单中选择“设计”菜单项，进入“表设计器”窗口。选中“XS表设计器”窗口中主键所对应的行，右击鼠标，在弹出的快捷菜单中选择“删除主键”菜单项即可。,33,深层分析,6.2.3 实体完整性的实现,2使用界面方式创建和删除UNIQUE约束 （1）创建UNIQUE约束 如果要对XS表中的“姓名”列创建UNIQUE约束，以保证该列取值的唯一性，可按以下步骤进行： 进入XS表的“表设计器”窗口，选择“姓名”属性列并右击鼠标，在弹出的快捷菜单中选择“索引/键”菜单项，打开“索引/键”窗口。,34,深层分析,6.2.3 实体完整性的实现,在窗口中单击“添加”按钮，并在右边的“标识”属性区域的“名称”一栏中输

36、入唯一键的名称（用系统默认的名或重新取名）。在常规属性区域的“类型”一栏中选择类型为“唯一键”，如图6.4所示。,图6.4 创建唯一键,（2）删除UNIQUE约束 打开如图6.4所示的“索引/键”窗口，选择要删除的UNIQUE约束，单击左下方的“删除”按钮，单击“关闭”按钮，保存表的修改即可。,35,深层分析,6.2.3 实体完整性的实现,3使用SQL命令创建及删除PRIMARY KEY约束或UNIQUE约束 （1）创建表的同时创建PRIMARY KEY约束或UNIQUE约束 语法格式： CREATE TABLE table_name /*指定表名*/ ( column_name dataty

37、pe /*定义字段*/ CONSTRAINT constraint_name /*约束名*/ PRIMARY KEY | UNIQUE /*定义约束类型*/ CLUSTERED | NONCLUSTERED /*定义约束的索引类型*/ ,n ) 说明： PRIMARY KEY | UNIQUE：定义约束的关键字，PRIMARY KEY为主键，UNIQUE为唯一键。 CLUSTERED | NONCLUSTERED：定义约束的索引类型，CLUSTERED表示聚集索引，NONCLUSTERED表示非聚集索引，与CREATE INDEX语句中的选项相同。,36,深层分析,6.2.3 实体完整性的实现

38、,【例6.16】 创建XS4表，并对借书证号字段创建PRIMARY KEY约束，对姓名字段定义UNIQUE约束。 USE XSBOOK GO CREATE TABLE XS4 ( 借书证号 char(8) NOT NULL CONSTRAINT XS_PK PRIMARY KEY, 姓名 char(8) NOT NULL CONSTRAINT XM_UK UNIQUE, 专业 char(12) NOT NULL, 性别 bit NOT NULL, 出生时间 date NOT NULL, 借书量 int CHECK (借书量 =0 AND 借书量=20) NULL, 照片 varbinary(M

39、AX) NULL ) GO,37,深层分析,6.2.3 实体完整性的实现,【例6.17】 创建借阅历史表JYLS2，由借书证号、索书号、借书时间作为联合主键。 CREATE TABLE JYLS2 ( 借书证号 char(8) NOT NULL, ISBN char(16) NOT NULL, 索书号 char(10) NOT NULL, 借书时间 date NOT NULL, 还书时间 date NOT NULL, PRIMARY KEY (索书号,借书证号,借书时间)/*定义主键*/ ),38,深层分析,6.2.3 实体完整性的实现,（2）修改表时创建PRIMARY KEY约束或UNIQU

40、E约束 创建PRIMARY KEY约束 语法格式： ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY | UNIQUE CLUSTERED | NONCLUSTERED ( column ,.n ) 【例6.18】 修改XS4表，向其中添加一个“身份证号码”字段，对该字段定义UNIQUE约束。对“出生时间”字段定义UNIQUE约束。 ALTER TABLE XS4 ADD身份证号码 char(18) CONSTRAINT SF_UK UNIQUE NONCLUSTERED (身份证号码) GO ALTER TAB

41、LE XS4 ADDCONSTRAINT CJSJ_UK UNIQUE NONCLUSTERED (出生时间),39,深层分析,6.2.3 实体完整性的实现,（3）删除PRIMARY KEY约束或UNIQUE约束 删除PRIMARY KEY约束或UNIQUE约束需要使用ALTER TABLE的DROP子句。 语法格式： ALTER TABLE table_name DROP CONSTRAINT constraint_name ,n 【例6.19】 删除前面例中在表XS4上创建的PRIMARY KEY约束和UNIQUE约束。 ALTER TABLE XS4 DROPCONSTRAINT XS_

42、PK, XM_UK GO,40,深层分析,6.2.4 参照完整性的实现,1使用界面方式定义表间的参照关系 例如：在数据库XSBOOK中要建立XS表与JY表之间的参照完整性，操作步骤如下： （1）按照前面所介绍的方法定义主表的主键。由于之前在创建表的时候已经定义XS表中的借书证号字段为主键，所以这里就不需要再定义主表的主键了。 （2）启动“SQL Server Management Studio”在“对象资源管理器”中展开“数据库”“XSBOOK”选择“数据库关系图”，右击鼠标，在出现的快捷菜单中选择“新建数据库关系图”菜单项，打开“添加表”窗口。 （3）在出现的“添加表”窗口中选择要添加的表，

43、这里选择表XS和表JY。单击“添加”按钮完成表的添加，之后单击“关闭”按钮退出窗口。 （4）在“数据库关系图设计”窗口将鼠标指向主表的主键，并拖动到从表，即将XS表中的“借书证号”字段拖动到从表JY中的“借书证号”字段。,41,深层分析,6.2.4 参照完整性的实现,（5）在弹出的“表和列”窗口中输入关系名、设置主键表和列名，如图6.5所示，单击“表和列”窗口中的“确定”按钮，再单击“外键关系”窗口中的“确认”按钮，进入如图6.6所示的界面。,图6.5 设置参照完整性,42,深层分析,6.2.4 参照完整性的实现,图6.6 主表和从表的参照关系图,43,深层分析,6.2.4 参照完整性的实现,

44、2使用界面方式删除表间的参照关系 如果要删除前面建立的XS表与JY表之间的参照关系，可按以下步骤进行： （1）在“XSBOOK”数据库的“数据库关系图”目录下选择要修改的“关系图”，如Diagram_0，右击鼠标，在弹出的快捷菜单中选择“修改”菜单项，打开“数据库关系图设计”窗口。 （2）在“数据库关系图设计”窗口中，选择已经建立的“关系”，单击鼠标右键，选择“从数据库中删除关系”，如图6.7所示。在随后弹出的对话框中，单击“是”按钮，删除表之间的关系。,图6.7 删除关系,44,深层分析,6.2.4 参照完整性的实现,3使用SQL命令定义表间的参照关系 前面已介绍了创建主键（PRMARY K

45、EY约束）及唯一键（UNIQUE约束）的方法，在此将介绍通过SQL命令创建外键的方法。 （1）创建表的同时定义外码约束 语法格式： CREATE TABLE table_name ( column_name datatype CONSTRAINT constraint_name FOREIGN KEY ( column ,.n ) REFERENCES referenced_table_name ( ref_column ,.n ) ON DELETE NO ACTION | CASCADE | SET NULL | SET DEFAULT ON UPDATE NO ACTION | CASC

46、ADE | SET NULL | SET DEFAULT NOT FOR REPLICATION ),45,深层分析,6.2.4 参照完整性的实现,【例6.20】 在XSBOOK数据库中创建主表XS1和BOOK1，借书证号为XS1表的主键，ISBN为BOOK1的主键，然后定义从表JY1，JY1.借书证号为外键，与XS1的主键对应，当对主表进行更新和删除操作时，对从表采用级联操作，JY1.ISBN为晚间，与BOOK1的主键对应，当对主表进行更新和删除时，对从表采用NO ACTION方式。 【例6.21】 创建point表，要求表中所有的索书号、借书证号和借书时间组合都必须出现在JYLS表中。 C

47、REATE TABLE point ( 借书证号 char(8) NOT NULL, ISBN char(16) NOT NULL, 索书号 char(10) NOT NULL, 借书时间 date NOT NULL, 还书时间 date NOT NULL, CONSTRAINT FK_point FOREIGN KEY (索书号,借书证号,借书时间) REFERENCES JYLS (索书号,借书证号,借书时间) ON DELETE NO ACTION ),46,深层分析,6.2.4 参照完整性的实现,（2）通过修改表定义外键约束 使用ALTER TABLE语句的ADD子句也可以定义外键约束

48、，语法格式： ALTER TABLE table_name ADDCONSTRAINT constraint_name 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 【例6.22】 假设XSBOOK数据库中XS表为主表，XS.借书证号字段已定义为主键。JY表为从表，如

49、下示例用于将JY.借书证号字段定义为外键。 ALTER TABLE JY ADD CONSTRAINT JY_foreign FOREIGN KEY (借书证号) REFERENCES XS(借书证号),47,深层分析,6.2.4 参照完整性的实现,4使用SQL命令删除表间的参照关系 删除表间的参照关系，实际上删除从表的外键约束即可。 语法格式与前面其它约束删除的格式同。 【例6.23】删除上例对JY.借书证号字段定义的外码约束。 ALTER TABLE JY DROP CONSTRAINT JY_foreign,48,深层分析,6.3 综合应用训练,1训练要求 （1）对于JYLS表按索书号+借书证号+借书时间创建主键约束。 （2）创建JYLS表与XS表之间的参照关系。 （3）统计2004年每位读者的读书量，并按降序排列。,49,深层分析,6.3 综合应用训练,2实现上述功能的程序代码如下： （1）创建JYLS的主键约束 USE XSBOOK GO ALTER TABLE JYLS ADD CONSTRAINT HT_PK PRIMARY KEY CLUSTERED (