第13章数据完整性(新)

第13章数据完整性13.1数据完整性概念13.2数据约束13.3缺省与规则13.4约束与缺省、规则比较13.1数据完整性概念什么是数据库的完整性数据库的完整性是指数据的正确性和相容性正确性：数据是符合现实世界语义、反映当前实际状况的。相容性：数据库同一对象在不同关系表中的数据是符合逻辑的。数据库完整性是防止不合语义或不正确的数据进入数据库，体现了是否真实地反映现实世界

1.提供定义完整性约束条件的机制DBMS应提供定义数据库完整性约束条件，并把它们存入数据库中。2.提供完整性检查的方法检查数据是否满足完整性约束条件的机制称为完整性检查。一般在INSERT、UPDATE、DELETE语句执行后开始检查。3.违约处理DBMS若发现用户的操作违背了完整性约束条件，就采取一定的动作以保证数据的完整性，如拒绝执行该操作，或级联执行其他操作。DBMS维护数据库完整性的机制：数据的完整性和安全性数据的完整性和安全性是两个不同概念数据的完整性防止数据库中存在不符合语义的数据，也就是防止数据库中存在不正确的数据防范对象：不合语义的、不正确的数据数据的安全性保护数据库防止恶意的破坏和非法的存取防范对象：非法用户和非法操作完整性类型13.1.1实体完整性13.1.2参照完整性13.1.3用户定义完整性SQL完整性组件域完整性：域完整性指列的值域的完整性。如数据类型、格式、值域范围、是否允许空值等。13.1.1实体完整性规则检查和违约处理SQLServer实现实体完整性的组件实体完整性规则规则1实体完整性规则（EntityIntegrity）若属性A是基本关系R的主属性，则属性A不能取空值。例1：单列SAP(SUPERVISOR,SPECIALITY,POSTGRADUATE)POSTGRADUATE：主码（假设研究生不会重名）不能取空值例2：多列SC（S#，C#，GRADE）属性S#和C#都不能取空。实体完整性说明(1)实体完整性规则是针对基本关系而言的。一个基本表通常对应现实世界的一个实体集。(2)现实世界中的实体是可区分的，即它们具有某种唯一性标识。(3)关系模型中以主码作为唯一性标识。(4)主码中的属性即主属性不能取空值。主属性取空值，就说明存在某个不可标识的实体，即存在不可区分的实体，这与第（2）点相矛盾，因此这个规则称为实体完整性实体完整性检查和违约处理插入或对主码列进行更新操作时，DBMS按照实体完整性规则自动进行检查。包括：1.检查主码值是否唯一，如果不唯一则拒绝插入或修改2.检查主码的各个属性是否为空，只要有一个为空就拒绝插入或修改实体完整性检查和违约处理(续)检查记录中主码值是否唯一的一种方法是进行全表扫描实体完整性检查和违约处理(续)在索引中检查是否唯一。

SQL完整性组件SQLServer实现实体完整性的组件索引：聚集索引Unique约束：定义非空的唯一约束Primary约束：主键约束Identity属性：自动增加Identity属性语法IDENTITY[(seed,increment)]seed：加载到表中的第一个行所使用的值。increment：与前一个加载的行的标识值相加的增量值。必须同时指定种子和增量，或者二者都不指定。如果二者都未指定，则取默认值(1,1)举例CREATETABLEnew_employees(id_numintIDENTITY(1,1),fnamevarchar(20),minitchar(1),lnamevarchar(30));13.1.2参照完整性关系间的引用外码参照完整性规则1.关系间的引用在关系模型中实体及实体间的联系都是用关系来描述的，因此可能存在着关系与关系间的引用。例1学生实体、专业实体

