数据库第7章约束和触发器

1、第7章 约束和触发器主讲人：骆炎民Email： 2主要内容7.1 键和外键 7.2 属性和元组上的约束 7.3 修改约束 7.4 断言 7.5 触发器 SQL提供了各种技术把完整性约束作为数据库模式的一部分。键约束，它将一个或一组属性声明为一个关系的键。SQL支持引用完整性，称为“外键约束” ，指一个关系中的一个或一组属性的值也必须在另一个关系的一个或一组属性的值中出现。SQL也允许属性上、元组上和关系之间的约束。关系之间的约束称为“断言”。“触发器”是主动元素的一种形式，它在某个特定事件发生时被调用，例如对一个特定关系的插入事件。3主动(active)元素主动元素是一个表达式或语句，只需编写

2、一次存储在数据库中，然后在适当的时间被执行。主动元素的执行可以是由于某个特定事件引发，如对关系插入元组，或者是当修改数据库的值引起某个逻辑值为真等。4SQL中完整性约束的几种方法键约束 参照完整性约束域的约束 基于属性/元组的CHECK约束断言 (整个关系或关系间的约束) 触发器(编程者指定触发时刻 一组命令)51. SQL中的键约束和单值约束键约束(主键约束)用 PRIMARY KEY 表示 (唯一 非空)单值约束(可用于标识候选键)用 UNIQUE 表示 (唯一 可空)基本表中只能有一个 主键可以有多个UNIQUE 说明6(1)主键的定义主键由关系的一个或多个属性组成在建表时说明法1：在列

3、出属性时，说明某个属性为主键法2：加入额外说明，说明某个或一组属性为主键7主键说明示例1 CREATE TABLE MovieStars( name CHAR(30) PRIMARY KEY, address VARCHAR(255), gender CHAR(1), birthdate DATETIME ); 法1: 单个属性name为主键，主键约束出现在属性说明中, PRIMARY KEY (name) ); 法2: 单独说明属性name为主键 8主键说明示例2CREATE TABLE Movies ( title VARCHAR(50), year CHAR(4), . producer

4、C# INT, PRIMARY KEY (title,year)； 组合码做主键，必须单独说明9(2) 单值约束的定义示例 CREATE TABLE MovieStars( name CHAR(30) PRIMARY KEY, address VARCHAR(255), gender CHAR(1) UNIQUE , birthdate DATETIME ) ; UNIQUE (address) ; ,10PRIMARY KEY 与UNIQUE细微差别1. 外键只能参照某个关系的主键2. DBMS的实现常常:为主键建索引按主键排序112. 外键约束声明外键：若关系R的一个属性(组) X，不是R

5、的主键，而与另一个关系S的主键相对应，则称X为关系R的外键称R为参照关系、称S为被参照关系。外键约束是一个断言，它判定一个关系中的非空外键，必须在另一个关系的主键中出现。考虑如下关系 ： Studios (name, address, presC# ) 参照关系 MovieExecs (name, address, cert# , netWorth) 被参照关系外键12(1) 声明外键的两种方法单一属性作外键 在属性定义后加 REFERENCES ()单独说明一个或多个属性为外键 FOREIGN KEY () REFERENCE ()13示例7.1Studio(name,address,pre

6、sC#) 参照关系 MovieExec(name,address,certs,netWorth) 被参照关系 要说明关系 Studio的外键是presC#，引用MovieExec的主键cert#属性。直接声明presC#引用cert#的语句如下： CREATE TABLE Studio ( 在属性定义中说明外键 name CHAR(30) PRIMARY KEY， address VARCHAR(255)， presC# INT REFERENCES MovieExec(cert#)； CREATE TABLE Studio( 单独说明外键 name CHAR(30) PRIMARY KEY,

7、 address VARCHAR(255), presC# INT, FOREIGN KEY (presC#) REFERENCES MovieExec(certS);外键为组合码时，必须单独说明14(2) 维护引用完整性对于上例，DBMS将阻止如下行为对Studio插入一新元组，其presC#值非空，但是它不是MovieExec关系中任何元组的cert#值。修改Studio关系元组的presC#属性为非空值，但是该值不是MovieExec关系中任何元组的cert#值。删除MovieExec元组，该元组的cert#值非空，是一个或多个Studio元组的presC#值。修改MoivExec元组的

8、cert#值，而旧的cert#值是某电影公司的presC#值。这两种更新是在声明了外键约束的关系(Studio)上做的修改；别无选择，系统必须拒绝这种违法修改。后两种更新是在被引用关系(MovieExec)上做的修改；设计者可以有三个选择：(a) 缺省原则(b) 级联原则(c) 置空值原则15考虑两组示例关系示例1： Students(Sno,Sname,Sage) (主表) SC(Sno,Cno,Grade) (子表) Courses(Cno,Cname,Ccredit) (主表)示例2： Eoyees(Eno,Ename,Eage,Dno) (子表) Departments(Dno,Dna

9、me,Phonempl) (主表)参照关系：子表(子记录)被参照关系：主表(主记录)1616方案1: 缺省原则-拒绝非法更新 a.插子记录必须有对应的主记录 如果插入选课记录(020506,25,90) 课程表中没有25号课程 则禁止插入 1717 b. 改子记录必须有对应的主记录 如果选课记录改为(020506,25,90) 课程表中没有25号课程 则禁止修改1818 c. 有子记录时禁止删除主记录 有020506的选课记录时, 不许删除Student中 020506的记录 d.有子记录时禁止修改主记录 有020506的选课记录 欲将Student表中的020506改为030712 则禁止修

10、改1919方案2: 级联原则 在删除和更新主表时对子表进行级联处理. a. 删除主记录 同时删除子记录 删除Students中020506的记录 同时删除020506的所有选课记录 2020 b.更新主记录 同时更新子记录 更新Students中020506的学号为030712 同时更新020506的所有选课记录的 学号为0307122121方案3: 置空值原则 在删除和更新主表时对子表进行置空处理. a. 删除主记录 同时将子记录外键改为NULL 删除部门表中4号部门信息 同时将4号部门的20个职工记录的部门号 置空 2222 b.更新主记录 同时将子记录外键改为NULL 修改部门表中4号部

11、号为14号 同时将4号部门的20个职工记录的部门号 置空 23三个选项的声明方法这些选项可独立地选择删除和修改，并且它们同外键一起声明。声明的方法是在ON DELETE或ON UPDATE后面加上SET NULL或CASCADE选项。示例7.2： 1) CREATE TABLE Studio( 2) name CHAR(30)PRIMARY KEY， 3) address VARCHAR(255)， 4) presC# INT REFERENCES MovieExec(cert#)5) ON DELETE SET NULL 6) ON UPDATE CASCADE)； 24使用特点该例中置空原

