数据库原理第四课件.ppt

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,Spring,2009.,Principles of Database,*,数据库原理,主讲：赵海霞,河南科技大学电信学院,第五章 数据库完整性,实体完整性,参照完整性,用户定义的完整性,完整性约束命名字句,触发器,小结,概述,数据库完整性：数据的,正确性,和,相容性,是衡量数据库数据质量好坏的一种标志，是确保数据库中数据的,一致,，,正确性,以及,符合企业规则,的一种思想，是使无序数据条理化，确保,正确的,数据被存放在,正确位置,的一种手段。,概述,数据的完整性和安全性是两个不同概念,完整性,防止数据库中存在不符合语义的数据，也就是防止数据库中存在不正确的数据,防范对象：不合语义的、不正确的数据,安全性,保护数据库防止恶意的破坏和非法的存取,防范对象：非法用户和非法操作,概述,满足数据完整性的要求必须满足以下特点：,数据的值必须正确无误，即数据类型必须正确，数据的值必须在规定范围之内,数据的存在必须确保同一表格数据之间及不同表格数据之间的和谐关系,概述,提供定义完整性约束条件的机制,提供完整性检查的方法,违约处理,为维护数据库的完整性，,DBMS,必须,:,第五章 数据库完整性,实体完整性,参照完整性,用户定义的完整性,完整性约束命名字句,触发器,小结,实体完整性定义,实体完整性检查和违约处理,5.1,实体完整性,5.1.1,实体完整性定义,关系模型的实体完整性,CREATE TABLE,中用,PRIMARY KEY,定义,单属性构成的码有两种说明方法,定义为列级约束条件,定义为表级约束条件,对多个属性构成的码只有一种说明方法,定义为表级约束条件,例,1,将,Student,表中的,Sno,属性定义为码,(1),在列级定义主码,CREATE TABLE Student,(,Sno,CHAR(9)PRIMARY KEY,，,Sname,CHAR(20)NOT NULL,，,Ssex,CHAR(2),，,Sage SMALLINT,，,Sdept,CHAR(20);,实体完整性定义,例,1,将,Student,表中的,Sno,属性定义为码,(2)在表级定义主码,CREATE TABLE Student,(,Sno,CHAR(9)，,Sname,CHAR(20)NOT NULL，,Ssex,CHAR(2)，,Sage SMALLINT，,Sdept,CHAR(20)，,PRIMARY KEY(,Sno,),);,实体完整性定义,例,2,将,SC,表中的,Sno,，,Cno,属性组定义为码,CREATE TABLE SC,(,Sno,CHAR(9)NOT NULL,，,Cno,CHAR(4)NOT NULL,，,Grade SMALLINT,，,PRIMARY KEY(,Sno,，,Cno,)/*,只能在表级定义主码*,/,);,实体完整性定义,实体完整性定义,实体完整性检查和违约处理,5.1,实体完整性,实体完整性检查和违约处理,插入或对主码列进行更新操作时，,RDBMS,按照实体完整性规则自动进行检查。包括：,1.,检查主码值是否唯一，如果不唯一则拒绝插入或修改,2.,检查主码的各个属性是否为空，只要有一个为空就拒绝插入或修改,方法：全表扫描和索引,第五章 数据库完整性,实体完整性,参照完整性,用户定义的完整性,完整性约束命名字句,触发器,小结,参照完整性定义,参照完整性检查和违约处理,5.2,参照完整性,5.2.1,参照完整性定义,关系模型的参照完整性定义,在,CREATE TABLE,中用,FOREIGN KEY,短语定义哪些列为外码,用,REFERENCES,短语指明这些外码参照哪些表的主码,5.2.1,参照完整性定义,例如，关系,SC,中一个元组表示一个学生选修的某门课程的成绩，（,Sno,，,Cno,）是主码。,Sno,，,Cno,分别参照引用,Student,表的主码和,Course,表的主码,5.2.1,参照完整性定义,例,3,定义,SC,中的参照完整性,CREATE TABLE SC,(,Sno,CHAR(9)NOT NULL,，,Cno,CHAR(4)NOT NULL,，,Grade SMALLINT,，,PRIMARY KEY(,Sno,，,Cno,),，,/*,在表级定义实体完整性*,/,FOREIGN KEY(,Sno,)REFERENCES,Student(Sno,),，,/*,在表级定义参照完整性*,/,FOREIGN KEY(,Cno,)REFERENCES,Course(Cno,),/*,在表级定义参照完整性*,/,);,参照完整性定义,参照完整性检查和违约处理,5.2,参照完整性,5.2.2,参照完整性检查和违约处理,可能破坏参照完整性的情况及违约处理,被参照表（例如,Student,）,参照表（例如,SC,）,违约处理,可能破坏参照完整性,插入元组,拒绝,可能破坏参照完整性,修改外码值,拒绝,删除元组,可能破坏参照完整性,拒绝,/,级连删除,/,设置为空值,修改主码值,可能破坏参照完整性,拒绝,/,级连修改,/,设置为空值,违约处理,参照完整性违约处理,拒绝,(NO ACTION),执行,默认策略,级联,(CASCADE),操作,设置为空值（,SET-NULL,）,对于参照完整性，除了应该定义外码，还应定义,外码列是否允许空值,违约处理,例,4,显式说明参照完整性的违约处理示例,CREATE TABLE SC,(,Sno,CHAR(9)NOT NULL,，,Cno,CHAR(4)NOT NULL,，,Grade SMALLINT,，,PRIMARY KEY,（,Sno,，,Cno,），,FOREIGN KEY(,Sno,)REFERENCES,Student(Sno,),ON DELETE CASCADE /*,级联删除,SC,表中相应的元组*,/,ON UPDATE CASCADE,，,/*,级联更新,SC,表中相应的元组*,/,FOREIGN KEY(,Cno,)REFERENCES,Course(Cno,),ON DELETE NO ACTION,/*,当删除,course,表中的元组造成了与,SC,表不一致时,拒绝删除,*,/,ON UPDATE CASCADE,/*,当更新,course,表中的,cno,时，,级联更新,SC,表中相应的元组*,/,),；,第五章 数据库完整性,实体完整性,参照完整性,用户定义的完整性,完整性约束命名字句,触发器,小结,5.3,用户定义的完整性,用户定义的完整性就是针对某一具体应用的数据必须满足的语义要求,RDBMS,提供，而不必由应用程序承担,5.3,用户定义的完整性,属性上的约束条件的定义,属性上的约束条件检查和违约处理,元组上的约束条件的定义,元组上的约束条件检查和违约处理,5.3.1,属性上的约束条件的定义,CREATE TABLE,时定义,列值非空（,NOT NULL,）,列值唯一（,UNIQUE,）,检查列值是否满足一个布尔表达式（,CHECK,）,属性上的约束条件的定义,1.,不允许取空值,例,5,在定义,SC,表时，说明,Sno,、,Cno,、,Grade,属性不允许取空值。,CREATE TABLE SC,（,Sno,CHAR(9),NOT NULL,，,Cno,CHAR(4),NOT NULL,，,Grade SMALLINT,NOT NULL,，,PRIMARY KEY(,Sno,，,Cno,),，,）；,属性上的约束条件的定义,2.,列值唯一,例,6,建立部门表,DEPT,，要求部门名称,Dname,列取值唯一，部门编号,Deptno,列为主码,CREATE TABLE DEPT,(,Deptno,NUMERIC(2),，,Dname,CHAR(9),UNIQUE,，,/*,要求,Dname,列值唯一*,/,Location CHAR(10),，,PRIMARY KEY(,Deptno,),),；,属性上的约束条件的定义,3.,用,CHECK,短语指定列值应该满足的条件,例,7,Student,表的,Ssex,只允许取“男”或“女”。,CREATE TABLE Student,(,Sno,CHAR(9)PRIMARY KEY,，,Sname,CHAR(8)NOT NULL,，,Ssex,CHAR(2),CHECK(,Ssex,IN(,男，女,),，,/*,性别属性,Ssex,只允许取,男,或,女,*/,Sage SMALLINT,，,Sdept,CHAR(20),);,5.3,用户定义的完整性,属性上的约束条件的定义,属性上的约束条件检查和违约处理,元组上的约束条件的定义,元组上的约束条件检查和违约处理,5.3.2,属性上的约束条件检查和违约处理,插入元组或修改属性,的值时，,RDBMS,检查属性上的约束条件是否被满足,如果不满足则操作被,拒绝,执行,5.3,用户定义的完整性,属性上的约束条件的定义,属性上的约束条件检查和违约处理,元组上的约束条件的定义,元组上的约束条件检查和违约处理,5.3.3,元组上的约束条件的定义,在,CREATE TABLE,时可以用,CHECK,短语定义元组上的约束条件，即元组级的限制,同属性值限制相比，元组级的限制可以设置,不同属性之间,的取值的相互约束条件,元组上的约束条件的定义,例,9,当学生的性别是男时，其名字不能以,Ms.,打头。,CREATE TABLE Student,(,Sno,CHAR(9),，,Sname,CHAR(8)NOT NULL,，,Ssex,CHAR(2),，,Sage SMALLINT,，,Sdept,CHAR(20),，,PRIMARY KEY(,Sno,),，,CHECK(,Ssex,=,女,OR,Sname,NOT LIKE Ms.%),/*,定义了元组中,Sname,和,Ssex,两个属性值之间的约束条件*,/,),；,性别是女性的元组都能通过该项检查，因为,Ssex,=,女成立；,当性别是男性时，要通过检查则名字一定不能以,Ms.,打头,5.3,用户定义的完整性,属性上的约束条件的定义,属性上的约束条件检查和违约处理,元组上的约束条件的定义,元组上的约束条件检查和违约处理,5.3.4,元组上的约束条件检查和违约处理,插入元组或修改,属性的值时，,RDBMS,检查元组上的约束条件是否被满足,如果不满足则操作被,拒绝,执行,第五章 数据库完整性,实体完整性,参照完整性,用户定义的完整性,完整性约束命名字句,触发器,小结,5.4,完整性约束命名子句,1.CONSTRAINT,约束,CONSTRAINT,PRIMARY KEY,短语,|FOREIGN KEY,短语,|CHECK,短语,完整性约束命名子句,例,10,建立学生登记表,Student,，要求学号在,9000099999,之间，姓名不能取空值，年龄小于,30,，性别只能是“男”或“女”。,CREATE TABLE Student,(,Sno,NUMERIC(6),CONSTRAINT C1 CHECK(,Sno,BETWEEN 90000 AND 99999),，,Sname,CHAR(20),CONSTRAINT C2 NOT NULL,，,Sage NUMERIC(3),CONSTRAINT C3 CHECK(Sage 30),，,Ssex,CHAR(2),CONSTRAINT C4 CHECK(,Ssex,IN(,男,，,女,),，,CONSTRAINT,StudentKey,PRIMARY,KEY(Sno,),),；,在,Student,表上建立了,5,个约束条件，包括主码约束（命名为,StudentKey,）以及,C1,、,C2,、,C3,、,C4,四个列级约束。,完整性约束命名子句,2.,修改表中的完整性限制,使用,ALTER TABLE,语句修改表中的完整性限制,完整性约束命名子句,例,13,修改表,Student,中的约束条件，要求学号改为在,900000999999,之间，年龄由小于,30,改为小于,40,可以先删除原来的约束条件，再增加新的约束条件,ALTER TABLE Student,DROP CONSTRAINT C1;,ALTER TABLE Student,ADD CONSTRAINT C1 CHECK(,Sno,BETWEEN 900000 AND 999999),，,ALTER TABLE Student,DROP CONSTRAINT C3;,ALTER TABLE Student,ADD CONSTRAINT C3 CHECK(Sage,在线,).,在这个例子中触发器发挥了它的作用,!,写个简单的,SQL,就可以让,SQLSERVER,在对,A,表插入登陆时间的同时在,B,中更改该用户状态,!,5.6,触发器,定义触发器,激活触发器,删除触发器,5.6.1,定义触发器,CREATE TRIGGER,语法格式,CREATE TRIGGER,BEFORE|AFTER ON,FOR EACH ROW|STATEMENT,WHEN,定义触发器,(,续,),定义触发器的语法说明,:,1.,创建者：表的,拥有者,2.,触发器名,3.,表名：触发器的目标表,4.,触发事件：,INSERT,、,DELETE,、,UPDATE,5.,触发器类型,行级触发器（,FOR EACH ROW,）,语句级触发器（,FOR EACH STATEMENT,）,定义触发器,(,续,),例如,假设在例,11,的,TEACHER,表上创建了一个,AFTER UPDATE,触发器。如果表,TEACHER,有,1000,行，执行如下语句：,UPDATE TEACHER SET,Deptno,=5;,如果该触发器为语句级触发器，那么执行完该语句后，触发动作只发生一次,如果是行级触发器，触发动作将执行,1000,次,定义触发器,(,续,),6.,触发条件,触发条件为真,省略,WHEN,触发条件,7.,触发动作体,触发动作体可以是一个匿名,PL/SQL,过程块,也可以是对已创建存储过程的调用,定义触发器,(,续,),例,18,定义一个,BEFORE,行级触发器，为教师表,Teacher,定义完整性规则“教授的工资不得低于,4000,元，如果低于,4000,元，自动改为,4000,元”。,CREATE TRIGGER,Insert_Or_Update_Sal,BEFORE,INSERT OR UPDATE,ON Teacher,/*,触发事件是插入或更新操作*,/,FOR,EACH ROW,/*,行级触发器*,/,AS BEGIN,/*,定义触发动作体，是,PL/SQL,过程块*,/,IF(,new.Job,=,教授,)AND(,new.Sal,4000)THEN,new.Sal,:=4000;,END IF;,END;,定义触发器,(,续,),例,19,定义,AFTER,行级触发器，当教师表,Teacher,的工资发生变化后就自动在工资变化表,Sal_log,中增加一条相应记录,首先建立工资变化表,Sal_log,CREATE TABLE,Sal_log,(,Eno,NUMERIC(4)references,teacher(eno,),，,Sal NUMERIC(7,，,2),，,Username char(10),，,Date TIMESTAMP,),；,定义触发器,(,续,),例,19,（续）,CREATE TRIGGER,Insert_Sal,AFTER INSERT,ON Teacher /*,触发事件是,INSERT*/,FOR EACH ROW,AS BEGIN,INSERT INTO,Sal_log,VALUES(,new.Eno,，,new.Sal,，,CURRENT_USER,，,CURRENT_TIMESTAMP);,END;,定义触发器,(,续,),例,19,（续）,CREATE TRIGGER,Update_Sal,AFTER UPDATE,ON Teacher /*,触发事件是,UPDATE*/,FOR EACH ROW,AS BEGIN,IF(,new.Sal,old.Sal,)THEN INSERT INTO,Sal_log,VALUES(,new.Eno,，,new.Sal,，,CURRENT_USER,，,CURRENT_TIMESTAMP);,END IF;,END;,SQL Server,中的触发器,理解触发器里面的两个临时表：,Deleted,Inserted,。,注意,Deleted,与,Inserted,分别表示触发事件的表“旧的一条记录”和“新的一条记录”。,SQL Server,中的触发器,两个虚拟表用于存储在表中记录改动的信息，分别是：,在表记录 虚拟表,Inserted,虚拟表,Deleted,新增时 存放新增的记录 不存储记录,修改时存放用来更新的新记录存放更新前的记录,删除时不存储记录 存放被删除的记录,一个,Update,的过程可以看作为：生成新的记录到,Inserted,表，复制旧的记录到,Deleted,表，然后删除,Student,记录并写入新记录。,SQL Server,中的触发器,创建触发器用,CREATE TRIGGERCREATE TRIGGER,触发器名称,ON,表名,FOR INSERT,、,UPDATE,或,DELETEAS T-SQL,语句,SQL Server,中的触发器,实例,1:,提示成果插入,create trigger,person_insert,on personfor insertas,print(ok,已经成功插入数据,),SQL Server,中的触发器,实例,2:,限制一定的数据不能删除如电话为,13545173242,的记录不可以删除,create trigger,person_delete,on personfor delete,asdeclare phone char(15)select phone=phone from deletedif phone=13545173242 begin print(,该记录不可以删除,.)rollback transaction endelse,print(ok,已经成功删除,.)go,SQL Server,中的触发器,实例,3,：用触发器实现级联更新,有这么两个表：,CreateTableStudent(-,学生表,StudentID,int,primarykey,-,学号,.)CreateTable,BorrowRecord,(-,学生借书记录表,BorrowRecord,int,identity(1,1),-,流水号,StudentID,int,-,学号,BorrowDate,datetime,-,借出时间,ReturnDAte,Datetime,-,归还时间,.),SQL Server,中的触发器,用到的功能有,:1.,如果我更改了学生的学号,我希望他的借书记录仍然与这个学生相关,(,也就是同时更改借书记录表的学号,);2.,如果该学生已经毕业，我希望删除他的学号的同时，也删除它的借书记录。,可以用触发器实现。,对于,1,，创建一个,Update,触发器：,SQL Server,中的触发器,CreateTrigger,truStudent,OnStudent-,在,Student,表中创建触发器,forUpdate-,为什么事件触发,As-,事件触发后所要做的事情,if,Update(StudentID,)beginUpdate,BorrowRecord,Set,StudentID,=,inserted.StudentID,Where,StudentID,in(select,StudentID,from Deleted)-,级联条件,(,方式,),end,/*,注意,Deleted,和,Inserted,表达的意义*,/,注意此例子,update,语句应写成嵌套语句，请大家思考。,SQL Server,中的触发器,对于,2,，创建一个,Delete,触发器,Createtrigger,trdStudent,OnStudentforDeleteAsDelete from,BorrowRecord,Where,StudentID,in(select,StudentID,from deleted),从这两个例子我们可以看到了触发器的关键：,A.2,个临时的表；,B.,触发机制。,5.6,触发器,定义触发器,激活触发器,删除触发器,激活触发器,触发器的执行，是由,触发事件激活,的，并由数据库服务器自动执行,一个数据表上可能定义了,多个触发器,同一个表上的多个触发器激活时遵循如下的执行顺序：,（,1,）执行该表上的,BEFORE,触发器；,（,2,）激活触发器的,SQL,语句；,（,3,）执行该表上的,AFTER,触发器。,激活触发器（续）,例,20,执行修改某个教师工资的,SQL,语句，激活上述定义的触发器。,UPDATE Teacher SET Sal=800 WHERE,Ename,=,陈平,;,执行顺序是：,执行触发器,Insert_Or_Update_Sal,执行,SQL,语句“,UPDATE Teacher SET Sal=800 WHERE,Ename,=,陈平,;”,执行触发器,Insert_Sal,；,执行触发器,Update_Sal,5.6,触发器,定义触发器,激活触发器,删除触发器,5.6.3,删除触发器,删除触发器的,SQL,语法：,DROP TRIGGER ON;,触发器必须是一个已经创建的触发器，并且只能由具有相应权限的用户删除。,例,21,删除教师表,Teacher,上的触发器,Insert_Sal,DROP TRIGGER,Insert_Sal,ON Teacher;,第五章 数据库完整性,实体完整性,参照完整性,用户定义的完整性,完整性约束命名字句,触发器,小结,5.7,小结,数据库的完整性是为了保证数据库中存储的数据是正确的,RDBMS,完整性实现的机制,完整性约束定义机制,完整性检查机制,违背完整性约束条件时,RDBMS,应采取的动作,