学生（学号，姓名，性别，专业号，年龄）专业（专业号，专业名）主码主码学生关系引用了专业关系的主码“专业号”。学生关系中的“专业号”值必须是确实存在的专业的专业号，即专业关系中有该专业的记录。关系间的引用(续)例2学生、课程、学生与课程之间的多对多联系学生（学号，姓名，性别，专业号，年龄）课程（课程号，课程名，学分）选修（学号，课程号，成绩）关系间的引用(续)例3学生实体及其内部的一对多联系学生（学号，姓名，性别，专业号，年龄，班长）“学号”是主码，“班长”是外码，它引用了本关系的“学号”“班长”必须是确实存在的学生的学号2．外码（ForeignKey）设F是基本关系R的一个或一组属性，但不是关系R的码。如果F与基本关系S的主码Ks相对应，则称F是基本关系R的外码基本关系R称为参照关系（ReferencingRelation）基本关系S称为被参照关系（ReferencedRelation）或目标关系（TargetRelation）外码(续)［例1］：学生关系的“专业号与专业关系的主码“专业号”相对应“专业号”属性是学生关系的外码专业关系是被参照关系，学生关系为参照关系

外码(续)［例2］：选修关系的“学号”与学生关系的主码“学号”相对应选修关系的“课程号”与课程关系的主码“课程号”相对应“学号”和“课程号”是选修关系的外码学生关系和课程关系均为被参照关系选修关系为参照关系外码(续)［例3］：“班长”与本身的主码“学号”相对应“班长”是外码学生关系既是参照关系也是被参照关系

外码(续)关系R和S不一定是不同的关系目标关系S的主码Ks和参照关系的外码F必须定义在同一个（或一组）域上外码并不一定要与相应的主码同名当外码与相应的主码属于不同关系时，往往取相同的名字，以便于识别3.参照完整性规则规则2.2参照完整性规则若属性（或属性组）F是基本关系R的外码它与基本关系S的主码Ks相对应（基本关系R和S不一定是不同的关系），则对于R中每个元组在F上的值必须为：或者取空值（F的每个属性值均为空值）或者等于S中某个元组的主码值

这条规则的实质是什么？“不允许引用不存在的实体”。参照完整性规则(续)[例1]：学生关系中每个元组的“专业号”属性只取两类值：（1）空值，表示尚未给该学生分配专业（2）非空值，这时该值必须是专业关系中某个元组的“专业号”值，表示该学生不可能分配一个不存在的专业参照完整性规则(续)〔例2〕：选修（学号，课程号，成绩）“学号”和“课程号”可能的取值：（1）选修关系中的主属性，不能取空值（2）只能取相应被参照关系中已经存在的主码值参照完整性规则(续)例3〕：学生（学号，姓名，性别，专业号，年龄，班长）“班长”属性值可以取两类值：（1）空值，表示该学生所在班级尚未选出班长（2）非空值，该值必须是本关系中某个元组的学号值13.1.3用户定义的完整性针对某一具体关系数据库的约束条件，反映某一具体应用所涉及的数据必须满足的语义要求关系模型应提供定义和检验这类完整性的机制，以便用统一的系统的方法处理它们，而不要由应用程序承担这一功能13.2数据约束13.2.1列级约束与表级约束13.2.2主键约束13.2.3唯一约束13.2.4外键约束13.2.5缺省约束13.2.6CHECK约束13.2.7启用和禁用约束使用sys.key_constraints目录视图

查看数据库约束Friday,February3,2023第30页13.2.1列级约束与表级约束列约束被指定为列定义的一部分，并且仅适用于指定的单个列。表约束的声明与列的定义无关，可以适用于表中一个以上的列。当一个约束中必须包含一个以上的列时，必须使用表约束。例如，如果一个表的主键内有两个或两个以上的列，则必须使用表约束将这两列加入主键内。列级约束与表级约束举例CREATETABLEtable7(c1int, c2int, c3char(5), c4char(10), CONSTRAINTc1PRIMARYKEY(c1,c2))CREATETABLEdepartment/*部门表*/( dnointPRIMARYKEY,/*部门号,为主键*/ dnamechar(20), /*部门名*/)表级约束列级约束13.2.2主键约束作为表定义的一部分在创建表时创建。添加到还没有PRIMARYKEY约束的表中（一个表只能有一个PRIMARYKEY约束）。如果已有PRIMARYKEY约束，则可对其进行修改或删除。例如，可以使表的PRIMARYKEY约束引用其他列，更改列的顺序、索引名、聚集选项或PRIMARYKEY约束的填充因子。定义了PRIMARYKEY约束的列的列宽不能更改。13.2.3唯一约束UNIQUEUNIQUE约束在列集内强制执行值的唯一性。对于UNIQUE约束中的列，表中不允许有两行包含相同的非空值。主键也强制执行唯一性，但主键不允许空值，而且每个表中主键只能有一个，但是UNIQUE列却可以有多个。UNIQUE约束优先于唯一索引。使用ALTERTABLE语句定义