12、则使删除具有更多的含义，而级联原则更适于修改。电影公司经理退休时，电影公司仍然存在，其经理属性值在经理没有确定前要取空值。电影公司经理证书号的修改更像是办事员的变更。此时人员继续存在，而且将是该电影公司的经理，因此Studio中presC#属性值也应该随着改变25(3) 延迟约束检查问题：循环约束 Studio (name, address, presC#)电影公司信息 MovieExec (name, address, cert#, netWorth)制片人信息同时给出两个外键约束：声明presC#是Studio的外键，参照关系MovieExec的主键certs声明cert#是MovieEx

13、ec 的外键，参照关系Studio的属性presC#造成循环约束26示例7.3如果一个新制片人要新建一个电影公司，并担任电影公司的经理。如果先在关系Studio中插入电影公司的信息，则由于Studio 外键presC#参照的MovieExec 关系中的certs属性尚未存在，从而违反外键约束。相反如果先在关系MovieExec中插入制片人信息，则由于MovieExec外键certs参照的Studio关系中的presC#属性尚未存在，从而也违反外键约束。27解决办法首先，必须将两个插入操作(一个插入Studio，另一个插入MovieExec)组成一个单一事务。然后，需要有一种方法通知DBMS不要

14、检查其约束，直到整个事务完成执行并要提交为止。即延迟约束检查。28延迟约束检查的设置任何约束的声明后面可以有两个选项NOT DEFERRABLE (缺省值)每次执行一条数据库更新语句时，如果该更新可能违反外键约束，则随后立即检查该约束。DEFERRABLE约束检查可以推迟到当前事务完成时进行。DEFERRABLE后也可以有两个选项INITIALLY DEFERRED：检查被推迟到事务提交前执行INITIALLY IMMEDIATE：检查在每个语句后立即被执行(缺省)29区分DEFERRABLE INITIALLY IMMEDIATE默认检查在每个语句后立即被执行。但在需要的时候，可以随时将约束

