MySQL 检查约束

检查约束是一种完整性约束，用于控制特定列中的值。它确保插入或更新列中的值必须与给定条件匹配。换句话说，它确定与该列关联的值是否有效。 根据给定条件确定 。

在8.0.16版本之前，MySQL使用了此约束的有限版本。在之前的版本中，我们可以创建此约束，但它不起作用。这意味着其语法得到支持，但在数据库中无效。在较早的版本中，CREATE TABLE语句可以包含CHECK约束，但它们会被MySQL解析和忽略。我们可以在先前的版本中使用以下语法：

CHECK (expr)  

如果我们使用之前的版本并且想要使用检查约束，我们可以使用视图 WITH CHECK OPTION 或触发器来仿真它。

在 8.0.16 版本之后，MySQL 对于所有存储引擎都使用检查约束，即表约束和列约束：

[CONSTRAINT [symbol]] CHECK (expr) [[NOT] ENFORCED]  

在这个语法中，我们首先需要为我们想创建的检查约束写入名称。如果我们没有写入名称，MySQL会自动根据以下规则生成一个名称：

table_name_chk_n

在这里， n 表示数字编号，如用户表的CHECK约束名称将为user_chk_1，user_chk_2等。接下来，我们需要指定一个 布尔表达式 ，该表达式应该对每个表记录评估为TRUE或UNKNOWN。如果该表达式返回FALSE，则违反了给定值的约束。

第三，我们可以选择使用强制执行子句来验证检查约束是否被强制执行。如果我们想创建和强制执行约束，请使用ENFORCED或省略子句。如果您希望创建一个约束但不强制执行它，请使用NOT ENFORCED子句。

我们之前讨论过，在MySQL中，CHECK约束可以作为表约束或列约束使用。如果我们将CHECK约束指定为表，它可以应用于多个列。相反，如果我们为列定义了这个约束，它只能被引用到定义它的那个列上。

MySQL CHECK约束示例

让我们通过各种示例来了解如何在列和表中使用检查约束。

1）带有列的MySQL CHECK约束

以下语句创建一个名为 vehicle 的新表，在其中我们在一列上指定了检查约束：

CREATE TABLE vehicle (
    vehicle_no VARCHAR(18) PRIMARY KEY,
    model_name VARCHAR(45),
    cost_price DECIMAL(10,2 ) NOT NULL CHECK (cost_price >= 0),
    sell_price DECIMAL(10,2) NOT NULL CHECK (sell_price >= 0)
);

在该语句中，我们可以看到 两个CHECK约束列 ，它们是 cost_price 和 sell_price 列。如前所述，如果我们没有显式地指定CHECK约束的名称，MySQL会自动为它们命名。

我们可以使用 SHOW CREATE TABLE 语句以以下形式显示带有CHECK约束名称的表信息：

mysql> SHOW CREATE TABLE vehicle;

我们应该得到下面的输出，我们可以看到MySQL为检查约束生成了以下名称： vehicle_chk_1 和 vehicle_chk_2 。

接下来，我们将使用以下语句向表中插入一些记录：

mysql> INSERT INTO vehicle(vehicle_no, model_name, cost_price, sell_price) 
VALUES('S2001', 'Scorpio', 950000, 1000000),
('M3000', 'Mercedes', 2500000, 3000000);

此语句被正确执行，因为它未违反检查约束条件。每当我们在检查约束列中插入或更新导致布尔表达式评估错误的值时，MySQL会反对更改并给出错误消息。请参见下面尝试向车辆表插入新记录的语句：

mysql> INSERT INTO vehicle(vehicle_no, model_name, cost_price, sell_price) 
VALUES('R0001', 'Rolls Royas', 75000000, -85000000);

在执行此语句之后， MySQL发出如下错误 ：

ERROR 3819 (HY000): Check constraint 'vehicle_chk_2' is violated.

我们将会得到这个错误是因为sell_price值列是负数，违反了检查约束规则，通过计算以下表达式 price >= 0 ** 得到 **FALSE 。

2) MySQL带有表的CHECK约束

首先，我们将使用以下语句删除上述表：

mysql> DROP TABLE IF EXISTS vehicle;

接下来，我们将再次使用下面的语句创建同名的vehicle表，其中包含一个额外的检查约束：

CREATE TABLE vehicle (
    vehicle_no VARCHAR(18) PRIMARY KEY,
    model_name VARCHAR(45),
    cost_price DECIMAL(10,2 ) NOT NULL CHECK (cost_price >= 0),
    sell_price DECIMAL(10,2) NOT NULL CHECK (sell_price >= 0),
    CONSTRAINT vehicle_chk_sp_gt_cp CHECK(sell_price > cost_price)
);

在这个语句中，我们可以看到一个新的子句，该子句定义了一个表的CHECK约束，以确保 sell_price始终高于cost_price 如下所示：

CONSTRAINT vehicle_chk_sp_gt_cp CHECK(sell_price > cost_price)

由于我们明确定义了检查约束的名称，MySQL将在表定义中添加具有指定名称的新约束。我们可以再次使用SHOW CREATE TABLE语句显示带有CHECK约束名称的表定义，如下所示：

在输出结果中，我们可以看到MySQL在列出列的后面为表生成了检查约束的名称，如红色矩形框所示。

接下来，我们将添加一些记录到表中，如下所示：

mysql> INSERT INTO vehicle(vehicle_no, model_name, cost_price, sell_price) 
VALUES('S2001', 'Avenger', 250000, 275000),
('M3000', 'Apache', 120000, 125000);

这个语句被正确执行，因为它没有违反检查约束条件。再次，我们将尝试插入一个新的记录到表中，其 售价小于成本价 ：

INSERT INTO vehicle (vehicle_no, model_name, cost_price, sell_price) 
VALUES('R0001', 'Honda', 80000, 70000);

MySQL拒绝更改并由于违反约束而给出错误消息。请参见下面的输出：

如何删除检查约束？

我们可以使用以下语句从表或列中删除检查约束：

ALTER TABLE table_name DROP CHECK constraint_name;
Or,
ALTER TABLE table_name DROP CONSTRAINT constraint_name;

例如， ，如果我们想要从表vehicle中删除检查约束，我们可以执行以下语句：

mysql> ALTER TABLE vehicle DROP CHECK vehicle_chk_sp_gt_cp;

该语句删除了指定的约束。验证后，我们可以在输出中看到指定的约束名称已成功删除。