第7章 键和约束

数据库原理复习专栏

7.1 约束的类型$\to constraint$

• 外键约束($foreign\ key$), 有两种声明方法：
  
  • references <tablename> (<attr>)
  • foreign key (<attr>) references <tabname> (<attr>)
• 引用完整性，简单来说便是“不允许引用不存在的对象”。有几种办法来维护：
  
  • 缺省原则，缺省情况下，拒绝任何破坏引用完整性的操作行为
  • 级联(cascade)原则，修改被引用属性时，同时修改外键。
  • 置空值(set null)原则，在被引用属性被修改或者删除时，将外键值设置为空(null)
  • 后两者常组合使用，如需要实现“被引用者被删除时对应键置空(set null)，被引用者更新时对应键同时修改(级联，cascade)”可以设置：

 create table studio (
    name varchar(30) primary key,
    address varchar(255),
    producer_id int references movie_exec(id) --制片人编号
        on delete set null
        on update cascade
);

又如需要“更新和删除被引用者时外键都对应地更新和删除”，可以写：

 create table studio (
    name varchar(30) primary key,
    address varchar(255),
    producer_id int references movie_exec(id) --制片人编号
        on delete cascade --注意此处换成了cascade，删除时也“级联”地删除
        on update cascade
);

• 延迟约束检查: 延迟检查，但需要将操作作为原子操作来执行(按下)。

习题 7.1.1

• $a)$

alter table movies
    add foreign key (producerC#) references MovieExec(name)

• $b)$ 更新和删除时均设置为空值

alter table Movies
    add foreign key (producerC#) references MovieExec(name)
    on update set null
    on delete set null

• $c)$

alter table Movies
    add foreign key (producerC#) references MovieExec(name)
    on update cascade
    on delete cascade

• $d)$

alter table StarsIn
    add foreign key (moveieTitle) references Movies(title)

• $e)$ 参考答案为：

alter table StartsIn
    add foreign key (starName) references MovieStar(name)
    on delete cascade

update时应该稍加考虑？

7.2 属性和元组上的约束$\to check$

• 非空值约束: 用NOT NULL来声明
• 基于属性的$CHECK$约束: 在声明属性后添加，如：

create table student (
    id int not null primary key,
    gender char(1) check (gender in ('F', 'M'))
);

当然，上述等价于：

create table student (
    id int not null primary key,
    gender enum('F', 'M')
);

• 基于元组的$CHECK$约束：将上述CHECK扩展，即可得到基于元组的约束，如：

create table movie_star (
    name varchar(30) primary key,
    address varchar(255),
    gender char(1),
    birthdate date,
    check (gender = 'F' or name not like 'Ms.%')
);

check中可以添加的语句和where时一样的，同样可以使用诸如or，like，not等等来表达想要得到的约束条件。最后，事实上把check写在单个属性的后面来添加对该属性的check约束只是一个习惯而已，单个属性后同样可以做多属性的check约束--唯一的区别就是此时无法使用尚未声明的属性。最后，几乎所有数据引擎(data engine，如InnoDB)都会解析check但并不会产生任何动作，合理的替代方案是使用Before触发器。

习题 7.2.3

• $a)$

create table StarsIn (
    -- ...
    check ((select year(birthdate) from MovieStar where name = starName) <= (select year from Movies where title = movieTitle))
);

• $b)$

create table Studio (
    -- ...
    unique key (address)
    -- 参考答案：CHECK (address IS UNIQUE)...
);

• $c)$

create table MovieStar (
    -- ...
    check (name not in (select name from MovieExec))
);

• $d)$

create table Studio (
    -- ...
    check (name in (select studioName from Movies))
);

• $e)$

create table Movies (
    -- ...
    check (
        producer not in (select presC# from studio)
        or studioName in (select name from Studio where pres = producer)
    )
);

7.3 修改约束 $\to alter$

• 给约束命名：在约束前加CONSTRAINT <约束名>即可
• 修改约束：
  
  • 删除alter table <表名> drop constraint <约束名>。注：mysql56需要将constraint换成primary key或者foreign key。如alter table test drop foreign key fk_test_student;。
  • 添加alter table <表名> add constraint <约束名> <约束类型> <约束内容>。如alter table test add constraint fk_test_student foreign key (id) references student(id)。

　习题 7.3.1

• $a)$ alter table Movie add constraint pkMovie primary key(title, year);
• $b)$ alter table Movie add constraint fkMovieMovieExec foreign key (producer) references MovieExec(cert);
• $c)$ alter table Movie add constraint ckMovieLength check (length>=60 and length <= 250);
• $d)$ alter table StarsIn add constraint ckStarName check (starName not in (select name from MovieExec));
• $e)$ alter table Studio add constraint ckAddress check (address is unique);

7.4 断言$\to assertion$

• 所谓断言，近义词断定，就是“窝确定此处必须为真，否则GG！”。考虑在C艹中的如下代码：

#include <iostream>
#include <cassert>
int main() {
    int x;
    while (std::cin >> x) {
        assert (x != 0);
        std::cout << 1.0 / x << std::endl;
    }
    return 0;
}

