数据库技术及应用：第6章 索引与数据完整性

第6章索引与数据完整性6.1索引6.3.1数据完整性的分类1．域完整性域完整性又称为列完整性，指给定列输入的有效性。【例6.15】定义表KC的同时定义学分的约束条件。USEXSCJCREATETABLEKC( 课程号char(6)NOTNULL,

课程名char(8)NOTNULL,

学分tinyintCHECK(学分>=0AND学分<=10)NUL/*通过CHECK子句定义约束条件*/

备注textNULL)GO2．实体完整性实体完整性又称为行的完整性，要求表中有一个主键，其值不能为空且能唯一地标识对应的记录。3．参照完整性参照完整性又称为引用完整性。6.3.1数据完整性的分类XS和XS_KC表的对应关系如表6.2、6.3所示。(1)从表不能引用不存在的键值。如果主表中的键值更改了，那么在整个数据库中，对从表中键值的所有引用要进行一致的更改。如果主表中没有关联的记录，则不能将记录添加到从表。6.3.2域完整性的实现1．CHECK约束的定义与删除对于TimeStamp和Identity两种类型字段不能定义CHECK约束。

(1)通过企业管理器创建与删除CHECK约束第1步在XS_KC表设计器界面右击鼠标，出现如图6.8所示的快捷菜单；6.3.2域完整性的实现第2步选择CHECK约束菜单项进入如图6.9所示的CHECK选项卡属性窗口；第3步选择“新建”，进入CHECK约束的输入窗口，输入约束表达式“成绩>=0and成绩<=100”，如图6.10所示，选择“关闭”。6.3.3实体完整性的实现PRIMARYKEY约束与UNIQUE约束的主要区别如下：(1)一个数据表只能创建一个PRIMARYKEY约束，但一个表中可根据需要对不同的列创建若干个UNIQUE约束；(2)PRIMARYKEY字段的值不允许为NULL，而UNIQUE字段的值可取NULL；(3)一般创建PRIMARYKEY约束时，系统会自动产生索引，索引的缺省类型为簇索引。PRIMARYKEY约束与UNIQUE约束的相同点在于：二者均不允许表中对应字段存在重复值。利用企业管理器创建和删除PRIMARYKEY约束（1）利用企业管理器创建PRIMARYKEY约束如果要对XS表按学号建立PRIMARYKEY约束，应如何操作呢？可以按下列步骤进行：6.3.3实体完整性的实现第1步选择XS表图标，右击，打开XS表的表设计器，进入如图6.16所示的表设计器界面。第2步选中“学号”对应的这一行，选择主键图标，这样在“学号”对应的这一行前面，将出现一主键图标。6.3.3实体完整性的实现（2）利用企业管理器删除PRIMARYKEY约束如果要删除对表XS中对学号字段建立的PRIMARYKEY约束，按如下步骤进行：第1步选择XS表图标，右击，打开XS表设计器，进入如图6.17所示的表设计器界面。第2步选中XS表设计器中主键对应的行，选择工具栏的“设置主键图标”，则取消了原来定义的主键。2．利用企业管理器创建和删除UNIQUE约束（1）利用企业管理器创建UNIQUE约束如果要对XS表中的“身份证号码”列创建UNIQUE约束，以保证该列取值的唯一性，可按以下步骤进行：第1步选择XS表图标，右击，打开XS表设计器，在表设计器中右击，出现如图6.18所示的快捷菜单。第2步选择“属性”菜单项，出现属性界面，在此界面中选择“索引/键”选项卡，如图6.19所示。6.3.3实体完整性的实现第3步在图6.19的属性界面中选择“新建”，输入新建索引的名字或使用系统缺省名，在“列名”下拉表中选择“身份证号码”，并设置索引顺序，将“创建UNIQUE约束”单选按钮设置为选中状态，如图6.20所示。6.3.3实体完整性的实现第3步选择“关闭”按钮。（2）利用企业管理器删除UNIQUE约束进入如图6.20所示的属性窗口，在“选定的索引”下拉框中选择要删除的UNIQUE约束的索引名，再点击“删除”按钮即删除了指定的UNIQUE约束。6.3.3实体完整性的实现3．利用SQL命令创建及删除PRIMARYKEY约束或UNIQUE约束（1）创建表的同时创建PRIMARYKEY约束或UNIQUE约束语法格式：CREATETABLEtable_name /*指定表名*/(column_namedatatype /*定义字段*/[CONSTRAINTconstraint_name] /*约束名*/

NOTNULLPRIMARYKEY｜UNIQUE /*定义约束类型*/[CLUSTERED|NONCLUSTERED] /*定义约束的索引类型*/ [,

…n]

) /*n表示可定义多个字段*/(2)通过修改表创建PRIMARYKEY约束或UNIQUE约束创建PRIMARYKEY约束语法格式：ALTERTABLEtable_nameADD

