数据库完整性

1、第三章第三章 SQLSQL补充内容补充内容 -数据库的完整性数据库的完整性SQL中的完整性约束完整性约束的目的是保证用户对数据库进行修改时不会破坏数据的一致性（防止对数据的意外破坏）。注意：数据的完整性与安全性的概念区别。前者是为了防止数据库中存在不符合语义的数据（防止数据垃圾），后者是防止对数据的非法存取。完整性约束条件 NOT NULL 约束 缺省值 UNIQUE 约束 PRIMARY KEY 约束 FOREIGN KEY 约束 CHECK 约束NOT NULL 约束 限定了表的某一列中必须有值；缺省值 在数据录入时，若没有输入数据，数据库自动输入一个预先规定的值；UNIQUE 约束 该列

2、的值在表中不能重复出现（可以有空值）；PRIMARY KEY 约束 用来唯一标识表中的一行（不能有空值），必须是唯一的列（应选择一般不会改变、短的和数值型的列）；CHECK 约束 根据逻辑表达式来限定值域；实体完整性实体完整性 例1： 将student表中的Sno属性定义为码 CREATE TABLE student（sno char(9) primary key, sname char(20) not null, sex char(2),）CREATE TABLE student（sno char(9) primary key, sname char(20) not null, sex ch

3、ar(2),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);实体完整性实体完整性参照完整性参照完整性例3： 定义参照完整性CREATE TABLE sc(sno char(9) not null, cno char(4) not null, grade smallint,primary key(sno,cno)Foreign key(sno) ref

4、rences Student(sno),Foreign key(cno) refrences course(cno),);用户自定义完整性用户自定义完整性例4：定义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);用户自定义完整性用户自定义完整性例5:建立部门DEPT表，要求部门名称dname列取值唯一，部门编号deptno列为主码。Create table dept(deptn

5、o numeric(2),Dname char(9) unique,Location char(10),Primary key(deptno);用户自定义完整性用户自定义完整性例6:student表中的sex只能取“男”或“女”。CREATE TABLE student(sno char(9) primary key, sname char(20) not null, sex char(2) check(sex in(男,女),);用户自定义完整性用户自定义完整性例sc表中grade的值应该在0和100之间.CREATE TABLE sc(sno char(9) not null, cno c

6、har(4) not null,Grade smallint check(grade=0 and grade=100),Primary key(sno,cno),Foreign key(sno) references Student(sno),Foreign key(cno) references course(cno);完整性约束命名子句完整性约束命名子句1.基本语法 constraint primary key|foreign key|checkCREATE TABLE SC(Sno CHAR(9)， Cno CHAR(4) constraint c1 check(cno between

7、1 and 1000) ， Grade SMALLINT constraint c2 check(grade=0 and grade =0 and grade=100) Alter table sc drop constraint c1;触发器触发器触发器（Trigger）是用户定义在关系表上的一类由事件驱动的特殊过程。一旦定义，任何用户对表的增、删、改操作均由服务器自动激活相应的触发器，在核心层进行集中的完整性控制。触发器类似约束，但比约束更加灵活，可以实施比约束更为复杂的检查和操作，具有更精细和更强大的数据控制能力。 定义触发器定义触发器 CREATE TRIGGER BEFORE/AFT

8、ER DELETE/INSERT/UPDATE OF ON FOR EACH ROW/STATEMENT BEGIN PL/SQL； END；重建表示触发动作发生在触发事件表示触发动作发生在触发事件“DELETE/INSERT/UPDATE OF ”执行前或者后。执行前或者后。 说明触发器类型，说明触发器类型，即行级触发器或者即行级触发器或者语句级触发器语句级触发器 触发动触发动作作为了更好地理解和使用触发器，注意以下几点：触发器总是在某个数据行被删除、插入或更新之前或者之后被调用。触发器总是与某个数据库表关联的。每个数据库表可以有一个或者多个触发器。触发器是作为触发它的事务的一部分被执行的。

9、 触发器说明：.表的拥有者才可以在表上建立触发器，并且一个表上只能创建一定数量的触发器。.触发器名 触发器名可以包含模式名，也可以不包含模式名。同一模式下，触发器名必须唯一，并且触发器名和表名必须在同一模式下。触发器.表名当这个表的数据发生变化时，将激活定义在该表上相应的触发器，因此，该表也称为触发器的目标表。4 .触发事件触发事件可以是INSERT,DELETE,UPDATE,也可以是这几个事件的组合。update后还可以of,进一步指明修改哪些列时触发器激活例1 定义一个触发器，如果在学生表中插入记录，则提示“欢迎新同学”。USE STUDENTIF OBJECT_ID(welcome_s

10、tudent,TR) IS NOT NULL DROP TRIGGER welcome_student;GOCREATE TRIGGER welcome_studentON studentAFTER INSERTAS PRINT 欢迎新同学！GO如果进行插入操作如：INSERTINTO student(sno,sname)VALUES(03250106，LILING)CREATE TRIGGER welcome_studentON studentAFTER INSERT AS declare sname varchar(10)select sname=sname from insertedPR

11、INT sname+同学,欢迎你！select * from studentcreate trigger trig_stu1after delete on studentfor each rowbegin delete sc where sc.sno=:old.sno;end;在student表上定义一个删除触发器create trigger trig_stu1on studentfor deleteasdelete scfrom sc,deletedwhere sc.sno=deleted.snoOracleSQL Server不同平台的差异不同平台的差异/禁用指定的约束，下面的触发器与sc

12、表的外键约束冲突 ，先禁用外键约束alter table sc NOCHECK constraint all/定义student表的delete触发器IF OBJECT_ID(st,TR) IS NOT NULL DROP TRIGGER st;gocreate trigger ston studentfor deleteasdelete scfrom sc,deletedwhere sc.sno=deleted.snogo触发器与约束冲突触发器与约束冲突/禁用指定的约束，下面的触发器与sc表的外键约束冲突 ，先禁用外键约束alter table sc NOCHECK constraint a

13、ll触发器与约束冲突触发器与约束冲突IF OBJECT_ID(st,TR) IS NOT NULL DROP TRIGGER st;/定义student表的delete触发器create trigger ston studentfor deleteasdelete scfrom sc,deletedwhere sc.sno=deleted.snodelete from studentwhere sno=1102/启用指定表的约束 alter table sc CHECK constraint allalter table sc NOCHECK constraint all /定义sc表的ins

14、ert，update触发器create trigger ston scfor insert，updateasdeclare sno char(10)begin select sno = sno from inserted insert into student(sno) values(sno)endinsert into scvalues(1234,1,NULL)alter table sc CHECK constraint all 触发器例2 :定义一个BEFORE行级触发器，为教师表TEACHER定义完整性规则教授的工资不得低于4000元，如果低于4000元，自动改为4000元Create

15、 trigger insert_or_update_salBefore insert or update on teacherFor each rowAs begin if (new.pjob=教授and(new.sal100) begin insert into sc(sno,cno,grade) values(sno,cno,0) end Endinsert into scvalues(1105,5,900)触发器例3：定义一个AFTER行级触发器,当教师表TEACHER的工资发生变化后就自动在工资变化表sal_log中增加一条相应的记录.首先建立工资变化表sal_logCreate ta

16、ble sal_log(eno numeric(4) reference teacher(eno), sal numeric(7,2),Username char(10),);触发器Create trigger insert_salAfter insert on teacherFor each row as begin insert into sal_log values(new.eno,new.sal,current_user) end;触发器Create trigger update_salAfter update on teacherFor each rowAs begin if (ne

17、w.salold.sal) then insert into sal_log values(new.eno,new.sal,current_user) endifEnd;激活触发器 触发器创建后就存在于数据库表上，当触发事件发生时，触发器创建后就存在于数据库表上，当触发事件发生时，触发器被激活，执行触发动作语句。触发器被激活，执行触发动作语句。删除触发器DROP TRIGGER ；举例：举例： 删除删除teacher表上的触发器表上的触发器del_teacher。DROP TRIGGER del_teacher； 触发器和存储过程 当对一个表执行特定的操作时，被调用或者被“触发”的SQL语句称

18、为触发器。触发器能够依次调用SQL语句或者存储过程。 触发器是事件驱动的SQL代码，当插入、删除、修改指定的表和列中的数据时，这些代码将自动执行，因此触发器总是与特定的数据库表及特定的数据库事件(如插入(Insert)、删除(Delete)、修改(Update)等)相联系。触发器的优点 触发器不依赖于任何客户端应用程序，也不依赖于访问数据库的语言。触发器有如下优点： 1、标准化。在整个应用上，触发器保证了数据的完整性和一致性，一旦在表上建立了触发器，它就存储在数据库中；这种方法消除了各个客户应用程序的冗余编码，便于规则发生变化时对编码进行修改； 2、高效率。触发器初始执行后，作为编译的代码执行

19、。它的运行速度快，与在客户工作站上执行这些代码相比，在服务器上执行这些代码减少了网络通讯量和网络冲突；触发器把数据完整性代码放在服务器平台上比放在客户工作站上更有效； 3、安全性。触发器运行要有表主人的授权，但是，触发器能够被在表中插入、删除、修改记录的任何一个用户触发。任何一个应用程序或交互式子用户都无法避开触发器。触发器的功能 触发器对于强制执行的工作是非常有用的，它主要用于下面两种情况： 1、保证数据的完整性和一致性。当规则太复杂，不能用数据定义语言(DDL)定义它们、这时使用触发器非常有效； 2、实现数据之间逻辑联系的业务规则。 例如：在职工表中插入一个新的职工记录时，业务规则要求职工

20、的工作终止日期字段是一个空值。这个规则不能使用数据定义语言中CREATE TABLE语句定义，它需要一个触发器。 对于不能由其它手段实现的规则可考虑使用触发器，以此改善系统的效率。如：参照完整性、表或列的约束等。触发器的类型 触发器是根据所触发事件的类型进行分类的。通常的RDBMS支持三种类型的触发器： 插入触发器 (lnsent trigger)，在表中企图插入记录时触发； 删除触发器 (Delete trigger)，在表中企图删除记录时触发； 修改触发器 (Update trigger)，在表中企图修改记录时触发。触发器的其他用途 触发器的许多使用都超出了完整性维护的范围。例如，希望知道

21、一个发出订单的顾客是否在上月进行了足够的交易以至满足了公司规定的打折条件。如果是这样，业务员必须被告之，从而通知顾客，以便达到促销的目的。触发器能够产生事件的日志，同时支持审计和安全检查。存储过程 SQL提供一种模块语言，它允许在SQL中定义过程。在SQL-92标准语言的扩展中也提供了一些过程结构，例如for、while、if-then-else和复合SQL语句。我们可以把过程存入到数据库中，用call语句来执行。这样的过程也叫存储过程(stored procedure)。存储过程非常有用，它允许外部程序在数据库上执行操作，而不暴露数据库的内部细节。定义存储过程1（无参数传递） Create

22、or replace Procedure Pro_demo1 as begin delete from teacher; end;调用存储过程 execute Pro_demo1; 定义存储过程2 （有参数传递） Create or replace Procedure Pro_demo2( v_tno in number,v_title in varchar) as Begin update teacher set title=v_title where tno=v_tno; end;调用存储过程 execute Pro_demo2(101,教授); 存储过程的设置大大提高了SQL语句的功能、

23、效率和灵活性，具体表现在：1、改善了性能。 存储过程与原始SQL语句或批处理中的SQL语句的最大区别在于它是预先编译好的，因此存储过程的执行较快。2、改善了安全性。 存储过程简化了安全机制，它可以简化对某些操作的授权。例如，一个用户不希望别人在他的表上使用SELECT语句，他可以写一个仅可以修改这个表的某些行或列的存储过程，然后将调用该过程的权限授给特定的用户，这样其它用户就可以通过该存储过程访问这个表。3、减少了网络通讯量。在客户/服务器环境下，存储过程经过编译和优化后，存储在服务器端而不是客户端。 这样，在网络上传输的只是一个远程调用和最终的执行结果，而不是一系列的SQL语句和它们的返回结

24、果，从而大大减少了网络上的传输量，提高了系统性能。4、完成一些难以完成的工作。如果本地数据库服务器和远程数据库服务器都允许远程登录，那么本地数据库服务器 就可以执行其它数据库服务器上的存储过程。例如，可以在本地服务器上写一个触发器， 当某一事件发生（如删除、更新或插入）时，该触发器执行远程服务器上的存储过程。5、提高了开发者的劳动生产率。存储过程消除了开发者为一个事务或一个运算法则的多次需求而编写和测试SQL语句的重复劳动。调用一个存储过程比写SQL语句更快、更简单。6、有利于专门技术的使用。技术熟练的开发者能够集中精力，写出复杂查询、运算法则和事务的存储过程。其它具有一般SQL经验的开发者能够调用这些存储过程。7、简化了应用的维护，增加了应用的灵活性。存储过程有助于从应用逻辑中把业务规则分离出来。当业务发生变化，需要修改业务规则时，这个变化只影响存储过程。与改变、测试和调整一个及多个客户的成千上万个用户程序相比，