第五章数据完整性

第五章数据完整性【本章要点】1、数据完整性的类型及区别2、约束的类型及创建和删除3、默认的创建、绑定和删除4、规则的创建、绑定和删除5.1数据完整性概念

当人们操作表中的数据时,由于一些人为原因,我们经常遇到一些问题,例如:员工进入公司的日期早于该员工的出生日期；如某公司的员工表中发现有相同的员工编号；又如某公司人事部有一张员工信息表和一张员工借款信息表,但后来发现借款表中的某个员工不是公司的员工。诸如此类的问题，不能仅靠数据录入人员和操作人员的认真和负责，而应该建立一套保障机制；要么防止这类问题的发生，要么发生这些问题时可以及时地发现。数据完整性就是解决这些问题的机制。

数据完整性（DataIntegrity）是指存储在数据库中的数据的一致性和准确性。

它是应防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。在MicrosoftSQLServer2005系统中数据完整性分为三类：域完整性、实体完整性、参照完整性5.1.1域完整性

域完整性，也可称为列完整性，指数据库表中的列必须满足某种特定的数据类型或约束。其中约束又包括取值范围、精度等规定。例如，在性别列中，限制其取值范围为“男”和“女”，其他的取值无效。5.1.2实体完整性

实体完整性，也可称为行完整性，规定表中的每一行都有惟一标识符，这种标识符一般称为主键值。例如，对于所有的中国公民来说,居民身份证号码是唯一的，使用居民身份证号码可以唯一地确定某一个人，因此可以把公民的居民身份证明号码作为主键对待，主键值是否能够被修改或表中的全部数据是否能够被全部删除都要依赖于主键表和其他表之间要求的完整性。5.1.3参照完整性

参照完整性，也可称为引用完整性，指两个表（被引用表）的主关键字和（引用表的）外部关键字的数据应对应一致。它确保了有主关键字的表中对应其它表的外关键字的行存在，即保证了表之间的数据的一致性，防止了数据丢失或无意义的数据在数据库中扩散。例如，在一个数据库中有两张表，即人事表和财务表。人事表中记录了本单位的所有员工的基本信息，财务表记录了本单位员工的借款信息。一般地，如果某个公司员工有借款，那么他就不能直接从人事表中删除。数据完整性分析5.2约束(constraints)

约束是通过限制列中数据、行中数据和表之间数据来保证数据完整性的非常有效的方法。建立和使用约束的目的是保证数据的完整性，在SQLServer2005中可使用5种约束来限制列中的数据、行中的数据和表之间的数据以保证数据的完整性。1、CHECK约束/检查约束：限制插入列中的值2、PRIMARYKEY约束/主键约束：要求主键的列上没有两行具有相同值，也没有空值。3、FOREIGNKEY约束/外键约束:要求指定的列（外键）中正被插入或更新的新值，必须在被参照表（主表）的相应列（主键）中已经存在。4、UNIQUE约束/唯一约束:要求表中在指定的列上没有两行具有相同的值。5、DEFAULT约束/缺省约束：当向数据库中的表插入数据时，如果用户没有明确给出某列的值，

SQL

Server自动为该列输入指定值。5.2.1check约束

CHECK约束用于限制输入到一列或多列中值的范围来保证SQLServer数据库中数据的域完整性，也就是一个字段的输入内容必须满足CHECK约束的条件，否则，数据无法正常输入，从而强制数据的域完整性。

方法一、使用SQLServer企业管理器定义、删除check约束

【例1】在客服表中定义性别列只能输入“男“或者“女“的check约束

使用T-SQL语句来创建check约束1.格式:创建表的同时创建check约束Createtable表名(…..，列名数据类型是否为空constraint约束名check(表达式),…)2.修改表时添加check约束。Altertable表名AddConstraint约束名check(表达式)创建客户表的同时定义check约束要求性别列只能输入“男“或者”女“Use手机Createtable客户表

