MySQL CHECK 约束模拟

---

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

要学习本教程，您需要充分了解触发器，视图和存储过程。

---

SQL CHECK约束简介

SQL标准为您提供了  CHECK约束，允许您在插入和更新之前验证列或列组的数据。例如，您可以定义CHECK约束以强制零件的成本为正，如下所示：

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

SQL标准还允许您将多个CHECK约束应用于多个列的列或codeCHECK约束。例如，要确保价格始终大于或等于成本，请使用以下CHECK约束：

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

一旦CHECK约束到位，无论何时插入或更新导致布尔表达式求值为false的值，数据库系统都会拒绝更改。 不幸的是，MySQL不支持CHECK约束。事实上，MySQL的CREATE TABLE语句接受CHECK像SQL标准的声明CHECK语句。但是，MySQL默默地忽略CHECK约束并且不执行数据验证。 要CHECK在MySQL中实现约束，可以使用触发器或视图。

---

使用触发器的MySQL 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 ;

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

• cost> 0
• price> 0
• price> =cost

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

Query OK, 1 row affected (0.01 sec)

INSERT语句调用BEFORE INSERT触发器并接受值。 以下INSERT语句失败，因为它违反了条件：cost> 0。

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

ERROR 1644 (45000): check constraint on parts.cost failed

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

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

ERROR 1644 (45001): check constraint on parts.price failed

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

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

ERROR 1644 (45002): check constraint on parts.price & parts.cost failed

让我们看看我们现在在parts表上有什么内容。

SELECT * FROM parts;

+---------+-------------+--------+--------+
| part_no | description | cost   | price  |
+---------+-------------+--------+--------+
| A-001   | Cooler      | 100.00 | 120.00 |
+---------+-------------+--------+--------+
1 row in set (0.00 sec)

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

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

ERROR 1644 (45002): check constraint on parts.price & parts.cost failed

更新被拒绝。 因此，通过使用两个触发器：BEFORE INSERT并且BEFORE UPDATE，您可以在MySQL中模拟CHECK约束。

---

使用Views检查MySQL CHECK约束

我们的想法是创建一个带有基表检查选项的视图。在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视图在parts_data表中插入一个新行：

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 1146 (42S02): Table 'mysqldemo.parts_checked' doesn't exist

在本教程中，您已经了解了CHECKSQL标准中的约束以及如何使用触发器或视图来模拟MySQL中的CHECK约束。

---