搜索
第6章索引与数据完整性6.1索引6.2数据完整性6.1索引6.1.1索引的分类1．聚集索引聚集索引将数据行的键值在表内排序并存储对应的数据记录，使得数据表物理顺序与索引顺序一致。2．非聚集索引非聚集索引完全独立于数据行的结构。SQLServer2008也是按B树方式组织非聚集索引的，与聚集索引的不同之处在于：非聚集索引B树的叶节点不存放数据页信息，而是存放非聚集索引的键值，并且每个键值项都有指针指向包含该键值的数据行。6.1.2索引的创建1．界面方式创建索引在“表设计器”窗口创建索引的方法如下。2．利用SQL命令建立索引使用CREATEINDEX语句可以为表创建索引。语法格式：CREATE[UNIQUE]/*指定索引是否唯一*/[CLUSTERED|NONCLUSTERED]/*索引的组织方式*/INDEXindex_name/*索引名称*/ON{[database_name.[schema_name].|schema_name.]table_or_view_name}(column[ASC|DESC][,...n])/*索引定义的依据*/[ON{partition_scheme_name(column_name)/*指定分区方案*/|filegroup_name/*指定索引文件所在的文件组*/|default}][;]【例6.1】为KCB表的“课程名”列创建索引。USEPXSCJGOCREATEINDEXkc_name_indONKCB(课程名)【例6.2】根据KCB表的“课程号”列创建唯一聚集索引，因为指定了CLUSTERED，所以该索引将对磁盘上的数据进行物理排序。CREATEUNIQUECLUSTEREDINDEXkc_id_indONKCB(课程号)【例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重建索引语法格式：ALTERINDEX{index_name|ALL}ON[database_name.[schema_name].|schema_name.]table_or_view_name{REBUILD|DISABLE}[;]例如，重建KCB表上的所有索引：USEPXSCJGOALTERINDEXALLONKCBREBUILD重建KCB表上的kc_name_id索引：ALTERINDEXkc_name_indONKCBREBUILD6.1.4索引的删除1．通过图形界面方式删除索引启动SQLServerManagementStudio，在对象资源管理器中展开数据库“PXSCJ→表→dbo.XSB→索引”，选择其中要删除的索引，单击鼠标右键，在弹出的快捷菜单上选择“删除”菜单项。在打开的“删除对象”窗口中单击“确定”按钮即可。2．通过SQL命令删除索引从当前数据库中删除一个或多个索引。语法格式：DROPINDEX{index_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中，参照完整性的实现是通过定义外键与主键之间或外键与唯一键之间的对应关系来实现的。码：即前面所说的关键字，又称为“键”，是能唯一标识表中记录的字段或字段组合。如果一个表有多个码，可选其中一个作为主键（主码），其余的称为候选键。外码：如果一个表中的一个字段或若干个字段的组合是另一个表的码，则称该字段或字段组合为该表的外码（外键）。例如，对于PXSCJ数据库中XSB表的每一个学号，在CJB表中都有相关的课程成绩记录，将XSB作为主表，“学号”字段定义为主键，CJB作为从表，表中的“学号”字段定义为外键，从而建立主表和从表之间的联系，实现参照完整性。XSB和CJB表的对应关系如表6.1和表6.2所示。6.2.2实体完整性的实现1．界面创建PRIMARYKEY约束（1）创建PRIMARYKEY约束。（2）删除PRIMARYKEY约束。2．界面创建UNIQUE约束（1）创建UNIQUE约束。（2）删除UNIQUE约束。3．命令创建PRIMARYKEY约束或UNIQUE约束（1）在创建表的同时创建PRIMARYKEY约束或UNIQUE约束。语法格式：CREATETABLEtable_name({列定义column_constraint}[,…n][table_constraint][,...n])其中，column_constraint为列的约束，table_constraint为表的约束。column_constraint::=/*定义列的约束*/[CONSTRAINTconstraint_name]{{PRIMARYKEY|UNIQUE}/*定义主键与UNIQUE键*/[CLUSTERED|NONCLUSTERED]/*定义约束的索引类型*/[ON{partition_scheme_name(partition_column_name)|filegroup|default}]|[FOREIGNKEY]reference_definition/*定义外键*/|CHECK(logical_expression)/*定义CHECK约束*/}table_constraint::=/*定义表的约束*/[CONSTRAINTconstraint_name]{{PRIMARYKEY|UNIQUE}[CLUSTERED|NONCLUSTERED](column[ASC|DESC][,...n])/*定义表的约束时需要指定列*/[ON{partition_scheme_name(partition_column_name)|filegroup|default}]|FOREIGNKEY(column[,...n])reference_definition|CHECK(logical_expression)}【例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.8】创建一个course_name表来记录每门课程的学生学号、姓名、课程号、学分和毕业日期。其中，学号、课程号和毕业日期构成复合主键，学分为唯一键。CREATETABLEcourse_name(学号varchar(6)NOTNULL,姓名varchar(8)NOTNULL,毕业日期dateNOTNULL,课程号varchar(3),学分tinyint,PRIMARYKEY(学号,课程号,毕业日期),CONSTRAINTXF_UKUNIQUE(学分))【例6.9】修改例6.7中的XSB1表，向其中添加一个“身份证号码”字段，对该字段定义UNIQUE约束。对“出生时间”字段定义UNIQUE约束。ALTERTABLEXSB1ADD身份证号码char(20)CONSTRAINTSF_UKUNIQUENONCLUSTERED(身份证号码)GOALTERTABLEXSB1ADDCONSTRAINTCJSJ_UKUNIQUENONCLUSTERED(出生时间)（2）通过修改表创建PRIMARYKEY约束或UNIQUE约束。删除PRIMARYKEY约束或UNIQUE约束需要使用ALTERTABLE的DROP子句。语法格式：ALTERTABLEtable_nameDROPCONSTRAINTconstraint_name[,...n]【例6.10】删除例6.7中创建的PRIMARYKEY约束和UNIQUE约束。ALTERTABLEXSB1DROPCONSTRAINTXH_PK,XM_UK（3）删除PRIMARYKEY约束或UNIQUE约束。6.2.3域完整性的实现1．CHECK约束CHECK约束实际上是字段输入内容的验证规则，表示一个字段的输入内容必须满足CHECK约束的条件，若不满足，则数据无法正常输入。（1）通过图形向导方式创建与删除CHECK约束。（2）利用SQL语句在创建表时创建CHECK约束。在创建表时可以使用CHECK约束表达式来定义CHECK约束，CHECK约束表达式语法格式如下：CHECK(logical_expression)【例6.11】创建一个表student，只考虑“学号”和“性别”两列，性别只能包含男或女。CREATETABLEstudent(学号char(6)NOTNULL,性别char(1)NOTNULLCHECK(性别IN('男','女')))【例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(最好成绩平均成绩))定义CHECK约束的语法格式为：ALTERTABLEtable_name[WITH{CHECK|NOCHECK}]ADD[column_definition][CONSTRAINTconstraint_name]CHECK(logical_expression)【例6.14】通过修改PXSCJ数据库的CJB表，增加“成绩”字段的CHECK约束。USEPXSCJGOALTERTABLECJBADDCONSTRAINTcj_constraintCHECK(成绩=0AND成绩=100)（3）利用SQL语句在修改表时创建CHECK约束。（4）利用SQL语句删除CHECK约束