MySQL 如何使用触发器来模拟CHECK CONSTRAINT？

阅读更多：MySQL 教程

介绍

在数据库中，一个CHECK CONSTRAINT可以在数据插入和更新时对数据进行检查，然后决定是否接受修改。例如，有一个表格需要保证某一列中的数字在1到100之间，那么就可以添加一个 CHECK CONSTRAINT。但是，有些数据库系统可能不支持CHECK CONSTRAINT，或者CHECK CONSTRAINT的实现可能不够灵活，这个时候就可以使用触发器来模拟CHECK CONSTRAINT。

触发器简介

触发器是一种特殊类型的存储过程，它在特定的数据库操作（如 INSERT、UPDATE、DELETE）发生时自动执行。在SQL Server数据库中，触发器一般分为三种类型：

INSTEAD OF 触发器：当进行INSERT、UPDATE、DELETE操作时，该触发器会代替原始操作。
AFTER 触发器：当进行INSERT、UPDATE、DELETE操作之后，该触发器会自动触发。
BEFORE 触发器：当进行INSERT、UPDATE、DELETE操作之前，该触发器会自动触发。

如何使用触发器模拟CHECK CONSTRAINT

在介绍如何使用触发器模拟CHECK CONSTRAINT之前，我们首先需要了解CREATE TRIGGER语句的语法格式：

CREATE TRIGGER trigger_name   
ON table_name  
AFTER INSERT, UPDATE, DELETE    -- 执行时机，可选项有 BEFORE和AFTER
AS   
BEGIN   
    --触发时要执行的代码语句
END;

在创建触发器之前，我们需要首先创建一个表格来模拟此次实验：

CREATE TABLE employee (
     id   INT PRIMARY KEY, 
     name VARCHAR(100),
     age  INT
);

接下来，我们需要将年龄列的数据限制在1到100之间。对于MySQL数据库，我们可以使用触发器来模拟CHECK CONSTRAINT：

DELIMITER  $CREATE TRIGGER check_age_range BEFORE INSERT ON employee FOR EACH ROW BEGIN IF NEW.age<1 OR NEW.age>100 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Age must be between 1 and 100'; END IF; END$ 
DELIMITER ;

以上代码创建了一个BEFORE INSERT触发器，该触发器将在每次向employee表格中插入数据之前进行检查。如果年龄不在1到100之间，该触发器将抛出一个SQLSTATE‘45000’异常。现在，我们来测试一下该触发器：

INSERT INTO employee (id, name, age) VALUES (1, 'Tom', 0);
-- 抛出 'Age must be between 1 and 100' 的异常

INSERT INTO employee (id, name, age) VALUES (2, 'John', 101);
-- 抛出 'Age must be between 1 and 100' 的异常

INSERT INTO employee (id, name, age) VALUES (3, 'Mike', 25);
-- 插入成功

现在，我们成功地模拟了CHECK CONSTRAINT的效果。除了MySQL之外，其他的数据库也可以使用触发器进行相似的操作。