第6章 数据库表数据完整性

第六章数据库表数据完整性了解数据完整性的概念SQLServer2008中的数据完整性机制创建、应用约束实施数据完整性，管理约束使用关系图工具创建数据库关系图实施数据完整性创建、应用默认值实施数据完整性，管理默认值创建、应用规则实施数据完整性，管理规则创建、应用触发器实施数据完整性，管理触发器教学目标6.1了解数据完整性任务：建立数据完整性的概念，了解SQLServer2008中的数据完整性机制。6.1.1什么是数据完整性6.1.2数据完整性需求分析4.1.1什么是数据完整性1．什么是数据完整性数据完整性指存储在数据库中的数据在逻辑上是一致的、准确的、完备的，这是现代数据库的一个典型特征。2．数据完整性的分类域完整性。即列完整性，产生在表中的一列或多列上，指定某个列的输入是否合法，以及确定该列是否允许空值。实现域完整性的方法有：限制类型（通过数据类型）、格式（通过CHECK约束和规则）或可能的取值范围（通过CHECK约束、DEFALUT定义、NOTNULL定义和规则）等。

CHECK约束实际上是字段输入内容的验证规则，表示一个字段的输入内容必须满足CHECK约束的条件，若不满足，则数据无法正常输入。CHECK约束通过显示输入到列中的值来实现域完整性；DEFAULT定义后，如果列中没有输入值则填充默认值来实现域完整性；通过定义列为NOTNULL限制输入的值不能为空也能实现域完整性。实体完整性。即行完整性，指定表中所有行的唯一性。它要求表中所有的行有一个唯一的标识符，即为主键，其值不能为空且能唯一地标识对应的记录。通过索引、UNIQUE约束、PRIMARYKEY约束或IDENTITY属性可实现数据的实体完整性。PRIMARYKEY约束与UNIQUE约束的主要区别如下：（1）一个数据表只能创建一个PRIMARYKEY约束，但一个表中可根据需要对表中不同的列创建若干个UNIQUE约束；（2）PRIMARYKEY字段的值不允许为NULL，而UNIQUE字段的值可取NULL；（3）一般创建PRIMARYKEY约束时，系统会自动产生索引，索引的默认类型为簇索引。创建UNIQUE约束时，系统会自动产生一个UNIQUE索引，索引的默认类型为非簇索引。

PRIMARYKEY约束与UNIQUE约束的相同点在于：二者均不允许表中对应字段存在重复值。参照完整性。即引用完整性，产生在表和表之间，要求在主键（被参照表）和外键之间的参照关系中得到维护，即参照表中的外键值必须始终参照被参照表中的主键值。用户自定义完整性。用户自定义的一组规则，不属于以上三种完整性的任一种。如果定义了两个表之间的参照完整性，则要求：（1）从表不能引用不存在的键值，例如，对于选修表中行记录出现的学号必须是学生表中已存在的学号；（2）如果主表中的键值更改了，那么在整个数据库中，对从表中该键值的所有引用要进行一致的更改，例如，如果对学生表中的某一学号修改，选修表中所有对应学号也要进行相应的修改；（3）如果主表中没有关联的记录，则不能将记录添加到从表；（4）如果要删除主表中的某一记录，应先删除从表中与该记录匹配的相关记录。4.1.2数据完整性需求分析1．“教学管理系统”中的域完整性（1）表student_teacher_course中的列grade和gotcredit使用检查约束指定取值范围分别为0<=grade<=100和0<=credit<=6。（2）多个列拥有DEFAULT约束，如列professionaltitle的默认值为“讲师”，ssex的默认值为“女”。2．“教学管理系统”中的实体完整性（1）共包含八个表，每个表具有唯一一个主键（2）表teacher的列identitycard指定UNIQUE约束，避免身份证号码重复。3．“教学管理系统”中的参照完整性student_teacher_course(sno+tno+cno)teacher__course(tno+classno+cno)teacher(tno)class(classno)course(cno)“教学管理系统”中的参照完整性6.1.2“教学管理系统”的数据完整性需求分析major_course(classno+cno)major(majorno)student(sno)6.2应用约束任务：了解SQLServer2008中的约束类型及作用。创建、应用NOTNULL、PRIMARYKEY、UNIQUE、CHECK、DEFAULT、FOREIGNKEY约束强制数据完整性，对约束进行简单管理。4.2.1约束的类型4.2.2使用SSMS创建约束4.2.3使用T-SQL创建约束