[CONSTRAINTconstraint_name]

PRIMARYKEYCLUSTERED|NONCLUSTERED(column[,...n])6.3.3实体完整性的实现【例6.24】对XSCJ数据库中XS表的学号字段创建PRIMARYKEY约束，对身份证号码字段定义UNIQUE约束。USEXSCJCREATETABLEXS(学号char(6)NOTNULLCONSTRAINTXH_PKPRIMARYKEY,

姓名char(8)NOTNULL,

身份证号码char(20)CONSTRAINTSH_UKUNIQUE,

专业名char(10)NULL,

性别bitNOTNULL,

出生时间smalldatetimeNOTNULL,

总学分tinyintNULL,

备注textNULL,

入学日期datetimeCONSTRAINTdatedfltdefaultgetdate() /*定义默认值约束*/)6.3.3实体完整性的实现【例6.25】先在XSCJ数据库中创建表XS，然后通过修改表，对学号字段创建PRIMARYKEY约束，对身份证号码字段定义UNIQUE约束。

USEXSCJCREATETABLEXS( 学号char(6)NOTNULL,

姓名char(8)NOTNULL,

身份证号码char(20),

专业名char(10)NULL,

性别bitNOTNULL,

出生时间smalldatetimeNOTNULL,

总学分tinyintNULL,

备注textNULL,

入学日期datetimeCONSTRAINTdatedfltdefaultgetdate() /*定义默认值约束*/)GOALTERTABLEXSADD

CONSTRAINTXS_PKPRIMARYKEYCLUSTERED(学号)GOALTERTABLEXSADD

CONSTRAINTXS_UK

UNIQUENONCLUSTEREDGO6.3.3实体完整性的实现（3）删除PRIMARYKEY约束或UNIQUE约束语法格式：ALTERTABLEtable_nameDROPCONSTRAINTconstraint_name[,…n]【例6.26】删除前面例中创建的PRIMARYKEY约束和UNIQUE约束。ALTERTABLEXSDROP

CONSTRAINTXS_PK,XS_UKGO6.3.4参照完整性的实现1．利用企业管理器定义表间的参照关系例如：要建立XS表与XS_KC表之间的参照完整性，操作步骤如下：第1步按照前面所介绍的方法定义主表的主码（或唯一码）。在此，定义XS表中的学号字段为主码。第2步选择企业管理器目录树中XSCJ数据库目录下的“关系图”图标右击，出现如图6.21所示的快捷菜单。第3步选择菜单项“新建数据库关系图”，进入如图6.22所示的欢迎界面。6.3.4参照完整性的实现第4步选择欢迎界面中的“下一步”按钮，进入如图6.23所示“创建关系图向导”的界面，从可用表中选择要添加到关系图中的表，本例中选择了XS表和XS_KC表。第5步点击“下一步”按钮，并按提示进行后继操作，进入如图6.24所示的关系图界面。6.3.4参照完整性的实现第6步在关系图上，将鼠标指向主表的主键并拖动到从表。对于本例：将XS表中的学号字段拖动到从表XS_KC，出现如图6.25所示的关系设置界面。第7步退出图6.26的关系图界面，并根据提示，将关系图的有关信息存盘，即创建了主表与从表之间的参照关系。6.3.4参照完整性的实现2．利用企业管理器删除表间的参照关系第1步进入XS表设计器右击，出现一快捷菜单如图6.27所示课程类别。第2步选择菜单项“关系”，出现如图6.28所示的属性界面。第3步在图6.28的属性界面的关系下拉表中选择要删除的关系，然后点击“删除键”，选择“关闭”按钮。6.3.4参照完整性的实现3．利用SQL命令定义表间的参照关系前面已介绍了创建主码（PRMARYKEY约束）及唯一码（UNIQUE约束）的方法，在此将介绍通过SQL命令创建外码的方法。（1）创建表的同时定义外码约束语法格式：

CREATETABLEtable_name /*指定表名*/(column_namedatatype[FOREIGNKEY]REFERENCESref_table(ref_column)[,

…n]

) /*n表示可定义多个字段*/（2）通过修改表定义外码约束语法格式：ALTERTABLEtable_nameADD

[

CONSTRAINTconstraint_name]FOREIGNKEY

(column[,...n])REFERENCESref_table(ref_column[,...n])

