MySQL CHECK Constraint

摘要：在本教程中，您将学习如何使用 MySQL CHECK约束来确保存储在列或列组中的值满足布尔表达式。

MySQL 8.0.16 实现了 SQL 检查约束。如果您将 MySQL 与早期版本一起使用，则可以使用视图WITH CHECK OPTION或触发器来模拟CHECK约束。

MySQL CHECK约束简介

在 MySQL 8.0.16 之前， CREATE TABLE允许您包含表CHECK约束。然而， CHECK约束只是被解析并忽略：

CHECK(expression)Code language: SQL (Structured Query Language) (sql)

从 MySQL 8.0.16 开始， CREATE TABLE支持所有存储引擎的表和列CHECK约束的基本功能。

语法如下：

[CONSTRAINT [constraint_name]] CHECK (expression) [[NOT] ENFORCED]
Code language: SQL (Structured Query Language) (sql)

在这个语法中：

首先，指定要创建的检查约束的名称。如果省略约束名称，MySQL 将自动生成具有以下约定的名称：

table_name_chk_nCode language: SQL (Structured Query Language) (sql)

其中n是序数 1,2,3... 例如， parts表的CHECK约束的名称将为parts_chk_1 、 parts_chk_2 、...

其次，指定一个布尔expression对于表的每一行，该表达式的计算结果必须为TRUE或UNKNOWN 。如果表达式的计算结果为FALSE ，则这些值违反约束或发生约束违反。

第三，可以选择指定强制子句来指示是否强制执行检查约束：

• 使用ENFORCED或仅省略ENFORCED子句来创建和强制执行约束。
• 使用NOT ENFORCED创建约束但不强制执行。

如前所述，您可以将CHECK约束指定为表约束或列约束。

表CHECK约束可以引用多个列，而列CHECK约束可以引用定义它的唯一列。

MySQL CHECK约束示例

让我们举一些使用CHECK约束的示例。

1）MySQL CHECK约束——列约束示例

此语句创建一个新的parts表：

CREATE TABLE 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)
);Code language: SQL (Structured Query Language) (sql)

在此语句中，我们有两个列CHECK约束：一个用于成本列，另一个用于价格列。

因为我们没有显式指定CHECK约束的名称，MySQL 自动为它们生成名称。

要使用CHECK约束名称查看表定义，请使用SHOW CREATE TABLE语句：

SHOW CREATE TABLE parts;Code language: SQL (Structured Query Language) (sql)

这是输出：

从输出中可以清楚地看到，MySQL 生成了检查约束parts_chk_1和parts_chk_2 。

一旦CHECK约束到位，每当您插入或更新导致布尔表达式计算结果为 false 的值时，MySQL 都会拒绝更改并发出错误。

此语句将新行插入到零件表中：

INSERT INTO parts(part_no, description,cost,price) 
VALUES('A-001','Cooler',0,-100);Code language: SQL (Structured Query Language) (sql)

MySQL 发出错误：

Error Code: 3819. Check constraint 'parts_chk_2' is violated.Code language: SQL (Structured Query Language) (sql)

因为price列的值为负数，这会导致表达式price > 0计算结果为FALSE ，从而导致违反约束。

2) MySQL CHECK约束 – 表约束示例

首先，删除parts表：

DROP TABLE IF EXISTS parts;Code language: SQL (Structured Query Language) (sql)

然后，创建一个新的parts表，并添加一个表CHECK约束：

CREATE TABLE 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),
    CONSTRAINT parts_chk_price_gt_cost 
        CHECK(price >= cost)
);Code language: SQL (Structured Query Language) (sql)

以下新子句定义了一个表CHECK约束，确保价格始终大于或等于成本：

CONSTRAINT parts_chk_price_gt_cost CHECK(price >= cost)Code language: SQL (Structured Query Language) (sql)

因为我们显式指定了CHECK约束的名称，MySQL 仅使用指定的名称创建新约束。

以下是parts表的定义：

SHOW CREATE TABLE parts;Code language: SQL (Structured Query Language) (sql)

表CHECK约束出现在表定义末尾的列列表之后。

此语句尝试插入一个价格低于成本的新零件：

INSERT INTO parts(part_no, description,cost,price) 
VALUES('A-001','Cooler',200,100);Code language: SQL (Structured Query Language) (sql)

这是由于违反约束而导致的错误：

Error Code: 3819. Check constraint 'parts_chk_price_gt_cost' is violated.Code language: SQL (Structured Query Language) (sql)

在本教程中，您了解了 MySQL CHECK约束，以确保存储在列中的值满足布尔条件。