(客户编号varchar(10)notnull,

客户姓名varchar(10)notnull,

客户密码char(6)notnull,

性别char(2)notnullConstraintck_性别check(性别=’男’or性别=’女’),

证件号varchar(18)notnull,

月收入smallmoneynull)创建职工表时，给年龄加个check约束，将年龄限制在18-60岁createtable职工(职工号char(20)null,姓名char(20)null,年龄intnullconstraintck_年龄check(年龄>=18and年龄<=60),籍贯char(20)null，学历char(20)null)给职工表的学历添加约束Altertable职工AddConstraintck_学历check(学历=‘本科’or学历=‘硕士’or学历=‘博士’)3.使用T-SQL语句删除check约束

(注意：次语句可用于任何一种约束的删除）语法：Altertable表名Dropconstraint约束名称(1)删除约束Altertable客户表Dropconstraintck_客户表5.2.2PRIMARYKEY约束

在数据库的每个表中，经常有一列或者多个列的组合，其值能唯一的标识表中的每一行。就好像我们平时使用的身份证，能够唯一的标识每个人一样。这样的一列或者多个列，被称为主键，通过主键，可以强制表的实体完整性。

每一张表中只能有一个PRIMARYKEY约束，在使用PRIMARYKEY约束时，该列的属性必须定义为空值NOTNULL，也就是说拥有主键的那一列，不能为空。

定义为主键的列，不允许有空值和重复值方法一、使用SQLServer企业管理器定义1、定义PRIMARYKEY约束【例5】对客户表中的证件号定义PRIMARYKEY约束使用T-SQL语句来创建格式一：创建表的时候创建主键约束Createtable表名(……列名数据类型是否为空constraint主键约束名primarykey,……)格式二：创建好的表添加primarykey约束Altertable表名AddConstraint主键约束名primarykey(主键列)1、在创建表的同时定义PRIMARYKEY约束【例6】创建客户表的同时定义列“证件号”为PRIMARYKEY约束

Use手机Createtable客户表(客户编号varchar(10)notnull,客户姓名varchar(10)notnull,客户密码char(6)notnull,性别char(2)notnull，证件号varchar(18)notnullConstraintpk_证件号primarykey,月收入smallmoneynull)创建工资表的同时为职工号创建主键createtable工资(职工号char(20)notnullconstraintccprimarykey,姓名char(20)null,工资char(20)null)假设创建工资表时未有主键现添加主键约束,约束名称是aaaaltertable工资addconstraintaaaprimarykey(职工号)2、删除PRIMARYKEY约束语法:Dropconstraint主键名称【例8】删除例5、9中为证件号添加的PRIMARYKEY约束Use手机Altertable客户表Dropconstraintpk_客户表

练习1.创建表学生(学号,姓名,籍贯,入学时间,性别)同时，为入学时间加个check约束，将时间约束在2009-9-1号以后2.添加check约束cc,将性别约束成为男或者为女3.将表成绩(学号，姓名，成绩)的学号添加为主键约束，约束名为:abc5.2.3FOREIGNKEY约束

FOREIGNKEY是用于建立和加强两个表数据之间的关系的一列或多列。通过将保存表中主键值的一列或多列添加到另一个表中，可创建两个表之间的关系，这个列就成为第二个表的外键。

例如，数据库‘教学(teach)’中的‘选课(xk)表’与‘学生(student)表’有关系，因为在学生和选课之间存在逻辑联系。‘选课表’中的学号(s_no)列与‘学生表’中的主键列学号(s_no)相对应，这时从表‘选课表’中的学号(s—no)列是主表‘学生表’的外键

定义外键约束时应注意以下几点：1、定义表之间的参照关系时应该先选定主表并定义其主键，再对从表定义外键约束.2、定义外键之前必须保证从表与主表相同列的数据满足要求。（相同列从表的数据小于或等于主表数据).3、一个表最多能够建立253个外键关系。4、外键约束同时也可以参照自身表中的其他列5、外键约束，只能参照本身数据库中的某个表，而不能参照其他数据库中的表。跨数据库的参照只能通过触发器来实现。createtable宿舍表(学号char(20)nullconstraintfk_ssjbforeignkeyreferences基本情况表(学号),宿舍号char(20)notnullprimarykey,宿舍名char(20)nullconstraintck_ssmcheck(宿舍名='1栋'or宿舍名='2栋'),管理员char(20)constraintmrglydefault'刘月明')使用T-SQL语句来创建、删除FOREIGNKEY约束

