MySQL 学习（12）约束与触发器

数据完整性约束

关系型数据库系统和文件系统的一个不同点是，关系数据库本身能保证存储数据的完整性，不需要应用程序的控制，而文件系统一般需要在程序端进行控制。

当前几乎所有的关系型数据库都提供了约束（constraint）机制，该机制提供了一条强大而简易的途径来保证数据库中数据的完整性。

一般来说，数据完整性有以下三种形式：

1、实体完整性 保证表中有一个主键。在 InnoDB 存储引擎表中，用户可以通过定义 Primary Key 或 Unique Key 约束来保证实体的完整性。用户还可以通过编写一个触发器来保证数据完整性。

2、域完整性 保证数据每列的值满足特定的条件。在 InnoDB 存储引擎表中，域完整性可以通过以下几种途径来保证：

选择合适的数据类型确保一个数据值满足特定条件。
外键（Foreign Key）约束。
编写触发器。
还可以考虑用 DEFAULT 约束作为强制域完整性的一个方面。

3、参照完整性 保证两张表之间的关系。InnoDB 存储引擎支持外键，因此允许用户定义外键以强制参照完整性，也可以通过编写触发器以强制执行。

对于 InnoDB 存储引擎本身而言，提供了以下几种约束：

Primary Key
Unique Key
Foreign Key
Default
NOT NULL

约束的建立与查找

约束的创建可以采用以下两种方式：

表建立时就进行约束定义
利用 ALTER TABLE 命令来进行创建约束

对 Unique Key（唯一索引）的约束，用户还可以通过命令 CREATE UNIQUE INDEX 来建立。对于主键约束而言，其默认约束名为 PRIMARY。而对于 Unique Key 约束而言，默认约束名和列名一样，当然也可以人为指定 Unique Key 约束的名字。

Foreign Key 约束似乎会有一个比较神秘的默认名称。

下面是一个简单的创建表的语句，表上有一个主键和一个唯一键：

create table t_user(
    id int(10),
    name varchar(32),
    primary key(id),
    unique key(name)
);

具体如何创建就不讲了~

索引和约束的区别

在前面的小节中已经看到 Primary Key 和 Unique Key 的约束，有人不禁会问：这不就是通常创建索引的方法吗？那约束和索引有什么区别呢？

的确，当用户创建了一个唯一索引就创建了一个唯一的约束。

但是约束和索引的概念还是有所不同的，约束更是一个逻辑的概念，用来保证数据的完整性，而索引是一个数据结构，既有逻辑上的概念，在数据库中还代表着物理存储的方式。

对错误数据的约束

在某些默认设置下，MySQL 数据库允许非法的或不正确的数据的插人或更新，又或者可以在数据库内部将其转化为一个合法的值，如向 NOT NULL 的字段插入一个 NULL 值，MySQL 数据库会将其更改为 0 再进行插人，因此数据库本身没有对数据的正确性进行约束。

例如：

create table a
(
    id   int  not null,
    data Date not null
)

-- 如果插入非 date 的数据进去它并不会报错，只会抛出警告
-- 如果想要报错而不是警告得使用 sql_mode 参数来严格审核输入的参数

set sql_mode = 'STRICT_TRANS_TABLES';

ENUM 和 SET 约束

MySQL 数据库不支持传统的 CHECK 约束，但是通过 ENUM 和 SET 类型可以解决部分这样的约束需求。

例如表上有一个性别类型，规定域的范围只能是 male 或 female,在这种情况下用户可以通过 ENUM 类型来进行约束。

CREATE TABLE a (
  id INT,
  sex ENUM('male', 'female')
);

INSERT INTO a SELECT 1, 'female';

INSERT INTO a SELECT 2, 'bi';

可以看到，在上述例子中对第二条记录的插人依然是报了警告。因此如果想实现 CHECK 约束，还需要配合设置参数 sql_mode

SET sql_mode = 'STRICT_TRANS_TABLES';

这次对非法的输入值进行了约束，但是只限于对离散数值的约束，对于传统 CHECK 约束支持的连续值的范围约束或更复杂的约束，ENUM 和 SET 类型还是无能为力，这时用户需要通过触发器来实现对于值域的约束。

触发器与约束器

通过前面小节的介绍，用户已经知道完整性约束通常也可以使用触发器来实现，因此在了解数据完整性前先对触发器来做一个了解。

触发器的作用是在执行 INSERT、DELETE 和 UPDATE 命令之前或之后自动调用 SQL 命令或存储过程。MySQL 5.0 对触发器的实现还不是非常完善，限制比较多，而从 MySQL 5.1 开始触发器已经相对稳定，功能也较之前有了大幅的提高。

