全部教學 » MySQL檢查約束

MySQL檢查約束

2019-10-16 22:58:47

在本教學中，您將學習如何使用帶有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 > 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選項來建立一個檢視。 在檢視的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約束的方法用法。