of {$slidecount} ½ {$title} ATZJG.NET {$author}

首页






约束(constraints)与触发器
外键约束
局部和整体约束
触发器


Haifeng Xu


(hfxu@yzu.edu.cn)

This slide is based on Jeffrey D. Ullman's work, which can be download from his website.

References: 刘增杰、张少军 《MySQL 5.5 从零开始学》

目录

约束与触发器

约束与触发器

约束(constraint) 是指数据元素之间的一种关系, 是 DBMS 必须强制执行的.

触发器(triggers) 仅当特定条件发生时才被激活. 比如当插入一个元组时.

约束的种类

约束的种类

回顾: 单属性组成的键

回顾: 单属性组成的键

在属性类型声明后面加上 PRIMARY KEYUNIQUE. 例如:

CREATE TABLE Beers (
	name	CHAR(20) UNIQUE,
	manf	CHAR(20)
);

回顾: 多属性组成的键

回顾: 多属性组成的键

Sells 关系中 barbeer 一起组成键:

CREATE TABLE Sells (
	bar	CHAR(20),
	beer	VARCHAR(20),
	price	REAL,
	PRIMARY KEY (bar, beer)
);

外键(Foreign Keys)

外键(Foreign Keys)

某个关系中某个属性或属性组上的值, 也必在另一个关系的某个或某些属性上出现.

例如: 在 Sells(bar, beer, price) 关系中, 我们预计 beer 属性上的值也会在 Beers.name 上出现.

表述外键(Expressing Foreign Keys)

表述外键(Expressing Foreign Keys)

使用关键词 REFERENCES, 可用两种方式声明:

被引用的另一个关系中的属性必须(在它所在的关系中)被声明为 PRIMARY KEYUNIQUE

例子: 紧跟属性后面

例子: 紧跟属性后面

CREATE TABLE Beers (
	name	CHAR(20) PRIMARY KEY,
	manf	CHAR(20) 
);
CREATE TABLE Sells (
	bar	CHAR(20),
	beer	CHAR(20) REFERENCES Beers(name),
	price REAL,
	PRIMARY KEY(bar,beer)
);

实验

Beers, Sells 这两个表已经存在于数据库 test_bar 中, 我们现在需要更改它们的结构. 值得注意的是, 如果表中数据量很大, 更改表的结构会带来某种程度的风险. 此时应该首先备份表中的数据.

mysql> alter table Sells
    -> add foreign key(beer) references Beers(name)
    -> on delete cascade
    -> on update cascade;

要删除刚才建立的外键约束, 则使用下面的语句

mysql> alter table Sells drop foreign key Sells_ibfk_1;

这里的Sells_ibfk_1 是刚刚建立的外键的名称, 由MySQL自动给出. 也就是说, 在执行上面的语句之前, 需要先查询一下表的结构, 以确定外键的名称.

mysql> show create table Sells\G;
*************************** 1. row ***************************
       Table: Sells