15、检查延后。在这种情况下该选项非常有用：多数时候需要在每个语句后立即检查约束，但偶尔有一批工作需要将约束检查延后。NOT DEFERRABLE永远无法将约束检查延后至事务提交时30延迟约束检查示例CREATE TABLE Studio ( name CHAR(30)PRIMARY KEY， address VARCHAR(255)， presC# INT UNIQUE REFERENCES MovieExec(cert#) DEFERRABLE INITIALLY DEFERRED )； 31延迟约束检查修改任何类型的约束都可以命名假设将上例的参照完整性约束命名为MyConstraint，则可以

16、用如下SQL语句将该约束从延迟约束检查改为立即检查：SET CONSTRAINT MyConstraint IMMEDIATE ;32主要内容7.1 键和外键 7.2 属性和元组上的约束 7.3 修改约束 7.4 断言 7.5 触发器 33属性和元组上的约束在SQL的CREATE TABLE语句中可以声明两种约束：在单一属性上的约束在整个元组上的约束341. 非空值约束CREATE TABLE Studio ( name CHAR(30) PRIMARY KEY， address VARCHAR(255)， presC# INT REFERENCES MovieExec(cert#) NOT

17、NULL)；对Studio关系插入元组时，不能只给出名字和地址，presC#的值不许为NULL置空值原则此处不能用。因为该原则通知系统，当违反外键约束时要将PresC#值置空。352. 基于属性的CHECK约束建表时 某个属性定义后 CHECK() 是该属性的每个值都应满足的，原则上可以是任何WHERE子句中允许的描述。36示例7.6-1约束证书号必须至少有6位数字CREATE TABLE Studio ( name CHAR(30) PRIMARY KEY， address VARCHAR(255)， presC# INT REFERENCES MovieExec(cert#) CHECK(

18、presC#=100000)；37示例7.6-2约束gender属性的值只能取F和MCREATE TABLE MovieStars( name CHAR(30) PRIMARY KEY, address VARCHAR(255), gender CHAR(1) CHECK (gender IN (F,M), birthdate DATETIME);38CHECK约束中的如果CHECK约束中的要引用其他关系或其他属性，则必须在中给出子查询。其引用的“其他关系”必须是子查询FROM子句中出现的关系其引用的“其他属性”必须是子查询FROM子句中关系的属性(即使该关系是被检查属性所在的关系也须如此)。

19、39CHECK约束的使用基于属性的CHECK约束是在元组为该属性获得新值(修改/插入) 时被检查。在修改的情况下，是对新值而非旧值进行约束检查。如果新值违反约束，则该修改被拒绝。如果数据库的修改没有改变与约束相关的属性，则不进行基于属性的CHECK约束检查40示例7.7例7.7 假设用基于属性的CHECK约束模拟引用完整性约束，要求被引用值必须存在。下面是模拟 Studio (name, address, presC#)关系中presC#值必须在关系MovieExec (name, address, cert#, netWorth)的cert#之中出现的错误尝试。41示例7.7(续)CREAT

20、E TABLE Studios( name CHAR(30) PRIMARY KEY, address VARCHAR (255), presC# INT CHECK ( presC# IN (SELECT cert# FROM MovieExecs);插入、更新Studios时 系统进行检验 阻止不合理的数据进入但删除MovieExecs中元组时，不做此校验，可能使Studio表中出现若干元组，找不到相应的cert#423. 基于元组的CHECK约束基于元组的CHECK约束：建表时在中也可用CHECK 可以是WHERE子句中出现的任何表达式。表达式被解释为关系R元组上的条件。关系R的属性都可

21、以直接出现在该表达式中。这一点与“基于属性的CHECK约束” (其他属性只能通过子查询引用)不同。43基于元组的CHECK约束的使用涉及整个元组而不仅针对某一个属性每次向R插入元组以及当R的元组被修改时，都要检查基于元组的CHECK约束条件。违规的插入或修改语句都将被拒绝。基于元组的CHECK约束对其他关系不可见如果条件在子查询中提及其他关系，而那个关系的改变将使关系R的某些元组对条件的计算结果为假, CHECK不能阻止这种改变。类似基于属性的CHECK约束44示例约束男影星姓名前不能加 Ms. CREATE TABLE MovieStars( name CHAR(30) PRIMARY KE

22、Y, address VARCHAR(255) , gender CHAR(1) CHECK (gender IN (F,M) ) , birthdate DATETIME , CHECK (gender=F OR name NOT LIKE Ms.%)454. 基于元组和基于属性的约束的比较如果元组上的约束涉及该元组的多个属性，则它必须作为基于元组的约束。如果约束仅涉及元组的一个属性，那么可以作为基于元组或基于属性的约束。基于元组的约束将比基于属性的约束更频繁地被检查只要该元组的任一个属性被改变，而不是仅当在约束中提及的属性改变时都要检查。46主要内容7.1 键和外键 7.2 属性和元组上的

23、约束 7.3 修改约束 7.4 断言 7.5 触发器 471. 给约束命名为了修改或删除一个已存在的约束，约束必须有名字CREATE TABLE MovieStars( name CHAR(30) CONSTRAINT NameIsKey PRIMARY KEY, address VARCHAR(255), gender CHAR(1) CONSTRAINT NoAndro CHECK (gender IN (F,M), birthdate DATETIME, CONSTRAINT RightTitle CHECK(GENDER=F OR name NOT LIKE Ms.%);482. 修改

24、表上的约束修改延迟约束检查SET CONSTRAINT MyConstraint DEFERRED / IMMEDIATE删除约束ALTER TABLE MovieStar DROP CONSTRAINT NamelsKey；ALTER TABLE MovieStar DROP CONSTRAINT NoAndro；ALTER TABLE MovieStar DROP CONSTRAINT RiShtTitle；49添加约束ALTER TABLE MovieStar ADD CONSTRAINT NamelsKey PRIMARY KEY(name);ALTER TABLE MovieStar

25、 ADD CONSTRAINT NoAndro CHECK(gender IU(F,M); ALTER TABLE MovieStar ADD CONSTRAINT RightTitle CHECK(gender=F OR name NOT LIKE Ms.%);这些约束都是基于元组而不是基于属性的检查，不能将其恢复到基于属性的约束。50主要内容7.1 键和外键 7.2 属性和元组上的约束 7.3 修改约束 7.4 断言 7.5 触发器 511. 创建断言断言的形式CREATE ASSERTlON CHECK()断言建立时，断言的条件必须是真，且要永远保持是真。任何引起断言条件为假的数据库更新

26、都被拒绝。约束的检查可以一直延期到事务提交前。如果对断言也这样做，到事务结束前它可能暂时变成假值。 已经介绍过的其他类型CHECK约束，如果涉及子查询，可以在某些条件下避免操作被拒绝。522. 使用断言基于元组的CHECK约束和断言约束在书写方式上有差别。基于元组的检查能直接引用在它声明中出现的关系的属性。断言没有如此特权。断言条件中引用的任何属性都必须要介绍，特别是要提及在select-from-where表达式中的关系。53示例7.13希望其净资产值少于S10 000 000的人不能成为电影公司经理。即声明经理净资产值少于$10000000的电影公司集合是空。MovieExec (name

27、, address, cert#, netWorth)Studio (name, address, presC#)CREATE ASSERTION RichPres CHECK (NOT EXISTS ( SELECT S FROM Studio,MovieExec WHERE presC#=cert# AND netWorth10000000 )； CREATE TABLE Studios( name CHAR(30) PRIMARY KEY, address VARCHAR (255), presC# INT REFERENCES MovieExec(cert#), CHECK ( pre

28、sC# NOT IN (SELECT cert# FROM MovieExecs WHERE netWorth= ALL (SELECT SUM(length) FROM Movies GROUP BY studioName) )； CHECK(10000 = ALL (SELECT SUM(1ength) FROM Movies GROUP DY studioName);必须=删除元组时仍有可能违反该约束P21455CHECK 约束与断言的区别约束类型声明的位置动作的时间确保成立?基于属性的CHECK属性对关系插入元组或属性修改时如果是子查询，则不能确保基于元组的CHECK关系模式 元素对关

29、系插入元组或属性修改时如果是子查询，则不能确保断言数据库模式元素对任何涉及的关系做改变时是563.删除断言DROP ASSERTION 57主要内容7.1 键和外键 7.2 属性和元组上的约束 7.3 修改约束 7.4 断言 7.5 触发器 58触发器也称事件-条件-动作规则(event-condition-action-rule: ECA)触发器与前面已介绍的几种约束有如下三点不同仅当数据库程序员声明的事件发生时，触发器被激活。 如插入、删除、修改或事务的结束等当触发器被事件激活时，触发器测试触发的条件。如果条件不成立，则响应该事件的触发器不做任何事情。 如果触发器声明的条件满足，则与该触发

30、器相连的动作由DBMS执行。动作可以是任何数据库操作序列，包括与触发事件毫无关联的操作。59SQL触发器语句的主要特征1.触发器的条件检查和动作可在触发事件执行前的数据库的状态上或在触发动作被执行后的状态上执行。2.条件和动作可引用元组的旧值和或触发事件中更新的元组的新值。3.更新事件可被局限到某个特定的属性或某些属性。4.程序员可选择动作执行的方式：a)一次只对一个更新元组(行级触发器) b)一次针对在数据库操作中被改变的所有元组 (语句级触发器)60示例7.151) CREATE TRIGGER NetWorthTrigger2) AFTER UPDATE OF netWorth ON M

31、ovieExec3) REFERENCING4) OLD ROW AS OldTuple，5) NEW ROW AS NewTuDle6) FOR EACH ROW7) WHEN ( Worth Worth)8) UPDATE MovieExec9) SET netWorth= Worthl0) WHERE cert#=NewTuple.cert#； 触发声明指出触发事件并告诉触发器在触发事件之前还是之后使用数据库状态允许触发器的条件和动作引用正被修改的元组。本例该子句允许给在改变之前和之后的元组命名该触发器是每修改一个元组执行一次的方式行级触发器触发条件动作部分61触发器设计的选项1AFTE

