《第8章数据库数据完整性约束课件.ppt》由会员分享，可在线阅读，更多相关《第8章数据库数据完整性约束课件.ppt（49页珍藏版）》请在三一办公上搜索。
1、第8章 数据完整性约束,8.1 数据完整性的概念8.2 实现数据完整性8.3 系统对完整性约束的检查8.4 删除约束8.5 触发器,2023年4月3日11时37分,1,8.1 数据完整性的概念,数据完整性是指数据的正确性和相容性。如：每个人的身份证号必须是唯一的，人的性别只能是男或女。为了维护数据的完整性，DBMS必须要提供一种机制来检查数据库中的数据。这些加在数据库数据之上的语义约束条件就称为数据完整性约束条件。DBMS中检查数据是否满足完整性条件的机制就称为完整性检查。,2023年4月3日11时37分,2,完整性约束条件的作用对象,列级约束元组约束关系约束,2023年4月3日11时37分,
2、3,列级约束,主要是对列的类型、取值范围、精度等的约束，包括：对数据类型的约束：数据类型、长度、精度等例：sno char(8)对数据格式的约束：Sno:201001101年专业班号序列号对取值范围的约束：如学生的成绩取值范围为0100。对空值的约束：列是否允许有空值。,2023年4月3日11时37分,4,元组约束,元组约束是元组中各个字段之间的联系的约束，如：开始日期小于结束日期订货数量小于等于库存数量最低工资不能低于规定的最低值,2023年4月3日11时37分,5,关系约束,是指若干元组之间、关系之间的联系的约束。比如：学号的取值不能重复也不能取空值学生修课表中学号的取值受学生表中学号取值
3、的限制等。,2023年4月3日11时37分,6,实现数据完整性的方法,可以在服务器端完成，也可以在客户端编程实现。在服务器端实现数据完整性的方法主要有两种：声明数据完整性触发器在客户端实现数据完整性主要是用前端开发工具，在应用程序中编写代码保证。,2023年4月3日11时37分,7,8.2 实现数据完整性,设有如下两张表：CREATE TABLE 职工表(职工编号 CHAR(7)NOT NULL,职工名 CHAR(10)NOT NULL,工作编号 CHAR(8),工资 SMALLINT,电话 CHAR(8),身份证号 CHAR(18),2023年4月3日11时37分,8,CREATE TABL
4、E 工作表(工作编号 CHAR(8)NOT NULL,最低工资 SMALLINT,最高工资 SMALLINT),1.实体完整性约束,实体完整性是用PRIMARY KEY来保证。注意：每个表只能有一个PRIMARY KEY约束；用PRIMARY KEY约束的列的取值必须是不重复的（对由多列构成的主键，是这些主键列组合起来取值不重），并且不允许有空值。,2023年4月3日11时37分,9,添加主键约束,ALTER TABLE 表名 ADD CONSTRAINT 约束名 PRIMARY KEY（,n）,2023年4月3日11时37分,10,示例,ALTER TABLE 职工表 ADD CONSTRA
5、INT PK_EMP PRIMARY KEY（职工编号）ALTER TABLE 工作表 ADD CONSTRAINT PK_JOB PRIMARY KEY（工作编号）,2023年4月3日11时37分,11,2.唯一值约束,用UNIQUE约束实现，用于限制一个列或者是多个列的组合取值不重复。用在事实上具有唯一性的属性列上，比如身份证号码、驾驶证号码等。注意：有UNIQUE约束的列允许有一个空值；在一个表中可以定义多个UNIQUE约束；可以在一个列或多个列上定义UNIQUE约束；,2023年4月3日11时37分,12,在创建表时定义UNIQUE约束,CREATE TABLE 表名（列名 类型 CO
6、NSTRAINT 约束名 UNIQUE（,n）,）或者：CREATE TABLE 表名（列名 类型,CONSTRAINT 约束名 UNIQUE（,n）,2023年4月3日11时37分,13,添加UNIQUE约束,ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE（,n）,2023年4月3日11时37分,14,示例创建表时定义,例2.为“身份证号”列添加唯一值约束。CREATE TABLE 职工表（身份证号 CHAR(19)UNIQUE,）或：CREATE TABLE 职工表（身份证号 CHAR(19),UNIQUE(身份证号),）,2023年4月3日11时37分,
7、15,示例添加约束,ALTER TABLE 职工表 ADD CONSTRAINT UN_EMP UNIQUE（身份证号）,2023年4月3日11时37分,16,复合唯一约束示例,例3.设authors表，其中有au_fname和au_lname两个列，现要限制这两个列组合起来不重复。CREATE TABLE authors(au_fname VARCHAR(20),au_lname VARCHAR(20)UNIQUE(au_fname,au_lname),-作为列级约束定义),2023年4月3日11时37分,17,复合唯一约束示例,CREATE TABLE authors(au_fname V
8、ARCHAR(20),au_lname VARCHAR(20),UNIQUE(au_fname,au_lname),-作为表级约束定义),18,2023年4月3日11时37分,复合唯一约束示例,在已创建好的表上添加唯一值约束：ALTER TABLE authors ADD CONSTRAINT UN_Name UNIQUE(au_fname,au_lname),2023年4月3日11时37分,19,3.参照完整性,参照完整性（引用完整性）用FOREIGN KEY约束保证.定义FOREIGN KEY约束时要注意：外键列引用的列必须是有PRIMARY KEY约束或UNIQUE约束的列，通常是有PR
9、IMARY KEY约束的列。,2023年4月3日11时37分,20,添加外键约束,ALTER TABLE 表名 ADD CONSTRAINT 约束名 FOREIGN KEY（）REFERENCES 引用表名（）ON DELETE CASCADE|NO ACTION ON UPDATE CASCADE|NO ACTION ON DELETE：级联或限制删除；ON UPDATE：级联或限制更新。,2023年4月3日11时37分,21,删除示例,2023年4月3日11时37分,22,ON DELETE CASCADE,ON DELETE NO ACTION,x,更新示例,2023年4月3日11时37
10、分,23,ON UPDATE CASCADE,ON UPDATE NO ACTION,x,示例,例4 为职工表的“工作编号”列添加外键约束，此列引用工作表中的“工作编号”列。ALTER TABLE 职工表 ADD CONSTRAINT FK_job_id FOREIGN KEY(工作编号)REFERENCES 工作表(工作编号),2023年4月3日11时37分,24,4.默认值约束,用DEFAULT约束实现，用于提供列的默认值。注意：只在向表中插入数据时才检查DEFAULT约束；每个列只能有一个DEFAULT约束。,2023年4月3日11时37分,25,定义或添加默认值约束,CREATE TA
11、BLE 表名（列名 类型 CONSTRAINT 约束名 DEFAULT 常量表达式,）ALTER TABLE 表名 ADD CONSTRAINT 约束名 DEFAULT 常量表达式 FOR 列名,2023年4月3日11时37分,26,示例,例5 在职工表中，如果某个职工没有电话，则写入默认值：11111111。CREATE TABLE 职工表(电话 CHAR(8)DEFAULT 11111111,)或：ALTER TABLE 职工表 ADD CONSTRAINT DF_PHONE DEFAULT 11111111 FOR 电话,2023年4月3日11时37分,27,5.列取值范围约束,用CHE
12、CK约束实现，用于限制列的取值在指定范围内，即约束列的取值符合应用语义，如：人的性别只能是“男”或“女”，工资必须大于1000。使用CHECK约束时注意：在执行INSERT语句和UPDATE语句时系统自动检查CHECK约束；CHECK约束可以限制一个列的取值范围，也可以限制同表多列之间的取值约束关系。,2023年4月3日11时37分,28,定义或添加CHECK约束,CREATE TABLE 表名(列名 类型 CONSTRAINT 约束名 CHECK(逻辑表达式),）或：ALTER TABLE 表名 ADD CONSTRAINT 约束名 CHECK（逻辑表达式）,2023年4月3日11时37分,
13、29,示例定义CHECK约束,例6 为职工表定义工资必须大于等于1000的约束。CREATE TABLE 职工表（工资 SMALLINT CHECK(工资=1000),）或者：CREATE TABLE 职工表（工资 SMALLINT,CHECK(工资=1000),）,2023年4月3日11时37分,30,示例添加CHECK约束,ALTER TABLE 职工表 ADD CONSTRAINT CHK_Salary CHECK(工资=1000),2023年4月3日11时37分,31,示例：定义多列取值约束,例7 限制“最低工资”=“最高工资”。CREATE TABLE 工作表（最低工资 int,最高
14、工资 int,CHECK（最低工资=最高工资）,）注意：多列之间的CHECK约束只能定义在表级约束处。,2023年4月3日11时37分,32,示例：添加多列取值约束,ALTER TABLE 工作表 ADD CONSTRAINT CHK_Job_Salary CHECK(最低工资=最高工资),2023年4月3日11时37分,33,示例,例8 限制电话号码列的每一位的取值必须是09之间的数字。CHECK（电话 LIKE 0-90-90-9 0-90-90-90-90-9）,2023年4月3日11时37分,34,8.3 系统对完整性约束的检查,主键约束：插入和更新数据时，检查新数据的主键值是否与已存
15、在的主键值重复，或者新主键值是否为空。唯一值约束：同主键约束。默认值约束：插入数据且没有为某个列提供值时检查。列取值范围约束：插入和修改有列取值约束的数据检查。,2023年4月3日11时37分,35,外键约束的检查对子表,插入数据时，检查新数据的外键值是否在主表的主键值范围内。修改外键列值时，检查修改后的外键值是否在主表的主键值范围内。,2023年4月3日11时37分,36,外键约束的检查对主表,删除数据时，检查被删除数据的主键值是否在子表中有对它的引用，若无，则删除之；若有，则看是否允许级联删除：若允许：则将子表中相应数据一起删掉；若不允许：则删除失败。更改主键列值时，检查被更改的主键值是否
16、在子表中有对它的引用，若无，则更改之；若有，则看是否允许级联更改：若允许：则将子表中相应数据一起进行更改；若不允许：则更改失败。,2023年4月3日11时37分,37,8.4 删除约束,ALTER TABLE 表名 DROP CONSTRAINT 约束名例9 删除在职工表上定义的限制电话号码的CHK_PHONE约束。ALTER TABLE 职工表 DROP CHK_PHONE,2023年4月3日11时37分,38,8.5 触发器,是一段由对数据的更改操作引发的自动执行的代码。更改操作包括：UPDATE、INSERT、DELETE通常用于保证业务规则和数据完整性主要优点是用户可以用编程的方法实现
17、复杂的处理逻辑和商业规则，增强了数据完整性约束的功能。,2023年4月3日11时37分,39,创建触发器,CREATE TRIGGER 触发器名称ON 表名|视图名 FOR|AFTER|INSTEAD OF INSERT,DELETE,UPDATE AS SQL 语句,2023年4月3日11时37分,40,注意,在一个表上可以建立多个名称不同、类型各异的触发器，每个触发器可由所有三个操作引发对AFTER型触发器，可以在同一种操作上建立多个触发器；对INSTEAD OF型触发器，在同一种操作上只能建立一个触发器。大部分SQL语句都可用在触发器中，但所有的创建和更改数据库以及数据库对象的语句、所有
18、的DROP语句都不允许在触发器中使用。,2023年4月3日11时37分,41,两个特殊的临时表,在触发器中可以使用两个特殊的临时表：INSERTEDDELETED由系统自动创建，结构同建立触发器的表结构只能用在触发器代码中。INSERTED：保存INSERT操作中新插入的数据和UPDATE操作中更新后的数据；DELETED：保存DELETE操作删除的数据和UPDATE操作中更新前的数据。,2023年4月3日11时37分,42,后触发型触发器,当后触发型触发器执行时，引发触发器执行的数据操作语句已经执行完成,2023年4月3日11时37分,43,执行到引发触发器执行的操作语句,执行触发器,执行该
19、语句,示例：维护不同表数据之间的取值约束,例1.针对职工表和工作表，限制职工工资必须在相应工作的最低工资到最高工资之间。CREATE Trigger tri_Salary ON 职工表 AFTER INSERT,UPDATEAS IF EXISTS(SELECT*FROM 职工表 a JOIN 工作表 b ON a.工作编号=b.工作编号 WHERE 工资 NOT BETWEEN 最低工资 AND 最高工资)ROLLBACK-撤销操作,2023年4月3日11时37分,44,示例：级联更新,例2.对职工表和工作表，实现工作表到工作编号与职工表的工作编号的级联更新。CREATE Trigger t
20、ri_Salary ON 工作表 AFTER UPDATEAS UPDATE 职工表 SET 工作编号=(SELECT 工作编号 FROM INSERTED)WHERE 工作编号 IN(SELECT 工作编号 FROM DELETED),2023年4月3日11时37分,45,前触发型触发器,指定执行触发器而不是执行引发触发器执行的SQL语句，从而替代引发语句的操作。,2023年4月3日11时37分,46,执行到引发触发器执行的操作语句,执行触发器,示例,例3 限制职工工资在最低工资到最高工资之间CREATE Trigger tri_Salary ON 职工表 INSTEAD OF INSERTAS IF NOT EXISTS(SELECT*FROM 职工表 a JOIN 工作表 b ON a.工作编号=b.工作编号 WHERE 工资 NOT BETWEEN 最低工资 AND 最高工资)INSERT INTO 职工表SELECT*FROM INSERTED,2023年4月3日11时37分,47,重做操作,删除触发器,DROP TRIGGER 触发器名例4 删除触发器tri1。DROP TRIGGER tri1,2023年4月3日11时37分,48,2023年4月3日11时37分,49,本章学习顺利结束,
