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

1、1第第7章章 约束和触发器约束和触发器第7章 约束和触发器2SQL中约束种类中约束种类n数据库设计质量体现为约束数据库设计质量体现为约束constraints所提供的可靠性保障。约束以表达式或所提供的可靠性保障。约束以表达式或语句的形式存储在数据库中。约束是一语句的形式存储在数据库中。约束是一种主动性种主动性(active)元素，当数据库特定元素，当数据库特定状态发生改变时自动运行。状态发生改变时自动运行。nSQL2提供部分完整性约束：键、参照完提供部分完整性约束：键、参照完整性、域约束、元组约束等。整性、域约束、元组约束等。nSQL3提供触发器提供触发器trigger机制：由特定机制：由特定

2、事件触发某种主动性元素。事件触发某种主动性元素。 第7章 约束和触发器37.1 键和外键键和外键第7章 约束和触发器4键与外键键与外键n键键key是最重要的约束。每个表都必须确定自是最重要的约束。每个表都必须确定自己的键。己的键。n每个表都可能有多个属性集可作为键，称为每个表都可能有多个属性集可作为键，称为“候选键候选键candicate key”。n一个表只能确定一个主键一个表只能确定一个主键(Primary Key)。n若某个属性说明为若某个属性说明为Unique，则它是一个候选，则它是一个候选键。键。n若关系的某个属性说明为外键，则该属性出现若关系的某个属性说明为外键，则该属性出现的值，

3、一定会在另一个关系的主键中出现。的值，一定会在另一个关系的主键中出现。第7章 约束和触发器5主键声明主键声明n如何说明表中的主键？如何说明表中的主键？ SQL语言有两种说明主键的方式。语言有两种说明主键的方式。方式方式1：Create Table语句中，某属性语句中，某属性说明之后加说明之后加Primary Key方式方式2：属性表之后，加：属性表之后，加Primary Key(属性属性1，属性，属性2，)u若主键有多个属性，则只能用方式若主键有多个属性，则只能用方式2。uGUI操作方式更方便直观。操作方式更方便直观。第7章 约束和触发器6用用UNIQUE声明键声明键n如何说明表中的主键？如何

4、说明表中的主键？ SQL语言有两种说明主键的方式。语言有两种说明主键的方式。方式方式1：Create Table语句中，某属性语句中，某属性说明之后加说明之后加UNIQUE方式方式2：属性表之后，加：属性表之后，加UNIQUE(属性属性1，属性，属性2，)u若用若用UNIQUE说明键有多个属性，则只说明键有多个属性，则只能用方式能用方式2。uGUI操作方式更方便直观操作方式更方便直观第7章 约束和触发器7例子例子n考虑关系考虑关系SalesMan的模式的模式(方式方式1) CREATE TABLE SalesMan( empid VARCHAR (10) PRIMARY KEY, idno V

5、ARCHAR (18) UNIQUE, name VARCHAR (30), gender CHAR(1), birthday DATE, address VARCHAR(255), phone VARCHAR(13) );第7章 约束和触发器8例子例子n考虑关系考虑关系SalesMan的模式的模式(方式方式2) CREATE TABLE SalesMan( empid VARCHAR (10), idno VARCHAR (18), name VARCHAR (30), gender CHAR(1), birthday DATE, address VARCHAR(255), phone VA

6、RCHAR(13), PRIMARY KEY(empid), UNIQUE(idno) );第7章 约束和触发器9主键和主键和Unique属性之间有何区别和联系属性之间有何区别和联系n一个表有且仅有一个主键；而一个表有且仅有一个主键；而Unique属性可属性可有多个或没有。有多个或没有。n主键是单个属性，则该属性隐含为主键是单个属性，则该属性隐含为Unique。n主键不允许有主键不允许有NULL值，属性为值，属性为Unique则允许则允许有有NULL值。值。n若主键是多个属性，则每个属性都不可能为若主键是多个属性，则每个属性都不可能为Unique。nDBMS对主键往往自动赋予一些特征，如建立对

7、主键往往自动赋予一些特征，如建立索引索引index等。等。第7章 约束和触发器10强制键约束强制键约束n什么操作会导致违背键约束？什么操作会导致违背键约束？对表的对表的delete操作不会违背该表的键约操作不会违背该表的键约束。束。insert和和update可能违背该表的键约束。可能违背该表的键约束。nSQL实施主键约束即是在实施主键约束即是在insert和和update时检验键值，避免空值或重复键时检验键值，避免空值或重复键值。值。第7章 约束和触发器11外键约束声明外键约束声明n如何说明外键如何说明外键 两种两种SQL方式：方式：方式方式1：某属性之后加：某属性之后加references

