在本教程中,您将学习如何使用带有check选项的触发器或视图来模拟MySQL CHECK约束。
注意: 要更好学习和理解本教程,您需要对,视图和有很好的了解。
SQL CHECK约束简介
标准SQL提供的检查(CHECK)约束指定某列中的值必须满足布尔表达式。 例如,您可以添加一个CHECK约束来强制成本(cost)列为正值,如下所示:
USE testdb; 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约束应用于一列或跨多个列。 例如,为了确保价格(price)列总是大于或等于成本(cost)列,可使用CHECK约束如下:
USE testdb; 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在语句中接受CHECK子句,但是它会以静默方式忽略它。
MySQL使用触发器CHECK约束
在MySQL中模拟CHECK约束的第一种方法是使用两个:BEFORE INSERT和BEFORE UPDATE。
首先,为了演示目的,我们先创建一个parts表,如下语句 –
USE testdb; 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);
INSERT语句调用BEFORE INSERT触发器并接受值。
演示-1以下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
演示-2以下INSERT语句执行将会失败,因为它违反了条件:price> 0。
INSERT INTO parts(part_no, description,cost,price) VALUES('A-002','Heater',100,-120);
执行上面插入语句,得到以下错误提示信息 –
Error Code: 1644. check constraint on parts.price failed
演示-3以下INSERT语句执行将会失败,因为它违反了条件:price > cost。
INSERT INTO parts(part_no, description,cost,price) VALUES('A-003','wiper',120,100);
执行上面插入语句,得到以下错误提示信息 –
1644 - check constraint on parts.price & parts.cost failed
现在,让我们来看看在parts表中的数据。
SELECT * FROM parts;
执行上面查询语句,得到以下结果 –
+---------+-------------+------+-------+ | part_no | description | cost | price | +---------+-------------+------+-------+ | A-001 | Cooler | 100 | 120 | +---------+-------------+------+-------+ 1 row in set
我们试图更新cost列的值,使其低于价格(price)列:
UPDATE parts SET price = 10 WHERE part_no = 'A-001';
执行上面更新语句,得到以下错误提示信息 –
Error Code: 1644. check constraint on parts.price & parts.cost failed
上面更新语句被拒绝执行了。
如上示例中所示,我们通过使用两个触发器:BEFORE INSERT和BEFORE UPDATE,来模拟MySQL中的CHECK约束。
MySQL CHECK约束使用可更新视图与check选项
这个方法是使用基于表的check选项来创建一个视图。 在视图的语句中,我们仅选择满足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_data表创建名为parts的视图。 通过这样做,我们可以保持使用parts表的应用程序的代码保持不变。 此外,旧零件表的所有权限保持不变。
CREATE VIEW vparts 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 vparts(part_no, description,cost,price) VALUES('A-001','Cooler',100,120);
上面的新行可被接受,因为新行有效,可以出现在视图中。
但是,以下语句失败,因为新行不会出现在视图中。
INSERT INTO vparts(part_no, description,cost,price) VALUES('A-002','Heater',-100,120);
执行上面语句,MySQL会发出以下错误 –
Error Code: 1369. CHECK OPTION failed 'testdb.parts_checked'
在本教程中,我们向您介绍了标准SQL CHECK约束和两种在MySQL中模拟CHECK约束的方法用法。
¥ 我要打赏 纠错/补充 收藏