在本教學中，您將學習如何使用帶有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在CREATE TABLE語句中接受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 > 0price > 0price >= 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選項來創建一個視圖。 在視圖的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_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約束的方法用法。