《数据库基础教程6第六章SQL中的约束和触发程序.ppt》由会员分享，可在线阅读，更多相关《数据库基础教程6第六章SQL中的约束和触发程序.ppt（48页珍藏版）》请在三一办公上搜索。
1、第六章 SQL中的约束和触发程序Constraints and Triggers in SQL,2,概述,关系数据模型关系数据结构关系操作集合关系完整性约束是对关系的某种约束条件防止对数据的意外破坏,3,概述,关系模型的完整性约束实体完整性参照完整性用户定义的完整性,4,概述,完整性的要求数据库中数据的正确性、相容性约束的实现DBMS支持对约束的声明for data使用触发器（触发程序）for action(event),5,SQL中的键码,主码和候选吗Primary KeyUniqueKey&Index一般在Primary Key上自动加上Index在Unique上的Index需另行声明,6
2、,SQL中的键码,实体完整性候选码的属性不能取Null、也不能有重复值反映同一关系中属性与属性之间的约束,7,SQL中的键码声明,Create Table MovieStar(name char(30)Primary Key,address Varchar(255),gender char(1),birthday Date);,8,SQL中的键码声明,Create Table MovieStar(name char(30),address Varchar(255),gender char(1),birthday Date,Primary Key(name);,9,SQL中的键码声明,Creat
3、e Table Movie(title char(20),year integer,incolor boolean,studioname char(20),producerC#integer,Primary Key(title,name);,10,SQL中的键码声明,Create Table MovieStar(name char(30)Primary Key,ID char(18)Unique,address Varchar(255),gender char(1),birthday Date);,11,SQL中的键码声明,Create Table MovieStar(name char(30
4、),ID char(18),address Varchar(255),gender char(1),birthday Date,Primary Key(name),Unique(ID);,12,参照完整性和外键,参照&被参照、主表&从表、主键&外键外键的值不允许参照不存在的主键的值主键与外键的相容类型属性名可以不同外键允许Null反映实体间存在的约束,13,参照完整性和外键声明,属性级sno char(10)References student(sno),cno char(10)References course(cno),.表级Foreign Key sno References stude
5、nt(sno),Foreign Key cno References course(cno),.,14,参照完整性和外键声明,15,保持参照完整性,参照完整性的违例从表插入从表元组，且外键不为Null修改从表外键，且不为Null主表删除主表元组，其已被参照修改主表主键，其已被参照Drop Table,16,保持参照完整性策略,RESTRICT前述违例的情况均不能操作这是所有DBMS产品均支持的方式安全的默认方式,17,保持参照完整性策略,CASCADE对主表的违例操作所有从表中的相关元组被级联删除或更新,18,保持参照完整性策略,SET NULL对主表的违例操作从表中相关外键的值均被置为Nul
6、l（前提是允许为Null）,19,保持参照完整性策略,SET DEFAULT对主表的违例操作从表中相关外键的值均被置为相应的Default Value,20,属性级sno char(10)References student(sno)On Delete Set NullOn Update Cascade,.表级Foreign Key sno References student(sno)On Delete Set NullOn Update Cascade,.,保持参照完整性策略,21,对属性值的约束,属性约束not nullcheck()with attribute域约束check()wit
7、h domain,22,对属性值的约束Not Null,在定义属性时约束该属性不可取Nullsname char(20)not nullPrimary Key约束：隐含Not Null不加约束：隐含允许Null,23,对属性值的约束Check,在定义属性时约束该属性必须使Check 为Truesage smallint Check(sage=15),gender char(1)Check(gender in(F,M)Be Careful!不合适的情形Check中的子查询Check中涉及其他属性或关系,24,对属性值的约束Domain,使Domain包含Check将Domain作用于属性Crea
8、te Domain GenderDomain char(1)Check(Value in(F,M)gender GenderDomain,以Value替代属性名,25,全局约束,针对元组/关系的约束声明基于元组的Check基于整个关系或多个关系的Check,26,全局约束元组Check,存在于关系定义中的Check对关系中的当前元组的约束不适于多关系、子查询check(gender=F OR sname Not Like Ms.%),check(gender=M OR sname Not Like Mr.%),27,全局约束断言Assertion,涉及整个关系、多关系的约束适用涉及子查询、复杂
9、约束真正的全局约束独立于关系,28,全局约束断言Assertion,Assertion的声明Create Assertion Check()表达式中属性的引用必须显式说明Select-From-WhereCheck(presC#Not In(Select cert#From MovieexecWhere netWorth 10000000),29,约束的命名,属性约束Name char(10)Constraint NameIsKey peimary Key域约束Create Domain GenderDomain char(1)Constraint GenderFM Check(Value i
10、n(F,M)元组约束Constraint RightTitle Check(gender=F OR name Not Like Ms.%),30,约束的命名,主码约束Constraint pk_stu Primary Key(sno),参照约束Constraint fk_sc_stu Foreign Key(sno)References student,31,约束的更新,对已命名的约束可实施更新操作Drop/Create各DBMS的方式有差异,32,约束的更新表的约束,Alter Table MovieStar Drop Constraint NameInKey;Alter Table Mov
11、ieStar Add Constraint NameInKey Primary Key(name),33,约束的更新表的约束,有些DBMS可以修改属性约束Alter Table MovieStar Modify gender Check Null;Alter Table MovieStar Modify gender Check(gender In(F,M);,34,约束的更新表的约束,Alter Table MovieStar Drop Constraint RightTitle;Alter Table MovieStar Add Constraint RightTitle Check(ge
12、nder=F OR name Not Like Ms.%);,35,约束的更新域,Alter Domain.Drop/Add Create Domain GenderDomain char(1)Constraint GenderFM Check(Value in(F,M)Alter Domain GenderDomain Drop Constraint GenderFM;Alter Domain GenderDomain Add Constraint GenderFM Check(Value in(F,M);,36,约束的更新 Assertion,Drop Assertion Create A
13、ssertion,37,存储过程,SQL的形式交互式嵌入式单行SQL语句、非过程性语言、上下文无关,38,存储过程,存储过程 Stored Procedure是为了完成特定的功能而汇集成一组的SQL语句集具有3GL的特点，含有程序控制语句If-Then/Case-When/While-Loop/Call/不同的DBMS，有很大的差异C/S的基础,39,存储过程原理,Create Procedure经过预编译，保存在DB的系统表中语法分析、有效格式生成查询树Execute Procedure编译、执行（依据查询树、统计信息、参数）确定访问路径建立查询计划,40,存储过程示例,Create Pro
14、cedure ProductType(IN product_id INT,OUT type CHAR(10)BEGIN Declare prod_name Char(20);Select name Into prod_nameFrom productWhere id=product_id;Case prod_name When Tee Shirt Then SET type=Shirt,WHEN Sweatshirt THEN SET type=Shirt WHEN Baseball Cap THEN SET type=Hat WHEN Visor THEN SET type=HatELSE
15、SET type=UNKNOWN END CASE;END,41,存储过程示例,Create Procedure update_dept(IN old_dept_num INT,IN new_dept_num INT,IN new_dept_name Char(20),IN new_dept_head_id INT)BEGIN ATOMIC Insert Into Department(dept_id,dept_name,dept_head_id)Values(new_dept_num,new_dept_name,new_dept_head_id);,Update employeeSet de
16、pt_id=new_dept_numWhere dept_id=old_dept_num;Delete From departmentWhere dept_id=old_dept_num;ENDExecute update_dept 123,212,cs,98001,42,SQL3中的触发程序 Trigger,约束是基于数据变化的Trigger则是基于对表的操作（动作）的当指定的表上发生特定的操作，系统便激活Trigger程序大部分DBMS产品均支持Trigger触发器类型Insert/Update/Delete/Column UpdateRow-level/Statement-Level B
17、efore/After,43,Trigger类型,触发器类型Insert/Update/Delete/Column UpdateRow-level/Statement-Level Before/After/Instead OfOld Value/New Value,44,Trigger示例,Create Trigger NetWorthTriggerAfter Update Of netWorth On MovieExecreferencingOld As OldTuple,New As NewTupleWhen(OldTWorth NewTWorth)Update MovieExecSet
18、netWorth=OldTWorthWhere cert#=NewTuple.cert#For Each Row,名称,激活的时机,事件,新值/修改后的值旧值/修改前的值,执行程序的条件,每修改一个元组便激活,45,Trigger示例,Create Trigger NetWorthTriggerAfter Update Of netWorth On MovieExecreferencingOld As OldTuple,New As NewTupleWhen(OldTWorth NewTWorth)Update MovieExecSet netWorth=OldTWorthWhere cert
19、#=NewTuple.cert#For Each Row,名称,激活的时机,事件,新值/修改后的值旧值/修改前的值,执行程序的条件,每修改一个元组便激活,46,Trigger示例 for each row,Create Trigger NetWorthTriggerAfter Update Of netWorth On MovieExecreferencingOld As OldTuple,New As NewTupleWhen(OldTWorth NewTWorth)Update MovieExecSet netWorth=OldTWorthWhere cert#=NewTuple.cert
20、#For Each Row,47,Trigger示例 for each statement,Create Trigger AvgNetWorthTriggerInstead Of Update Of netWorth On MovieExecreferencingOld_Table As OldStuff,New_Table As NewStuffWhen(500000=Select Avg(netWorth)From(MovieExec Except OldStuff)Union NewStuff)Delete From MovieExec Where(name,address,cert#,netWorth)In OldStuff;Insert Into MovieExec(Select*From NwStuff);,48,包含Trigger的SQL,Create Assertion RichPrssAfter Insert On Studio;Update On Studio,Update Of netWorth On MovieExec,Insert On MovieExecCheck(Not ExistSelect*From Studio,MovieExecWhere presC=cert#And netWorth10000000),