UNIQUE约束在students表中，主键约束创建在studentID列上，如果这时还需要保证该表中的存储身份证号的SSL列的数据是唯一的，可以使用UNIQUE约束。一般地，把UNIQUE约束称为候选的主键约束。Friday,February3,2023第35页13.2.4外键约束FOREIGNKEY约束称为外键约束，用于标识表之间的关系，以强制参照完整性，即为表中一列或者多列数据提供参照完整性。一个表中最多可以有253个可以参照的表，因此每个表最多可以有253个FOREIGNKEY约束。在FOREIGNKEY约束中，只能参照同一个数据库中的表，而不能参照其他数据库中的表。FOREIGNKEY子句中的列数目和每个列指定的数据类型必须和REFERENCE子句中的列相同。FOREIGNKEY约束不能自动创建索引。参照同一个表中的列时，必须只使用REFERENCE子句，而不能使用FOREIGNKEY子句。在临时表中，不能使用FOREIGNKEY约束。外键约束的基本语法形式在CREATETABLE语句中定义列的外键约束FOREIGNKEYREFERENCES外表名（参照列）ONDELETE{NOACTION|CASCADE|SETNULL|SETDEFAULT}ONUPDATE{NOACTION|CASCADE|SETNULL|SETDEFAULT}在ALTERTABLE语句中定义列的外键约束FOREIGNKEY(COLUMN)REFERENCES外表名（参照列）ONDELETE{NOACTION|CASCADE|SETNULL|SETDEFAULT}ONUPDATE{NOACTION|CASCADE|SETNULL|SETDEFAULT}在CREATETABLE语句中定义多列的外键约束FOREIGNKEY(COLUMN_list)REFERENCES外表名（参照列名表）ONDELETE{NOACTION|CASCADE|SETNULL|SETDEFAULT}ONUPDATE{NOACTION|CASCADE|SETNULL|SETDEFAULT}

USEtestGOCREATETABLEworker /*职工表*/( nointPRIMARYKEY, /*编号,为主键*/ namechar(8), /*姓名*/ sexchar(2), /*性别*/ dnoint /*部门号*/ FOREIGNKEYREFERENCESdepartment(dno) ONDELETENOACTION,/*删除主键数据时外键表的行为*/ addresschar(30) /*地址*/)GO

该程序使用FOREIGNKEY子句在worker表中建立了一个删除约束，即worker表的dno列（是一个外键）与department表的dno列关联。如果外键表有数据，则删除失败，NOACTION是默认选项【例】在表定义时创建外键在ALTERTABLE语句中定义外键约束Friday,February3,2023第39页13.2.5缺省约束DEFAULT缺省约束是加在列上的缺省值。缺省值是指当对列数据进行INSERT操作时系统自动提供的值。列的缺省约束值可以是常数或系统函数列的缺省约束值也可以是事先定义的缺省，在创建数据表的时候定义缺省约束时有两种方法：用界面操作方便用命令操作需要两步：第一步定义表结构，第二步绑定缺省列的缺省约束值是常数的情况CREATETABLEjsy_temp4(驾照号 int IDENTITY(1,1)NOTNULL PRIMARYKEYCLUSTERED, 姓名varchar(8) NOTNULL, 是否见习varchar(20)DEFAULT(‘是’), 身份证号char(18) NOTNULL UNIQUENONCLUSTERED)使用ALTERTABLE语句定义DEFAULT约束Friday,February3,2023第42页13.2.6CHECK约束CHECK约束通过限制用户输入的值来加强域完整性。它指定应用于列中输入的所有值的布尔（取值为TRUE或FALSE）搜索条件，拒绝所有不取值为TRUE的值。可以为每列指定多个CHECK约束。以下程序在test数据库中创建一个table6表，其中使用CHECK约束来限定f2列只能为0~100分：

