第8章-数据库--数据完整性约束.ppt

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