《《设计数据完整性》PPT课件.ppt》由会员分享，可在线阅读，更多相关《《设计数据完整性》PPT课件.ppt（70页珍藏版）》请在金锄头文库上搜索。
1、第十五章第十五章设计数据完整性设计数据完整性主要内容主要内容1、数据完整性的基础知识、数据完整性的基础知识2、使用约束实施数据完整性、使用约束实施数据完整性3、使用默认值实施数据完整性、使用默认值实施数据完整性4、使用规则实施数据完整性、使用规则实施数据完整性nSQL数数据据库库不不只只是是存存储储数数据据，还还必必须须保保证证所所存存储储的的数数据据是是正正确确的的。必必须须提提供供一一种种机机制制，来来检检查查数数据据是是否否满满足足规规定定的的条条件件，以以保保证证数数据据正正确确，避避免免不不合合语语义义的的错错误误数数据据的的输输入入和和输输出出。SQL提提供供了了大大量量的的完完整
2、整性性约约束束，保保证证数数据据库库表表中中数数据据的的完完整整性性。本本章章将将主主要要讨讨论论各各种种类类型型的的约约束束及及其其在在SQL数数据据库库中中的的应应用用 数据完整性基础数据完整性基础数数据据完完整整性性：是是衡衡量量数数据据库库中中数数据据质质量量好好坏坏的的一一种种标标志志，是是确确保保数数据据库库中中数数据据一一致致、正正确确以以及及符符合合企企业业规规则则的的一一种种思思想想，是是使使无无序序的的数数据据条条理理化化，确确保保正确的数据存放在正确位置的一种手段。正确的数据存放在正确位置的一种手段。完整性要求的数据具有以下完整性要求的数据具有以下3个特点：个特点：1)数
3、数据据的的值值正正确确无无误误.数数据据类类型型要要正正确确,数数据据的的值值必必须须存在于正确的范围内存在于正确的范围内.2)数据的存在必须确保同一表格数据之间的和谐关系数据的存在必须确保同一表格数据之间的和谐关系 例如例如:一个表中不允许有相同的实体存在一个表中不允许有相同的实体存在.3)数据的存在必须确保不同表格数据之间的和谐关系数据的存在必须确保不同表格数据之间的和谐关系主主键键和和外外键键的的关关系系,外外键键所所在在表表中中的的值值必必须须在在主主键键所所在表中存在，如果不存在就失去意义在表中存在，如果不存在就失去意义.14.1 完整性约束简介n完完整整性性约约束束简简称称约约束束
4、，是是关关系系数数据据库库中中的的对对象象，用用以以存存放放插插入入到到一一个个表表某某一一列列数数据据的的规规则则。在在SQL中中有多种不同类型的约束。有多种不同类型的约束。14.1.1 数据的完整性n约约束束是是用用来来确确保保数数据据的的准准确确性性和和一一致致性性。数数据据的的完完整整性性就就是是对对数数据据的的准准确确性性和和一一致致性性的的一一种种保保证证。数数据据完完整整性性（Data Integrity）是是指指数数据据的精确（的精确（Accuracy）和可靠性（）和可靠性（Reliability）。）。n数据完整性分为以下四类。数据完整性分为以下四类。n实体完整性：规定表的每
5、一行在表中是惟一的实体。实体完整性：规定表的每一行在表中是惟一的实体。n域域完完整整性性：是是指指表表中中的的列列必必须须满满足足某某种种特特定定的的数数据据类类型型或或约约束束，其中约束又包括取值范围、精度等规定。其中约束又包括取值范围、精度等规定。n参参照照完完整整性性：是是指指两两个个表表的的主主关关键键字字和和外外关关键键字字的的数数据据应应一一致致，保保证证了了表表之之间间的的数数据据的的一一致致性性，防防止止了了数数据据丢丢失失或或无无意意义义的的数数据据在在数数据库中扩散。据库中扩散。n用用户户定定义义的的完完整整性性：不不同同的的关关系系数数据据库库系系统统根根据据其其应应用用
6、环环境境的的不不同同，往往往往还还需需要要一一些些特特殊殊的的约约束束条条件件。用用户户定定义义的的完完整整性性即即是是针针对对某某个个特特定定关关系系数数据据库库的的约约束束条条件件，它它反反映映某某一一具具体体应应用用必必须须满满足足的的语语义义要求。要求。实施数据完整性的途径实施数据完整性的途径实实体体完完整整性性：Primary key 、unique 、unique 、identity column值值域域完完整整性性：default、check、Foreign key、not null 引引用用（参参照照）完完整整性性：Foreign key 、check、triggers、pro
7、cedure自定义完整性：自定义完整性：rule、 triggers、 procedure、 create table中的所有列级和表级约束中的所有列级和表级约束一、使用约束实施数据完整性一、使用约束实施数据完整性Primary key Foreign key UniqueCheckNullable（可否为空）约束（可否为空）约束Cascade（级联引用一致性约束）（级联引用一致性约束）Primary key 约束约束Primary key 约约束束利利用用表表中中的的一一列列或或多多列列数数据据来来惟惟一一地标识一行数据。地标识一行数据。当当在在一一个个已已经经存存放放了了数数据据的的表表上
8、上增增加加主主键键时时，SQL Server会会自自动动对对表表中中的的数数据据进进行行检检查查，以以确确保这些数据能够满足主键约束的要求：保这些数据能够满足主键约束的要求：1、不存在为、不存在为null的值的值2、不存在重复的值、不存在重复的值创建约束的三种方式：创建约束的三种方式：1、创建表格时定义约束、创建表格时定义约束2、在已有数据但没有重复值的列上添加约束、在已有数据但没有重复值的列上添加约束3、修改或删除表上已定义的约束、修改或删除表上已定义的约束注意：注意：n）修修改改已已经经建建立立的的主主键键时时，只只能能先先删删除除原原来来的的主主键键再再重重新新建建立立一一个个新新的的主
9、主键键当当一一列列受受到到主主键键约约束束时，不允许修改该列的长度时，不允许修改该列的长度n）当当主主键键被被别别的的表表中中的的外外键键所所引引用用时时，不不允允许许删删除主键除非首先将引用主键的外键删除除主键除非首先将引用主键的外键删除定义定义Primary key 的语法的语法create tablecreate tablecreate tablecreate table 表名表名 ( (列名列名 数据类型数据类型 constraint constraint 约束名约束名 primary keyprimary keyprimary keyprimary key CLUSTERED | N
10、ONCLUSTERED , CLUSTERED | NONCLUSTERED , CLUSTERED | NONCLUSTERED , CLUSTERED | NONCLUSTERED ,nnnn constraint constraint 约束名约束名 primary keyprimary keyprimary keyprimary key CLUSTERED | NONCLUSTERED , CLUSTERED | NONCLUSTERED , CLUSTERED | NONCLUSTERED , CLUSTERED | NONCLUSTERED ,nnnn ) ) ) ) 【例例2 2】
11、创建表时，为主键约束命名创建表时，为主键约束命名use wanzhenggocreate table test( bianhao smallint constraint pk_id primary key , gongzi int not null ) 添加添加Primary key Primary key 的语法的语法：alter table alter table 表名表名 add constraint constraint 约束名约束名 primary key primary key CLUSTERED CLUSTERED | | NONCLUSTERED NONCLUSTERED (
12、(列列 名名 ,n),n)【例例6 6】添加主键（续）添加主键（续）alter table test1add constraint pk_id1primary key (bianhao)【例例4 4】使用系统存储过程可以浏览指定表上使用系统存储过程可以浏览指定表上的主键信息的主键信息语法：语法：Sp_pkeys table_nameuse wanzhenggosp_pkeys test1说说明明：如如果果没没有有为为主主键键提提供供名名字字，SQL SERVER会会自自动动为为主主键键提提供供一一个个名名字字。建建议议主主动动提提供供一一个个具具有有一定意义的名字。一定意义的名字。【例例5 5
13、】删除主键删除主键语法：语法：ALTER TABLE table_name drop constraint constraint 约束名约束名 ,n ,n实例：实例：use wanzhenggoalter table test1drop constraint pk_id 在企业管理器中创建主键在企业管理器中创建主键方法：方法： 建建立立表表时时，在在指指定定的的列列上上右右击击，在在右右键键快快捷捷菜菜单单中中选选择择设设置置主主键键，该该列列将将被被设设置置为为主主键键，在在该该列列上上再再次次执执行行设设置置主主键键命命令令将将撤撤消消对对该该列列的的主主键键约约束束 。 用来确保非主键列
14、上数据的惟一性约束。用来确保非主键列上数据的惟一性约束。主键与主键与unique约束的区别：约束的区别：1 、Unique约束主要用在非主键的一列或多列上约束主要用在非主键的一列或多列上2、 Unique约束允许该列上存在约束允许该列上存在null值值,而主键决不而主键决不允许出现这种情况。允许出现这种情况。3、 一个表可以有多个一个表可以有多个Unique约束，但只能有一个约束，但只能有一个主键主键4、 建立建立Unique约束后，约束后，SQL Server会自动在相应会自动在相应列上创建列上创建Unique索引。索引。使用使用unique约束约束n1)要要修修改改一一个个unique约约
15、束束，惟惟一一的的办办法法是是，先先删删除除该该unique约约束束，然然后后在在对对应应的的列列上上重重新新建建立立新新的的unique约束约束n）unique也也可可以以在在列列级级或或在在表表级级上上设设置置如如果果要要在在多多于于一一列列上上设设置置unique约约束束，则则必必须须设设置置表表级级约约束束n当当在在一一个个已已经经存存放放了了数数据据的的表表上上增增加加unique约约束束时时，sqlserver会会自自动动对对表表中中的的数数据据进进行行检检查查，以以确确保保数数据据能能够够满满足足unique约约束束的的要要求求，即即除除null外外，所所有有的的数数据据的的值值
16、必必须须惟惟一一否否则则系系统统会会返返回回错错误误信信息，并拒绝执行增加约束的操作息，并拒绝执行增加约束的操作定义定义unique约束的语法：约束的语法： create table create table 表名表名 ( ( 列名列名 数据类型数据类型 constraint constraint 约束名约束名 unique CLUSTERED CLUSTERED | | NONCLUSTERED NONCLUSTERED ,n,n ) )【例例1 1】在姓名列上添加在姓名列上添加UNIQUEUNIQUE约束约束create table test7( bianhao smallint prim
17、ary key clustered, xingming char(20), gongzi smallint,constraint uniq_xm unique(xingming) )【例例3 3】添加添加QUIQUEQUIQUE约束约束添加添加uniqueunique约束：约束：alter table alter table 表名表名 add constraint add constraint add constraint add constraint 约束名约束名约束名约束名 unique unique unique unique CLUSTERED CLUSTERED | | NONCLU
18、STERED NONCLUSTERED ( (列列 名名 ,n),n)alter table test7add constraint uniq_xm2unique (gongzi)使用企业管理器完成使用企业管理器完成UNIQUE约束约束【例例2 2】删除删除QUIQUEQUIQUE约束约束语法：语法：alter table alter table 表名表名 drop constraint drop constraint 约束名约束名 alter table test3drop constraint uniq_xm使用使用UNIQUE约束总结：约束总结：要要修修改改一一个个UNIQUE约约束束，
19、惟惟一一的的办办法法是是先先删删除除该该UNIQUE约约束束，然然后后再再在在对对应应的的列列上上重重新新建建立立新新的的约束。约束。当当在在一一个个已已经经存存在在数数据据的的表表上上增增加加UNIQUE约约束束时时，系系统统会会自自动动对对表表中中的的数数据据进进行行检检查查，以以确确保保满满足足UNIQUE约约束束的的要要求求，即即除除NULL外外，所所有有的的数数据据值值必必须须惟惟一一。否否则则，系系统统会会返返回回错错误误的的信信息息，并并拒拒绝执行增加约束的操作。绝执行增加约束的操作。Check检查约束检查核查检查核查 (CHECK) (CHECK) 检检查查约约束束使使用用逻逻
20、辑辑表表达达式式来来限限制制列列上上可可以以接接受受的的数数据据。可可以以在在一一列列上上使使用用多多个个检检查查约约束束，依依次次检检查查每每一一个个要要进进入入数数据据库库的的数数据据，只只有有符符合条件的数据才允许通过合条件的数据才允许通过建表时定义check约束create tablecreate tablecreate tablecreate table 表名表名 ( ( 列名列名 数据类型数据类型 constraint constraint 约束名约束名 check check not for replicationnot for replicationnot for replic
21、ationnot for replication（约束表达式约束表达式） ,n,n,n,n) )检检查查约约束束则则是是通通过过对对一一个个逻逻辑辑表表达达式式的的结结果果进进行判断来对数据进行检查行判断来对数据进行检查可可以以在在一一列列上上设设置置多多个个检检查查约约束束，也也可可以以将将一一个个检检查查约约束束应应用用于于多多列列当当一一列列受受多多个个检检查查约约束束控控制制时时，所所有有的的约约束束按按照照创创建建的的顺顺序序，依次进行数据有效性检查依次进行数据有效性检查【例例1 1】增加增加CHECKCHECK约束约束create table test9( bianhao smal
22、lint primary key, xingming char(10), gongzi smallint, constraint check_id check (gongzi=1000)alter tablealter table 表名表名 add add 列名列名 数据类型数据类型 constraint constraint 约束名约束名 check check not for replication not for replication（约束表达式约束表达式）,n,n或或with check|with check| add add check check not not for for
23、replicationreplication（约约束束表表达达式式）,n,n 添加check约束【例例3 3】添加约束添加约束alter table test9addconstraint check_idcheck(gongzi=1000 and gongzi=2000use wanzhengcreate rule xingming_ruleAs xingming in(张三张三,李四李四,王五王五)描描述述规规则则的的条条件件表表达达式式中中可可以以包包含含的的数数学学运运算算符符关关系系运运算算符符以以及及诸诸如如in，like,between等等关关键键字字只只要要是是在在where子子
24、句句中中合合法法的的语语句句一般都可以用作规则一般都可以用作规则规规则则中中不不能能引引用用表表中中的的数数据据列列以以及及别别的的数数据据库库对对象象在在条条件件表表达达式式中中包包含含有有一一个个局局部部变变量量，该变量必须以打头该变量必须以打头绑定规则绑定规则SP_bindruleSP_bindrule 规则名规则名,表名表名. .列名列名SP_bindruleSP_bindrule 规则名规则名,用户定义的数据类型用户定义的数据类型说说明明：创创建建规规则则仅仅仅仅是是在在数数据据库库中中创创建建一一个个数数据据库库对对象象。在在将将这这个个规规则则应应用用于于列列之之前前，这这个个规
25、规则则毫毫无无用处。将规则应用于列成为用处。将规则应用于列成为“绑定绑定”n实例实例：nsp_bindrule gongzi_rule, test1.gongzi绑定自定义数据类型绑定自定义数据类型1)创建创建 数据类型数据类型:sp_addtype MobilePhone,nchar(12),not null2)创建规则创建规则:create rule mobilephone_ruleasphone like 130-90-90-90-90-90-90-90-90-9绑定自定义数据类型绑定自定义数据类型3) 完成绑定完成绑定.SP_bindrule mobilephone_rule,Mobi
26、lePhone4)创建表格创建表格create table www ( name varchar(10), mobilePhone MobilePhone) 【例例4 4】解除绑定解除绑定SP_unbindruleSP_unbindrule 表名表名. .列名列名SP_unbindruleSP_unbindrule 用户定义的数据类型用户定义的数据类型实例：实例：SP_unbindrule test1.gongzi【例例2 2】删除规则删除规则语法：语法格式：语法：语法格式：DROP RULE 规则名规则名例子：例子：DROP RULE gongzi_rule1、 使使用用SP_bindrul
27、eSP_bindrule会会将将该该列列中中原原有有的的规规则则覆覆盖。盖。2、 若若该该列列有有CHECK约约束束，则则所所有有约约束束和和规规则同时生效。则同时生效。3、 SP_bindruleSP_bindrule可可以以绑绑定定规规则则到到自自定定义义数数据据类类型，但不可绑定系统数据类型。型，但不可绑定系统数据类型。4、 规则对列中已有数据不起作用。规则对列中已有数据不起作用。5、 若若规规则则与与某某列列或或自自定定义义数数据据类类型型绑绑定定时时，该列无法删除。该列无法删除。注意事项注意事项三、使用默认值三、使用默认值默认值默认值(DEFAULT)(DEFAULT)某某列列的的值
28、值不不能能确确定定，或或该该列列的的值值大大量量重重复复的的取取同同一一个个值值，这这时时可可以以将将该该列列定定义义为为允允许许接接受受空空值值或给该列定义一个默认值。或给该列定义一个默认值。 默默认认值值的的使使用用方方法法与与规规则则非非常常相相似似，可可以以通通过过和和表表列列或或用用户户自自定定义义数数据据类类型型进进行行绑绑定定，对新加的数据进行约束。对新加的数据进行约束。Create default 默认值名称默认值名称As 默认值表达式默认值表达式创建表格创建表格test8create table test8( xingming varchar(20), xingbie var
29、char(2), nianling smallint,)Create defaultAs 男男SP_bindefault default_xb,test8.xingbie解除对表列的默认值绑定：解除对表列的默认值绑定：SP_unbindefault test8.xingbie删除默认值：删除默认值：DROP DEFAULT default_xb默认值的注意事项默认值的注意事项1) 必须保证列对于该默认的存储空间不超长必须保证列对于该默认的存储空间不超长.2)默默认认需需和和它它要要绑绑定定的的列列或或者者用用户户自自定定义义数数据据类型具有相同的数据类型类型具有相同的数据类型.3)默默认认值值应应符符合合该该列列任任何何规规则则和和所所有有的的check约束约束.