6.3.4参照完整性的实现【例6.27】在XSCJ数据库中创建主表XS，XS.学号为主键，然后定义从表XS_KC，XS_KC.学号为外码。USEXSCJCREATETABLEXS( 学号char(6)NOTNULL CONSTRAINTXH_PKPRIMARYKEY,

姓名char(8)NOTNULL,

专业名char(10)NULL,

性别bitNOTNULL,

出生时间smalldatetimeNOTNULL,

总学分tinyintNULL,

备注textNULL)GOCREATETABLEXS_KC( 学号char(6)NOTNULLFOREIGNKEY

REFERENCESXS(学号),

课程号char(3)NOTNULL,

成绩smallint,

学分smallint)GO6.3.4参照完整性的实现【例6.28】假设XSCJ数据库中KC表为主表，KC.课程号字段已定义为主键。XS_KC表为从表，如下示例用于将XS_KC.课程号字段定义为外码。USEXSCJALTERTABLEXS_KC ADD

CONSTRAINTkc_foreign

FOREIGNKEY

(课程号)

REFERENCESKC(课程号)

GO4．利用SQL命令删除表间的参照关系删除表间的参照关系，实际上删除从表的外码约束即可。语法格式与前面其它约束删除的格式同。【例6.29】删除上例对XS_KC.课程号字段定义的外码约束。USEXSCJ ALTERTABLEXS_KC DROPCONSTRAINTkc_foreignGO7.2.1利用SQL命令创建触发器举例【例7.11】对于XSCJ数据库，如果在XS表中添加或更改数据，则将向客户端显示一条信息。/*使用带有提示消息的触发器*/USEXSCJIFEXISTS(SELECTnameFROMsysobjects WHEREname='reminder'ANDtype='TR')DROPTRIGGERreminderGOCREATETRIGGERreminderONXS FORINSERT,UPDATEASRAISERROR(4008,16,10)GO7.2.1利用SQL命令创建触发器使用触发器的限制使用触发器有下列限制：(1)CREATETRIGGER必须是批处理中的第一条语句，并且只能应用到一个表中。(2)触发器只能在当前的数据库中创建，但触发器可以引用当前数据库的外部对象。(3)如果指定触发器所有者名限定触发器，要以相同的方式限定表名。(4)在同一CREATETRIGGER语句中，可以为多种操作（如INSERT和UPDATE）定义相同的触发器操作。(5)一个表的外键在DELETE、UPDATE操作上定义了级联，不能在该表上定义INSTEADOFDELETE、INSTEADOFUPDATE触发器。7.2.1利用SQL命令创建触发器(7)在触发器内可以指定任意的SET语句，所选择的SET选项在触发器执行期间有效，并在触发器执行完后恢复到以前的设置。

(8)触发器中不允许包含以下T-SQL语句：CREATEDATABASE、ALTERDATABASE、LOADDATABASE、RESTOREDATABASE、DROPDATABASE、LOADLOG、RESTORELOG、DISKINIT、DISKRESIZE和RECONFIGURE(9)触发器不能返回任何结果，为了阻止从触发器返回结果，不要在触发器定义中包含SELECT语句或变量赋值。4．权限CREATETRIGGER权限默认授予定义触发器的表所有者、sysadmin固定服务器角色成员、db_owner和db_ddladmin固定数据库角色成员，并且不可转让。7.2.1利用SQL命令创建触发器【例7.12】在数据库XSCJ中创建一触发器，当向XS_KC表插入一记录时，检查该记录的学号在XS表是否存在，检查课程号在KC表中是否存在，若有一项为否，则不允许插入。USEXSCJIFEXISTS(SELECTnameFROMsysobjectsWHEREname='check_trig'ANDtype='TR')DROPTRIGGERcheck_trigGOCREATETRIGGERcheck_trig ONXS_KC FORINSERTASSELECT* FROMinserteda WHEREa.学号NOTIN(SELECTb.学号FROMXSb)ORa.课程号NOTIN(SELECTc.课程号FROMKCc)BEGIN RAISERROR('违背数据的一致性.',16,1) ROLLBACKTRANSACTIONEND7.2.2利用企业管理器创建触发器

步骤如下：第1步展开服务器组，然后展开服务器。第2步展开“数据库”文件夹，展开将定义触发器的表所属的数据库，然后单击“表”文件夹。第3步选择将在其上创建触发器的表右击，出现快捷菜单，选择“所有任务”菜单项下的“管理触发器”子菜单项，如图7.1所示。执行该命令后，进入如图7.2所示的界面。第4步在“名称”中，单击“新建”，在“文本”框中输入触发器文本。若要检查语法，单击“检查语法”命令。7.2.3触发器的修改1．利用SQL命令修改触发器语法格式：ALTERTRIGGERtrigger_nameON(table|view)[WITHENCRYPTION]{

{(FOR|AFTER|INSTEADOF){[DELETE][,][INSERT][,][UPDATE]}

[NOTFORREPLICATION]

AS

sql_statement[

...n]

}

|{(FOR|AFTER|INSTEADOF){[INSERT][,][UPDATE]}

[NOTFORREPLICATION]

AS

{IFUPDATE(column)

[{AND|OR}UPDA