Create Table: CREATE TABLE `Sells` (
  `bar` varchar(20) NOT NULL DEFAULT '',
  `beer` varchar(20) NOT NULL DEFAULT '',
  `price` double DEFAULT NULL,
  PRIMARY KEY (`bar`,`beer`),
  KEY `beer` (`beer`),
  CONSTRAINT `Sells_ibfk_1` FOREIGN KEY (`beer`) REFERENCES `Beers` (`name`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

要注意的是, 如果用 SET NULL 则会返回错误. 这是因为 Sells 表中的 beer 属性是键成员, 不允许取 NULL 值.

mysql> alter table Sells
    -> add foreign key (beer) references Beers (name)
    -> on delete set null
    -> on update cascade;
ERROR 1005 (HY000): Can't create table 'test_bar.#sql-35e_2' (errno: 150)
mysql> desc Beers;

例子: 作为模式的元素

例子: 作为模式的元素

CREATE TABLE Beers (
	name	CHAR(20) PRIMARY KEY,
	manf	CHAR(20) 
);
CREATE TABLE Sells (
	bar	CHAR(20),
	beer	CHAR(20),
	price	REAL,
	FOREIGN KEY (beer) REFERENCES
		Beers(name)
);

强制外键约束(Enforcing Foreign-Key Constraints)

强制外键约束(Enforcing Foreign-Key Constraints)

如果关系 RS 存在一个外键约束, ( S 是被引用的关系.) 可能存在下面的违法情况:

  1. 关系 R 的插入或更新的值在关系 S 中未找到.
  2. 删除或更新关系 S 中的元组将导致关系 R 中的某些元组成为“悬浮元组”.

对于第一种, 系统拒绝这种违法修改. 但是, 对于在被引用关系上的修改, 设计者可以有三种选择:

  1. 缺省原则(The Default Policy): 拒绝违法更新.
  2. 级联原则(The Cascade Policy)
  3. 置空值原则(The Set-Null Policy)

Actions Taken --- (1)

Actions Taken --- (1)

例子: 设 R=Sells, S=Beers. Sells 中存在到 Beers 的一个外键约束.

对于 R=Sells 的插入和更新操作, 如果涉及到的啤酒是 Beers 中不存在的, 则该操作被拒绝.

S=Beers 的删除或更新操作, 会导致 R=Sells 关系中某些相关元组在这些属性上的值产生改变, 具体有下面三种方式.

  1. 缺省原则(The Default Policy): 拒绝违法更新.
  2. 级联原则(The Cascade Policy): 在 Sells 中作相同的修改.
  3. 置空值原则(The Set-Null Policy): 当在被引用关系上更新时. 即当 S=Beers 的删除或更新操作, 会导致 R=Sells 中相应元组的属性被置空.

例子: Cascade

例子: Cascade

Beers 中删除 Bud 元组:

Bud 更改为 Budweiser.

例子: 置空(Set NULL)

例子: 置空(Set NULL)

Beers 中删除 Bud 元组:

Bud 更改为 Budweiser.

选择其中一个原则

选择其中一个原则

当我们声明一个外键时, 对于删除和更新操作可以独立地设置置空(SET NULL)还是级联(CASCADE)原则.

在外键的声明之后加上
ON [UPDATE, DELETE] [SET NULL| CASCADE]

如果不作出上面的选取, 则采用缺省原则(即拒绝更新).

例子: 设置更新原则

例子: 设置更新原则

CREATE TABLE Sells (
	bar	CHAR(20),
	beer CHAR(20),
	price REAL,
	FOREIGN KEY(beer)
		REFERENCES Beers(name)
		ON DELETE SET NULL
		ON UPDATE CASCADE
);

试一下下面的语句是否能够创建外键

CREATE TABLE Sells (
	bar	CHAR(20),
	beer CHAR(20) REFERENCES Beers(name)
		ON DELETE SET NULL
		ON UPDATE CASCADE,
	price REAL		
);

事实上, 上面的语句能运行, 但是并没有创建外键.

基于属性的检查

基于属性的检查

对特定属性的值作约束, 可以在对该属性声明数据类型时加上

CHECK(<condition>)

条件 condition 中可以使用该属性的名字, 但是
任何其他关系或属性都必须位于子查询中.

例子: 基于属性的检查

例子: 基于属性的检查

CREATE TABLE Sells (
    bar     CHAR(20),
    beer    CHAR(20)    CHECK ( beer IN
        (SELECT name FROM Beers)),
    price   REAL    CHECK ( price <= 5.00 )
);

实验(under MySQL)

在 MySQL5.5 版本中, 上面的语句可以成功创建表 Sells, 但基于属性的检查不起作用.

检查的时机

检查的时机

基于属性的检查仅在属性的值被插入或更新时才进行.

基于元组的检查

基于元组的检查

CHECK(<condition>) 可以作为关系模式的元素加入到表的创建声明中.

这里的 condition 可以引用关系中的任意属性,

检查仅在元组被插入或更新时进行.

例子: 基于元组的检查

例子: 基于元组的检查

只有 Joe's Bar 可以销售价格超过 $\$ 5$ 的啤酒.

CREATE TABLE Sells (
    bar    CHAR(20),
    beer   CHAR(20),
    price  REAL,
    CHECK (bar = ’Joe’’s Bar’ OR price <= 5.00)
);

实验(under MySQL)

在 MySQL5.5 版本中, 上面的语句可以成功创建表 Sells, 但基于元组的检查也不起作用.

断言(Assertions)

断言(Assertions)

断言是数据库模式(schema)的一部分, 等同于表(relation)或视图(view).

断言的定义是:
CREATE ASSERTION <name> CHECK (<condition>);

<condition> 可以涉及数据库模式中的任何关系或属性.

MySQL 目前不支持断言. 但是可以使用触发器来实现断言所述的功能.

例子: 断言(Assertion)

例子: 断言(Assertion)

Sells(bar, beer, price) 关系中, 要求没有一个酒吧的啤酒平均售价是超过 $\$ 5$ 的.

CREATE ASSERTION NoRipoffBars CHECK(
    NOT EXISTS (
        SELECT bar FROM Sells
        GROUP BY bar
        HAVING 5.00 < AVG(price)
));

其中子查询所得的结果关系是那些啤酒平均售价超过 $\$ 5$ 的酒吧.

实验(under MySQL)

mysql> CREATE ASSERTION NoRipoffBars CHECK(
    ->     NOT EXISTS (
    ->         SELECT bar FROM Sells
    ->         GROUP BY bar
    ->         HAVING 5.00 < AVG(price)
    -> ));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ASSERTION NoRipoffBars CHECK(
    NOT EXISTS (
        SELECT bar FROM Sells
   ' at line 1
mysql>

例子: 断言(Assertions)

例子: 断言(Assertions)

对于关系 Drinkers(name, addr, phone)Bars(name, addr, license), 不会出现酒吧的数量大于酒客数量的情况.

CREATE ASSERTION FewBar CHECK (
    (SELECT COUNT(*) FROM Bars) <=
    (SELECT COUNT(*) FROM Drinkers)
);

断言执行检查的时机

断言执行检查的时机

原则上, 对于数据库中任何关系的更改, 每个断言都必须执行.

一个聪明的系统可以观察到仅某些更改会导致断言被违反.

触发器(Triggers): 动机

触发器(Triggers): 动机

断言的确很强大, 但 DBMS 经常不知道何时需要检查断言.

基于属性和基于元组的检查, 知道何时检查, 但它们不够强大.

触发器允许用户在给定时机检查任何给定的条件.

MySQL 中的触发器

MySQL 的触发器和存储过程一样, 都是嵌入到 MySQL 的一段程序.

触发器是由事件来触发某个操作, 这些事件包括 INSERT, UPDATEDELETE 语句. 如果定义了触发程序, 当数据库执行这些语句的时候就会激发触发器执行相应的操作, 触发程序是与表有关的命名数据库对象, 当表上出现特定事件时, 将激活该对象.

事件-条件-动作规则(Event-Condition-Action Rules)

事件-条件-动作规则(Event-Condition-Action Rules)

Event: 指典型的数据库更改类型, 如: 对于关系 Sells 的插入、删除或修改.

Condition: 任何返回布尔值的SQL 表达式.

Action: 任何 SQL 语句.

初级的例子: A Trigger

初级的例子: A Trigger

外键约束将使得一个在 Beers 关系中不存在的啤酒不能插入到 Sells(bar,beer, price) 关系中. 与外键不同的是, 触发器可以将这个啤酒加入到 Beers 中, 相应的属性 manf 置为 NULL.

例子

CREATE TRIGGER BeerTrig
    BEFORE INSERT ON Sells
    REFERENCING NEW ROW AS NewTuple
    FOR EACH ROW
    WHEN (NewTuple.beer NOT IN
        (SELECT name FROM Beers))
    INSERT INTO Beers(name)
        VALUES(NewTuple.beer);

MySQL 中上面的代码会出错. MySQL 中是用 NEWOLD

例子: 触发器的定义

例子: 触发器的定义

MySQL 中建立触发器的语法

CREATE TRIGGER trigger_name 
trigger_time trigger_event ON tbl_name 
FOR EACH ROW 
trigger_stmt

触发器程序可以使用 BEGINEND 作为开始和结束, 中间包含多条语句.

实验(under MySQL)

mysql> delimiter //
mysql> CREATE TRIGGER ins_check BEFORE INSERT ON Sells
    -> FOR EACH ROW
    -> BEGIN
    ->     IF NEW.price < 0 THEN
    ->         SET NEW.price = 0;
    ->     ELSEIF NEW.price > 100 THEN
    ->         SET NEW.price = 100;
    ->     END IF;
    -> END//
mysql> delimiter ;

继续实验, 看看会有什么问题

mysql> delimiter //
mysql> CREATE TRIGGER PriceTrig
    ->   AFTER UPDATE ON Sells
    ->   FOR EACH ROW
    -> BEGIN
    ->   IF NEW.price > OLD.price +1.00 THEN
    ->   SET NEW.price = OLD.price +1.00;
    ->   END IF;
    -> END//
mysql> delimiter ;
ERROR 1362 (HY000): Updating of NEW row is not allowed in after trigger
mysql> delimiter //
mysql> CREATE TRIGGER PriceTrig
    ->   BEFORE UPDATE ON Sells
    ->   FOR EACH ROW
    -> BEGIN
    ->   IF NEW.price > OLD.price +1.00 THEN
    ->   SET NEW.price = OLD.price +1.00;
    ->   END IF;
    -> END//
mysql> delimiter ;
ERROR 1235 (42000): This version of MySQL doesn't yet support
 'multiple triggers with the same action time and event for one table'

请先删除刚才建立的触发器, 然后完成触发器的创建.

删除触发器的语句是:

mysql> drop trigger ins_check;

实验

例子: 单执行语句的触发器

首先建立一张表

mysql> CREATE TABLE account(
    -> acct_num INT,
    -> amount DECIMAL(10,2)
    -> );
Query OK, 0 rows affected (0.04 sec)

然后针对这张表的插入事件建立名为 ins_sum 的触发器, 触发时机是在 INSERT 之前.

mysql> CREATE TRIGGER ins_sum BEFORE INSERT
    -> ON account
    -> FOR EACH ROW
    -> SET @sum=@sum+NEW.amount;
Query OK, 0 rows affected (0.03 sec)

最后, 先设置用户变量, 然后执行插入.

mysql> SET @sum=0;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO account
    -> VALUES (1,1.00), (2,2.00);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

检查用户变量

mysql> SELECT @sum;
+------+
| @sum |
+------+
| 3.00 |
+------+
1 row in set (0.00 sec)

选项: CREATE TRIGGER

选项: CREATE TRIGGER

CREATE TRIGGER <name>

或者

CREATE OR REPLACE TRIGGER <name>

选项: The Event

选项: The Event

AFTER 可以改为 BEFORE

INSERT 可以换为 DELETEUPDATE.

选项: FOR EACH ROW

选项: FOR EACH ROW

触发器或者是 "row-level" 或者是 "statement-level".

FOR EACH ROW 指明是 row-level 的; 如果不指明则认为是 statement-level 的.

行级触发器(Row-level triggers): 对于每个被更新的元组执行一次.

语句级触发器(Statement-level triggers): 对每个 SQL 语句执行一次, 而不管有多少元组被更改.

选项: REFERENCING

选项: REFERENCING

INSERT 语句会产生一个新的元组(对于 row-level), 或者新的表(table)(对于 statement-level).

DELETE implies an old tuple or table.

UPDATE implies both.

Refer to these by
[NEW OLD] [TUPLE TABLE] AS <name>

选项: The Condition

选项: The Condition

可以是任意返回布尔值的条件.

条件(condition)在触发器事件之前或之后(取决于使用的是 BEFORE 还是 AFTER) 被赋值.

REFERENCING 子句允许触发器的条件通过名字引用 new/old 元组/表.

选项: The Action

选项: The Action

动作中可以有不止一个 SQL 语句.

但是查询在动作中没有太大的实际意义(尽管是可以的, 可能有的时候也是需要的, 比如自动化显示等等), 因此我们这里只限定为修改.

另一个触发器的例子

另一个触发器的例子

将关系 Sells(bar, beer, price) 中涨价超过1美元的酒吧记录到一个单属性关系 RipoffBars(bar) 中.

The Trigger

The Trigger

CREATE TRIGGER PriceTrig
   AFTER UPDATE OF price ON Sells
   REFERENCING
       OLD ROW AS ooo
       NEW ROW AS nnn
   FOR EACH ROW
   WHEN (nnn.price > ooo.price + 1.00)
   INSERT INTO RipoffBars
        VALUES (nnn.bar);

实验(under MySQL)

mysql> delimiter //
mysql> CREATE TRIGGER PriceTrig
    ->   AFTER UPDATE ON Sells
    ->   FOR EACH ROW
    -> BEGIN
    ->   IF NEW.price > OLD.price +1.00 THEN
    ->   INSERT INTO RipoffBars VALUES (NEW.bar);
    ->   END IF;
    -> END//
mysql> delimiter ;

如果指明对属性 price 进行修改, 试一下下面的语句

mysql> delimiter //
mysql> CREATE TRIGGER PriceTrig
    ->   AFTER UPDATE of Price ON Sells
    ->   FOR EACH ROW
    -> BEGIN
    ->   IF NEW.price > OLD.price +1.00 THEN
    ->   INSERT INTO RipoffBars VALUES (NEW.bar);
    ->   END IF;
    -> END//
mysql> delimiter ;

会导致语法错误.

另一个要注意的是, 尽管 RipoffBars 这个表没有建立, 但是触发器也是可以建立的.

查看建立的触发器

mysql> SHOW TRIGGERS\G;

例子

触发器例子

例子来源

创建表

create table t(s1 integer);

创建触发器

delimiter |
CREATE TRIGGER t_trigger 
BEFORE INSERT ON t  
FOR EACH ROW
BEGIN
    SET @x = "hello trigger";
    SET NEW.s1 = 55;
END;
|
delimiter ;  

最后一行 delimiter ; 是恢复分号作为语句结束符, 注意有 delimiter 和 ; 之间有空格.

查看触发器

show triggers\G;

删除触发器

drop trigger t_trigger;

例子

触发器例子

参考自 http://oak.cs.ucla.edu/cs143/project/mysql/assertion.html

DELIMITER //             -- change the end of statement delimiter from ; to //
    CREATE TRIGGER CS143Mandatory
    AFTER INSERT ON Student
    FOR EACH ROW             -- note that "REFERECING NEW ROW AS NEW" is missing
    BEGIN
       IF NEW.GPA > 2.0 THEN -- this is equivalent to WHEN (NEW.GPA > 2.0)
          INSERT INTO Enroll VALUES (NEW.sid, 'CS', 143, 1);
       END IF;
    END //                   -- mark the end of trigger with //
    DELIMITER ;              -- change the delimiter back to ;

我们可以推出表 Enroll 具有四个属性: sid(学号), 学院, 课程号, 最后一个可能是自增(auto_increment)属性.

MySQL 中触发器的例子

触发器例子

创建一个单执行语句的触发器. 由于是单条语句, 故不必更换分隔符.

CREATE TABLE account(
	acct_num INT,
	amount DECIMAL(10,2)
);

CREATE TRIGGER ins_sum 
BEFORE INSERT ON account
FOR EACH ROW
SET @sum=@sum+NEW.amount;

实验

mysql> CREATE TABLE account(
    -> 	acct_num INT,
    -> 	amount DECIMAL(10,2)
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> CREATE TRIGGER ins_sum 
    -> BEFORE INSERT ON account
    -> FOR EACH ROW
    -> SET @sum=@sum+NEW.amount;
Query OK, 0 rows affected (0.03 sec)

mysql> SET @sum=0;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO account VALUES(1,1.00),(2,2.00),(3,3.00),(4,4.00),(5,5.00);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT @sum;
+-------+
| @sum  |
+-------+
| 15.00 |
+-------+
1 row in set (0.00 sec)

注意, 当 MySQL 重新启动后, 变量 @sum 就消失了.

mysql> select @sum;
+------+
| @sum |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

MySQL 中触发器的例子

创建有多个执行语句的触发器

此时将执行语句放在 BEGINEND 之间, 并且需要更换分隔符.

CREATE TABLE test1(
	a1	INT;
);

CREATE TABLE test2(
	a2	INT;
);

CREATE TABLE test3(
	a3	INT;
);

CREATE TABLE test4(
	a4	INT;
	b4	INT;
);
DELIMITER //

CREATE TRIGGER testref 
BEFORE INSERT ON test1
FOR EACH ROW
BEGIN
	INSERT INTO test2 VALUES(NEW.a1);
	DELETE FROM test3 WHERE a3=NEW.a1;
	UPDATE test4 SET b4=b4+1 WHERE a4=NEW.a1;
END
//

DELIMITER ;

MySQL 同一张表中关于同样事件和作用时机的多个触发器

MySQL 同一张表中关于同样事件和作用时机的多个触发器

Before MySQL 5.7.2, there cannot be multiple triggers for a given table that have the same trigger event and action time. For example, you cannot have two BEFORE UPDATE triggers for a table.

关于 FETCH ... INTO ... 的详细用法, 请参考 https://dev.mysql.com/doc/refman/5.7/en/fetch.html

MySQL 下触发器的编写

MySQL 下触发器的编写

我们以书上第7章 P.197 图7-5的例子, 将之改写为 MySQL 下可以运行的触发器.

CREATE TRIGGER NetWorthTrigger
  AFTER UPDATE ON MovieExec
  FOR EACH ROW
   
BEGIN
  IF OLD.netWorth > NEW.netWorth THEN
    UPDATE MovieExec  
      SET netWorth = OLD.netWorth     
      WHERE certNo = NEW.certNo;
  END IF;
END;//

书第7章 P.197 图7-5的例子

CREATE TRIGGER NetWorthTrigger
AFTER UPDATE OF netWorth ON MovieExec
REFERENCING
    OLD ROW AS OldTuple,
	NEW ROW AS NewTuple
FOR EACH ROW
WHEN (OldTuple.netWorth > NewTuple.netWorth)
    UPDATE MovieExec
      SET netWorth = OldTuple.netWorth  
      WHERE cert# = NewTuple.cert#;

 

End






Thanks very much!

This slide is based on Jeffrey D. Ullman's work, which can be download from his website.