约束（CONSTRAINT）是强制数据完整性非常有效的方法，属于声明型数据完整性。对数据库来说，约束分为列约束和表约束。列约束作为列定义的一部分只作用于该列。表约束作为表定义的一部分，作用于表的多列。6.2.1约束的类型SQLServer2008包含六种基本约束：（1）PRIMARYKEY约束：实施实体完整性。（2）UNIQUE约束：实施实体完整性。（3）DEFAULT约束：实施域完整性。（4）CHECK约束：实施域完整性。（5）NOTNULL约束：实施域完整性。（6）FOREIGNKEY约束：实施参照完整性。6.2.1约束的类型如下代码中既包含列约束，又有表约束。CREATETABLEmajor_course(majornochar(3)NOTNULL,--列约束，列定义的一部分

cnochar(4)NOTNULL,--列约束，列定义的一部分

termchar(1),typechar(10)default('专业基础'),--列约束，列定义的一部分

creditnumeric(2,1)CHECK(0<=creditANDcredit<=6)--列约束，列定义的一部分

periodtinyint,--列约束，列定义的一部分

weekstinyintdefault(18),--列约束，列定义的一部分

examinationtypechar(6)default(‘考试')--列约束，列定义的一部分

theorperiodtinyintdefault(8),--列约束，列定义的一部分

experimentperiodtinyintdefault(8),--列约束，列定义的一部分

CONSTRAINTpkccPRIMARYKEY(majorno,cno),--表约束，表定义的一部分

CONSTRAINTfkcc1FOREIGNKEY(majorno)REFERENCESmajor(majorno),--表约束

CONSTRAINTfkcc2FOREIGNKEY(cno)REFERENCEScourse(cno)--表约束)

6.2.1约束的类型启动SSMS，打开“对象资源管理器”，依次展开“数据库”→JXGL→“表”节点。1．创建PRIMARYKEY约束2．创建NOTNULL约束3．创建DEFAULT约束4．创建UNIQUE约束5．创建CHECK约束6．创建FOREIGNKEY约束6.2.2使用SSMS创建约束提示：若主键是多个列的组合时，可按住键盘上的Shift键或CTRL键连续选择多个键。一个表中可以包含多个声明UNIQUE约束的列。1．在CREATETABLE语句中创建约束使用CREATETABLE语句可以在创建表的过程中创建约束，约束是表定义的一部分。6.2.3使用T-SQL创建约束提示：①若想CREATETABLE语句成功执行，必须先删除掉数据库中原来重名的表。②列约束放在列定义语句中，表约束放在所有列定义语句之后。③最后一个列定义语句和约束定义语句之后没有逗号“,”分隔，其他定义语句之后都有逗号。【例6-1】使用CREATETABLE语句定义表teacher及其约束。CREATETABLEteacher(tnochar(4)NOTNULL,--NOTNULL约束tnamevarchar(20)NOTNULL,--NOTNULL约束tsexchar(2),birthdaysmalldatetime,professionaltitlevarchar(10)CONSTRAINTdftpDEFAULT('副教授'),--DEFAULT约束,awardsvarchar(100),homeaddressvarchar(40)NULL,telvarchar(20)NULL,politicalbackgroundchar(12),emailvarchar(40)NULL,identitycardchar(18)CHECK(len(identitycard)=15OR(len(identitycard)=18)),--CHECK约束departmentvarchar(20)default('计算机科学与技术学院'),CONSTRAINTpktPRIMARYKEY(tno)--PRIMARYKEY约束)6.2.3使用T-SQL创建约束2．在ALTERTABLE语句中创建约束如果表已经创建完成，但没有声明任何约束，可以使用ALTERTABLE语句添加约束声明。【例6-2】使用ALTERTABLE语句为表teacher添加约束（假设表teacher没有声明约束）。代码如下：（1）添加NOTNULL约束ALTERTABLEteacherALTERCOLUMNsexchar（2）NOTNULL（2）添加DEFAULT约束。ALTERTABLEteacherADDCONSTRAINTdftsexDEFAULT('女')FORsex（3）添加UNIQUE约束。ALTERTABLEteacherADDCONSTRAINTu1UNIQUE(identitycard)6.2.3使用T-SQL创建约束（4）添加CHECK约束。ALTERTABLEteacherADDCONSTRAINTc1CHECK(sex='男'ORsex='女')（5）添加PRIMARYKEY约束。ALTERTABLEteacherADDCONSTRAINTpktPRIMARYKEY(tno)6.2.3使用T-SQL创建约束在声明约束时应注意以下问题：（1）执行系统存储过程sp_help可以查看表的所有约束信息。执行ALTERTABLE…DROP…语句可以删除相应的约束。想删除表中的某一列，必须先删除该列中的约束。（2）一个表中可以声明多个CHECK约束，一列只能声明一个CHECK约束。不能在具有IDENTITY属性或timestamp型、uniqueidentifine型数据的列上声明CHECK约束。（3）一个表中只能创建一个PRIMARYKEY约束，且不允许为NULL。系统自动在主键列上创建一个聚集索引（将在任务五中介绍）。一个表中可以有多个UNIQUE约束，且允许为NULL，用于强制在指定列上创建一个UNIQUE索引。6.2.3使用T-SQL创建约束6.3创建和管理关系图

任务：应用数据库关系图工具描述一个数据库中表之间的关联关系，实施参照完整性。

创建和管理关系图

提示：数据库关系图工具将所有的操作暂存在内存中，直到保存关系图。同时有多个开发人员在同一数据库中创建或修改关系图时，都不会影响其他开发者的工作，除非保存并刷新关系图。SQLServer2008的SSMS中提供了数据库关系图工具，主要用于对数据库进行文档化，同时也是数据库的开发和维护工具。应用它可以创建表之间的参照关系，强制参照完整性。数据库关系图工具可以实现数据库中每一张表同其他表进行关联的文档化描述，通过关系图直接显示数据库中的表、列及表和表之间的关系，并以黄色钥匙图案标识主键。数据库JXGL的关系图6.4应用默认值

任务：创建默认值对象，并绑定到列或数据类型，实施域完整性。1．什么是默认值对象默认值也是实施数据完整性的有效手段，与DEFAULT约束的作用非常相近。当用户不能向表中的某列输入具体数据，或暂时没有合适的数据输入时，系统会自动取出默认值并输入到该列。默认值本身由用户提前指定，并不是由系统随机产生。与DEFAULT约束不同的是，作为数据库对象之一的默认值一旦由用户定义并被保存在数据库中，可以多次应用到任意表的多个列或用户自定义数据类型，不会随着表或数据类型的删除而被删除。应用默认值2．创建默认值对象创建默认值的T-SQL语句是CREATEDEFAULT，基本语法如下：CREATEDEFAULTdefault_nameASconstraint_expression3．绑定默认值对象使用系统存储过程sp_bindefault可以将定义的默认值对象绑定到表的某个列或自定义数据类型。默认值只有被绑定成功才能有效，并可以被绑定到多个表的多个列或用户自定义数据类型。基本语法如下：sp_bindefaultdefault_name,'table_name.column|type_name'应用默认值其中：

default_name：默认值对象的名称，必须符合SQLServer标识符的命名规则。

constraint_expression：默认值对象的值，是一个常量表达式，在该表达式中不能包含任何数据库对象的名字，但可以是SQLServer的内部函数，如getdate()。4．解除默认值对象的绑定当某个列或自定义数据类型不再需要与某个默认值绑定，使用系统存储过程sp_unbindefault可以将绑定解除。默认值的绑定被解除后，默认值对象仍然存储在数据库中。基本语法如下：sp_unbindefault'table_name.column|type_name'5．删除默认值对象当默认值对象不再有用时，可以DROPDEFAULT语句将它从数据库中删除。基本语法如下：DROPDEFAULTdefault_name应用默认值【例6-4】在数据库JXGL中创建默认值对象desex，并将它绑定到表student的ssex和teacher的tsex列上，然后解除这个绑定，并从数据库中将desex删除。代码如下：createdefaultdfsexas'男'gosp_bindefaultdfsex,'student.ssex'--绑定默认值到表student的ssex列GOsp_bindefaultdfsex,'teacher.sex'--绑定默认值到表teacher的sexGosp_unbindefault'student.ssex'--解除默认值在表student的ssex列上的绑定GOsp_unbindefault'teacher.sex'--解除默认值在表teacher的sex列上的绑定GO应用默认值创建、绑定、解除绑定默认值应注意以下问题：（1）默认值与其绑定的列或自定义数据类型要具有相同的数据类型。（2）默认值被强制执行被绑定的列包含的所有约束和规则。（3）若将一个默认值绑定到自定义数据类型，并将该数据类型应用于某个列。之后将另一默认值绑定到该列上，则绑定到列的默认值优先，绑定到数据类型中的默认值不再起作用。（4）要想删除一个默认值对象，必须先解除它与所有对象的绑定。（5）创建与绑定默认值不应该在同一个批中。应用默认值6.5应用规则

任务：创建规则对象，并绑定到列或数据类型，实施域完整性。

1．什么是规则对象

规则用来验证将要输入或已经存在于数据库中的数据是否处于指定的值或范围，或者是否与特定的格式相匹配，可以实现数据完整性中的域完整性，作用与CHECK约束的作用相似。不同的是，规则是一种数据库对象，在这点上与默认值相同。2．创建规则对象使用T-SQL的CREATERULE语句创建规则。语法如下：CREATERULErule_nameAScondi