创建触发器的命令是 CREATE TRIGGER，只有具备 Super 权限的 MySQL 数据库用户才可以执行这条命令：

CREATE <触发器名> < BEFORE | AFTER >
<INSERT | UPDATE | DELETE >
ON <表名> FOR EACH Row<触发器主体>

最多可以为一个表建立 6 个触发器，即分别为 INSERT、UPDATE、DELETE 的 BEFORE 和 AFTER 各定义一个。BEFORE 和 AFTER 代表触发器发生的时间，表示是在每行操作的之前发生还是之后发生。

当前 MySQL 数据库只支持 FOR EACH ROW 的触发方式，即按每行记录进行触发，不支持像 DB2 的 FOR EACH STATEMENT 的触发方式。

通过触发器，用户可以实现 MySQL 数据库本身并不支持的一些特性，如对于传统 CHECK 约束的支持，物化视图、高级复制、审计等特性。这里先关注触发器对于约束的支持。

示例：创建学生信息表 tb_student 时，将年龄（age）的值设置在 1 至 100 之间的数值。

-- 学生信息表
CREATE TABLE tb_student
(
	id INT AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(30),
	age INT NOT NULL CHECK(age>0 AND age<100)
);

上述示例中，虽然给年龄（age）字段设置了 CHECK 检查约束，但是仍然可以往该数据表中添加不符合要求的年龄数组（原因上面说了），例如：-1 或者 120。

解决方法：使用触发器实现 CHECK 检查约束的功能。

1、创建 tb_student（学生信息表）。

-- 创建学生信息表
CREATE TABLE tb_student
(
	id INT AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(30),
	age INT NOT NULL
);

2、创建检查年龄（age）字段是否有效的触发器。

-- 创建触发器
CREATE TRIGGER trg_tb_student_insert_check BEFORE INSERT
ON tb_student FOR EACH ROW
BEGIN
	DECLARE msg varchar(100);
 
	IF NEW.age <= 0 OR NEW.age >= 100 
	THEN
		SET msg = CONCAT('您输入的年龄值：',NEW.age,' 为无效的年龄，请输入0到100以内的有效数字。');
		SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = msg;
	END IF;
END;

3、编写测试语句。

INSERT INTO tb_student(name,age) VALUES('测试数据~',120);

这时就会抛出错误

外键约束

外键用来保证参照完整性，MySQL 数据库的 MyISAM 存储引擎本身并不支持外键，对于外键的定义只是起到一个注释的作用。而 InnoDB 存储引擎则完整支持外键约束。

一般来说，称被引用的表为父表，引用的表称为子表。外键定义时的 ON DELETE 和 ON UPDATE 表示在对父表进行 DELETE 和 UPDATE 操作时，对子表所做的操作，可定义的子表操作有：

CASCADE
SET NULL
NO ACTION
RESTRICT

1、CASCADE 表示当父表发生 DELETE 或 UPDATE 操作时，对相应的子表中的数据也进行 DELETE 或 UPDATE 操作。SET NULL 表示当父表发生 DELETE 或 UPDATE 操作时，相应的子表中的数据被更新为 NULL 值，但是子表中相对应的列必须允许为 NULL 值。NO ACTION 表示当父表发生 DELETE 或 UPDATE 操作时，抛出错误，不允许这类操作发生。

2、RESTRICT 表示当父表发生 DELETE 或 UPDATE 操作时，抛出错误，不允许这类操作发生。如果定义外键时没有指定 ON DELETE 或 ON UPDATE，RESTRICT 就是默认的外键设置。

在其他数据库中，如 Oracle 数据库，有一种称为延时检查 deferred check 的外键约束，即检查在 SQL 语句运行完成后再进行。而目前 MySQL 数据库的外键约束都是即时检查（immediatecheck）

因此从上面的定义可以看出，在 MySQL 数据库中 NO ACTION 和 RESTRICT 的功能是相同的。

对于参照完整性约束，外键能起到一个非常好的作用。但是 对于数据的导人操作时，外键往往导致在外键约束的检查上花费大量时间。

因为 MySQL 数据库的外键是即时检查的，所以对导入的每一行都会进行外键检查。但是用户可以在导人过程中忽视外键的检查，如：

-- 导入大量数据前先关闭外键检查
SET foreign_key_checks = 0;
LOAD Data ...
-- 导入后再开启
SET foreign_key_checks = 1;

数据完整性约束​

约束的建立与查找​

索引和约束的区别​

对错误数据的约束​

ENUM 和 SET 约束​

触发器与约束器​

外键约束​