A2-03-07.DDL-MySQL CHECK Constraint Emulation
转载自：http://www.mysqltutorial.org/mysql-check-constraint/
MySQL CHECK Constraint Emulation
Summary: in this tutorial, you will learn how to use triggers or views with check option to emulate MySQL CHECK constraint.
To follow this tutorial, you need to have a good understanding of triggers, views, and stored procedures.
Introduction to the SQL CHECK constraint
Standard SQL provides CHECK constraints that specify a value in a certain column must satisfy a Boolean expression. For example, you can add a CHECK constraint to enforce the cost of a part to be positive as follows:
|
1
2
3
4
5
6
|
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 allows you to apply multiple CHECK constraints to a column or a CHECK constraint across multiple columns. For example, to make sure that the price is always greater or equal cost, you use the CHECK constraint as follows:
|
1
2
3
4
5
6
7
|
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)
);
|
Once the CHECK constraints are in place, whenever you insert or update a value that causes the Boolean expression evaluates to false, the check constraint is violated and the database system rejects the change.
Unfortunately, MySQL does not support CHECK constraint. Actually, MySQL accepts the CHECK clause in the CREATE TABLE statement but it ignores it silently.
MySQL CHECK constraint using triggers
The first way to simulate the CHECK constraint in MySQL, we use two triggers: BEFORE INSERT and BEFORE UPDATE.
First, create the parts tables for the demonstration.
|
1
2
3
4
5
6
|
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
);
|
Second, create a stored procedure to check the values in the cost and price columns.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
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 ;
|
Third, create BEFORE INSERT and BEFORE UPDATE triggers. Inside the triggers, call the check_parts() stored procedure.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
-- 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 ;
|
Fourth, insert a new row that satisfies all the following conditions:
- cost > 0
- And price > 0
- And price >= cost
|
1
2
|
INSERT INTO parts(part_no, description,cost,price)
VALUES('A-001','Cooler',100,120);
|
|
1
|
1 row(s) affected
|
The INSERT statement invokes the BEFORE INSERT trigger and accepts the values.
The following INSERT statement fails because it violates the condition: cost > 0.
|
1
2
|
INSERT INTO parts(part_no, description,cost,price)
VALUES('A-002','Heater',-100,120);
|
|
1
|
Error Code: 1644. check constraint on parts.cost failed
|
The following INSERT statement fails because it violates the condition: price > 0.
|
1
2
|
INSERT INTO parts(part_no, description,cost,price)
VALUES('A-002','Heater',100,-120);
|
|
1
|
Error Code: 1644. check constraint on parts.price failed
|
The following INSERT statement fails because it violates the condition: price > cost.
|
1
2
|
INSERT INTO parts(part_no, description,cost,price)
VALUES('A-003','wiper',120,100);
|
Let’s see what we are having now in the parts table.
|
1
|
SELECT * FROM parts;
|
We try to update the cost to make it lower than the price:
|
1
2
3
|
UPDATE parts
SET price = 10
WHERE part_no = 'A-001';
|
|
1
|
Error Code: 1644. check constraint on parts.price & parts.cost failed
|
It was rejected.
So by using two triggers: BEFORE INSERT and BEFORE UPDATE, we are able to emulate the CHECK constraint in MySQL.
MySQL CHECK constraint using an updatable view with check option
The idea is to create a view with check option against the base table. In the SELECT statement of the view, we select only valid rows that satisfy the CHECK conditions. Any insert or update against the view will be rejected if it would cause the new row to not appear in the view.
First, drop the parts table to remove all the associated triggers and create a new table like the partstable but have a different name parts_data:
|
1
2
3
4
5
6
7
8
|
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
);
|
Second, create a view named parts based on the parts_data table. By doing this, we can keep the code of the applications that use the parts table remain intact. In addition, all the privileges to the old partstable remain unchanged.
|
1
2
3
4
5
6
7
8
|
CREATE VIEW parts AS
SELECT
part_no, description, cost, price
FROM
parts
WHERE
cost > 0 AND price > 0 AND price >= cost
WITH CHECK OPTION;
|
Third, insert a new row into the parts_data table through the parts view:
|
1
2
|
INSERT INTO parts(part_no, description,cost,price)
VALUES('A-001','Cooler',100,120);
|
It is accepted because the new row is valid which can appear in the view.
However, the following statement fails because the new row would not appear in the view.
|
1
2
|
INSERT INTO parts_checked(part_no, description,cost,price)
VALUES('A-002','Heater',-100,120);
|
|
1
|
Error Code: 1369. CHECK OPTION failed 'classicmodels.parts_checked'
|
In this tutorial, we have introduced you to the standard SQL CHECK constraint and two ways to emulate the CHECK constraint in MySQL.
