第8章-数据库-数据完整性约束课件

第8章数据完整性约束8.1数据完整性的概念8.2实现数据完整性8.3系统对完整性约束的检查8.4删除约束8.5触发器*18.1数据完整性的概念数据完整性是指数据的正确性和相容性。如：每个人的身份证号必须是唯一的，人的性别只能是‘男’或‘女’。为了维护数据的完整性，DBMS必须要提供一种机制来检查数据库中的数据。这些加在数据库数据之上的语义约束条件就称为数据完整性约束条件。DBMS中检查数据是否满足完整性条件的机制就称为完整性检查。*2完整性约束条件的作用对象列级约束元组约束关系约束*3列级约束主要是对列的类型、取值范围、精度等的约束，包括：对数据类型的约束：数据类型、长度、精度等例：snochar(8)对数据格式的约束：Sno:201001101

年专业班号序列号对取值范围的约束：如学生的成绩取值范围为0～100。对空值的约束：列是否允许有空值。*4元组约束元组约束是元组中各个字段之间的联系的约束，如：开始日期小于结束日期订货数量小于等于库存数量最低工资不能低于规定的最低值*5关系约束是指若干元组之间、关系之间的联系的约束。比如：学号的取值不能重复也不能取空值学生修课表中学号的取值受学生表中学号取值的限制等。*6实现数据完整性的方法可以在服务器端完成，也可以在客户端编程实现。在服务器端实现数据完整性的方法主要有两种：声明数据完整性触发器在客户端实现数据完整性主要是用前端开发工具，在应用程序中编写代码保证。*78.2实现数据完整性设有如下两张表：CREATETABLE职工表(职工编号CHAR(7)NOTNULL,职工名CHAR(10)NOTNULL,工作编号CHAR(8),工资SMALLINT,电话CHAR(8),身份证号CHAR(18))

*8CREATETABLE工作表(工作编号CHAR(8)NOTNULL,最低工资SMALLINT,最高工资SMALLINT)1.实体完整性约束实体完整性是用PRIMARYKEY来保证。注意：每个表只能有一个PRIMARYKEY约束；用PRIMARYKEY约束的列的取值必须是不重复的（对由多列构成的主键，是这些主键列组合起来取值不重），并且不允许有空值。*9添加主键约束ALTERTABLE表名ADD[CONSTRAINT约束名]PRIMARYKEY（<列名>[,…n]）*10示例ALTERTABLE职工表ADDCONSTRAINTPK_EMPPRIMARYKEY（职工编号）ALTERTABLE工作表ADDCONSTRAINTPK_JOBPRIMARYKEY（工作编号）*112.唯一值约束用UNIQUE约束实现，用于限制一个列或者是多个列的组合取值不重复。用在事实上具有唯一性的属性列上，比如身份证号码、驾驶证号码等。注意：有UNIQUE约束的列允许有一个空值；在一个表中可以定义多个UNIQUE约束；可以在一个列或多个列上定义UNIQUE约束；*12在创建表时定义UNIQUE约束CREATETABLE表名（

…

列名类型[CONSTRAINT约束名]UNIQUE（<列名>[,…n]）,

…）或者：CREATETABLE表名（

列名类型,[CONSTRAINT约束名]UNIQUE（<列名>[,…n]））*13添加UNIQUE约束ALTERTABLE表名ADD[CONSTRAINT约束名]UNIQUE（<列名>[,…n]）*14示例——创建表时定义例2.为“身份证号”列添加唯一值约束。CREATETABLE职工表（

…

身份证号CHAR(19)UNIQUE,

…

）或：CREATETABLE职工表（

…

身份证号CHAR(19),

