第3章 完整约束.ppt

1、SQL中的完整性约束 完整性约束的目的是保证用户对数据库进行修改时不会破坏数据的一致性（防止对数据的意外破坏）。 注意：数据的完整性与安全性的概念区别。前者是为了防止数据库中存在不符合语义的数据（防止数据垃圾），后者是防止对数据的非法存取。,DBMS的完整性约束 数据库的完整性是指数据的正确性和相容性。DBMS必须提供一些功能来保证数据库中数据的完整性，也称其为完整性检查，即提供一定的机制来检查数据库中的数据是否满足规定的条件（称为完整性约束条件）。,1、完整性约束条件 NOT NULL 约束 缺省值 UNIQUE 约束 PRIMARY KEY 约束 FOREIGN KEY 约束 CHECK

2、约束,NOT NULL 约束 限定了表的某一列中必须有值； 缺省值 在数据录入时，若没有输入数据，数据库自动输入一个预先规定的值； UNIQUE 约束 该列的值在表中不能重复出现（可以有空值）； PRIMARY KEY 约束 用来唯一标识表中的一行（不能有空值），必须是唯一的列（应选择一般不会改变、短的和数值型的列）； CHECK 约束 根据逻辑表达式来限定值域；,FOREIGN KEY 约束 用来约束两个表间的关系（也称引用完整性约束）,编号 姓名 年龄 职称,医生记录,病员记录,编号 姓名 主治医生姓名,外键,注意：Oracle/SQL Server规定，外键只能引用在引用表中Primar