创建表的同时创建外键约束Createtable表名(….列名数据类型是否为空constraint约束名ForeignkeyReferences主表(列名),…)已创建好的表中添加外键约束Altertable表名AddConstraint约束名称foreignkey(列名)References主表(列名)Use手机Createtable订单表

( 订单编号 varchar(10) notnullprimarykey,

客户编号varchar(10)notnullconstraintggforeignkeyReferences客户表(客户编号),

商品编号varchar(10)notnull,

订购数量int,

销售额money,

购物时间smalldatetime)5.2.4UNIQUE约束

UNIQUE约束用于表中的非主键列，UNIQUE约束保证一列或者多列的数据完整性，确保这些列不会输入重复的值，即没有相同的两列值。例如，客户表中客户编号列为主键，但是其中还包括证件号码列，由于所有证件号码不可能出现重复，所以可以在此列上建立UNIQUE约束，确保不会输入重复的证件号码。与PRIMARYKEY约束类似，UNIQUE约束也强制惟一性，为表中的一列或多列提供实体完整性，且每个UNIQUE约束要建立一个惟一索引；UNIQUE约束和PRIMARYKEY约束主要区别如下：

1）UNIQUE约束用于非主键的一列或者多列组合；

2）一个表可以定义多个UNIQUE约束，而只能定义一个PRIMARYKEY约束；

3）UNIQUE约束可以用于定义允许空值的列，而PRIMARYKEY约束则不能用于允许空值的列。Use手机Createtable客户表

(客户编号varchar(10)notnull,

客户姓名varchar(10)notnull,

客户密码char(6)notnull,

性别char(2)notnull，证件号varchar(18)nullConstraintwyunqiue,

月收入smallmoneynull)altertable工资addconstraintfk_职工_工资foreignkey(职工号)references职工(职工号)Createtable宿舍分配表(职工号varchar(20)notnullprimarykeyconstraintxxforeignkeyreferences职工(职工号),姓名char(20)null,寝室号char(20)null)Unique约束确保数据库在的元组在非主键列上取值不同。比如说一个学生信息表，学号是主键，假设在表里还有一个身份证号的列，该列不作为主键，但它的取值也不能相同，所以，可以用unique约束保证输入数据库中的身份证号是不同的。用SQL创建UNIQUE约束1.创建表时加约束Createtable表名(……,列名数据类型nullConstraint约束名unique,…)2.往已经创建好的表中添加Altertable表名AddConstraint约束名Unique(列名)5.2.5DEFAULT约束

如对表中的某列定义了default约束后,用户在插入新的数据行时,如果没有为该列制定数据,那么系统将默认值赋予该列,默认值可以为空值(null)

方法一、使用SQLServer企业管理器定义、删除DEFAULT约束1、使用SQLServer企业管理器定义DEFAULT约束【例16】为客户表中的性别列定义default约束，使其性别的默认值为“男”

