数据库系统原理-第七章约束与触发器

第7章约束与触发器

7.1键与外键7.2属性和元组上的约束7.3修改约束7.4断言7.5触发器Page1SQL中约束种类数据库设计质量体现为约束constraints所提供的可靠性保障。约束以表达式或语句的形式存储在数据库中。约束是一种主动性(active)元素，当数据库特定状态发生改变时自动运行。SQL2提供部分完整性约束：键、参照完整性、域约束、元组约束等。SQL3提供触发器trigger机制：由特定事件触发某种主动性元素。Page2在第二章中已经介绍了SQL中通过保留字PRIMARYKEY或UNIQUE来定义一个属性或一组属性为关系的键。如：CREATETABLEMovies(titlechar(20),yearint,lengthint,genrechar(10),studioNamechar(30),producerCint,PRIMARYKEY(title,year));7.1键与外键Page3键key是最重要的约束。每个表都必须确定自己的键。每个表都可能有多个属性集可作为键，称为“候选键candidatekey”。一个表只能确定一个主键(PrimaryKey)。若某个属性说明为Unique，则它是一个候选键。若关系的某个属性说明为外键，则该属性出现的值，一定会在另一个关系的主键中出现。7.1键与外键Page47.1键与外键外键约束声明隐含两层意思：①被引用的另一个关系的属性必须是主键②外键属性取值必须属于被引用的另一个关系的主键属性的值。Page5举例学籍管理数据库Page6声明外键的方法和声明主键的方法一样，声明外键的方法也有两种。①如果外键是单个属性，则可以在此属性的名字和类型之后，声明其引用某个表的某个属性。格式如下：REFERENCES<表名>(<属性名>)Page7举例电影数据库模式如下：Movies(title,year,length,genre,studioName,producerC)

名称年份长度(分钟)流派电影公司名称导演证书号MovieStar(name,address,gender,birthdate)

姓名住址性别生日StarsIn(movieTitle,movieYear,starName)

影片名称年份主演姓名MovieExec(name,address,cert,netWorth)

导演姓名住址导演证书号净资产Studio(name,address,presC)

电影公司名称地址经理证书号要声明StarsIn表中的starName属性对MovieStar表的name属性的引用完整性约束，声明方法如下：Page8举例CREATETABLEStarsIn(movieTitlechar(20),movieYearint,starNamechar(12)REFERENCESMovieStar(name),PRIMARYKEY(movieTitle,movieYear,starName));Page9举例CREATETABLEStarsIn(movieTitlechar(20),movieYearint,starNamechar(12)REFERENCESMovieStar(name),PRIMARYKEY(movieTitle,movieYear,starName));如果在StarsIn表中插入周星驰参演少林足球信息(周星驰未在MovieStar中出现)，能否插入？INSERTINTOStarsInVALUES('少林足球',2001,'周星驰');不能，因为违反了在starName属性上定义的引用完整性约束。Page10举例CREATETABLEStarsIn(movieTitlechar(20),movieYearint,starNamechar(12)REFERENCESMovieStar(name),PRIMARYKEY(movieTitle,movieYear,starName));如果在StarsIn表中插入徐帆参演唐山大地震的信息（徐帆在MovieStar）中出现，能否插入？INSERTINTOStarsInVALUES('唐山大地震',2001,'徐帆');能，因为不违反在starName属性上定义的引用完整性约束。Page11声明外键的方法和声明主键的方法一样，声明外键的方法也有两种。②如果外键是由两个属性以上的属性组成，则要在CREATETABLE语句中专门追加一个声明，格式如下：FOREIGNKEY(<属性名列表>)REFERENCES<表名>(<属性名列表>)Page12举例电影数据库模式如下：Movies(title,year,length,genre,studioName,producerC)

名称年份长度(分钟)流派电影公司名称导演证书号MovieStar(name,address,gender,birthdate)

姓名住址性别生日StarsIn(movieTitle,movieYear,starName)

影片名称年份主演姓名MovieExec(name,address,cert,netWorth)

导演姓名住址导演证书号净资产Studio(name,address,presC)

