第6章索引与数据完整性.ppt

第第6章章 索引与数据完整性索引与数据完整性6.1 索引索引6.2 数据完整性数据完整性6.1 索引索引6.1.1 索引的分类索引的分类1聚集索引聚集索引聚集索引将数据行的键值在表内排序并存储对应的数据记录，使得数据表物理顺序与索引顺序一致。2非聚集索引非聚集索引非聚集索引完全独立于数据行的结构。SQLServer2008也是按B树方式组织非聚集索引的，与聚集索引的不同之处在于：非聚集索引B树的叶节点不存放数据页信息，而是存放非聚集索引的键值，并且每个键值项都有指针指向包含该键值的数据行。6.1.2 索引的创建索引的创建1界面方式创建索引界面方式创建索引6.1.2 索引的创建索引的创建在“表设计器”窗口创建索引的方法如下。6.1.2 索引的创建索引的创建2利用利用SQL命令建立索引命令建立索引使用CREATEINDEX语句可以为表创建索引。语法格式：CREATEUNIQUE/*指定索引是否唯一*/CLUSTERED|NONCLUSTERED/*索引的组织方式*/INDEXindex_name/*索引名称*/ONdatabase_name.schema_name.|schema_name.table_or_view_name(columnASC|DESC,.n)/*索引定义的依据*/ONpartition_scheme_name(column_name)/*指定分区方案*/|filegroup_name/*指定索引文件所在的文件组*/|default;6.1.2 索引的创建索引的创建【例例6.1】为KCB表的“课程名”列创建索引。USEPXSCJGOCREATEINDEXkc_name_indONKCB(课程名)【例例6.2】根据KCB表的“课程号”列创建唯一聚集索引，因为指定了CLUSTERED，所以该索引将对磁盘上的数据进行物理排序。CREATEUNIQUECLUSTEREDINDEXkc_id_indONKCB(课程号)6.1.2 索引的创建索引的创建【例例6.3】根据CJB表的“学号”列和“课程号”列创建复合索引。CREATEINDEXCJB_indONCJB(学号,课程号)WITH(DROP_EXISTING=ON)【例例6.4】根据XSB表中的“总学分”列创建索引，例中使用了FILLFACTOR子句。CREATENONCLUSTEREDINDEXscore_indONXSB(总学分)WITHFILLFACTOR=60【例例6.5】根据XSB表中“学号”列创建唯一聚集索引。如果输入了重复的键，将忽略该INSERT或UPDATE语句。CREATEUNIQUECLUSTEREDINDEXxs_indONXSB(学号)WITHIGNORE_DUP_KEY6.1.3 重建索引重建索引语法格式：ALTERINDEXindex_name|ALLONdatabase_name.schema_name.|schema_name.table_or_view_nameREBUILD|DISABLE;例如，重建KCB表上的所有索引：USEPXSCJGOALTERINDEXALLONKCBREBUILD重建KCB表上的kc_name_id索引：ALTERINDEXkc_name_indONKCBREBUILD6.1.4 索引的删除索引的删除1通过图形界面方式删除索引通过图形界面方式删除索引启动SQLServerManagementStudio，在对象资源管理器中展开数据库“PXSCJ表dbo.XSB索引”，选择其中要删除的索引，单击鼠标右键，在弹出的快捷菜单上选择“删除”菜单项。在打开的“删除对象”窗口中单击“确定”按钮即可。2通过通过SQL命令删除索引命令删除索引从当前数据库中删除一个或多个索引。语法格式：DROPINDEXindex_nameONtable_or_view_name,.n|table_or_view_name.index_name,.n【例例6.6】删除PXSCJ数据库中表KCB的一个索引名为kc_name_ind的索引。IFEXISTS(SELECTnameFROMsysindexesWHEREname=kc_name_ind)DROPINDEXKCB.kc_name_ind6.2 数据完整性数据完整性6.2.1 数据完整性的分类数据完整性的分类1实体完整性实体完整性实体完整性又称为行的完整性，要求表中有一个主键，其值不能为空且能唯一地标识对应的记录。通过索引、UNIQUE约束、PRIMARYKEY约束或IDENTITY属性可实现数据的实体完整性。2域完整性域完整性域完整性又称为列完整性，指给定列输入的有效性。实现域完整性的方法有：限制类型（通过数据类型）、格式（通过CHECK约束和规则）或可能的取值范围（通过CHECK约束、DEFALUT定义、NOTNULL定义和规则）等。3参照完整性参照完整性参照完整性又称为引用完整性。参照完整性保证主表中的数据与从表（被参照表）中数据的一致性。在SQLServer2008中，参照完整性的实现是通过定义外键与主键之间或外键与唯一键之间的对应关系来实现的。6.2.1 数据完整性的分类数据完整性的分类码：即前面所说的关键字，又称为“键”，是能唯一标识表中记录的字段或字段组合。如果一个表有多个码，可选其中一个作为主键（主码），其余的称为候选键。外码：如果一个表中的一个字段或若干个字段的组合是另一个表的码，则称该字段或字段组合为该表的外码（外键）。6.2.1 数据完整性的分类数据完整性的分类例如，对于PXSCJ数据库中XSB表的每一个学号，在CJB表中都有相关的课程成绩记录，将XSB作为主表，“学号”字段定义为主键，CJB作为从表，表中的“学号”字段定义为外键，从而建立主表和从表之间的联系，实现参照完整性。XSB和CJB表的对应关系如表6.1和表6.2所示。6.2.2 实体完整性的实现实体完整性的实现1界面创建界面创建PRIMARY KEY约束约束（1）创建PRIMARYKEY约束。（2）删除PRIMARYKEY约束。2界面创建界面创建UNIQUE约束约束（1）创建UNIQUE约束。（2）删除UNIQUE约束。6.2.2 实体完整性的实现实体完整性的实现3命令创建命令创建PRIMARY KEY约束或约束或UNIQUE约束约束（1）在创建表的同时创建PRIMARYKEY约束或UNIQUE约束。语法格式：CREATETABLEtable_name(,n,.n)6.2.2 实体完整性的实现实体完整性的实现其中，为列的约束，为表的约束。:=/*定义列的约束*/CONSTRAINTconstraint_namePRIMARYKEY|UNIQUE/*定义主键与UNIQUE键*/CLUSTERED|NONCLUSTERED/*定义约束的索引类型*/ONpartition_scheme_name(partition_column_name)|filegroup|default|FOREIGNKEY/*定义外键*/|CHECK(logical_expression)/*定义CHECK约束*/:=/*定义表的约束*/CONSTRAINTconstraint_namePRIMARYKEY|UNIQUECLUSTERED|NONCLUSTERED(columnASC|DESC,.n)/*定义表的约束时需要指定列*/ONpartition_scheme_name(partition_column_name)|filegroup|default|FOREIGNKEY(column,.n)|CHECK(logical_expression)6.2.2 实体完整性的实现实体完整性的实现【例例6.7】创建XSB1表（假设XSB1表未创建），并对“学号”字段创建PRIMARYKEY约束，对“姓名”字段定义UNIQUE约束。USEPXSCJGOCREATETABLEXSB1(学号char(6)NOTNULLCONSTRAINTXH_PKPRIMARYKEY,姓名char(8)NOTNULLCONSTRAINTXM_UKUNIQUE,性别bitNOTNULLDEFAULT1,出生时间dateNOTNULL,专业char(12)NULL,总学分 intNULL,备注varchar(500)NULL)6.2.2 实体完整性的实现实体完整性的实现【例例6.8】创建一个course_name表来记录每门课程的学生学号、姓名、课程号、学分和毕业日期。其中，学号、课程号和毕业日期构成复合主键，学分为唯一键。CREATETABLEcourse_name(学号varchar(6)NOTNULL,姓名varchar(8)NOTNULL,毕业日期dateNOTNULL,课程号varchar(3),学分tinyint,PRIMARYKEY(学号,课程号,毕业日期),CONSTRAINTXF_UKUNIQUE(学分)6.2.2 实体完整性的实现实体完整性的实现（2）通过修改表创建PRIMARYKEY约束或UNIQUE约束。【例例6.9】修改例6.7中的XSB1表，向其中添加一个“身份证号码”字段，对该字段定义UNIQUE约束。对“出生时间”字段定义UNIQUE约束。ALTERTABLEXSB1ADD身份证号码char(20)CONSTRAINTSF_UKUNIQUENONCLUSTERED(身份证号码)GOALTERTABLEXSB1ADDCONSTRAINTCJSJ_UKUNIQUENONCLUSTERED(出生时间)6.2.2 实体完整性的实现实体完整性的实现（3）删除PRIMARYKEY约束或UNIQUE约束。删除PRIMARYKEY约束或UNIQUE约束需要使用ALTERTABLE的DROP子句。语法格式：ALTERTABLEtable_nameDROPCONSTRAINTconstraint_name,.n【例例6.10】删除例6.7中创建的PRIMARYKEY约束和UNIQUE约束。ALTERTABLEXSB1DROPCONSTRAINTXH_PK,XM_UK6.2.3 域完整性的实现域完整性的实现1CHECK约束约束CHECK约束实际上是字段输入内容的验证规则，表示一个字段的输入内容必须满足CHECK约束的条件，若不满足，则数据无法正常输入。（1）通过图形向导方式创建与删除CHECK约束。6.2.3 域完整性的实现域完整性的实现（2）利用SQL语句在创建表时创建CHECK约束。在创建表时可以使用CHECK约束表达式来定义CHECK约束，CHECK约束表达式语法格式如下：CHECK(logical_expression)【例例6.11】创建一个表student，只考虑“学号”和“性别”两列，性别只能包含男或女。CREATETABLEstudent(学号char(6)NOTNULL,性别char(1)NOTNULLCHECK(性别IN(男,女)6.2.3 域完整性的实现域完整性的实现【例例6.12】创建一个表student1，只考虑“学号”和“出生日期”两列，出生日期必须大于1980年1月1日，并命名CHECK约束。CREATETABLEstudent1(学号char(6)NOTNULL,出生时间datetimeNOTNULL,CONSTRAINTDF_student1_cjsjCHECK(出生时间1980-01-01)【例例6.13】创建表student2，有“学号”、“最好成绩”和“平均成绩”三列，要求最好成绩必须大于平均成绩。CREATETABLEstudent2(学号char(6)NOTNULL,最好成绩INTNOTNULL,平均成绩INTNOTNULL,CHECK(最好成绩平均成绩)6.2.3 域完整性的实现域完整性的实现（3）利用SQL语句在修改表时创建CHECK约束。定义CHECK约束的语法格式为ALTERTABLEtable_nameWITHCHECK|NOCHECKADDCONSTRAINTconstraint_nameCHECK(logical_expression)【例例6.14】通过修改PXSCJ数据库的CJB表，增加“成绩”字段的CHECK约束。USEPXSCJGOALTERTABLECJBADDCONSTRAINTcj_constraintCHECK(成绩=0AND成绩=100)6.2.3 域完整性的实现域完整性的实现（4）利用SQL语句删除CHECK约束。使用ALTERTABLE语句的DROP子句可以删除CHECK约束。语法格式：ALTERTABLEtable_nameDROPCONSTRAINTcheck_name【例例6.15】删除CJB表“成绩”字段的CHECK约束。ALTERTABLECJBDROPCONSTRAINTcj_constraint6.2.3 域完整性的实现域完整性的实现2规则对象规则对象（1）规则对象的定义。语法格式：CREATERULEschema_name.rule_nameAScondition_expression;（2）将规则对象绑定到用户定义数据类型或列。将规则对象绑定到列或用户定义数据类型中可以使用系统存储过程sp_bindrule。语法格式：sp_bindrulerulename=rule,objname=object_name,futureonly=futureonly_flag6.2.3 域完整性的实现域完整性的实现【例例6.16】如下程序创建一个规则，并绑定到表KCB的课程号列，用于限制课程号的输入范围。USEPXSCJGOCREATERULEkc_ruleASrangelike1-50-90-9GOEXECsp_bindrulekc_rule,KCB.课程号/*执行存储过程使用EXEC命令*/GO6.2.3 域完整性的实现域完整性的实现（3）规则对象的删除。在删除规则对象前，首先应使用系统存储过程sp_unbindrule解除被绑定对象与规则对象之间的绑定关系，使用格式如下：sp_unbindruleobjname=object_name,futureonly=futureonly_flag在解除列或自定义类型与规则对象之间的绑定关系后，就可以删除规则对象了。语法格式：DROPRULEschema_name.rule_name,.n;【例例6.17】解除规则kc_rule与列或用户定义类型的绑定关系，并删除规则对象kc_rule。EXECsp_unbindruleKCB.课程号EXECsp_unbindrulecourse_numGODROPRULEkc_rule6.2.4 参照完整性的实现参照完整性的实现1界面定义表间的参照关系界面定义表间的参照关系例如，要实现XSB表与CJB表之间的参照完整性，操作步骤如下。第1步：按照前面所介绍的方法定义主表的主键。第2步：启动SQLServerManagementStudio，在对象资源管理器中展开“数据库PXSCJ”，选择“数据库关系图”，右击鼠标，在出现的快捷菜单中选择“新建数据库关系图”菜单项，打开“添加表”窗口。第3步：在出现的“添加表”窗口中选择要添加的表，本例中选择了表XSB和表CJB。单击“添加”按钮完成表的添加，之后单击“关闭”按钮退出窗口。第4步：在“数据库关系图设计”窗口将鼠标指向主表的主键，并拖动到从表，即将XSB表中的“学号”字段拖动到从表CJB中的“学号”字段。6.2.4 参照完整性的实现参照完整性的实现第5步：在弹出的“表和列”窗口中输入关系名、设置主键表和列名，如图6.5所示，单击“表和列”窗口中的“确定”按钮，再单击“外键关系”窗口中的“确认”按钮，进入如图6.6所示的界面。6.2.4 参照完整性的实现参照完整性的实现6.2.4 参照完整性的实现参照完整性的实现如果要在图6.6的基础上再添加KCB表并建立相应的参照完整性关系，可以使用以下步骤：右击图6.6中的空白区域，选择“添加表”菜单项，在随后弹出的“添加表”窗口中添加KCB表，之后定义CJB表和KCB表之间的参照关系，结果如图6.7所示。6.2.4 参照完整性的实现参照完整性的实现2图形向导删除表间的参照关系图形向导删除表间的参照关系如果要删除前面建立的XSB表与CJB表之间的参照关系，可按以下步骤进行。第1步：在PXSCJ数据库的“数据库关系图”目录下选择要修改的关系图，如Diagram_0，右击鼠标，在弹出的快捷菜单中选择“修改”菜单项，打开“数据库关系图设计”窗口。第2步：在“数据库关系图设计”窗口中，选择已经建立的“关系”，单击鼠标右键，选择“从数据库中删除关系”，如图6.8所示。6.2.4 参照完整性的实现参照完整性的实现3命令定义表间的参照关系命令定义表间的参照关系（1）创建表的同时定义外键约束。语法格式在6.2.2小节中已经列出，这里只列出定义外键部分的语法。CREATETABLEtable_name/*指定表名*/(CONSTRAINTconstraint_nameFOREIGNKEY(column,.n)其中，:=REFERENCESreferenced_table_name(ref_column,.n)ONDELETENOACTION|CASCADE|SETNULL|SETDEFAULTONUPDATENOACTION|CASCADE|SETNULL|SETDEFAULT6.2.4 参照完整性的实现参照完整性的实现【例例6.18】创建stu表，要求stu表中所有的学生学号都必须出现在XSB表中，假设已经使用“学号”列作为主键创建了XSB表。CREATETABLEstu(学号char(6)NOTNULLFOREIGNKEY(学号)REFERENCESXSB(学号),姓名char(8)NOTNULL,出生时间datetimeNULL)【例例6.19】创建point表，要求表中所有的学号、课程号组合都必须出现在CJB表中。CREATETABLEpoint(学号char(6)NOTNULL,课程号char(3)NOTNULL,成绩intNULL,CONSTRAINTFK_pointFOREIGNKEY(学号,课程号)REFERENCESCJB(学号,课程号)ONDELETENOACTION)6.2.4 参照完整性的实现参照完整性的实现（2）通过修改表定义外键约束。使用ALTERTABLE语句的ADD子句也可以定义外键约束，语法格式与定义其他约束类似，这里不再列出。【例例6.20】假设KCB表为主表，KCB的“课程号”字段已定义为主键。CJB表为从表，如下示例用于将CJB表的“课程号”字段定义为外键。ALTERTABLECJBADDCONSTRAINTkc_foreignFOREIGNKEY(课程号)REFERENCESKCB(课程号)6.2.4 参照完整性的实现参照完整性的实现4命令删除表间的参照关系命令删除表间的参照关系语法格式与前面其他约束删除的格式类似。【例例6.21】删除上例对CJB表的“课程号”字段定义的外键约束。ALTERTABLECJBDROPCONSTRAINTkc_foreign