数据库系统-学习记录7

ch7.约束与触发器

键和外键

  外键约束：出现在一个关系中的值，必然出现在另一个关系中的主键属性中

外键约束声明

  在属性声明的时候，往后添加references <table>(<attribute>)，例如：

presC int references MovieExec(cert)

  表示属性presC参照关系MovieExec中的cert属性，presC的值必须来自于cert的值

  被引用的属性必须是primary key或unique的

  如果外键包含多个属性，则可以在属性声明完成后，单独使用foreign key (<attributes>) references <table>(<attributes>)进行声明：

...
presC int
foreign key (presC) references MovieExec(cert)

维护引用完整性

  在外键的影响下，部分操作会受到限制。例如，当关系Studio中的属性presC，参照关系MovieExec中的属性cert时，会无法进行下述操作：
  1、试图向Studio插入一个新的元组，这个元组中的presC值没有出现在MovieExec的cert中
  2、试图更新Studio的一个元组，presC值被更新为一个没有出现在MovieExec的cert中的值
  3、删除MovieExec中的一个元组，而这个元组中的cert值在Studio中的presC属性中出现过
  4、更新MovieExec中的一个元组，而这个元组中的cert值在Studio中的presC属性中出现过

  对于前两种操作，直接拒绝修改。而对于后两种操作，可以进行如下选择：
  1、默认原则：拒绝修改
  2、级联原则：删除被参照的关系中的元组时，也会同时删除相应的参照这个关系的关系的元组；同理，更新被参照的关系中的元组时，也会更新相应的元组
  3、置空值原则：当被参照关系的元组的更新，影响到了一个外键的值时，后者将被设置为NULL

  而这几种选择可以设置为只对删除或更新生效，例如：

on delete set null # 置空值原则，只对删除生效
on update cascade # 级联原则，只对更新生效

   选项是紧跟在参照完整性之后的，例如：

create table Movies(
    title char(20),
    year_ integer,
    length integer,
    genre char(20),
    studioName char(20),
    producerC integer references MovieExec(name)
    on delete set null
    on update set null,
    primary key (title, year_)
);

  声明了Movies中的producerC属性，是参照关系MovieExec中的name属性的，并且在非法进行更新或删除操作时，对相应受影响的属性置null值

推迟约束检查

  两个关系相互引用时，直接插入会无法成功，因此需要推迟约束的检查

  关于推迟约束检查，默认模式为not deferable，即每完成一项改变时，都立即检查一遍约束

  deferrable模式下，可以使用：
  1、initially deferred：检查仅被推迟到事务提交前执行
  2、initially immediate：在每个语句后，检查都立即被执行

属性和元组的约束

非空值约束

  非空值约束不允许属性被赋予NULL值

  声明方式：not NULL，例如：

presC int references MovieExec(cert) not NULL

基于属性的CHECK约束

  将保留字check和条件加在属性声明上，使得当修改的新值不满足约束时，对这条修改进行拒绝，例如：

presC int references MovieExec(cert)
check (presC >= 100000)

  当修改的presC值小于100000时，会拒绝修改

  约束还可以强化为只有有限个期望值的情况，例如：

gender char(1) check (gender in ('F', 'M'))

  表示修改的gender值只能为’F’或’M’

基于元组的CHECK约束

  类似于基于属性的check约束，基于元组的check约束可以同时对多个属性进行约束，例如：

CREATE TABLE MovieStar (
    name CHAR(30) PRIMARY KEY,
    address VARCHAR(265),
    gender CHAE(1),
    birthdate DATE,
	CHECK (gender = 'F' OR name NOT LIKE 'Ms.%')
)；

修改约束

给约束命名

  如果要修改或删除一个已存在的约束，则约束必须得有名字。给约束命名的方法为：在约束前加保留字constraint和该约束的名字，例如：

name char(30) constraint NameIsKey primary key;

  表示对主键约束进行命名，命名为：NameIsKey

修改表上的约束

  使用alter table来影响约束：