…UNIQUE(身份证号),…）*15示例——添加约束ALTERTABLE职工表ADDCONSTRAINTUN_EMPUNIQUE（身份证号）*16复合唯一约束示例例3.设authors表，其中有au_fname和au_lname两个列，现要限制这两个列组合起来不重复。CREATETABLEauthors(

…au_fnameVARCHAR(20),au_lnameVARCHAR(20)UNIQUE(au_fname,au_lname),--作为列级约束定义

…)*17复合唯一约束示例CREATETABLEauthors(

…au_fnameVARCHAR(20),au_lnameVARCHAR(20),UNIQUE(au_fname,au_lname),

--作为表级约束定义

…)18*复合唯一约束示例在已创建好的表上添加唯一值约束：ALTERTABLEauthorsADDCONSTRAINTUN_Name

UNIQUE(au_fname,au_lname)*193.参照完整性参照完整性（引用完整性）用FOREIGNKEY约束保证.定义FOREIGNKEY约束时要注意：外键列引用的列必须是有PRIMARYKEY约束或UNIQUE约束的列，通常是有PRIMARYKEY约束的列。*20添加外键约束ALTERTABLE表名ADD[CONSTRAINT约束名]

[FOREIGNKEY]（<列名>）REFERENCES引用表名（<列名>）[ONDELETE{CASCADE|NOACTION}][ONUPDATE{CASCADE|NOACTION}]ONDELETE：级联或限制删除；ONUPDATE：级联或限制更新。*21删除示例*22SnoSnameSsexSageSdept0811101李勇

男21计算机系0811102刘晨

男20计算机系0811103王敏

女20计算机系0811104张小红

女19计算机系0821101张立

男20信息管理系0821102吴宾

女19信息管理系0821103张海

男20信息管理系SnoCnoGrade0811101C001960811101C002800811101C003840811101C005620811102C001920811102C002900811102C004840821102C001760821102C004850821102C005730821102C007NULL0821103C001500821103C004800811102刘晨

男20计算机系0811102C001920811102C002900811102C00484ONDELETECASCADE[ONDELETENOACTION]√x0811102刘晨

男20计算机系0811102C001920811102C002900811102C00484更新示例*23SnoSnameSsexSageSdept0811101李勇

男21计算机系0811102刘晨

男20计算机系0811103王敏

女20计算机系0811104张小红

女19计算机系0821101张立

男20信息管理系0821102吴宾

女19信息管理系0821103张海

男20信息管理系SnoCnoGrade0811101C001960811101C002800811101C003840811101C005620811102C001920811102C002900811102C004840821102C001760821102C004850821102C005730821102C007NULL0821103C001500821103C004800811102刘晨

男20计算机系0811102C001920811102C002900811102C00484ONUPDATECASCADE[ONUPDATENOACTION]√x0811110刘晨

男20计算机系0811110C001920811110C002900811110C00484示例例4为职工表的“工作编号”列添加外键约束，此列引用工作表中的“工作编号”列。ALTERTABLE职工表ADDCONSTRAINTFK_job_id

FOREIGNKEY(工作编号)REFERENCES工作表(工作编号)*244.默认值约束用DEFAULT约束实现，用于提供列的默认值。注意：只在向表中插入数据时才检查DEFAULT约束；每个列只能有一个DEFAULT约束。*25定义或添加默认值约束CREATETABLE表名（

…

列名类型[CONSTRAINT约束名]DEFAULT常量表达式,

