数据完整性和索引

第三章数据库完整性和索引3.1数据完整性的概述一、数据完整性的定义数据完整性（DataIntegrity）是指数据的精确性和可靠性。定义数据完整性的目的：

1、防止数据库中存在不符合语义规定的数据；

2、防止错误信息的输入和输出而造成无效操作；3.1数据完整性的概述二、数据完整性分类1、实体完整性（EntityIntegrity）

将数据表中的每一行看作一个实体，所有行具有非空且没有重复的标识字段。实现手段：唯一索引、主键约束、唯一约束、列的Identity属性限制3.1数据完整性的概述二、数据完整性分类

2、域完整性（DomainIntegrity）

域（或列）完整性是指定列指定一组有效的值并决定是否可为空值。实现手段：

1.数据类型或是NOTNULL；

2.检查约束和规则；

3.外键约束、默认值等规则限制。3.1数据完整性的概述二、数据完整性分类3、参照完整性（ReferentialIntegrity）

参照完整性确保主键（在被引用表中）和外键（在引用表中）之间的关系得到保持。

例如修改父表中关键字值后，子表关键字值未做相应改变；删除父表的某记录后，子表的相应记录未删除，致使这些记录称为孤立记录；对于子表插入的记录，父表中没有相应关键字值的记录；3.2约束学号姓名专业性别出生时间990201王俊鹏计算机男1983/10/1990202王巍计算机女1987/9/5990203李海燕计算机女1985/4/3学号课程号成绩学分990201101804990201102902990202201784990202202954990203101882主键外键被引用表引用表学生信息表学生课程表3.1数据完整性的概述二、数据完整性分类4、用户定义的完整性（User-definedIntegrity）用户自定义完整性指针对某一具体关系数据库的约束条件，它反映某一具体应用所涉及的数据必须满足的语义要求。

3.1数据完整性的概述二、实施数据完整性的方法1、用定义约束实现数据完整性通过针对表和字段定义声明的约束，将数据完整性成为数据定义的一部分，使用约束实现数据完整性。2、用编写程序实现数据完整性可以使用存储过程或触发器实施程序化数据完整性。3、选取数据完整性的实施方法由于约束在SQLSERVER的可执行部分有一段代码路径,执行速度比其它方式快，所以应优先选用约束。需要在多个表之间保持数据的一致性，可选择使用约束中的主键和外键约束；需要特别复杂的完整性检验,则使用触发器和存储过程

3.2约束一、

主键约束（PrimaryKeyConstraint）1、主键约束：主键是能够唯一标识表中某一行的属性或属性组。语法：[CONSTRAINT约束名]PRIMARYKEY[CLUSTERED|NONCLUSTERED]{(列[,...n])}应用PRIMARYKEY约束的注意事项

1、每张表只能有一个PRIMARYKEY约束2、输入的值必须是惟一的3、不允许空值4、将在指定列上创建惟一索引PRIMARYKEY约束（续）PRIMARYKEY约束示例在Customers表上创建PRIMARYKEY约束，指明表的主键值是CustomerID，并且创建非聚集索引以强制约束4.4.3PRIMARYKEY约束USENorthwindALTERTABLEdbo.Customers

ADDCONSTRAINTPK_CustomersPRIMARYKEYNONCLUSTERED(CustomerID)排序规则指定表示每个字符的位模式以及存储和比较字符所使用的规则。

Collate是一个子句，可应用于数据库定义或列定义以定义排序规则，或应用于字符串表达式以应用排序规则投影。语法：Collatecollation_name

排序规则排序规则名称由两部份构成，前半部份是指本排序规则所支持的字符集。

如：

Chinese_PRC_CS_AI_WS

前半部份：指UNICODE字符集，Chinese_PRC_指针对大陆简体字UNICODE的排序规则。

排序规则的后半部份即后缀含义：

_BIN二进制排序

_CI(CS)是否区分大小写，CI不区分，CS区分

_AI(AS)是否区分重音，AI不区分，AS区分

区分大小写:如果想让比较将大写字母和小写字母视为不等，请选择该选项。

区分重音:如果想让比较将重音和非重音字母视为不等，请选择该选项。如果选择该选项,比较还将重音不同的字母视为不等。3.2约束二、外键约束（ForeignKeyConstraint）1、外键约束：定义外键的目的是在于限制可以保存在外键所在表中的数据。外键由一个列或多个列的组合构成，用来实现两张表之间的数据联系，维护两个表间的一致性关系。语法：[CONSTRAINT约束名][FOREIGNKEY][(列[,…n])]

REFERENCES引用表