8、 (被参照属性被参照属性)。方式方式2：属性表之后加：属性表之后加foreign key references (被被参照属性参照属性)。 其中：被参照属性应是被参照表的其中：被参照属性应是被参照表的Primary Key或或unique属性。属性。第7章 约束和触发器12例子例子n例：例：CREATE TABLE StarsIn( Movietitle VARCHAR(90) NOT NULL, Movieyear INT NOT NULL , StarName VARCHAR(30) NOT NULL, PRIMARY KEY (Movietitle, Movieyear, StarNam

9、e), FOREIGN KEY (Movietitle, Movieyear) References Movie(title, year), FOREIGN KEY StarName References Stars(name) );第7章 约束和触发器13外键约束声明外键约束声明n是否可定义一个表参照自己？是否可定义一个表参照自己？可以。可以。 例如：例如：salesman(empid, idno, name, managerid, deptid, )n外键是否可取外键是否可取NULL值？值？可以。可以。第7章 约束和触发器14维护引用完整性维护引用完整性n数据库更新时如何保证参照完整性数据

10、库更新时如何保证参照完整性? 有三种可选策略，以保证参照完整性：有三种可选策略，以保证参照完整性：n1 Restrict限制限制(缺省缺省)n2 Cascade级联级联n3 Set Null置空置空第7章 约束和触发器15Restrict限制限制(缺省缺省)n以以StarsIn(movieTitle,MovieYear, )参照参照Movie(title,year, ) 为例：为例：n对于对于StarsIn (参照表参照表)，下面操作被拒绝：，下面操作被拒绝：ninsert语句中语句中movieTitle和和MovieYear值不值不是是Movie中已有的一个主键值。中已有的一个主键值。nup

11、date语句中改变语句中改变movieTitle和和MovieYear值为不是值为不是Movie中已有的主键值。中已有的主键值。n对于对于Movie(被参照表被参照表)，Restrict拒绝以下操拒绝以下操作：作：ndelete语句删除一个被语句删除一个被StarsIn参照的元组。参照的元组。nupdate语句修改一个被语句修改一个被StarsIn参照的参照的title和和year主键值。主键值。第7章 约束和触发器16Cascade级联级联n影响被参照表的影响被参照表的delete和和update操作。操作。n以以StarsIn(movieTitle,MovieYear, )参照参照Movi