3、y Key或Unique约束的列。,唯一性约束,2、DBMS中的完整性约束实现机制 1) 定义表的约束条件 SQL create table demo ( tno number(4,0) primary key, tname char(8) not null, sex char(2) check(sex in (男,女), salary number(6,2) default(0), tcode number(4,0) unique, dept varchar2(200) );,create table father_t ( Cno integer primary key, Cname cha

4、r(10) not null, Credit numeric(3,1) );,create table son_t ( st_no integer primary key, fk_cno integer, grade integer, foreign key(fk_cno) references father_t( Cno ) );,2) 断言（Assertion） 一个断言就是一个谓词，表达了我们希望数据库总能满足的一个条件。 请看下页示例：,create Table Course( Cno integer not null, Tno integer not null, Cname char

5、(10) not null, credit numeric(3,1) not null, Primary key(cno,tno);,create table Teacher( Tno integer Primary Key, Tname char(6) not null, Title char(6), Dept char(10);,问题：每学期上课教师的人数不高于教师总数的60%,Create assertion sum-constraint check ( ( select count(distinct Tno) from course ) ( select count( * ) from

6、 teacher )*0.6 ) 断言是不与任何一个表相联系的约束。,3) 定义触发器（Trigger） 触发器是一种特殊的存储过程，它在插入、删除、修改特定表中的数据时，触发执行（由DBMS自动调用）。它能提供比数据库本身标准功能更精细、更复杂的数据控制能力。可用于数据库的安全性控制和完整性检查。 请看下页范例：,create table Teacher( Tno integer Primary Key, Tname char(6) not null, Title char(6), Dept char(10); insert into Teacher values (101,李华,讲师,计算

7、机);,create Table Course( Cno integer not null, Tno integer not null, Cname char(10) not null, credit numeric(3,1) not null, Primary key(cno,tno); insert into Course values(1,101,数据库,3.5);,Tno 教师编号 Tname 姓名 Title 职称 Dept 部门,Cno 课程编号 Tno 教师编号 Cname 课程名称 Credit 学分,create trigger trig_demo1 after delete

8、 on teacher for each row begin delete course where course.tno=:old.tno; end;,在Teacher表上定义一个删除触发器,create trigger trig_demo1 on teacher for delete as delete course from course,deleted where course.tno=deleted.tno,Oracle 9i,SQL Server,在Teacher表上定义一个Update触发器,create trigger trig_demo2 after update on te

9、acher for each row begin update course set course.Tno=:new.Tno where course.Tno=:old.Tno; end;,Oracle 9i,create trigger trig_demo2 on teacher for update as if update(Tno) begin Declare old_Tno integer,new_Tno integer select old_Tno=Tno from deleted; select new_Tno=Tno from inserted; update course se

10、t course.Tno=new_Tno where course.Tno=old_Tno; end;,SQL Server,触发器和存储过程 当对一个表执行特定的操作时，被调用或者被“触发”的SQL语句称为触发器。触发器能够依次调用SQL语句或者存储过程。 触发器是事件驱动的SQL代码，当插入、删除、修改指定的表和列中的数据时，这些代码将自动执行，因此触发器总是与特定的数据库表及特定的数据库事件(如插入(Insert)、删除(Delete)、修改(Update)等)相联系。,触发器的优点 触发器不依赖于任何客户端应用程序，也不依赖于访问数据库的语言。触发器有如下优点： 1、标准化。在整个应用

11、上，触发器保证了数据的完整性和一致性，一旦在表上建立了触发器，它就存储在数据库中；这种方法消除了各个客户应用程序的冗余编码，便于规则发生变化时对编码进行修改；,2、高效率。触发器初始执行后，作为编译的代码执行。它的运行速度快，与在客户工作站上执行这些代码相比，在服务器上执行这些代码减少了网络通讯量和网络冲突；触发器把数据完整性代码放在服务器平台上比放在客户工作站上更有效； 3、安全性。触发器运行要有表主人的授权，但是，触发器能够被在表中插入、删除、修改记录的任何一个用户触发。任何一个应用程序或交互式子用户都无法避开触发器。,触发器的功能 触发器对于强制执行的工作是非常有用的，它主要用于下面两种

12、情况： 1、保证数据的完整性和一致性。当规则太复杂，不能用数据定义语言(DDL)定义它们、这时使用触发器非常有效；,2、实现数据之间逻辑联系的业务规则。 例如：在职工表中插入一个新的职工记录时，业务规则要求职工的工作终止日期字段是一个空值。这个规则不能使用数据定义语言中CREATE TABLE语句定义，它需要一个触发器。 对于不能由其它手段实现的规则可考虑使用触发器，以此改善系统的效率。如：参照完整性、表或列的约束等。,触发器的类型 触发器是根据所触发事件的类型进行分类的。通常的RDBMS支持三种类型的触发器： 插入触发器 (lnsent trigger)，在表中企图插入记录时触发； 删除触发

13、器 (Delete trigger)，在表中企图删除记录时触发； 修改触发器 (Update trigger)，在表中企图修改记录时触发。,触发器的其他用途 触发器的许多使用都超出了完整性维护的范围。例如，希望知道一个发出订单的顾客是否在上月进行了足够的交易以至满足了公司规定的打折条件。如果是这样，业务员必须被告之，从而通知顾客，以便达到促销的目的。 触发器能够产生事件的日志，同时支持审计和安全检查。,存储过程 SQL提供一种模块语言，它允许在SQL中定义过程。在SQL-92标准语言的扩展中也提供了一些过程结构，例如for、while、if-then-else和复合SQL语句。 我们可以把过程

14、存入到数据库中，用call语句来执行。这样的过程也叫存储过程(stored procedure)。 存储过程非常有用，它允许外部程序在数据库上执行操作，而不暴露数据库的内部细节。,定义存储过程1（无参数传递） Create 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 Be

15、gin update teacher set title=v_title where tno=v_tno; end;,调用存储过程 execute Pro_demo2(101,教授);,存储过程的设置大大提高了SQL语句的功能、效率和灵活性，具体表现在： 1、改善了性能。 存储过程与原始SQL语句或批处理中的SQL语句的最大区别在于它是预先编译好的，因此存储过程的执行较快。 2、改善了安全性。 存储过程简化了安全机制，它可以简化对某些操作的授权。,3、减少了网络通讯量。 在客户/服务器环境下，存储过程经过编译和优化后，存储在服务器端而不是客户端。 这样，在网络上传输的只是一个远程调用和最终的执

16、行结果，而不是一系列的SQL语句和它们的返回结果，从而大大减少了网络上的传输量，提高了系统性能。,4、完成一些难以完成的工作。 如果本地数据库服务器和远程数据库服务器都允许远程登录，那么本地数据库服务器 就可以执行其它数据库服务器上的存储过程。例如，可以在本地服务器上写一个触发器， 当某一事件发生（如删除、更新或插入）时，该触发器执行远程服务器上的存储过程。,5、提高了开发者的劳动生产率。 存储过程消除了开发者为一个事务或一个运算法则的多次需求而编写和测试SQL语句的重复劳动。调用一个存储过程比写SQL语句更快、更简单。 6、有利于专门技术的使用。 技术熟练的开发者能够集中精力，写出复杂查询、

17、运算法则和事务的存储过程。其它具有一般SQL经验的开发者能够调用这些存储过程。,7、简化了应用的维护，增加了应用的灵活性。 存储过程有助于从应用逻辑中把业务规则分离出来。当业务发生变化，需要修改业务规则时，这个变化只影响存储过程。与改变、测试和调整一个及多个客户的成千上万个用户程序相比，改变和测试存储过程是更有效、更省时间、少犯错误的好方法。,8、保证了运算法则的一致性。 存储过程能够确保运算法则每次以同样的精度运行。与此相反，在一个或多个客户应用中多处存放的嵌入式SQL语句，它们写的稍有不同，就会产生不同的结果。,通过建立一个存储过程，可以提高存取速度，并且可以达到安全控制的目的。 使用存储

18、过程，可以将用户和数据分开。例如，可以设置用户对表没有更新权限，但有使用存储过程的权限。用户把参数提供给插入存储过程，插入存储过程即为用户执行插入操作；用户提供检索的条件，检索存储过程返回找到的数据行。,这种方法不允许任何对表的完全访问和更新，只是提供实现与表交互的存储过程。这个方法的好处在于它完全控制了信息的存储和检索，任何不想让用户访问的列都可以被忽略，任何在插入之前所需要做的附加处理都可以做到。这对于数据存储和检索来说，是一个带有更多的面向对象风格的方法，它有助于保证数据的正确与安全。,存储过程潜在的缺点如下： 1、难以保持负荷平衡。 存储过程提出了一种在服务器上集中处理的模型，用集中处理代替事务的分布处理。 2、增加了管理要求。 存储过程是一个共享的资源。管理员必须确保使用这些存储过程，不允许同一个业务规则、事务、运算法则的多个存储过程存在。,3、存储过程在不同的RDBMS上语法不同。 存储过程使