[(引用列

[,…n])]3.2约束学号姓名专业性别出生时间990201王俊鹏计算机男1983/10/1990202王巍计算机女1987/9/5990203李海燕计算机女1985/4/3学号课程号成绩学分990201101804990201102902990202201784990202202954990203101882主键外键主表从表学生信息表学生课程表2、外键约束注意要点创建外键约束的前提是完成主表的主键定义；外键约束允许存在NULL值；外键约束不能用于临时表和不同的数据库之间。FOREIGNKEY约束（续）FOREIGNKEY约束的示例使用FOREIGNKEY约束，确保Orders表中的客户标识与Customers表中的有效的客户标识相关联USENorthwindALTERTABLEdbo.Orders

ADDCONSTRAINTFK_Orders_CustomersFOREIGNKEY(CustomerID)REFERENCESdbo.Customers(CustomerID)3.2约束三、惟一约束（UniqueConstraint）1、惟一约束：

在表指定的列或组列上不允许两行是具有重复值时，则需要该列或组列上指定UNIQUE完整性约束。在UNIQUE约束定义中的列或组列称为唯一码。2、主键与UNIQUE约束的区别主要为：

UNIQUE约束，主要用在非主键的一列或多列上要求数据惟一的情况。

UNIQUE约束，允许该列上存在NULL值，而主键不允许。可在表中设置多个UNIQUE约束，而在只能设置一个主键约束。应用UNIQUE约束的注意事项应用UNIQUE约束的注意事项允许一个空值在一个表上允许多个UNIQUE约束可在一个或者多个列上定义是通过一个惟一索引强制约束的3.2约束3、创建惟一约束的方法：

（1）使用SSMS创建惟一约束“对象资源管理器”右击表名称，选择快捷菜单中的“修改”，进入表结构修改状态。单击工具栏处“管理索引和键”按钮（2）使用Transact-SQL语言创建惟一约束方法一：在创建表结构的同时创建惟一约束，语法格式为：

CREATETABLE表名称(字段定义，CONSTRAINT约束名UNIQUE(列名))方法二：在已经建立结构的表定义中添加惟一约束，语法格式：ALTERTABLE表名称ADDCONSTRAINT约束名UNIQUE(字段名)3.2约束四、检查约束(CheckConstraint)1、检查约束：

限制输入到一列或多列中的可能值,它通过检查输入表列的数据的值来维护值域的完整性。注意：1、一个表可以定义多个检查约束，但每个建库命令只能为每列定义一个检查约束；2、在每次插入记录或是修改记录时，检查约束会做相应检查；3、自动编号字段不能应用检查约束。应用CHECK约束的注意事项在每次执行INSERT或者UPDATE语句的时候校验数据值可以引用同表中的其他列不能包含子查询列级CHECK约束可省略名字，让系统自动生成表达式可以用AND以及OR连接以表示复杂逻辑表级CHECK约束可引用同行中的多列，但不能跨行或跨表可用CHECK约束来防止输入NULL值，例如CHECK(输入值

ISNOTNULL)3.2约束（2）使用Transact-SQL语言创建方法一：建立表结构同时创建检查约束，语法格式：CREETETABLE表名称(字段定义，CONSTRAINT约束名CHECK

逻辑表达式)

方法二：表建立后增加检查约束，语法格式如下：ALTERTABLE表名称ADDCONSTRAINT约束名CHECK

逻辑表达式3.2约束【例3.11】在CJGL数据库中为XS表字段XB（性别）设置检查约束，要求XB字段值只能为“男”和“女”。

ALTERTABLEXSADDCONSTRAINTCK_XBCHECK性别IN(‘男’,’女’)禁用约束当在表上添加约束的时候，我们可以禁用对已有数据的约束检查

语法：ALTERTABLE表名[WITHCHECKWITHNOCHECK]ADDCONSTRAINT约束名[FOREIGNKEY][(column[,…n])]REFERENCES引用表

[(引用列[,…n])][CHECK(搜索条件)]

禁用现有数据上的约束检查（续）对已有数据禁用约束检查的注意事项只能禁用CHECK和FOREIGNKEY约束当为一个已有数据的表添加CHECK或FOREIGNKEY约束的时候，使用WITHNOCHECK选项来禁用对已有数据的约束检查当现有数据不再变化的时候，使用WITHNOCHECK选项。若数据被更新，则它的新值必须符合CHECK约束确定禁用约束检查是恰当的。在决定添加约束前，可修改现有数据在加载新数据时禁用约束检查为了避免约束检查的开销，有时候可能希望禁用约束已经确保数据符合约束数据并不符合约束，但稍后可以更改其值并重用约束语法：ALTERTABLE表名{CHECK|NOCHECK}CONSTRAINT{ALL|约束名[,...n]}查看约束是启用还是禁用的状态系统存储过程sp_help系统函数OBJECTPROPERTY的CnstIsDisabled