12、e(title,year, ) 为例：为例：n当当Movie (被参照表被参照表)delete删除某个元组删除某个元组时，时，StarsIn (参照表参照表)中所有参照元组被自中所有参照元组被自动删除。动删除。n当当Movie (被参照表被参照表)update修改某个元组修改某个元组的的title或或year值时，值时，StarsIn (参照表参照表)中所中所有参照元组被自动修改。有参照元组被自动修改。第7章 约束和触发器17Set Null置空置空n影响被参照表的影响被参照表的delete和和update操作。操作。n以以Movie(title,year, , ProducerC#)参照参照

13、MovieExec(name, ,cert#, )为例：为例：n首先首先ProducerC#应允许应允许NULL。n当当MovieExec (被参照表被参照表)delete删除某个元组时，删除某个元组时，Movie (参照表参照表)中所有参照元组的中所有参照元组的ProducerC#被被置空。置空。n当当MovieExec (被参照表被参照表)update修改某个元组的修改某个元组的cert#时，时，Movie (参照表参照表)中所有参照元组的中所有参照元组的ProducerC#被置空。被置空。第7章 约束和触发器18维护引用完整性策略的语法维护引用完整性策略的语法n这些策略可在说明外键的同时

14、描述。这些策略可在说明外键的同时描述。 references (属性属性表表)Action 其中：其中： Action: ON Update | Delete Restrict | Cascade | Set Null 第7章 约束和触发器19例子例子CREATE TABLE Movie( title VARCHAR(90) NOT NULL, year INT NOT NULL, length INT, colorIn CHAR(1) NOT NULL, producerC# INT, PRIMARY KEY (title,year), FOREIGN KEY producerC# Refe

15、rences MovieExec(cert#) ON DELETE CASCADE ON UPDATE CASCADE);u注意：对于一个外键，注意：对于一个外键，Update和和Delete可分别采用可分别采用不同的策略。不同的策略。第7章 约束和触发器20“悬挂元组悬挂元组” dangling tuples n什么是什么是“悬挂元组悬挂元组” dangling tuples？对于参照关系对于参照关系A，外键值未出现在被参照表中，外键值未出现在被参照表中的元组，即违背参照完整性的元组。的元组，即违背参照完整性的元组。n如何避免出现如何避免出现“悬挂元组悬挂元组”？1 Restrict策略：在

16、参照关系中对产生悬挂元组策略：在参照关系中对产生悬挂元组的操作予以禁止。的操作予以禁止。2 Cascade策略：自动删除或修改产生出来的策略：自动删除或修改产生出来的悬挂元组。悬挂元组。3 Set Null策略：产生出来的每个悬挂元组的外策略：产生出来的每个悬挂元组的外键值置空键值置空NULL，使其不参照任何元组。，使其不参照任何元组。第7章 约束和触发器217.2 属性和元组上的约束属性和元组上的约束第7章 约束和触发器22属性值约束属性值约束n限制某些属性的值在特定范围内限制某些属性的值在特定范围内.n这类约束可用下列方式表达这类约束可用下列方式表达:在关系式定义中给出属性的约束。在关系式

17、定义中给出属性的约束。在整个元组上的约束。该约束是关系模在整个元组上的约束。该约束是关系模式的一部分，不与任何属性相关。式的一部分，不与任何属性相关。第7章 约束和触发器23非空约束非空约束n如何说明某属性非空如何说明某属性非空NOT NULL？在在create table指令中，属性说明之后加指令中，属性说明之后加NOT NULL；若不显式说明，该属性隐含为允许空值。若不显式说明，该属性隐含为允许空值。n说明非空属性有何效果？说明非空属性有何效果？ 不能用不能用update修改其值为修改其值为NULL。 insert时必须指定一个非空值。时必须指定一个非空值。 不能使用外键的不能使用外键的S

18、et Null策略。策略。u注意：主键属性和注意：主键属性和unique属性隐含非空；允属性隐含非空；允许为空的属性不可能是主键或许为空的属性不可能是主键或unique。第7章 约束和触发器24基于属性的基于属性的Check约束约束n如何限制某属性的值在特定范围？如何限制某属性的值在特定范围？在属性说明之后，增加在属性说明之后，增加check(条件条件)，属性的值应使条件为真。属性的值应使条件为真。条件的语法与条件的语法与Where子句的条件一样。子句的条件一样。当当insert和和update使属性值改变时，执使属性值改变时，执行行check检验，拒绝预约属性不一致的检验，拒绝预约属性不一致

19、的更新。更新。第7章 约束和触发器25例子例子n设有关系模式：设有关系模式： Stodio(name,address,presC#) 要求其证书号必须至少为位数字，则可写出约束：要求其证书号必须至少为位数字，则可写出约束： presC# INT REFERENCES MovieExec(cert#) CHECK(presC#=100000)n设有关系模式：设有关系模式： MovieStar(name,address,gender,birthday) 要求其性别只能为要求其性别只能为和和，则可写出约束：，则可写出约束： gender CHAR(1) CHECK(gender IN (,)第7章

20、约束和触发器26基于属性的基于属性的Check约束约束n何时检查何时检查check条件？条件？只有当该表执行只有当该表执行insert或或update时才检测时才检测check条件。条件。u注意：注意：check条件中可包含其它关系的属性，但应避条件中可包含其它关系的属性，但应避免。免。例如，设有关系例如，设有关系 Stodio(name,address,presC#) 其关系中其关系中presC#值必须在关系值必须在关系 MovieExec(name,address,cert#,networth) 的的cert#之中出现约束，则在定义中给出约束：之中出现约束，则在定义中给出约束： presC

21、# INT CHECK(presC# IN(SELECT cert# FROM MovieExec) 修改或删除修改或删除cert#的值，使某个的值，使某个presC#对应的键值不对应的键值不在在MovieExec中是可能的。中是可能的。第7章 约束和触发器27域约束域约束n域域domain 也称为用户定义数据类型也称为用户定义数据类型user-defined data type.n基于某种基本数据类型且增加特定约束，基于某种基本数据类型且增加特定约束，存于数据库中，可供多个表使用。存于数据库中，可供多个表使用。 例如：例如： CREATE DOMAIN sex integer NOT NUL

22、L DEFAULT 1 CHECK(VALUE IN(0,1) )第7章 约束和触发器28基于元组的基于元组的Check约束约束n对某个表中元组的约束而不是对某个属性的约束。对某个表中元组的约束而不是对某个属性的约束。 例如：在关系模式例如：在关系模式 MovieStar(name,address,gender,birthday) 中要求每个元组的审核：如果影星为男性，则他的名中要求每个元组的审核：如果影星为男性，则他的名字不能与字不能与Ms.开头。开头。n在定义表的性质时增加如下约束：在定义表的性质时增加如下约束： CHECK(gender= F OR name NOT LIKE Ms.%)

23、n何时检查何时检查check条件？条件？只有当该表执行只有当该表执行insert或或update时才检测时才检测check条件。条件。u注意：如果在表中既有属性注意：如果在表中既有属性check也有元组也有元组check，则先检测属性。则先检测属性。第7章 约束和触发器297.3 修改约束修改约束第7章 约束和触发器30给约束命名给约束命名n为修改或删除一个已经存在的约束，约束必须有名字。为修改或删除一个已经存在的约束，约束必须有名字。为了命名，在约束前加保留字为了命名，在约束前加保留字CONSTRAINT和该约和该约束的名字。束的名字。例如：设有关系模式例如：设有关系模式 MovieStar

24、(name,address,gender,birthday)为其主键约束命名：为其主键约束命名： name CHAR(30) CONSTRAINT NameIsKey PRIMARY KEY为影星的性别约束命名：为影星的性别约束命名： gender CHAR(1) CONSTRAINT NoAndro CHECK(gender IN (,)第7章 约束和触发器31修改表上的约束修改表上的约束n可用可用ALTER TABLE来修改约束。来修改约束。用保留字用保留字DROP和要删除的约束名字来删除约束。和要删除的约束名字来删除约束。用保留用保留ADD，后跟要添加的约束来实现约束的添加。，后跟要添加

25、的约束来实现约束的添加。例如：要删除例如：要删除MovieStar的主键约束的主键约束 ALTER TABLE MovieStar DROP NameIsKey 要添加要添加MovieStar的主键约束的主键约束 ALTER TABLE MovieStar ADD CONSTRAINT NameIsKey PRIMARY KEY(name)第7章 约束和触发器32断言断言(Assertion)nSQL标准提出了一种强制任何条件的简标准提出了一种强制任何条件的简单的断言形式，与单的断言形式，与check类似。类似。n断言的定义为断言的定义为 CREATE ASSERTION CHECK()n当建

26、立断言时，断言的条件必须是真，当建立断言时，断言的条件必须是真，并且要永远保持为真。任何引起断言条并且要永远保持为真。任何引起断言条件为假的数据库更新都被拒绝。件为假的数据库更新都被拒绝。第7章 约束和触发器33断言断言(Assertion)n断言要写什么？断言要写什么？在写断言条件时所引用的任何属性都必在写断言条件时所引用的任何属性都必须标明。须标明。n何时检查断言的何时检查断言的check条件？条件？只有当该表执行只有当该表执行insert或或update时才检时才检测测check条件。条件。n何时删除断言？何时删除断言？DROP ASSERTION 第7章 约束和触发器34例子例子n假定

27、希望其净资产值少于假定希望其净资产值少于10000000的人不能成为制片厂经理。的人不能成为制片厂经理。 CREATE ASSERTION RichPres CHECK (NOT EXISTS (SELECT * FROM Studio,MovieExec WHERE presC#=cert# AND networth10000000 ) )注意与约束的区别：注意与约束的区别：CREATE TABLE Studio( name CHAR(30) PRIMARY KEY, address VARCHAR(255), presC# INT REFERENCES MovieExec(cert#),

28、CHECK (presC# NOT IN (SELECT cert# FROM MovieExec WHERE networth10000000) ) )第7章 约束和触发器35约束的比较约束的比较n下面表格列出了基于属性的检查约束、基于元下面表格列出了基于属性的检查约束、基于元组的检查约束和断言之间的主要区别。组的检查约束和断言之间的主要区别。约束类型约束类型声明的位置声明的位置动作的时间动作的时间确保成立？确保成立？基于属性的基于属性的CHECK属性属性对关系插入元组对关系插入元组或属性修改或属性修改如果是子查询，如果是子查询，则不能确保则不能确保基于元组的基于元组的CHECK关系模式元素

29、关系模式元素对关系插入元组对关系插入元组或属性修改或属性修改如果是子查询，如果是子查询，则不能确保则不能确保断言断言数据库模式元素数据库模式元素对任何提及的关对任何提及的关系改变系改变是是第7章 约束和触发器367.4 触发器触发器第7章 约束和触发器37触发器触发器(trigger)n基于特定事件触发的特定的约束检验。基于特定事件触发的特定的约束检验。u注意：各种商业数据库可能使用不同语注意：各种商业数据库可能使用不同语法。法。n触发器是什么？触发器是什么？一个触发器一个触发器trigger是存储在某个表中的是存储在某个表中的一个命名的数据库对象。当该表进行某一个命名的数据库对象。当该表进行

30、某种数据更新时，将自动触发一组种数据更新时，将自动触发一组SQL语语句的执行。句的执行。第7章 约束和触发器38事件条件动作规则事件条件动作规则 nEvent事件：数据更新指令：事件：数据更新指令：insert / delete /updatenCondition条件：当事件发生后，检查条件是条件：当事件发生后，检查条件是否满足：否满足：若不满足，则不执行动作而状态转移。若不满足，则不执行动作而状态转移。若满足，则执行一组动作之后状态转移。若满足，则执行一组动作之后状态转移。nAction动作：一组动作：一组SQL指令，通常是更新操作指令，通常是更新操作 Old状态New状态Event/Con

31、ditionAction第7章 约束和触发器39触发器与约束不同触发器与约束不同n当数据库程序员声明的事件发生时，触当数据库程序员声明的事件发生时，触发器被激活。事件可以是对某个特定关发器被激活。事件可以是对某个特定关系的插入、删除或修改。系的插入、删除或修改。n当触发器被事件激活时，不是立即执行，当触发器被事件激活时，不是立即执行，而是首先由触发器测试触发条件。如果而是首先由触发器测试触发条件。如果条件不成立，则响应事件的触发器不做条件不成立，则响应事件的触发器不做任何事情。任何事情。n如果触发器声明的条件满足，则与触发如果触发器声明的条件满足，则与触发器相连的动作由器相连的动作由DBMS执

32、行。执行。第7章 约束和触发器40SQL中的触发器特征中的触发器特征n动作可以在触发事件之前或之后被执行。动作可以在触发事件之前或之后被执行。n在被触发的事件中，动作既可指向被插入、删在被触发的事件中，动作既可指向被插入、删除、修改元组的新值，也可以指向其旧值。除、修改元组的新值，也可以指向其旧值。n更新事件可以被局限到某个特定的属性或某一更新事件可以被局限到某个特定的属性或某一些属性。些属性。n条件由条件由WHEN短语给出。仅仅当规则被触发，短语给出。仅仅当规则被触发，并且触发事件的发生使条件成立时，动作才被并且触发事件的发生使条件成立时，动作才被执行。执行。n程序员可以选择动作执行的声明方

33、式：程序员可以选择动作执行的声明方式：n一次只对一个更新元组，或者一次只对一个更新元组，或者n一次针对在数据库操作中被改变的所有元组。一次针对在数据库操作中被改变的所有元组。第7章 约束和触发器41触发器的语法触发器的语法 CREATE TRIGGER trigger-name ON table-name FOR trigger-event AS trigger-name：一个表可定义多个不重一个表可定义多个不重名的触发器。名的触发器。trigger-event: DELETE | INSERT | UPDATEAS：是触发器要执行的操作。：是触发器要执行的操作。 第7章 约束和触发器42例子

34、例子n要求：每个销售员只能属于某一个部门；部门要求：每个销售员只能属于某一个部门；部门经理只能由本部门中某个销售员担任。经理只能由本部门中某个销售员担任。Salesman (empid, idno, name, gender, phone, deptid)Department (deptid, name, headerid)第7章 约束和触发器43例子例子n仅靠外键建立的参照完整性，不能解决的问题：仅靠外键建立的参照完整性，不能解决的问题：1 当当update department set headerid = ? where deptid = ? 时时 不能保证新不能保证新headerid在在Salesman中是本部门成员。中是本部门成员。 例如：例如：update department set headerid = A0044 where deptid = 2; A0044是部门是部门1的成员却担任了部门的成员却担任了部门2的经理。的经理。 应在其担任部门应在其担任部门2经理之前，先更新经理之前，先更新salesman使其作使其作为部门为部门2的成员。的成员。 update salesman set