电影公司名称地址经理证书号要声明StarsIn表中的movieTitle,movieYear属性对Movies表的title,year属性的引用完整性约束。Page13举例CREATETABLEStarsIn(movieTitlechar(20),movieYearint,starNamechar(12)REFERENCESMovieStar(name),PRIMARYKEY(movieTitle,movieYear,starName),FOREIGNKEY(movieTitle,movieYear)REFERENCESMovies(title,year));声明StarsIn表中的movieTitle,movieYear属性对Movies表的title,year属性的引用完整性约束的方法如下：Page14举例此时，如果在StarsIn表中插入徐帆参演唐山大地震的信息（未出现在Movies中），能否插入，为什么？INSERTINTOStarsInVALUES('唐山大地震',2001,'徐帆');不能，虽然不违反在starName属性上定义的引用完整性约束。但是违反了在movieTitle和movieYear属性上定义的引用完整性约束。Page15外键约束声明是否可定义一个表参照自己？可以。例如：salesman(empid,idno,name,managerid,deptid,…)外键是否可取NULL值？可以。Page16维护引用完整性数据库更新时如何保证参照完整性?

有三种可选策略，以保证参照完整性：1Restrict限制(缺省)2Cascade级联3SetNull置空Page17Restrict限制(缺省)以StarsIn(movieTitle,MovieYear,…)参照Movie(title,year,…)为例：对于StarsIn(参照表)，下面操作被拒绝：insert语句中movieTitle和MovieYear值不是Movie中已有的一个主键值。update语句中改变movieTitle和MovieYear值为不是Movie中已有的主键值。对于Movie(被参照表)，Restrict拒绝以下操作：delete语句删除一个被StarsIn参照的元组。update语句修改一个被StarsIn参照的title和year主键值。Page18Cascade级联影响被参照表的delete和update操作。以StarsIn(movieTitle,MovieYear,…)参照Movie(title,year,…)为例：当Movie(被参照表)delete删除某个元组时，StarsIn(参照表)中所有参照元组被自动删除。当Movie(被参照表)update修改某个元组的title或year值时，StarsIn(参照表)中所有参照元组被自动修改。Page19SetNull置空影响被参照表的delete和update操作。以Movie(title,year,…,ProducerC#)参照MovieExec(name,…,cert#,…)为例：首先ProducerC#应允许NULL。当MovieExec(被参照表)delete删除某个元组时，Movie(参照表)中所有参照元组的ProducerC#被置空。当MovieExec(被参照表)update修改某个元组的cert#时，Movie(参照表)中所有参照元组的ProducerC#被置空。Page20维护引用完整性策略的语法这些策略可在说明外键的同时描述。

references<被参照表>(属性表)[Action]

其中：

Action:ON{Update|Delete}{Restrict|Cascade|SetNull}Page21例子CREATETABLEStudio(nameVARCHAR(30)NOTNULL,addressVARCHAR(255),presC#INT,

PRIMARYKEY(Name),

FOREIGNKEY(presC#)ReferencesMovieExec(cert#)

ONDELETESETNULL

ONUPDATECASCADE);注意：对于一个外键，Update和Delete可分别采用不同的策略。Page22“悬挂元组”danglingtuples什么是“悬挂元组”danglingtuples？对于参照关系A，外键值未出现在被参照表中的元组，即违背参照完整性的元组。如何避免出现“悬挂元组”？1Restrict策略：在参照关系中对产生悬挂元组的操作予以禁止。2Cascade策略：自动删除或修改产生出来的悬挂元组。3SetNull策略：产生出来的每个悬挂元组的外键值置空NULL，使其不参照任何元组。Page23延迟约束检查假设施瓦辛格卸任加州州长后，建立一个电影公司，公司名称为LaVista，他作为公司的经理，则InsertintoStudioValues('LaVista','NewYork',23456)则数据插入有一些麻烦，原因是假设23456是最新颁发的证书。InsertintoStudio(name,address)Values('LaVista','NewYork')将证书号插入MovieExec关系后，修改UpdateStudioSETpresC#=23456Wherename='LaVista'Page24延迟约束检查当如果发生循环约束(circularconstraint)时，上述的安排也不能解决问题。例：如果电影制片人被约束为电影公司经理，则要声明cent#是引用Studio(presC#)的外键。于是presC#必须声明为UNIQUE。这样就不能进行插入操作。解决方法：1.必须将两个插入操作组成一个单一的事务。2.需要有一种方法通知DBMS不要检查约束，直到整个事务完成执行并提交为止。Page25延迟约束检查在任何的约束声明后可有DEFERRABLE或NOTDEFERRABLE(缺省值)选项。如果没有明确写明则表示进行更新操作时，立即检查该约束。但如果约束被声明为DEFERRABLE，则约束检查将推迟到当前事务完成时进行。保留字DEFERRABLE后面有两个选项：INITIALLYDEFERRED:检查仅推迟到事务提交前执行INITIALLYIMMERDIATE:检查在每个语句后立即执行对于推迟约束检查，有两点要记住：任何类型的约束都可以命名。如果约束有名字，则可以用SQL语句将该约束从立即检查改为推迟检查。Page26练习P.188/7.1.1/b)CreateTableMovies(titleChar(30),yearINT,….producerC#INTReferencesMovieExec(cert#)OnDeleteSetNULLOnUpdateSetNULLPrimaryKey(title,year));Page27SQL的CREATETABLE语句中还可以声明两种其他约束：①在单一属性上的约束；②在整个元组上的约束。7.2.1非空值约束7.2.2基于属性的CHECK约束7.2.3基于元组的CHECK约束7.2属性和元组上的约束Page28属性值约束限制某些属性的值在特定范围内.这类约束可用下列方式表达:⑴在关系式定义中给出属性的约束。⑵在整个元组上的约束。该约束是关系模式的一部分，不与任何属性相关。Page29非空约束如何说明某属性非空NOTNULL？在createtable指令中，属性说明之后加NOTNULL；若不显式说明，该属性隐含为允许空值。说明非空属性有何效果？⑴不能用update修改其值为NULL。⑵insert时必须指定一个非空值。⑶不能使用外键的SetNull策略。注意：主键属性和unique属性隐含非空；允许为空的属性不可能是主键或unique。Page30举例CREATETABLEStarsIn(movieTitlechar(20)NOTNULL,movieYearintNOTNULL,starNamechar(12)REFERENCESMovieStar(name)

NOTNULL,PRIMARYKEY(movieTitle,movieYear,starName),FOREIGNKEY(movieTitle,movieYear)REFERENCESMovies(title,year));声明非空值约束的方法如下：Page31基于属性的Check约束如何限制某属性的值在特定范围？在属性说明之后，增加check(条件)，属性的值应使条件为真。条件的语法与Where子句的条件一样。当insert和update使属性值改变时，执行check检验，拒绝导致属性值不一致的更新。Page32例子设有关系模式：

Studio(name,address,presC#)

要求其证书号必须至少为６位数字，则可写出约束：

presC#INTREFERENCESMovieExec(cert#)CHECK(presC#>=10000000)设有关系模式：

MovieStar(name,address,gender,birthday)

要求其性别只能为'Ｆ'

和'Ｍ'，则可写出约束：

genderCHAR(1)CHECK(genderIN('Ｆ','Ｍ'))Page33基于属性的Check约束何时检查check条件？只有当该表执行insert或update时才检测check条件。注意：check条件中可包含其它关系的属性，但应避免（除了是子查询的From子句中出现的关系）。例如，设有关系

Studio(name,address,presC#)

其关系中presC#值必须在关系

MovieExec(name,address,cert#,networth)

的cert#之中出现，则在定义中给出约束：

presC#INTCHECK(presC#IN(SELECTcert#FROMMovieExec))

修改或删除cert#的值，使某个presC#对应的键值不在MovieExec中是可能的。Page34基于元组的Check约束对某个表中元组的约束而不是对某个属性的约束。例如：在关系模式

MovieStar(name,address,gender,birthday)

中要求每个元组的审核：如果影星为男性，则他的名字不能以’Ms.’开头。在定义表的性质时增加如下约束：

CHECK(gender='F'ORnameNOTLIKE'Ms.%')何时检查check条件？只有当该表执行insert或update时才检测check条件。注意：如果在表中既有属性check也有元组check，则先检测属性。Page35基于元组和基于属性约束的比较如果元组上的约束涉及该元组的多个属性，那么就必须作为基于元组的约束。如果约束仅涉及元组的一个属性，那么可以作为基于元组或基于属性的约束。约束检查基于属性的约束：条件检查是一样的。基于元组的约束：比基于属性的约束更频繁的被检查。Page36练习P.192/7.2.3/a)CreateTableStarsIn(….starnameChar(30)Check(starnameIn(SelectnameFromMovieStarWhereYear(birthdate)<movieYear))….);Page3738在SQL中提供了增加、删除和修改约束的方式。7.3.1给约束命名7.3.2修改表上的约束7.3修改约束397.3.1给约束命名修改或删除约束是通过约束的名字进行的。创建约束时用户可以给约束命名，否则DBMS会给约束起一个名字。用户给约束命名的方式：在约束前面加保留字CONSTRAINT和该约束的名字。40举例CREATETABLEMovieStar(namechar(12)CONSTRAINTNameIsKeyPRIMARYKEY,addresschar(30),genderchar(2)CONSTRAINTNoAndroCHECK(genderIN('M','F')),birthdatedate,

CONSTRAINTRightTitleCHECK(gender='F'ORnameNOTLIKE'Ms.%'));417.3.2修改表上的约束通过ALTERTABLE语句对约束进行修改ALTERTABLE语句有多种方式影响约束：①用保留字DROP和要删除的约束的名字可以删除约束；②用保留字ADD，后跟要添加的约束实现约束添加。42举例删除MovieStar表中的约束。ALTERTABLEMovieStarDROPCONSTRAINTNameIsKey;ALTERTABLEMovieStarDROPCONSTRAINTNoAndro;ALTERTABLEMovieStarDROPCONSTRAINTRightTitle;43举例在MovieStar表中添加约束。ALTERTABLEMovieStarADDCONSTRAINTNameIsKeyPRIMARYKEY(name);ALTERTABLEMovieStarADDCONSTRAINTNoAndroCHECK(genderIN('M','F'));ALTERTABLEMovieStarADDCONSTRAINTRightTitleCHECK(gender='F'ORnameNOTLIKE'Ms.%');SQL标准提出了一种强制任何条件的简单的断言形式，与check类似，但其用于关系之间的约束。断言的定义为

CREATEASSERTION<断言名>CHECK(<条件>)当建立断言时，断言的条件必须是真，并且要永远保持为真。任何引起断言条件为假的数据库更新都被拒绝。7.4断言Page44断言使用断言要写什么？在写断言条件时所引用的任何属性都必须标明。何时检查断言的check条件？当任何提及的表执行delete,insert或update时检测check条件。删除断言

DROPASSERTION<断言名>Page45例子假定希望其净资产值少于10000000的人不能成为电影公司经理。

CREATEASSERTIONRichPresCHECK(NOTEXISTS(SELECT*FROMStudio,MovieExecWHEREpresC#=cert#ANDnetworth<10000000))注意与约束的区别：

CREATETABLEStudio(nameCHAR(30)PRIMARYKEY,addressVARCHAR(255),presC#INTREFERENCESMovieExec(cert#),CHECK(presC#NOTIN(SELECTcert#FROMMovieExecWHEREnetworth<10000000)))

Page46例子声明对一个给定电影公司，其所有电影的总长度不能超过10000分钟。用断言实现

CREATEASSERTIONSumLengthCHECK(10000>=ALL(SELECTSUM(length)FROMMoviesGroupBystudioname));基于元组的Check约束：

CHECK(10000>=ALL(SELECTSUM(length)FROMMoviesGroupBystudioname));

Page47约束的比较下面表格列出了基于属性的检查约束、基于元组的检查约束和断言之间的主要区别。约束类型声明的位置动作的时间确保成立？基于属性的CHECK属性对关系插入元组或属性修改如果是子查询，则不能确保基于元组的CHECK关系模式元素对关系插入元组或属性修改如果是子查询，则不能确保断言数据库模式元素对任何提及的关系改变是Page48练习P.196/7.4.1/b)CreateAssertionCheck(NotExists(SelectmakerFromProductNaturalJoinPCWherespeed>All(SelectL2.speedFromProductP2,LaptopL2WhereP2.maker=makerANDP2.model=L2.model)));

Page49触发器是什么？基于特定事件触发的特定的约束检验。一个触发器trigger是存储在某个表中的一个命名的数据库对象。当该表进行某种数据更新时，将自动触发一组SQL语句的执行。注意：各种商业数据库可能使用不同语法。7.5触发器Page50触发器(trigger)触发器与约束不同：仅当数据库程序员声明的事件发生时，触发器被激活。所允许的事件种类通常是对某个特定关系的插入、删除或修改。当触发器被事件激活时，触发器测试触发条件。如果条件不成立，则相应该事件的触发器不做任何事情。如果触发器满足声明的条件，则与该触发器相连的动作由DBMS执行。Page51事件－条件－动作规则Event事件：数据更新指令：insert/delete/updateCondition条件：当事件发生后，检查条件是否满足：若不满足，则不执行动作。若满足，则执行一组动作之后状态转移。Action动作：一组SQL指令，通常是更新操作Old状态New状态Event/Condition[Action]Page52SQL中的触发器特征动作可以在触发事件之前或之后被执行。在被触发的事件中，动作既可指向被插入、删除、修改元组的新值，也可以指向其旧值。更新事件可以被局限到某个特定的属性或某一些属性。条件由WHEN短语给出。仅仅当规则被触发，并且触发事件的发生使条件成立时，动作才被执行。程序员可以选择动作执行的方式：一次只对一个更新元组(行级触发器)

，或者一次针对在数据库操作中被改变的所有元组(语句级触发器)。Page5354示例7.131)CREATETRIGGERNetWorthTrigger2)AFTERUPDATEOFnetWorthONMovieExec3)REFERENCING4)OLDROWASOldTuple，5)NEWROWASNewTuple6)FOREACHROW7)WHEN(OldTWorth>NewTWorth)8)UPDATEMovieExec9)SETnetWorth=OldTWorthl0)WHEREcert#=NewTuple.cert#；触发声明指出触发事件并告诉触发器在触发事件之前还是之后使用数据库状态允许触发器的条件和动作引用正被修改的元组。本例该子句允许给在改变之前和之后的元组命名该触发器是每修改一个元组执行一次的方式——行级触发器触发条件动作部分55触发器设计的选项1AFTER：条件测试和动作将在触发事件之后的数据库状态上被执行。BEFORE：WHEN条件将在触发事件执行之前的数据库状态上测试。如果条件是真，则在该状态上执行触发器的动作最后执行唤醒触发器的事件，不管条件是否仍为真INSTEADOF：将在8.2.3节中讨论，它与视图的修改有关。56触发器设计的选项2UPDATEOF<属性(组)>短语是UPDATE事件的可选项若给出该选项，那么它定义的事件仅仅是OF保留字后列出的属性(组)的修改。INSERTDELETEOF短语在INSERT或DELETE事件中不可使用，因为这两个事件都是作用在整个元组上。57触发器设计的选项3WHEN短语是可选项如果该短语缺省，则只要触发器被唤醒，都要执行动作。若有该短语，则仅当WHEN后的条件为真时执行动作。58触发器设计的选项4触发器的动作部分可以由单个或任意多个SQL语句组成。这些语句需由BEGIN…END括起，并且语句之间用分号分隔。59触发器设计的选项5FOREACHROW：行级触发器行级触发器触发事件是修改旧元组：修改之前的元组，用OLDROWAS短语命名新元组：修改之后的元组，用NEWROWAS短语命名行级触发器触发事件是插入旧元组：OLDROWAS不可使用新元组：使用NEWROWAS短语命名被插入的元组行级触发器触发事件是删除旧元组：OLDROWAS被用于命名被删除的元组新元组：NEWROWAS不可使用60触发器设计的选项6FOREACHSTATEMENT：语句级触发器[默认]一旦有合适类型的语句被执行，语句级触发器就被执行，而不问它实际上会影响多少元组(0个、1个或多个)例如：如果用SQL更新语句更新整个表语句级的修改触发器将只执行一次元组级触发器将对要修改的元组一次一个地执行。61触发器设计的选项7语句级触发器中不能直接引用旧的和新的元组任何触发器(元组级或语句级)都可引用旧元组(删除的元组或更新的元组的旧版本)的关系和新元组(插入元组或更新元组的新版本)的关系声明方式OLDTABLEASOldStuffNEWTABLEASNewSuff62关于OldStuff和NewSuff的说明语句级触发器触发事件是修改NewStuff和OldStuff中分别是被更新元组的新版本和旧版本语句级触发器触发事件是删除删除元组是OldStuff，不能声明NewStuff语句级触发器触发事件是插入插入的元组是NewStuff，不能声明OldStuff63示例7.14假定要阻止电影制作人的平均净资产值降到$500000对关系MovieExec(