USEtestGOCREATETABLEtable6( f1int, f2intNOTNULLCHECK(f2>=0ANDf2<=100))GO当执行如下语句：

INSERTtable6VALUES(1,120)则会出现如图13.2所示的错误消息。例:CHECK约束的使用方法13.2.7启用和禁用约束（1）当建立主键约束或惟一约束时，SQLServer会在后台自动建立惟一索引，并对表中的数据进行检查，以确保数据符合约束要求，这种自查过程是无法关闭。（2）当创建检查约束和外键约束时，在缺省状态下，系统也会自动对数据进行检查，与主键约束和惟一约束不同的是，它可以取消。当确认表中的数据已经符合约束条件时，就不必再浪费时间重复检查。如果已确认表中的数据不符合约束条件，又不想马上清理当前的数据，也不必对数据进行检查，此时可以使用WITHNOCHECK选项关闭检查当前数据。（3）当为某列加入一个新的缺省约束，SQLServer并不对该列已有数据进行检查，表中已有记录保留原有的取值或NULL。当新建一列同时指定其缺省值时，SQLServer才为已有记录填充数值。如果使用了WITHVALUES选项，SQLServer会把缺省值赋给新列，否则新列为NULL。如果新列不允许为NULL，则无论是否指定了WITHVALUES选项，都使用缺省值。（4）当向数据库添加大量的数据时，可以临时禁止检查约束和外键约束。使用WITHNOCHECK子句该约束对表中已有数据不强制检查。但是，如果以后更新表中数据要检查更新后的数据是否满足约束。第46页13.3缺省与规则SQLServer中缺省（默认值）和规则的概念缺省（默认值）是为列提供数据的一种方式，如果用户进行INSERT操作时不为列输入数据，则使用缺省值。规则是当用户进行INSERT或UPDATE操作时，对输入列中的数据设定的取值范围。缺省与规则不是ANSI标准，一般不提倡使用，应尽可能使用约束，任何可以使用缺省与规则的地方都有可以使用约束。13.3缺省与规则特点(1)缺省与规则是数据库对象，它们是独立于表和列而建立的。(2)缺省与规则建立后与列或数据类型产生关联，列和数据类型就具有了缺省与规则的属性。(3)缺省与规则定义后，可以重复使用，可以绑定到多个列或数据类型上。(4)缺省与规则不随表同时调入内存，当用到时才被调入内存，这可能会使程序执行出现延时。13.3缺省与规则13.3.1创建缺省和规则13.3.2绑定13.3.3取消绑定13.3.4删除缺省和规则13.3.1创建缺省和规则CREATEDEFAULTdefault_nameASconstant_expression其中default_name为缺省值对象名，constant_expression为一个常量、数学表达式或内置函数，不能引用其列或别的数据库对象。创建缺省举例/*使用下面语句创建con3默认对象,其默认值为10*/createdefaultcon3as10go13.3.1创建缺省和规则CREATERULErule_nameASrule_conditions其中rule_name为规则对象名，rule_conditions为规则表达式，表示数据需满足的条件。例：创建一个名为rule1的规则，限定输入的值必须在0~10之间。

USEtestGOCREATERULErule1AS@c1BETWEEN0and10GO13.3.2绑定绑定到某列或用户自定义的数据类型上，以便它们在数据输入或修改操作中起作用。使用时要注意缺省和规则所用的数据类型必须与将要绑定的列数据类型一致，还要遵循已应用到该列中的约束。缺省和默认值绑定格式SP_BINDEFAULT‘default_name’,’object_name’[,FUTUREONLY]SP_BINDRULE‘rule_name’,‘object_name’,FUTUREONLY]1）default_name