MySQL 检查约束仿真

摘要：在本教程中，您将学习如何 CHECK 使用触发器或视图在 MySQL 中使用模拟约束。

MySQL 8.0.16 完全实现了 SQL CHECK 约束。如果您使用 MySQL 8.0.16 或更高版本，请查看 CHECK 约束教程。

CHECK 使用触发器模拟约束

要模拟 CHECK MySQL 中的约束，您可以使用两个触发器： BEFORE INSERT 和 BEFORE UPDATE 。

首先，创建一个以 parts 演示命名 的新表：

CREATE TABLE IF NOT EXISTS parts (
    part_no VARCHAR(18) PRIMARY KEY,
    description VARCHAR(40),
    cost DECIMAL(10 , 2 ) NOT NULL,
    price DECIMAL(10,2) NOT NULL
);

接下来，创建一个存储过程来检查 cost 和 price 列中的值。

`DELIMITER $

CREATE PROCEDURE check_parts (IN cost DECIMAL(10,2), IN price DECIMAL(10,2)) BEGIN IF cost < 0 THEN SIGNAL SQLSTATE ‘45000’ SET MESSAGE_TEXT = ‘check constraint on parts.cost failed’; END IF;

IF price < 0 THEN
SIGNAL SQLSTATE '45001'
   SET MESSAGE_TEXT = 'check constraint on parts.price failed';
END IF;

IF price < cost THEN
SIGNAL SQLSTATE '45002'
       SET MESSAGE_TEXT = 'check constraint on parts.price & parts.cost failed';
END IF;

END$ DELIMITER ;`

然后，创建 BEFORE INSERT 和 BEFORE UPDATE 触发。在触发器内部，调用 check_parts() 存储过程。

-- before insert
DELIMITER $
CREATE TRIGGER  `parts_before_insert`  BEFORE INSERT ON  `parts

FOR EACH ROW BEGIN CALL check_parts(new.cost,new.price); END$
DELIMITER ; – before update DELIMITER $ CREATE TRIGGER parts_before_update BEFORE UPDATE ON parts FOR EACH ROW BEGIN CALL check_parts(new.cost,new.price); END$
DELIMITER ;`

之后，插入满足以下所有条件的新行：

• 成本 > 0
• 价格 > 0
• 价格 >= 成本

INSERT INTO parts(part_no, description,cost,price)
VALUES('A-001','Cooler',100,120);

1 row(s) affected

该 INSERT 语句调用 BEFORE INSERT 触发器并接受值。

以下 INSERT 语句失败，因为它违反了条件：cost > 0。

INSERT INTO parts(part_no, description,cost,price)
VALUES('A-002','Heater',-100,120);

Error Code: 1644\. check constraint on parts.cost failed

以下 INSERT 语句失败，因为它违反了条件：价格 > 0。

INSERT INTO parts(part_no, description,cost,price)
VALUES('A-002','Heater',100,-120);

Error Code: 1644. check constraint on parts.price failed

以下 INSERT 语句失败，因为它违反了条件：价格 > 成本。

INSERT INTO parts(part_no, description,cost,price)
VALUES('A-003','wiper',120,100);

让我们看看我们现在在 parts 表中有什么。

SELECT * FROM parts;

以下语句尝试更新成本以使其低于价格：

UPDATE parts
SET price = 10
WHERE part_no = 'A-001';

Error Code: 1644\. check constraint on parts.price & parts.cost failed

该声明被拒绝。

因此，通过使用两个触发器： BEFORE INSERT and BEFORE UPDATE ，您可以模拟 CHECK MySQL 中的约束。

CHECK 使用视图模拟约束

这个想法是 WITH CHECK OPTION 基于基础表创建一个视图。在 SELECT 视图定义的语句中，我们只选择满足 CHECK 条件的有效行。如果针对视图的任何插入或更新导致新行未出现在视图中，则将被拒绝。

首先，删除 parts 表以删除所有关联的触发器并创建一个与该 parts 表类似但具有不同名称的新表 parts_data ：

DROP TABLE IF EXISTS parts;

CREATE TABLE IF NOT EXISTS parts_data (
    part_no VARCHAR(18) PRIMARY KEY,
    description VARCHAR(40),
    cost DECIMAL(10,2) NOT NULL,
    price DECIMAL(10,2) NOT NULL
);

接下来，创建一个 parts 基于 parts_data 表命名的视图。通过这样做，我们可以保持使用该 parts 表的应用程序的代码保持不变。此外，旧 parts 表的所有权限保持不变。

CREATE VIEW parts AS
    SELECT 
        part_no, description, cost, price
    FROM
        parts_data
    WHERE
        cost > 0 AND price > 0 AND price >= cost 
WITH CHECK OPTION;

然后，通过视图在表中插入一个新行： parts_data parts

INSERT INTO parts(part_no, description,cost,price)
VALUES('A-001','Cooler',100,120);

它被接受，因为出现在视图中的新行是有效的。

之后，尝试插入一个不会出现在视图中的新行。

INSERT INTO parts_checked(part_no, description,cost,price)
VALUES('A-002','Heater',-100,120);

Error Code: 1369. CHECK OPTION failed 'classicmodels.parts_checked'

在本教程中，您学习了如何使用触发器或视图来模拟 CHECK MySQL 中的约束。