首页

约束(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 必须强制执行的.

例如: 键约束(key constraints)

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

触发器的优点是: 比复杂的约束容易补充.

约束的种类

键(Keys).
外键(Foreign-key), 或者“引用完整性”(referential-integrity).
基于值的约束(Valued-based constraints: 对于属性取值的约束.
基于元组的约束(Tuple-based constraints: 属性之间的约束.
断言(assertions): 关系之间的约束, 可以是任意 SQL 布尔表达式.

回顾: 单属性组成的键

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

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

回顾: 多属性组成的键

Sells 关系中 bar 和 beer 一起组成键:

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

外键(Foreign Keys)

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

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

表述外键(Expressing Foreign Keys)

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

对于单属性键, 在属性后面加上
REFERENCES <relation>(<attribute>)
作为模式(schema)的一个元素:
FOREIGN KEY (<list of attributes>) REFERENCES <relation>(<attributes>)

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

例子: 紧跟属性后面

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)

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

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

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

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

Actions Taken --- (1)

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

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

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

缺省原则(The Default Policy): 拒绝违法更新.
级联原则(The Cascade Policy): 在 Sells 中作相同的修改.

若删除 beer: 则相应地删除 Sells 中的元组.
若更新 beer: 则相应地改变 Sells 中的值.

置空值原则(The Set-Null Policy): 当在被引用关系上更新时. 即当 S=Beers 的删除或更新操作, 会导致 R=Sells 中相应元组的属性被置空.

例子: Cascade

从 Beers 中删除 Bud 元组:

这会导致 Sells 关系中所有 beer='Bud' 的元组都被删除.

将 Bud 更改为 Budweiser.

这使得 Sells 关系中所有 beer='Bud' 的啤酒名称都被更改为 Budweiser.

例子: 置空(Set NULL)

从 Beers 中删除 Bud 元组:

这将导致 Sells 关系中所有 beer='Bud' 的元组在属性 beer 上的值都更改为NULL.

将 Bud 更改为 Budweiser.

同上面一样, 这使得 Sells 关系中所有 beer='Bud' 的啤酒名称都被更改为 NULL.

选择其中一个原则

当我们声明一个外键时, 对于删除和更新操作可以独立地设置置空(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, 但基于属性的检查不起作用.

在 MySQL8.4.2 版本中, 可以起作用.

将下面的代码输入 mysql 交互式终端. (假设使用的是 MySQL 8.4.2)

CREATE TABLE voter(
  voter_id INTEGER PRIMARY KEY,
  name TEXT,
  age INTEGER,
  date_of_birth DATE,
  check(age >= 18)
 );

然后输入 SHOW CREATE TABLE voter\G;

       Table: voter
Create Table: CREATE TABLE `voter` (
  `voter_id` int NOT NULL,
  `name` text,
  `age` int DEFAULT NULL,
  `date_of_birth` date DEFAULT NULL,
  PRIMARY KEY (`voter_id`),
  CONSTRAINT `voter_chk_1` CHECK ((`age` >= 18))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

当我们尝试插入年龄小于 18 的数据时便会发生错误.

INSERT INTO voter(voter_id, name, age, date_of_birth) VALUE (123, "Mike", 16, '2008-02-01');
ERROR: 3819 (HY000): Check constraint 'voter_chk_1' is violated.

检查的时机

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

例: CHECK (price <= 5.00) 对于每个新插入的价格检查是否大于 $\$ 5$ .
例: CHECK (beer IN (SELECT name FROM Beers)) 当从关系 Beers 中删除某个啤酒时, 这个检查并不执行. (这与外键是不一样的.)

基于元组的检查

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, 但基于元组的检查也不起作用.

登录 MySQL(5.7.23), 发现 test_bar 数据库中的 sells 表暂时没有数据. 添加下面的约束.

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.23    |
+-----------+
1 row in set (0.04 sec)

mysql> ALTER TABLE sells
    -> ADD CHECK (bar='Joe'' Bar' OR price<=5.00);
Query OK, 0 rows affected (0.06 sec)
Enregistrements: 0  Doublons: 0  Avertissements: 0

成功. 但是查看表的信息

CREATE TABLE `sells` (
  `bar` varchar(20) NOT NULL,
  `beer` varchar(20) NOT NULL,
  `price` double DEFAULT NULL,
  PRIMARY KEY (`bar`,`beer`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

表并没有发生改变. 依然可以插入不符合约束的数据.

mysql> INSERT INTO sells(bar, beer, price)
    -> VALUE ('Jack''s Bar', 'Chimay', 6.00);
Query OK, 1 row affected (0.04 sec)

使用 MySQL 8

登录 MySQL 8.4.2, 发现 test_bar 数据库中已经有表 Sells. 使用 show create table Sells; 查看.

CREATE TABLE `sells` (
  `bar` varchar(20) NOT NULL,
  `beer` varchar(20) NOT NULL,
  `price` double DEFAULT NULL,
  PRIMARY KEY (`bar`,`beer`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

现在增加 Check 约束.

mysql> ALTER TABLE Sells
    -> ADD CHECK (Bar='Joe''s Bar' OR price <=5.00);
ERROR 3819 (HY000): Check constraint 'sells_chk_1' is violated.

出现这种错误的原因是原表中存在违反约束的数据.

mysql> select * from sells;
+------------------+---------------+-------+
| bar              | beer          | price |
+------------------+---------------+-------+
| 3DArtBar         | 喜力          |    25 |
| 3DArtBar         | 嘉士伯        |    32 |
| 3DArtBar         | 安贝夫        |    40 |
| 3DArtBar         | 百威          |    30 |
| 3DArtBar         | 贝克          |    35 |
| HardRock         | 健力士黑啤    |    39 |
| HardRock         | 喜力          |    28 |
| HardRock         | 嘉士伯        |    30 |
| HardRock         | 安贝夫        |    39 |
| HardRock         | 百威          |    32 |
| HardRock         | 科罗娜        |    40 |
| HardRock         | 贝克          |    33 |
| Westside         | 健力士黑啤    |    39 |
| Westside         | 南非啤酒      |    36 |
| Westside         | 朝日          |    26 |
| Westside         | 百威          |    32 |
| Westside         | 科罗娜        |    40 |
| Westside         | 纯种苦啤酒    |    33 |
| Westside         | 贝克          |    28 |
| 宝莱纳餐厅       | 健力士黑啤    |    39 |
| 宝莱纳餐厅       | 南非啤酒      |    36 |
| 宝莱纳餐厅       | 喜力          |    28 |
| 宝莱纳餐厅       | 朝日          |    26 |
| 宝莱纳餐厅       | 百威          |    32 |
| 宝莱纳餐厅       | 科罗娜        |    40 |
| 宝莱纳餐厅       | 纯种苦啤酒    |    33 |
| 扬州老啤酒厂酒吧 | Stella Artois |    36 |
| 扬州老啤酒厂酒吧 | 健力士黑啤    |    26 |
| 扬州老啤酒厂酒吧 | 喜力          |    35 |
| 扬州老啤酒厂酒吧 | 安贝夫        |    40 |
| 扬州老啤酒厂酒吧 | 百威          |    32 |
| 扬州老啤酒厂酒吧 | 科罗娜        |    40 |
| 扬州老啤酒厂酒吧 | 米勒          |    50 |
| 扬州老啤酒厂酒吧 | 纯种苦啤酒    |    33 |
| 扬州老啤酒厂酒吧 | 贝克          |    42 |
| 扬州老啤酒厂酒吧 | 麒麟          |    39 |
| 木板房啤酒吧     | 嘉士伯        |    43 |
| 木板房啤酒吧     | 安贝夫        |    40 |
| 木板房啤酒吧     | 生力          |    25 |
| 木板房啤酒吧     | 米勒          |    32 |
| 木板房啤酒吧     | 贝克          |    35 |
| 木板房啤酒吧     | 麒麟          |    30 |
| 苏荷酒吧         | 健力士黑啤    |    39 |
| 苏荷酒吧         | 南非啤酒      |    36 |
| 苏荷酒吧         | 喜力          |    28 |
| 苏荷酒吧         | 朝日          |    26 |
| 苏荷酒吧         | 生力          |    38 |
| 苏荷酒吧         | 百威          |    32 |
| 苏荷酒吧         | 科罗娜        |    40 |
| 苏荷酒吧         | 纯种苦啤酒    |    33 |
+------------------+---------------+-------+
50 rows in set (0.01 sec)

mysql> ALTER TABLE sells
    -> ADD CHECK (price<=50);
Query OK, 50 rows affected (0.07 sec)
Records: 50  Duplicates: 0  Warnings: 0

此时就成功添加了约束, 因为表中原有数据最大值是50.

再检查 Sells 表的定义, 可以看到新添加了约束, 名字sells_chk_1是系统自动起的.

CREATE TABLE `sells` (
  `bar` varchar(20) NOT NULL,
  `beer` varchar(20) NOT NULL,
  `price` double DEFAULT NULL,
  PRIMARY KEY (`bar`,`beer`),
  CONSTRAINT `sells_chk_1` CHECK ((`price` <= 50))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

使用DROP语句可以删除此约束. 注意DROP后面跟CONSTRAINT.

mysql> ALTER TABLE Sells
    -> DROP sells_chk_1;
ERROR 1091 (42000): Can't DROP 'sells_chk_1'; check that column/key exists

DROP 后面一般跟 DATABASE, TABLE 等.

mysql> ALTER TABLE Sells
    -> DROP CONSTRAINT sells_chk_1;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

DROP 列的测试

mysql> select * from drinkers;
Empty set (0.02 sec)

drinkers 表中没有数据, 我们就以它为试验.

mysql> desc drinkers;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name  | varchar(20)  | NO   | PRI | NULL    |       |
| addr  | varchar(255) | YES  |     | NULL    |       |
| phone | varchar(15)  | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

将 phone 这一列删除.

mysql> ALTER TABLE drinkers
    -> DROP COLUMN phone;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

检查后发现 phone 这个属性已经被删除了.

mysql> DESC drinkers;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name  | varchar(20)  | NO   | PRI | NULL    |       |
| addr  | varchar(255) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.04 sec)

为继续实验, 再将 phone 这个属性加回去.

mysql> ALTER TABLE drinkers
    -> ADD COLUMN phone VARCHAR(15);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC drinkers;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name  | varchar(20)  | NO   | PRI | NULL    |       |
| addr  | varchar(255) | YES  |     | NULL    |       |
| phone | varchar(15)  | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

断言(Assertions)

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

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

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

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

MySQL 8

由于 Sells 表中有很多数据. 为此先将其复制到一个空表, 名为 Sells2.

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.4.2     |
+-----------+
1 row in set (0.01 sec)

mysql> CREATE TABLE Sells2 LIKE Sells;
Query OK, 0 rows affected (0.04 sec)

mysql> show tables;
+--------------------+
| Tables_in_test_bar |
+--------------------+
| bars               |
| beers              |
| drinkers           |
| frequents          |
| likes              |
| potbuddies         |
| sells              |
| sells2             |
+--------------------+
8 rows in set (0.00 sec)

mysql> DESC Sells;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| bar   | varchar(20) | NO   | PRI | NULL    |       |
| beer  | varchar(20) | NO   | PRI | NULL    |       |
| price | double      | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

注: 此时表 Sells 是空的, 只是结构与 Sells 相同.

若要复制完整的表(即结构和数据都要复制), 则使用

CREATE TABLE Sells2 AS SELECT * FROM Sells;

mysql> DROP TABLE Sells2;
Query OK, 0 rows affected (0.04 sec)

mysql> CREATE TABLE Sells2 AS SELECT * FROM Sells;
Query OK, 50 rows affected (0.03 sec)
Records: 50  Duplicates: 0  Warnings: 0

我们清空 Sells2 表.

mysql> TRUNCATE TABLE Sells2;
Query OK, 0 rows affected (0.04 sec)

mysql> SELECT * FROM Sells2;
Empty set (0.00 sec)

例子: 断言(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 5.7.23)

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>

mysql> CREATE ASSERTION NoRipoffBars CHECK(price<60);
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(price<60)' at line 1

实验(under MySQL 8.4.2)

例子: 断言(Assertions)

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

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

断言执行检查的时机

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

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

例如: Bars 不改动不会影响 FewBar, 同样的, Drinkers 的任何插入也不会影响断言 FewBar.

触发器(Triggers): 动机

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

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

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

MySQL 中的触发器

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

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

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

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

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

Action: 任何 SQL 语句.

初级的例子: 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);

其中 BEFORE INSERT ON Sells 是事件(event).
WHEN (...) 是条件(condition).
INSERT INTO ... 是动作(action).

MySQL 中上面的代码会出错. MySQL 中是用 NEW 和 OLD

例子: 触发器的定义

MySQL 中建立触发器的语法

CREATE TRIGGER trigger_name 
trigger_time trigger_event ON tbl_name 
FOR EACH ROW 
trigger_stmt

trigger_name: 触发器的名称;
trigger_time: 触发时机(BEFORE, AFTER);
trigger_event: 触发事件, 包括 INSERT, UPDATE 和 DELETE;
trigger_stmt: 触发器程序体.

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

实验(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 <name>

或者

CREATE OR REPLACE TRIGGER <name>

当存在名为 <name> 的触发器, 并且想修改它时很有用.

选项: The Event

AFTER 可以改为 BEFORE

如果关系是视图(view), 则也可以是 INSTEAD OF
执行视图(view)更新时的一个聪明的方法是: have triggers translate them to appropriate modifications on the base tables.

INSERT 可以换为 DELETE 或 UPDATE.

UPDATE 也可以换成 UPDATE ... ON 加一个指定的属性.

选项: FOR EACH ROW

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

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

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

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

选项: REFERENCING

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

这里的表(table) 是指插入的若干元组组成的集合(包).

DELETE implies an old tuple or table.

UPDATE implies both.

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

选项: The Condition

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

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

但总是在改变生效之前

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

选项: The Action

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

如果 SQL 语句不止一个, 则放在 BEGIN ... END 中.

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

另一个触发器的例子

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

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);

AFTER UPDATE OF price ON Sells 指出事件是: 在价格变动后.
REFERENCING ... 使条件和动作可以引用元组的旧值和/或触发事件中更新的元组的新值.
FOR EACH ROW: 我们需要考虑每一个元组中价格的变化.
WHEN ...: 条件: 是提价 $\$ 1$.
INSERT INTO ...: 动作: 当提价超过 $\$ 1$, 则把该酒吧加入关系 RipoffBars.

实验(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 中触发器的例子

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

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

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 同一张表中关于同样事件和作用时机的多个触发器

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 下触发器的编写

我们以书上第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#;

实验中的问题

添加外键时要注意的事项

回顾 test_students 数据库中有两张表, studentInfo 和 user. 首先查看它们的结构.

mysql> show create table user\G;
*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE `user` (
  `id` char(9) NOT NULL,
  `username` varchar(20) DEFAULT NULL,
  `password` char(41) DEFAULT NULL,
  `timestamp` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk
1 row in set (0.00 sec)

ERROR:
No query specified

这里报错的原因是在使用 \G 后仍加了分号.

mysql> show create table studentinfo\G
*************************** 1. row ***************************
       Table: studentinfo
Create Table: CREATE TABLE `studentinfo` (
  `studentID` char(9) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  `gender` char(4) DEFAULT NULL,
  `department` varchar(20) DEFAULT NULL,
  `major` varchar(30) DEFAULT NULL,
  `class` varchar(10) DEFAULT NULL,
  `QQ` varchar(20) DEFAULT NULL,
  `email` varchar(50) DEFAULT NULL,
  `phone` varchar(11) DEFAULT NULL,
  `state` varchar(10) DEFAULT NULL,
  `course_attribute` varchar(10) DEFAULT NULL,
  `remark` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`studentID`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk
1 row in set (0.00 sec)

在 user 表中添加外键, 将 user.id 指向 studentInfo.studentID.

mysql> ALTER TABLE user
    -> ADD CONSTRAINT fk_user_id FOREIGN KEY (id) REFERENCES studentInfo(studentID);
Query OK, 50 rows affected (0.09 sec)
Enregistrements: 50  Doublons: 0  Avertissements: 0

mysql> SHOW CREATE TABLE user\G
*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE `user` (
  `id` char(9) NOT NULL,
  `username` varchar(20) DEFAULT NULL,
  `password` char(41) DEFAULT NULL,
  `timestamp` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk
1 row in set (0.04 sec)

发现没有外键. 原因是 MyISAM 引擎不支持外键. 我们将表 user 的引擎改为 innoDB.

mysql> ALTER TABLE user
    -> ENGINE=InnoDB;
Query OK, 50 rows affected (0.11 sec)
Enregistrements: 50  Doublons: 0  Avertissements: 0

mysql> SHOW CREATE TABLE user\G
*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE `user` (
  `id` char(9) NOT NULL,
  `username` varchar(20) DEFAULT NULL,
  `password` char(41) DEFAULT NULL,
  `timestamp` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk
1 row in set (0.01 sec)

mysql> ALTER TABLE user
    -> ADD CONSTRAINT fk_user_id FOREIGN KEY (id) REFERENCES studentInfo(studentID);
ERROR 1215 (HY000): Impossible d'ajouter des contraintes d'index externe

无法添加外部索引约束.

mysql> ALTER TABLE studentInfo
    -> ENGINE=InnoDB;
Query OK, 50 rows affected (0.16 sec)
Enregistrements: 50  Doublons: 0  Avertissements: 0

mysql> SHOW CREATE TABLE studentInfo\G
*************************** 1. row ***************************
       Table: studentInfo
Create Table: CREATE TABLE `studentinfo` (
  `studentID` char(9) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  `gender` char(4) DEFAULT NULL,
  `department` varchar(20) DEFAULT NULL,
  `major` varchar(30) DEFAULT NULL,
  `class` varchar(10) DEFAULT NULL,
  `QQ` varchar(20) DEFAULT NULL,
  `email` varchar(50) DEFAULT NULL,
  `phone` varchar(11) DEFAULT NULL,
  `state` varchar(10) DEFAULT NULL,
  `course_attribute` varchar(10) DEFAULT NULL,
  `remark` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`studentID`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk
1 row in set (0.02 sec)

再次尝试对 user 表添加外键.

mysql> ALTER TABLE user
    -> ADD CONSTRAINT fk_user_id FOREIGN KEY (id) REFERENCES studentInfo(studentID);
Query OK, 50 rows affected (0.17 sec)
Enregistrements: 50  Doublons: 0  Avertissements: 0

添加成功.

mysql> SHOW CREATE TABLE user\G
*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE `user` (
  `id` char(9) NOT NULL,
  `username` varchar(20) DEFAULT NULL,
  `password` char(41) DEFAULT NULL,
  `timestamp` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_user_id` FOREIGN KEY (`id`) REFERENCES `studentinfo` (`studentID`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk
1 row in set (0.05 sec)

其他添加失败的原因

两个属性的 CHARACTER SET 不一致也可导致添加失败.

End

Thanks very much!

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

首页

Haifeng Xu

(hfxu@yzu.edu.cn)

目录

约束与触发器

约束与触发器

约束的种类

约束的种类

回顾: 单属性组成的键

回顾: 单属性组成的键

回顾: 多属性组成的键

回顾: 多属性组成的键

外键(Foreign Keys)

外键(Foreign Keys)

表述外键(Expressing Foreign Keys)

表述外键(Expressing Foreign Keys)

例子: 紧跟属性后面

例子: 紧跟属性后面

实验

例子: 作为模式的元素

例子: 作为模式的元素

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

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

Actions Taken --- (1)

Actions Taken --- (1)

例子: Cascade

例子: Cascade

例子: 置空(Set NULL)

例子: 置空(Set NULL)

选择其中一个原则

选择其中一个原则

例子: 设置更新原则

例子: 设置更新原则

基于属性的检查

基于属性的检查

例子: 基于属性的检查

例子: 基于属性的检查

实验(under MySQL)

检查的时机

检查的时机

基于元组的检查

基于元组的检查

例子: 基于元组的检查

例子: 基于元组的检查

实验(under MySQL)

使用 MySQL 8

DROP 列的测试

断言(Assertions)

断言(Assertions)

MySQL 8

例子: 断言(Assertion)

例子: 断言(Assertion)

实验(under MySQL 5.7.23)

实验(under MySQL 8.4.2)

例子: 断言(Assertions)

例子: 断言(Assertions)

断言执行检查的时机

断言执行检查的时机

触发器(Triggers): 动机

触发器(Triggers): 动机

MySQL 中的触发器

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

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

初级的例子: A Trigger

初级的例子: A Trigger

例子

例子: 触发器的定义

例子: 触发器的定义

MySQL 中建立触发器的语法

实验(under MySQL)

实验

选项: CREATE TRIGGER

选项: CREATE TRIGGER

选项: The Event

选项: The Event

选项: FOR EACH ROW

选项: FOR EACH ROW

选项: REFERENCING

选项: REFERENCING

选项: The Condition