MySQL CHECK约束

时间:2019-11-20 08:52:12  来源:igfitidea点击:

简介:在本教程中,您将学习如何使用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约束,以确保存储在列中的值满足布尔条件。