…）ALTERTABLE表名ADD[CONSTRAINT约束名]DEFAULT常量表达式FOR列名*26示例例5在职工表中，如果某个职工没有电话，则写入默认值：‘11111111’。CREATETABLE职工表( …

电话CHAR(8)DEFAULT'11111111',

…)或：ALTERTABLE职工表ADDCONSTRAINTDF_PHONEDEFAULT'11111111'FOR电话*275.列取值范围约束用CHECK约束实现，用于限制列的取值在指定范围内，即约束列的取值符合应用语义，如：人的性别只能是“男”或“女”，工资必须大于1000。使用CHECK约束时注意：在执行INSERT语句和UPDATE语句时系统自动检查CHECK约束；CHECK约束可以限制一个列的取值范围，也可以限制同表多列之间的取值约束关系。*28定义或添加CHECK约束CREATETABLE表名(

…

列名类型[CONSTRAINT约束名

]CHECK(逻辑表达式),

…）或：ALTERTABLE表名ADD[CONSTRAINT约束名]

CHECK（逻辑表达式）*29示例——定义CHECK约束例6为职工表定义工资必须大于等于1000的约束。CREATETABLE职工表（…

工资SMALLINTCHECK(工资>=1000),

…

）或者：CREATETABLE职工表（…

工资SMALLINT,

…

CHECK(工资>=1000),

…

）*30示例——添加CHECK约束ALTERTABLE职工表

ADDCONSTRAINTCHK_SalaryCHECK(工资>=1000)*31示例：定义多列取值约束例7限制“最低工资”<=“最高工资”。CREATETABLE工作表（…最低工资int,最高工资int,[…]CHECK（最低工资<=最高工资）,…

）注意：多列之间的CHECK约束只能定义在表级约束处。*32示例：添加多列取值约束ALTERTABLE工作表ADDCONSTRAINTCHK_Job_Salary

CHECK(最低工资<=最高工资)*33示例例8限制电话号码列的每一位的取值必须是0~9之间的数字。

…CHECK（电话LIKE‘[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]’）

…*348.3系统对完整性约束的检查主键约束：插入和更新数据时，检查新数据的主键值是否与已存在的主键值重复，或者新主键值是否为空。唯一值约束：同主键约束。默认值约束：插入数据且没有为某个列提供值时检查。列取值范围约束：插入和修改有列取值约束的数据检查。*35外键约束的检查——对子表插入数据时，检查新数据的外键值是否在主表的主键值范围内。修改外键列值时，检查修改后的外键值是否在主表的主键值范围内。*36外键约束的检查——对主表删除数据时，检查被删除数据的主键值是否在子表中有对它的引用，若无，则删除之；若有，则看是否允许级联删除：若允许：则将子表中相应数据一起删掉；若不允许：则删除失败。更改主键列值时，检查被更改的主键值是否在子表中有对它的引用，若无，则更改之；若有，则看是否允许级联更改：若允许：则将子表中相应数据一起进行更改；若不允许：则更改失败。*378.4删除约束ALTERTABLE表名DROP[CONSTRAINT]约束名例9删除在职工表上定义的限制电话号码的CHK_PHONE约束。ALTERTABLE职工表DROPCHK_PHONE*388.5触发器是一段由对数据的更改操作引发的自动执行的代码。更改操作包括：UPDATE、INSERT、DELETE通常用于保证业务规则和数据完整性主要优点是用户可以用编程的方法实现复杂的处理逻辑和商业规则，增强了数据完整性约束的功能。*39创建触发器CREATETRIGGER触发器名称ON{表名|视图名}{FOR|AFTER|INSTEADOF}{[INSERT][,][DELETE][,][UPDATE]}AS

SQL语句*40注意在一个表上可以建立多个名称不同、类型各异的触发器，每个触发器可由所有三个操作引发对AFTER型触发器，可以在同一种操作上建立多个触发器；对INSTEADOF型触发器，在同一种操作上只能建立一个触发器。大部分SQL语句都可用在触发器中，但所有的创建和更改数据库以及数据库对象的语句、所有的DROP语句都不允许在触发器中使用。*41两个特殊的临时表在触发器中可以使用两个特殊的临时表：INSERTEDDELETED由系统自动创建，结构同建立触发器的表结构只能用在触发器代码中。INSERTED：保存INSERT操作中新插入的数据和UPDATE操作中更新后的数据；DELETED：保存DELETE操作删除的数据和UPDATE操作中更新前的数据。*42后触发型触发器当后触发型触发器执行时，引发触发器执行的数据操作语句已经执行完成*43执行到引发触发器执行的操作语句执行触发器执行该语句示例：维护不同表数据之间的取值约束例1.针对职工表和工作表，限制职工工资必须在相应工作的最低工资到最高工资之间。CREATETriggertri_SalaryON职工表AFTERINSERT,UPDATEASIFEXISTS(SELECT*FROM