程序功能是不断读入一个整数，并输出其倒数。注意上述assert，我们断定括号中的必须为真，即$x$必须不能为$0$，否则直接中断，$unix$下该程序进程会被发送一个$sig\ quit$($kill\ id$为$3$)，即核心转储并终止进程($core\ dump\ and\ terminate$)，上述$assert$在程序调试时非常地有用。回到数据库中，这个断言也是差不多的，唯一的区别就是在数据库中断言失败后中断的不是程序进程，而是诸如过程($procedure$)、函数($function$)以及数据库操作(增删改)。

• 添加断言，create assertion <断言名> <断言内容>;

CREATE ASSERTION RichPres
CHECK(NOT EXISTS
    (SELECT Studio.name FROM Studio, MovieExec
    WHERE pres = cert AND netWorth < 10000000
    )
);

• 删除断言：drop assertion <断言名>;

• CHECK和ASSERTION都是用来做一些检查的，那么它们有啥区别呢？

说白了，前两者是放在某个具体的表中的，因此仅在表数据修改时(更新UPDATE,添加INSERT,删除DELETE)检查，如果做的是查询(SELECT)则不影响表中数据，自然不会去检查；断言是放在某个数据库中且不存放于具体的某个表中的，因此balabala......

7.5 触发器$\to trigger$

• 触发器在《触发器、视图、索引和存储过程》一文中已经提及。简单地说，触发器就是类似$Java$中的$Action\ Listener$、$QT\ C艹$中的$slot$的东西，后两者在发生诸如“鼠标单击”、“键盘某个按键被按下”等事件时，会调用执行相应的函数，具体地说，在$QT$中有connect(buttonBox, SIGNAL(accepted()), this, SLOT(foo()));(其中$this$指代自身，是一个对话框)，那么在按钮$buttonBox$的“确认”按钮被点击后，会发送($emmit$)一个$accepted$信号，而上述语句又会将该信号和对话框的$foo$函数连接，于是又会调用$foo$函数。回到数据库中，其触发器的触发和上述类似。“某个表被插入了一条新纪录”、“某个表有一条记录被更新”、“某个表中的某条记录被删除”等均为“事件”，如果声明了对应的触发器（可理解为响应函数），数据库系统($DBMS$)就会调用该触发器，执行里面的语句。
• 触发器举例：

CREATE TRIGGER NetWorthTrigger -- 触发器名称为NetWorthTrigger
AFTER UPDATE OF netWorth -- AFTER指定触发器在事件发生后触发，UPDATE指定事件为“发生记录的更新”，OF指定列，合起来就是“在列netWorth被更新后触发这个触发器”
ON MovieExec -- 在哪个表上添加这个触发器
REFERENCING -- 下面做一些别名(alias)设置
    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;

注：$mysql$不能完整地支持上述$referncing$和$of\ \left<column name\right>$的特性，其新记录直接使用名称$new$，旧记录直接为$old$，在需要指定属性(即指定列)时可以在触发器的$body$部分使用如if (new.name <> old.name) then ...来替代解决。

• 一些解释

  • AFTER表示事件发生后触发，BEFORE表示事件发生时先执行触发器中语句再执行触发事件的(增、删、改)语句，INSTEAD OF表示事件触发时先执行触发器中语句然后忽略触发对应事件的(增、删、改)语句。分别称为AFTER触发器、BEFORE触发器和INSTEAD OF触发器。INSTEAD OF触发器与视图有关，MySQL不支持INSTEAD OF触发器。
  • 更新时，有new row和old row；删除时有old row，而new row不可用；添加时仅new row可用。这是很自然的。
  • FOR EACH ROW指定对每一行受影响的元组都执行后面的动作，称为“行级触发器”。如果不写或者写FOR EACH STATEMENT则为“语句级触发器”，无论受影响的元组有多少都仅执行一次。务必注意，默认为后者，因此即使你认为你要做的更新只针对某一行，也务必写上“FOR EACH ROW”，毕竟在DBMS看来，人家咋知道会不会有一个更新语句会更新到多个元组...

• 触发器实例2

create trigger fix_year
before insert on movies
referencing
    new row as newrow
    old row as oldrow
for each row
when newrow.year is null
update newstuff set year = 1995;

• 触发器实例3

create trigger avgtrigger
after update of netWorth on MovieExec
referencing
    old table as oldstuff
    new table as newstuff
for each statement
when (60000 > (select avg(netWorth) from MovieExec))
begin
    delete from MovieExec
    where (name, address, cert, netWorth) in newstuff;
    insert into MovieExec (SELECT * FROM oldstuff);
end;

功能是如果更新记录后新的平均值过低(小于60000)，则恢复之前的数据(删掉新加的数据，并添加进原有数据)。

习题 7.5.1

create trigger avg_delete_trigger
after delete on MovieExec
referencing
    old table as oldstuff
for each statement
when (60000 > (select avg(netWorth) from MovieExec))
begin
    insert into MovieExec (select * from oldstuff);
end; -- 删除后平均值过低则恢复被删除数据
create trigger avg_insert_trigger
after insert on MovieExec
referencing
    new table as newstuff
for each statement
when (60000 > (select avg(netWorth) from MovieExec))
begin
    delete from MovieExec where (name, address, cert, netWorth) in newstuff;
end; -- 添加后平均值过低则删除添加进的数据