第8章 数据库 数据完整性约束

第8章数据完整性约束8.1数据完整性的概念8.2实现数据完整性8.3系统对完整性约束的检查8.4删除约束8.5触发器2020年2月8日6时29分18.1数据完整性的概念数据完整性是指数据的正确性和相容性。如：每个人的身份证号必须是唯一的，人的性别只能是‘男’或‘女’。为了维护数据的完整性，DBMS必须要提供一种机制来检查数据库中的数据。这些加在数据库数据之上的语义约束条件就称为数据完整性约束条件。DBMS中检查数据是否满足完整性条件的机制就称为完整性检查。2020年2月8日6时29分2完整性约束条件的作用对象列级约束元组约束关系约束2020年2月8日6时29分3列级约束主要是对列的类型、取值范围、精度等的约束，包括：对数据类型的约束：数据类型、长度、精度等例：snochar(8)对数据格式的约束：Sno:201001101年专业班号序列号对取值范围的约束：如学生的成绩取值范围为0～100。对空值的约束：列是否允许有空值。2020年2月8日6时29分4元组约束元组约束是元组中各个字段之间的联系的约束，如：开始日期小于结束日期订货数量小于等于库存数量最低工资不能低于规定的最低值2020年2月8日6时29分5关系约束是指若干元组之间、关系之间的联系的约束。比如：学号的取值不能重复也不能取空值学生修课表中学号的取值受学生表中学号取值的限制等。2020年2月8日6时29分6实现数据完整性的方法可以在服务器端完成，也可以在客户端编程实现。在服务器端实现数据完整性的方法主要有两种：声明数据完整性触发器在客户端实现数据完整性主要是用前端开发工具，在应用程序中编写代码保证。2020年2月8日6时29分78.2实现数据完整性设有如下两张表：CREATETABLE职工表(职工编号CHAR(7)NOTNULL,职工名CHAR(10)NOTNULL,工作编号CHAR(8),工资SMALLINT,电话CHAR(8),身份证号CHAR(18))2020年2月8日6时29分8CREATETABLE工作表(工作编号CHAR(8)NOTNULL,最低工资SMALLINT,最高工资SMALLINT)1.实体完整性约束实体完整性是用PRIMARYKEY来保证。注意：每个表只能有一个PRIMARYKEY约束；用PRIMARYKEY约束的列的取值必须是不重复的（对由多列构成的主键，是这些主键列组合起来取值不重），并且不允许有空值。2020年2月8日6时29分9添加主键约束ALTERTABLE表名ADD[CONSTRAINT约束名]PRIMARYKEY（列名[,…n]）2020年2月8日6时29分10示例ALTERTABLE职工表ADDCONSTRAINTPK_EMPPRIMARYKEY（职工编号）ALTERTABLE工作表ADDCONSTRAINTPK_JOBPRIMARYKEY（工作编号）2020年2月8日6时29分112.唯一值约束用UNIQUE约束实现，用于限制一个列或者是多个列的组合取值不重复。用在事实上具有唯一性的属性列上，比如身份证号码、驾驶证号码等。注意：有UNIQUE约束的列允许有一个空值；在一个表中可以定义多个UNIQUE约束；可以在一个列或多个列上定义UNIQUE约束；2020年2月8日6时29分12在创建表时定义UNIQUE约束CREATETABLE表名（…列名类型[CONSTRAINT约束名]UNIQUE（列名[,…n]）,…）或者：CREATETABLE表名（列名类型,[CONSTRAINT约束名]UNIQUE（列名[,…n]））2020年2月8日6时29分13添加UNIQUE约束ALTERTABLE表名ADD[CONSTRAINT约束名]UNIQUE（列名[,…n]）2020年2月8日6时29分14示例——创建表时定义例2.为“身份证号”列添加唯一值约束。CREATETABLE职工表（…身份证号CHAR(19)UNIQUE,…）或：CREATETABLE职工表（…身份证号CHAR(19),…UNIQUE(身份证号),…）2020年2月8日6时29分15示例——添加约束ALTERTABLE职工表ADDCONSTRAINTUN_EMPUNIQUE（身份证号）2020年2月8日6时29分16复合唯一约束示例例3.设authors表，其中有au_fname和au_lname两个列，现要限制这两个列组合起来不重复。CREATETABLEauthors(…au_fnameVARCHAR(20),au_lnameVARCHAR(20)UNIQUE(au_fname,au_lname),--作为列级约束定义…)2020年2月8日6时29分17复合唯一约束示例CREATETABLEauthors(…au_fnameVARCHAR(20),au_lnameVARCHAR(20),UNIQUE(au_fname,au_lname),--作为表级约束定义…)182020年2月8日6时29分复合唯一约束示例在已创建好的表上添加唯一值约束：ALTERTABLEauthorsADDCONSTRAINTUN_NameUNIQUE(au_fname,au_lname)2020年2月8日6时29分193.参照完整性参照完整性（引用完整性）用FOREIGNKEY约束保证.定义FOREIGNKEY约束时要注意：外键列引用的列必须是有PRIMARYKEY约束或UNIQUE约束的列，通常是有PRIMARYKEY约束的列。2020年2月8日6时29分20添加外键约束ALTERTABLE表名ADD[CONSTRAINT约束名][FOREIGNKEY]（列名）REFERENCES引用表名（列名）[ONDELETE{CASCADE|NOACTION}][ONUPDATE{CASCADE|NOACTION}]ONDELETE：级联或限制删除；ONUPDATE：级联或限制更新。2020年2月8日6时29分21删除示例2020年2月8日6时29分22SnoSnameSsexSageSdept0811101李勇男21计算机系0811102刘晨男20计算机系0811103王敏女20计算机系0811104张小红女19计算机系0821101张立男20信息管理系0821102吴宾女19信息管理系0821103张海男20信息管理系SnoCnoGrade0811101C001960811101C002800811101C003840811101C005620811102C001920811102C002900811102C004840821102C001760821102C004850821102C005730821102C007NULL0821103C001500821103C004800811102刘晨男20计算机系0811102C001920811102C002900811102C00484ONDELETECASCADE[ONDELETENOACTION]0811102刘晨男20计算机系0811102C001920811102C002900811102C00484更新示例2020年2月8日6时29分23SnoSnameSsexSageSdept0811101李勇男21计算机系0811102刘晨男20计算机系0811103王敏女20计算机系0811104张小红女19计算机系0821101张立男20信息管理系0821102吴宾女19信息管理系0821103张海男20信息管理系SnoCnoGrade0811101C001960811101C002800811101C003840811101C005620811102C001920811102C002900811102C004840821102C001760821102C004850821102C005730821102C007NULL0821103C001500821103C004800811102刘晨男20计算机系0811102C001920811102C002900811102C00484ONUPDATECASCADE[ONUPDATENOACTION]0811110刘晨男20计算机系0811110C001920811110C002900811110C00484示例例4为职工表的“工作编号”列添加外键约束，此列引用工作表中的“工作编号”列。ALTERTABLE职工表ADDCONSTRAINTFK_job_idFOREIGNKEY(工作编号)REFERENCES工作表(工作编号)2020年2月8日6时29分244.默认值约束用DEFAULT约束实现，用于提供列的默认值。注意：只在向表中插入数据时才检查DEFAULT约束；每个列只能有一个DEFAULT约束。2020年2月8日6时29分25定义或添加默认值约束CREATETABLE表名（…列名类型[CONSTRAINT约束名]DEFAULT常量表达式,…）ALTERTABLE表名ADD[CONSTRAINT约束名]DEFAULT常量表达式FOR列名2020年2月8日6时29分26示例例5在职工表中，如果某个职工没有电话，则写入默认值：‘11111111’。CREATETABLE职工表(…电话CHAR(8)DEFAULT'11111111',…)或：ALTERTABLE职工表ADDCONSTRAINTDF_PHONEDEFAULT'11111111'FOR电话2020年2月8日6时29分275.列取值范围约束用CHECK约束实现，用于限制列的取值在指定范围内，即约束列的取值符合应用语义，如：人的性别只能是“男”或“女”，工资必须大于1000。使用CHECK约束时注意：在执行INSERT语句和UPDATE语句时系统自动检查CHECK约束；CHECK约束可以限制一个列的取值范围，也可以限制同表多列之间的取值约束关系。2020年2月8日6时29分28定义或添加CHECK约束CREATETABLE表名(…列名类型[CONSTRAINT约束名]CHECK(逻辑表达式),…）或：ALTERTABLE表名ADD[CONSTRAINT约束名]CHECK（逻辑表达式）2020年2月8日6时29分29示例——定义CHECK约束例6为职工表定义工资必须大于等于1000的约束。CREATETABLE职工表（…工资SMALLINTCHECK(工资=1000),…）或者：CREATETABLE职工表（…工资SMALLINT,…CHECK(工资=1000),…）2020年2月8日6时29分30示例——添加CHECK约束ALTERTABLE职工表ADDCONSTRAINTCHK_SalaryCHECK(工资=1000)2020年2月8日6时29分31示例：定义多列取值约束例7限制“最低工资”=“最高工资”。CREATETABLE工作表（…最低工资int,最高工资int,[…]CHECK（最低工资=最高工资）,…）注意：多列之间的CHECK约束只能定义在表级约束处。2020年2月8日6时29分32示例：添加多列取值约束ALTERTABLE工作表ADDCONSTRAINTCHK_Job_SalaryCHECK(最低工资=最高工资)2020年2月8日6时29分33示例例8限制电话号码列的每一位的取值必须是0~9之间的数字。…CHECK（电话LIKE‘[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]’）…2020年2月8日6时29分348.3系统对完整性约束的检查主键约束：插入和更新数据时，检查新数据的主键值是否与已存在的主键值重复，或者新主键值是否为空。唯一值约束：同主键约束。默认值约束：插入数据且没有为某个列提供值时检查。列取值范围约束：插入和修改有列取值约束的数据检查。2020年2月8日6时29分35外键约束的检查——对子表插入数据时，检