数据库系统基础教程(第7章)

第7章约束和触发器1SQL中约束种类数据库设计质量体现为约束constraints所提供的可靠性保障。约束以表达式或语句的形式存储在数据库中。约束是一种主动性(active)元素，当数据库特定状态发生改变时自动运行。SQL2提供部分完整性约束：键、参照完整性、域约束、元组约束等。SQL3提供触发器trigger机制：由特定事件触发某种主动性元素。27.1键和外键3键与外键键key是最重要的约束。每个表都必须确定自己的键。每个表都可能有多个属性集可作为键，称为“候选键candicatekey”。一个表只能确定一个主键(PrimaryKey)。若某个属性说明为Unique，则它是一个候选键。若关系的某个属性说明为外键，则该属性出现的值，一定会在另一个关系的主键中出现。4主键声明如何说明表中的主键？SQL语言有两种说明主键的方式。方式1：CreateTable语句中，某属性说明之后加PrimaryKey方式2：属性表之后，加PrimaryKey(属性1，属性2，…)若主键有多个属性，则只能用方式2。GUI操作方式更方便直观。5用UNIQUE声明键如何说明表中的主键？SQL语言有两种说明主键的方式。方式1：CreateTable语句中，某属性说明之后加UNIQUE方式2：属性表之后，加UNIQUE(属性1，属性2，…)若用UNIQUE说明键有多个属性，则只能用方式2。GUI操作方式更方便直观6例子考虑关系SalesMan的模式(方式1)CREATETABLESalesMan(empidVARCHAR(10)PRIMARYKEY,idnoVARCHAR(18)UNIQUE,nameVARCHAR(30),genderCHAR(1),birthdayDATE,addressVARCHAR(255),phoneVARCHAR(13));7例子考虑关系SalesMan的模式(方式2)CREATETABLESalesMan(empidVARCHAR(10),idnoVARCHAR(18),nameVARCHAR(30),genderCHAR(1),birthdayDATE,addressVARCHAR(255),phoneVARCHAR(13),PRIMARYKEY(empid),UNIQUE(idno));8主键和Unique属性之间有何区别和联系一个表有且仅有一个主键；而Unique属性可有多个或没有。主键是单个属性，则该属性隐含为Unique。主键不允许有NULL值，属性为Unique则允许有NULL值。若主键是多个属性，则每个属性都不可能为Unique。DBMS对主键往往自动赋予一些特征，如建立索引index等。9强制键约束什么操作会导致违背键约束？对表的delete操作不会违背该表的键约束。insert和update可能违背该表的键约束。SQL实施主键约束即是在insert和update时检验键值，避免空值或重复键值。10外键约束声明如何说明外键两种SQL方式：方式1：某属性之后加references<被参照表>(被参照属性)。方式2：属性表之后加foreignkey<参照属性>references<被参照表>(被参照属性)。其中：被参照属性应是被参照表的PrimaryKey或unique属性。11例子例：CREATETABLEStarsIn(MovietitleVARCHAR(90)NOTNULL,MovieyearINTNOTNULL,StarNameVARCHAR(30)NOTNULL,PRIMARYKEY(Movietitle,Movieyear,StarName),FOREIGNKEY(Movietitle,Movieyear)ReferencesMovie(title,year),FOREIGNKEYStarNameReferencesStars(name));12外键约束声明是否可定义一个表参照自己？可以。例如：salesman(empid,idno,name,managerid,deptid,…)外键是否可取NULL值？可以。13维护引用完整性数据库更新时如何保证参照完整性?有三种可选策略，以保证参照完整性：1Restrict限制(缺省)2Cascade级联3SetNull置空14Restrict限制(缺省)以StarsIn(movieTitle,MovieYear,…)参照Movie(title,year,…)为例：对于StarsIn(参照表)，下面操作被拒绝：insert语句中movieTitle和MovieYear值不是Movie中已有的一个主键值。update语句中改变movieTitle和MovieYear值为不是Movie中已有的主键值。对于Movie(被参照表)，Restrict拒绝以下操作：delete语句删除一个被StarsIn参照的元组。update语句修改一个被StarsIn参照的title和year主键值。15Cascade级联影响被参照表的delete和update操作。以StarsIn(movieTitle,MovieYear,…)参照Movie(title,year,…)为例：当Movie(被参照表)delete删除某个元组时，StarsIn(参照表)中所有参照元组被自动删除。当Movie(被参照表)update修改某个元组的title或year值时，StarsIn(参照表)中所有参照元组被自动修改。16SetNull置空影响被参照表的delete和update操作。以Movie(title,year,…,ProducerC#)参照MovieExec(name,…,cert#,…)为例：首先ProducerC#应允许NULL。当MovieExec(被参照表)delete删除某个元组时，Movie(参照表)中所有参照元组的ProducerC#被置空。当MovieExec(被参照表)update修改某个元组的cert#时，Movie(参照表)中所有参照元组的ProducerC#被置空。17维护引用完整性策略的语法这些策略可在说明外键的同时描述。references<被参照表>(属性表)[Action]其中：Action:ON{Update|Delete}{Restrict|Cascade|SetNull}18例子CREATETABLEMovie(titleVARCHAR(90)NOTNULL,yearINTNOTNULL,lengthINT,colorInCHAR(1)NOTNULL,producerC#INT,PRIMARYKEY(title,year),FOREIGNKEYproducerC#ReferencesMovieExec(cert#)ONDELETECASCADEONUPDATECASCADE);注意：对于一个外键，Update和Delete可分别采用不同的策略。19“悬挂元组”danglingtuples什么是“悬挂元组”danglingtuples？对于参照关系A，外键值未出现在被参照表中的元组，即违背参照完整性的元组。如何避免出现“悬挂元组”？1Restrict策略：在参照关系中对产生悬挂元组的操作予以禁止。2Cascade策略：自动删除或修改产生出来的悬挂元组。3SetNull策略：产生出来的每个悬挂元组的外键值置空NULL，使其不参照任何元组。207.2属性和元组上的约束21属性值约束限制某些属性的值在特定范围内.这类约束可用下列方式表达:⑴在关系式定义中给出属性的约束。⑵在整个元组上的约束。该约束是关系模式的一部分，不与任何属性相关。22非空约束如何说明某属性非空NOTNULL？在createtable指令中，属性说明之后加NOTNULL；若不显式说明，该属性隐含为允许空值。说明非空属性有何效果？⑴不能用update修改其值为NULL。⑵insert时必须指定一个非空值。⑶不能使用外键的SetNull策略。注意：主键属性和unique属性隐含非空；允许为空的属性不可能是主键或unique。23基于属性的Check约束如何限制某属性的值在特定范围？在属性说明之后，增加check(条件)，属性的值应使条件为真。条件的语法与Where子句的条件一样。当insert和update使属性值改变时，执行check检验，拒绝预约属性不一致的更新。24例子设有关系模式：Stodio(name,address,presC#)要求其证书号必须至少为６位数字，则可写出约束：presC#INTREFERENCESMovieExec(cert#)CHECK(presC#>=100000)设有关系模式：MovieStar(name,address,gender,birthday)要求其性别只能为’Ｆ’和’Ｍ’，则可写出约束：genderCHAR(1)CHECK(genderIN(’Ｆ’,’Ｍ’))25基于属性的Check约束何时检查check条件？只有当该表执行insert或update时才检测check条件。注意：check条件中可包含其它关系的属性，但应避免。例如，设有关系Stodio(name,address,presC#)其关系中presC#值必须在关系MovieExec(name,address,cert#,networth)的cert#之中出现约束，则在定义中给出约束：presC#INTCHECK(presC#IN(SELECTcert#FROMMovieExec))修改或删除cert#的值，使某个presC#对应的键值不在MovieExec中是可能的。26域约束域domain也称为用户定义数据类型user-defineddatatype.基于某种基本数据类型且增加特定约束，存于数据库中，可供多个表使用。例如：CREATEDOMAIN"sex"integerNOTNULLDEFAULT1CHECK(VALUEIN(0,1))27基于元组的Check约束对某个表中元组的约束而不是对某个属性的约束。例如：在关系模式MovieStar(name,address,gender,birthday)中要求每个元组的审核：如果影星为男性，则他的名字不能与’Ms.’开头。在定义表的性质时增加如下约束：CHECK(gender=’F’ORnameNOTLIKE’Ms.%’)何时检查check条件？只有当该表执行insert或update时才检测check条件。注意：如果在表中既有属性check也有元组check，则先检测属性。287.3修改约束29给约束命名为修改或删除一个已经存在的约束，约束必须有名字。为了命名，在约束前加保留字CONSTRAINT和该约束的名字。例如：设有关系模式MovieStar(name,address,gender,birthday)⑴为其主键约束命名：nameCHAR(30)CONSTRAINTNameIsKeyPRIMARYKEY⑵为影星的性别约束命名：genderCHAR(1)CONSTRAINTNoAndroCHECK(genderIN(’Ｆ’,’Ｍ’))30修改表上的约束可用ALTERTABLE来修改约束。用保留字DROP和要删除的约束名字来删除约束。用保留ADD，后跟要添加的约束来实现约束的添加。例如：要删除MovieStar的主键约束ALTERTABLEMovieStarDROPNameIsKey要添加MovieStar的主键约束ALTERTABLEMovieStarADDCONSTRAINTNameIsKeyPRIMARYKEY(name)31断言(Assertion)SQL标准提出了一种强制任何条件的简单的断言形式，与check类似。断言的定义为CREATEASSERTION<断言名>CHECK(<条件>)当建立断言时，断言的条件必须是真，并且要永远保持为真。任何引起断言条件为假的数据库更新都被拒绝。32断言(Assertion)断言要写什么？在写断言条件时所引用的任何属性都必须标明。何时检查断言的check条件？只有当该表执行insert或update时才检测check条件。何时删除断言？DROPASSERTION<断言名>33例子假定希望其净资产值少于10000000的人不能成为制片厂经理。CREATEASSERTIONRichPresCHECK(NOTEXISTS(SELECT*FROMStudio,MovieExecWHEREpresC#=cert#ANDnetworth<10000000))注意与约束的区别：CREATETABLEStudio(nameCHAR(30)PRIMARYKEY,addressVARCHAR(255),presC#INTREFERENCESMovieExec(cert#),CHECK(presC#NOTIN(SELECTcert#FROMMovieExecWHEREnetworth<10000000)))34约束的比较下面表格列出了基于属性的检查约束、基于元组的检查约束和断言之间的主要区别。约束类型声明的位置动作的时间确保成立？基于属性的CHECK属性对关系插入元组或属性修改如果是子查询，则不能确保基于元组的CHECK关系模式元素对关系插入元组或属性修改如果是子查询，则不能确保断言数据库模式元素对任何提及的关系改变是357.4触发器36触发器(trigger)基于特定事件触发的特定的约束检验。注意：各种商业数据库可能使用不同语法。触发器是什么？一个触发器trigger是存储在某个表中的一个命名的数据库对象。当该表进行某种数据更新时，将自动触发一组SQL语句的执行。37事件－条件－动作规则Event事件：数据更新指令：insert/delete/updateCondition条件：当事件发生后，检查条件是否满足：若不满足，则不执行动作而状态转移。若满足，则执行一组动作之后状态转移。Action动作：一组SQL指令，通常是更新操作Old状态New状态Event/Condition[Action]38触发器与约束不同当数据库程序员声明的事件发生时，触发器被激活。事件可以是对某个特定关系的插入、删除或修改。当触发器被事件激活时，不是立即执行，而是首先由触发器测试触发条件。如果条件不成立，则响应事件的触发器不做任何事情。如果触发器声明的条件满足，则与触发器相连的动作由DBMS执行。39SQL中的触发器特征动作可以在触发事件之前或之后被执行。在被触发的事件中，动作既可指向被插入、删除、修改元组的新值，也可以指向其旧值。更新事件可以被局限到某个特定的属性或某一些属性。条件由WHEN短语给出。仅仅当规则被触发，并且触发事件的发生使条件成立时，动作才被执行。程序员可以选择动作执行的声明方式：一次只对一个更新元组，或者一次针对在数据库操作中被改变的所有元组。40触发器的语法CREATETRIGGERtrigger-nameON[table-name]FORtrigger-eventAS……trigger-name：一个表可定义多个不重名的触发器。trigger-event:DELETE|INSERT|UPDATEAS：是触发器要执行的操作。41例子要求：每个销售员只能属于某一个部门；部门经理只能由本部门中某个销售员担任。Salesman(empid,idno,name,gender,phone,deptid)Department(deptid,name,headerid)42例子仅靠外键建立的参照完整性，不能解决的问题：1当updatedepartmentsetheaderid=?wheredeptid=?时不能保证新headerid在Salesman中是本部门成员。例如：updatedepartmentsetheaderid='A0044'wheredeptid=2;'A0044'是部门1的成员却担任了部门2的经理。应在其担任部门2经理之前，先更新salesman使其作为部门2的成员。updatesalesmansetdeptid=2whereempid='A0044'43例子CREATETRIGGERT_EditPresONdepartme