属性3.2约束五、默认值约束（DefaultConstraint）1、默认值约束：可用于给列赋予一个常量值，用户在插入新的数据行时,系统将默认该列以常量值为指定数据。注意：默认值可以是常量、函数、空值（NULL）等。2、创建默认值约束的方法（1）使用SSMS创建默认值约束（2）使用Transact-SQL语言DEFAULT约束如果一个列的值在INSERT语句中没有指定，DEFAULT约束将自动输入一个值，可以是预先指定的常量、NULL或者一个系统函数运行时的值

语法：[CONSTRAINT约束名]DEFAULT约束表达式

创建DEFAULT约束的两种方法创建一个默认（CREATEDEFAULT），然后使用存储过程sp_bindefault

将默认绑定到一个列CREATETABLE或ALTERTABLE时使用DEFAUTL约束应用DEFAULT约束的注意事项DEFAULT约束创建时将检查表中的现存数据DEFAULT约束只对INSERT语句有效每列只能定义一个DEFAULT约束DEFAULT约束使用注意事项有可能会和CHECK约束冲突为具有PRIMARYKEY或UNIQUE约束的列指定默认值是没有意义的常量值外面可以加或者不加括号，字符或者日期常量必须加上单引号或双引号3.2约束六、约束管理1、查看约束SP_HELPCONSTRAINT表名称2、删除约束ALTERTABLE表名称

DROPCONSTRAINT约束名决定使用何种约束完整性类型约束类型描述域DEFAULT如果在INSERT语句中未显式提供

值，则指定为列提供的值CHECK指定列中可接受的数据值实体PRIMARYKEY惟一标识每一列，确保用户没有输入重复的值。同时创建一个索引以增强性能。不允许空值UNIQUE确保在非主键列中不输入重复值，并创建一个索引以增强性能。允许空值引用FOREIGNKEY定义一列或多列的值与同表或其他表中主键的值匹配CHECK基于同表中其他列的值，指定列中可接受的数据值使用约束的注意事项可直接在表上创建、更改和删除约束，而不必删除并重建表SQLServer里的约束只是“最后防线”当给一个表添加约束的时候，SQLServer将检查现有数据是否违反约束建议创建约束的时候指定名称，否则系统将为约束自动产生一个复杂的名称名称必须惟一，且符合SQLServer标识符的规则查看约束的信息系统存储过程：sp_helpconstraint、sp_help创建约束使用CREATETABLE或者ALTERTABLECREATETABLE是在创建表时创建约束ALTERTABLE是在一个已有的表上创建约束可以添加约束到已有数据的表上可添加单列或多列约束若约束应用于单列，称为列级约束若约束引用了多列，称为表级约束，即使它并没有引用表中的所有列3.3索引一、索引的作用与类型1、索引的作用索引是一个再表或视图上创建的对象，当用户查询索引字段时，它可以快速实施数据检索操作。借助索引，执行查询时不必扫描整个表就能够快速找到所需要的数据。注意：（1）可以在那些经常被用来查询的列上建立索引，以提高查找效率。【例子】在Student表中可以分别建立基于学号、姓名的索引（2）索引将占用磁盘空间，并且降低添加、删除和更新行的速度，所以要慎用！货号货名规格3002CPU处理器SY88001002计算机LX1003计算机FZ1001计算机LC2002显示器172001显示器153001CPU处理器P44001内存储器2564002内存储器512索引编号指针地址100141002210033200162002530017300214001840029表与索引示例索引的类型2、索引的类型（1）簇索引（CLUSTEREDINDEX）簇索引又称聚集索引。簇索引在数据表中按照索引列的排序规则排列数据的物理顺序。（2）非簇索引（NONCLUSTEREDINDEX）非簇索引是具有与表的数据完全分离的结构，它不会改变行的物理存储顺序，但是它是由数据行指针和一个索引值构成的。（3）唯一索引（UNIQUEINDEX）唯—索引可以确保所有数据行中任意两行的被索引列不包括NULL在内的重复值。每个表只能有一个簇索引，因为一个表中的记录只能以一种物理顺序存放。但是，一个表可以有不止一个非簇索引。

从建立了簇索引的表中取出数据要比建立了非聚簇索引的表快。非簇索引需要大量的硬盘空间和内存。

非簇索引可以提高从表中取数据的速度，它也会降低向表中插入和更新数据的