用Sql语言创建默认约束1.创建表的同时添加Createtable表名(……列名数据类型nullConstraint约束名Default‘值’,…)2.创建完了后添加Altertable表名AddConstraint约束名default‘值’for列名创建宿舍分配表，并将宿舍号默认成’101’createtable宿舍分配(职工号varchar(20)notnull,姓名char(20)null,宿舍号char(20)nullconstraintmr_宿舍号default'101')将职工表的籍贯列默认成’成都’altertable职工addconstraintmr_籍贯default'成都'for籍贯练习1.创建表：学生(学号,姓名,性别,专业,身份证号)且创建的同时将学号设置主键约束，身份证号设定unique约束2.创建表:成绩(学号,姓名,成绩,奖学金)创建的同时将奖学金设置默认为:无3.以成绩为从表，学生为主表，为成绩创建一个外键约束cc4.为成绩表中的姓名添加unique约束，名称为ll5.为学生表中的“专业”添加默认约束ff,使专业默认为”计算机”createtable学生(学号char(20)notnullconstraintpk_xhprimarykey,姓名char(20)null,性别char(2)null,专业char(20)null,身份证号char(18)nullconstraintun_sfzhunique)createtable成绩(学号char(20)notnull,姓名char(20)null,成绩floatnull,奖学金moneynullconstraintde_jxjdefault'无')altertable成绩addconstraintfk_xhforeignkey(学号)references学生(学号)altertable成绩addconstraintllunique(专业)altertable学生addconstraintffdefault'计算机'for专业5.3默认与规则5.3.1默认我们在向数据表中输入数值时，希望表里面的某些列已经具有一些默认值，不必用户一一输入，或者是用户现在还不准备输入但又不想空着。例如，输入学生性别的时候，先默认所有学生的性别为“男”，如果输入的是个男生则“性别”列不必每次输入了，如果是女生就将“男”改为“女”，这样大大减少输入数据的工作量。又如输入一个班的课程信息，由于全班同学在一个教室上课，那么设定教室一个默认值，则每个同学的上课教室就不必输入了。

默认对象在功能上与默认约束是一样的，但在使用上有所区别：默认约束是在创建表或者修改表时定义的，嵌入到被定义的表结构中，在删除表的同时默认约束也被删除；默认对象需要用CREATEDEFAULT语句进行定义，作为一种单独存储的数据库对象，它独立于表结构之外，默认对象的使用必须要通过绑定，删除表时并不能删除默认对象，而需要使用DROPDEFAULT语句进行删除。

创建默认格式:Createdefault默认名as‘值’例如createdefaultddas'男’创建完后，需要绑定在表中的某列，才能真正起到默认的作用绑定到表中的某列格式：Sp_bindefault默认名,’表名.列名’例如sp_bindefaultdd,'职工.性别‘删除默认绑定去除格式:sp_unbindefault‘表名.列名’例如sp_unbindefault'成绩.专业‘删除格式:Dropdefault默认名创建默认zy，值是”物流”Createdefaultzyas‘物流’将它绑定到学生成绩表中的专业列上Sp_bindfaultzy,’学生成绩.专业’删除默认sp_unbindefault'成绩.专业‘Dropdefaultzy5.3.2规则

规则就是创建一套准则，并将其结合到表的列或用户自定义数据类型上，它的作用类似于CHECK约束，添加完之后它会检查添加的数据或者对表所作的修改是否满足所设置的条件。

规则也是一种独立的数据库对象，规则可以绑定到一列或者多列上，也可以绑定到用户自定义数据类型上。规则与其作用的表或用户自定义数据类型是相互独立的，即表或用户自定义对象的删除、修改不会对与之相连的规则产生影响。

规则对象在功能上与CHECK约束是一样的，但在使用上也有区别：

1、CHECK约束是在创建表或者修改表时定义的，嵌入到被定义的表结构中，在删除表的同时CHECK约束也被删除；

2、规则对象需要用CREATERULE语句进行定义，作为一种单独存储的数据库对象，它独立于表结构之外，规则对象的使用必须要通过绑定，删除表时并不能删除规则对象，而需要使用DROPRULE语句进行删除。

方法二、使用T-SQL语言CREATERULE语句创建规则语法格式:Createrule规则名称as@规则名称条件表达式

【例27】以例26为例，用CREATERULE语句创建规则