MySQL CHECK约束-之路教程

MySQL CHECK约束

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

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

MySQL CHECK约束简介

在MySQL 8.0.16之前，CREATE TABLE允许您包括表CHECK约束。
但是，CHECK约束只是被解析和忽略的：

CHECK(expression)

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

语法如下：

[CONSTRAINT [constraint_name]] CHECK (expression) [[NOT] ENFORCED]

使用以下语法：

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

table_name_chk_n

其中n是序数1,2,3…例如，部件表的CHECK约束的名称将为parts_chk_1，parts_chk_2，…。

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

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

使用ENFORCED或仅忽略ENFORCED子句即可创建和实施约束。
使用NOT ENFORCED创建约束，但不强制执行。

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

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

MySQL CHECK约束示例

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

1)MySQL 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)
);

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

因为我们没有明确指定CHECK约束的名称，所以MySQL自动为它们生成名称。

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

SHOW CREATE TABLE parts;

这是输出：

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

一旦设置了CHECK约束，只要您插入或更新一个导致布尔表达式评估为false的值，MySQL就会拒绝更改并发出错误。

该语句在部件表中插入新行：

INSERT INTO parts(part_no, description,cost,price) 
VALUES('A-001','Cooler',0,-100);

MySQL发出错误：

Error Code: 3819. Check constraint 'parts_chk_2' is violated.

因为price列的值为负，这导致表达式price> 0评估为FALSE，这导致约束冲突。

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

首先，删除零件表：

DROP TABLE IF EXISTS 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)
);

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

CONSTRAINT parts_chk_price_gt_cost CHECK(price >= cost)

因为我们为CHECK约束明确指定了名称，所以MySQL只是使用指定的名称创建新约束。

这是零件表的定义：

SHOW CREATE TABLE parts;

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

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

INSERT INTO parts(part_no, description,cost,price) 
VALUES('A-001','Cooler',200,100);

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

Error Code: 3819. Check constraint 'parts_chk_price_gt_cost' is violated.

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

日期：2019-11-20 08:52:12 来源：oir作者：oir

←MySQL UNIQUE约束

模拟MySQL CHECK约束→

MySQL CHECK约束简介

MySQL CHECK约束示例

1)MySQL CHECK约束–列约束示例

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

目录