alter table MovieStar drop constraint NameIsKey; # 删除约束NameIsKey
alter table MovieStar add constraint NameIsKey primary key (name); # 添加约束NameIsKey

断言

创建断言

  断言的形式：

create assertion <断言名> check (<条件>)

  建立断言时，断言的条件必须为真，如果对数据库的更新会导致断言条件变为假，则拒绝更新

使用断言

  因为断言的条件必须是逻辑值，所以必须使用某种方式聚集条件的结果来获得逻辑值，例如：

create assertion RichPres check
    (not exists
        (select Studio.name
         from Studio, MovieExec
         where presC = cert and netWorth < 10000000
        )
    );

  就对表的元组进行了相应的限制，如果更新的表不满足这个限制，则拒绝更新

  删除断言的语句格式为：

drop assertion <assertion name>

触发器

  当声明的事件发生时，触发器被激活，如果触发器测试触发的条件成立，则由DBMS执行与触发器相连的动作

SQL中的触发器

  行级触发器：每条记录触发
  语句级触发器：执行完后触发

  触发器举例：

CREATE TRIGGER NetWorthTrigger
AFTER UPDATE OF netWorth ON MovieExec
REFERENCING # REFERENCING子句允许触发器的条件和动作引用正被修改的元组
	OLD ROW AS OldTuple,
	NEW ROW AS NewTuple
FOR EACH ROW # FOR EACH ROW短语表达了该触发器是每修改一个元组执行一次的方式
WHEN (OldTuple.netWorth > NewTuple.netWorth) # 触发的条件
	UPDATE MovieExec
	SET netWorth = OldTuple.netWorth
	WHERE cert = NewTuple.cert;

  此例表示：修改netWorth属性时，触发器被激活。此触发器用于阻止降低电影制作人净资产值

  old有时也叫deleted，new有时也叫inserted

  触发器越多，检查越多，数据库效率越低

触发器设计选项

  触发器提供下述选项：
  after/before：条件测试在触发时间之后/之前执行
  update/insert/delete
  when：仅当when后的条件为真时执行动作
  begin…end：动作为多个SQL语句时，使用begin…end括起，并在语句间使用分号分隔
  for each row/for each statement：语句级触发器：一旦有合适类型的语句被执行，语句级触发器就被执行
  old table as/new table as：引用旧元组和新元组

ch8.视图和索引

虚拟视图

  只有定义，没有数据——虚表

  视图通过类似查询的表达方式定义，可以将视图当做物理存在进行查询，某些时候也能更新视图

视图定义

create view <视图名> as <视图定义>; # 最简单的视图定义

  例如：

CREATE VIEW ParamountMovies AS
SELECT title, year_
FROM Movies
WHERE studioName = 'Paramount';

  视图的定义是SQL查询

视图查询

  视图可以像一个真正的表一样来查询

属性重命名

  可以在创建视图的时候，将自定义的属性名放入create view后视图名后面的括号中，例如：

CREATE VIEW MovieProd(movieTitle, prodName) AS
SELECT title, name
FROM Movies, MovieExec
WHERE producerC = cert;

视图更新

  视图作用：屏蔽数据，提高数据安全性

视图删除

drop view <视图名称>; # 删除了视图的定义，但不会影响基本关系中的任何元组，但反之不然，如果删除了基本关系，则相对应的视图也将变得不可用

可更新视图

  SQL提供了允许视图修改操作的形式定义

视图中的替换触发器

  视图上定义触发器时，可以用instead of代替before/after

索引

  索引：用于加快查找速度

  最重要的索引数据结构为B树

建立索引

  添加索引后，查询效率会大大提高

  索引过多时，由于每更新一次数据都要更新索引，所以并不是索引越多越好

索引声明

  例如：

create index YearIndex on Movies(year_);

  表示了在year_属性上建立索引。这样一来，在指定年份进行查询的时候，就只会对年份为指定值的Movies的元组进行测试，从而缩短查询时间