MySQL CHECK Constraint Emulation

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

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

使用触发器模拟CHECK约束

要模拟 MySQL 中的CHECK约束，您可以使用两个触发器： 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
);Code language: SQL (Structured Query Language) (sql)

接下来，创建一个存储过程来检查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 ;Code language: SQL (Structured Query Language) (sql)

然后，创建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 ;Code language: SQL (Structured Query Language) (sql)

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

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

INSERT INTO parts(part_no, description,cost,price)
VALUES('A-001','Cooler',100,120);Code language: SQL (Structured Query Language) (sql)

1 row(s) affected

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

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

INSERT INTO parts(part_no, description,cost,price)
VALUES('A-002','Heater',-100,120);Code language: SQL (Structured Query Language) (sql)

Error Code: 1644. check constraint on parts.cost failedCode language: SQL (Structured Query Language) (sql)

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

INSERT INTO parts(part_no, description,cost,price)
VALUES('A-002','Heater',100,-120);Code language: SQL (Structured Query Language) (sql)

Error Code: 1644. check constraint on parts.price failedCode language: JavaScript (javascript)

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

INSERT INTO parts(part_no, description,cost,price)
VALUES('A-003','wiper',120,100);Code language: SQL (Structured Query Language) (sql)

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

SELECT * FROM parts;Code language: SQL (Structured Query Language) (sql)

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

UPDATE parts
SET price = 10
WHERE part_no = 'A-001';Code language: SQL (Structured Query Language) (sql)

Error Code: 1644. check constraint on parts.price & parts.cost failedCode language: SQL (Structured Query Language) (sql)

该声明被拒绝。

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

使用视图模拟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
);
Code language: SQL (Structured Query Language) (sql)

接下来，根据parts_data表创建一个名为parts的视图。通过这样做，我们可以保持使用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;Code language: SQL (Structured Query Language) (sql)

然后，通过parts视图将新行插入到parts_data表中：

INSERT INTO parts(part_no, description,cost,price)
VALUES('A-001','Cooler',100,120);Code language: SQL (Structured Query Language) (sql)

它被接受，因为视图中显示的新行有效。

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

INSERT INTO parts_checked(part_no, description,cost,price)
VALUES('A-002','Heater',-100,120);
Code language: SQL (Structured Query Language) (sql)

Error Code: 1369. CHECK OPTION failed 'classicmodels.parts_checked'Code language: JavaScript (javascript)

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