32、R：条件测试和动作将在触发事件之后的数据库状态上被执行。BEFORE：WHEN条件将在触发事件执行之前的数据库状态上测试。如果条件是真，则在该状态上执行触发器的动作最后执行唤醒触发器的事件，不管条件是否仍为真INSTEAD OF：将在8.2.3节中讨论，它与视图的修改有关。62触发器设计的选项2UPDATEOF 短语是UPDATE事件的可选项若给出该选项，那么它定义的事件仅仅是OF保留字后列出的属性(组)的修改。INSERTDELETEOF短语在INSERT或DELETE事件中不可使用，因为这两个事件都是作用在整个元组上。63触发器设计的选项3WHEN短语是可选项如果该短语缺省，则只要触发器被

33、唤醒，都要执行动作。若有该短语，则仅当WHEN后的条件为真时执行动作。64触发器设计的选项4触发器的动作部分可以由单个或任意多个SQL语句组成。这些语句需由BEGINEND括起，并且语句之间用分号分隔。 65触发器设计的选项5FOR EACH ROW：行级触发器行级触发器触发事件是修改旧元组：修改之前的元组，用OLD ROW AS短语命名新元组：修改之后的元组，用NEW ROW AS短语命名行级触发器触发事件是插入旧元组： OLD ROW AS不可使用新元组：使用NEW ROW AS短语命名被插入的元组行级触发器触发事件是删除旧元组： OLD ROW AS被用于命名被删除的元组新元组： NEW

34、 ROW AS不可使用66触发器设计的选项6FOR EACH STATEMENT：语句级触发器默认一旦有合适类型的语句被执行，语句级触发器就被执行，而不问它实际上会影响多少元组(0个、1个或多个)例如：如果用SQL更新语句更新整个表语句级的修改触发器将只执行一次元组级触发器将对要修改的元组一次一个地执行。67触发器设计的选项7语句级触发器中不能直接引用旧的和新的元组任何触发器(元组级或语句级)都可引用旧元组(删除的元组或更新的元组的旧版本)的关系和新元组(插入元组或更新元组的新版本)的关系声明方式OLD TABLE AS OldStuffNEW TABLE AS NewSuff68关于OldS

35、tuff和NewSuff的说明语句级触发器触发事件是修改NewStuff和OldStuff中分别是被更新元组的新版本和旧版本语句级触发器触发事件是删除删除元组是OldStuff，不能声明NewStuff语句级触发器触发事件是插入插入的元组是NewStuff，不能声明OldStuff69示例7.14假定要阻止电影制作人的平均净资产值降到$500000对关系MovieExec (name, address, cert#, netWorth)的netWorth列做插入、删除或修改时可能会违反上述约束注意：可在一个语句中插入、删除或修改MovieExec的许多元组。修改期间平均净资产值可暂低于$500

36、 000 ;当所有变更结束时，其净资产值将超过$500 000约束要做的工作：若语句执行结束后，净资产值仍低于$500 000，则整组更新操作被拒绝。70示例7.14(续)对于关系MovieExec的插入、删除和修改这三个事件有必要分别写一个触发器。下面只给出了修改事件的触发器。插入和删除事件的触发器与此类似。71示例7.14(续) 1) CREATE TRIGGER AvgNetWorthTrigger 2) AFTER UPDATE OF netWorth ON MovieExec 3) REFERENCING 4) OLD TABLE AS OldStuff， 5) NEW TABLE AS NewStuff 6) FOR EACH STATEMENT 7