MySQL - CHECK 约束

MySQL - CHECK 约束

一个 CHECK 约束是一种数据库约束类型，它允许您指定一个条件，该条件对于插入或更新到列中的任何数据都必须为真。这是在数据库层面直接强制执行数据完整性（data integrity）和业务规则的强大功能。

### 重要版本说明

历史上，MySQL 会解析 `CHECK` 约束语法，但并不强制执行它。**自 MySQL 8.0.16 起，`CHECK` 约束已得到全面支持和强制执行。** 本教程重点介绍现代的原生实现。旧的通过触发器（triggers）实现的变通方法将作为复杂场景的替代方案进行讨论。

在单列上添加 CHECK 约束

您可以在创建表时定义 CHECK 约束。该约束确保单个列的值满足特定条件。

语法

您可以在列定义中内联（inline）定义约束，也可以在表级别定义。

-- 内联语法
CREATE TABLE table_name (
    column1_name data_type,
    column2_name data_type CHECK (condition),
    ...
);

-- 表级别语法
CREATE TABLE table_name (
    column1_name data_type,
    column2_name data_type,
    ...,
    [CONSTRAINT constraint_name] CHECK (condition)
);

示例

让我们创建一个 Products 表，其中 stock_quantity（库存数量）不能为负，price（价格）必须大于零。

CREATE TABLE Products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    stock_quantity INT NOT NULL CHECK (stock_quantity >= 0),
    price DECIMAL(10, 2),
    CONSTRAINT price_must_be_positive CHECK (price > 0.00)
);

现在，让我们测试这个约束。尝试插入一个数量为负的产品将会失败。

INSERT INTO Products (product_name, stock_quantity, price)
VALUES ('Laptop', -5, 999.99);

输出

MySQL 将拒绝插入并返回错误，因为 CHECK 约束被违反了。

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

同样，插入价格为零或负数的产品也将违反命名的约束。

INSERT INTO Products (product_name, stock_quantity, price)
VALUES ('Mouse', 100, 0.00);

输出：

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

在多列上添加 CHECK 约束

一个 CHECK 约束还可以引用同一表中的多个列。这对于强制执行依赖于不同字段之间关系的规则非常有用。

示例

让我们创建一个 Campaigns 表，包含 start_date（开始日期）和 end_date（结束日期）。我们希望确保 end_date 始终在 start_date 之后。

CREATE TABLE Campaigns (
    campaign_id INT AUTO_INCREMENT PRIMARY KEY,
    campaign_name VARCHAR(255) NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    CONSTRAINT valid_date_range CHECK (end_date >= start_date)
);

有效的插入将成功：

INSERT INTO Campaigns (campaign_name, start_date, end_date)
VALUES ('Summer Sale', '2024-06-01', '2024-08-31');
-- 查询成功，1 行受影响 (0.01 秒)

当 end_date 在 start_date 之前时，无效的插入将被拒绝：

INSERT INTO Campaigns (campaign_name, start_date, end_date)
VALUES ('Winter Promo', '2024-12-01', '2024-11-30');
-- 错误 3819 (HY000): Check 约束 'valid_date_range' 被违反。

向现有表添加 CHECK 约束

您可以使用 ALTER TABLE 语句向现有表添加 CHECK 约束。执行此操作时，MySQL 会检查所有现有行，以确保它们符合新约束。如果任何行未能通过检查，该操作将失败。

语法

ALTER TABLE table_name
ADD CONSTRAINT constraint_name CHECK (condition);

示例

首先，让我们创建一个不带约束的 CUSTOMERS 表。

CREATE TABLE CUSTOMERS(
    ID INT NOT NULL PRIMARY KEY,
    NAME VARCHAR(20) NOT NULL,
    AGE INT NOT NULL
);

现在，让我们添加一个 CHECK 约束，以确保所有客户的年龄都至少为 18 岁。

ALTER TABLE CUSTOMERS
ADD CONSTRAINT check_customer_age CHECK (AGE >= 18);

删除 CHECK 约束

要删除 CHECK 约束，您必须首先知道其名称。您可以在 information_schema.table_constraints 表中或通过使用 SHOW CREATE TABLE 命令找到它。

语法

ALTER TABLE table_name
DROP CHECK constraint_name;

示例

让我们从 CUSTOMERS 表中删除 check_customer_age 约束。

ALTER TABLE CUSTOMERS
DROP CHECK check_customer_age;

高级：使用触发器模拟 CHECK 约束

在 MySQL 8.0.16 之前，开发人员使用触发器（triggers）来模拟 CHECK 约束功能。虽然现在原生约束因其简洁性和性能而更受青睐，但触发器对于实现 CHECK 约束无法处理的复杂业务逻辑（例如，需要查询另一个表的规则）仍然很有用。

示例：使用触发器强制执行规则

假设我们有一个规则，即客户的工资在单次更新中不能增加超过 20%。CHECK 约束无法处理这种情况，因为它无法比较行的旧状态和新状态。触发器非常适合此目的。

DELIMITER //

CREATE TRIGGER check_salary_increase
BEFORE UPDATE ON CUSTOMERS
FOR EACH ROW
BEGIN
    -- 比较新旧工资
    IF NEW.SALARY > (OLD.SALARY * 1.20) THEN
        -- 抛出错误以阻止更新
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = '工资增长不能一次性超过20%。';
    END IF;
END;
//

DELIMITER ;

此触发器将拦截对 CUSTOMERS 表的 UPDATE 操作，并强制执行我们的自定义规则。

在客户端程序中处理约束冲突

当 CHECK 约束被违反时，数据库服务器会返回一个错误。您的应用程序代码应准备好捕获并优雅地处理这些错误，例如，通过向用户提供清晰的反馈。

Node.js（使用 mysql2/promise）
Python（使用 mysql-connector-python）



```javascript
// handleConstraint.js
// 先决条件: npm install mysql2 dotenv

require('dotenv').config();
const mysql = require('mysql2/promise');

async function testCheckConstraint() {
  let connection;
  try {
    connection = await mysql.createConnection({
        host: process.env.DB_HOST, user: process.env.DB_USER,
        password: process.env.DB_PASSWORD, database: 'test_db'
    });
    console.log('Connected to test_db.');

    // 设置: 带 CHECK 约束的表
    await connection.execute(`DROP TABLE IF EXISTS Products`);
    await connection.execute(`
        CREATE TABLE Products (
            product_name VARCHAR(100),
            stock_quantity INT CHECK (stock_quantity >= 0)
        );
    `);
    console.log('Products table created.');

    // 尝试违反约束
    console.log('Attempting to insert invalid record...');
    await connection.execute(
      `INSERT INTO Products (product_name, stock_quantity) VALUES (?, ?)`,
      ['Gadget', -1]
    );

  } catch (err) {
    // MySQL CHECK 约束错误的错误码是 3819
    if (err.errno === 3819) {
        console.error('Caught expected error:');
        console.error(`Error: ${err.message}`);
        console.error('This happened because the stock_quantity was negative, violating the CHECK constraint.');
    } else {
        console.error(`An unexpected error occurred: ${err.message}`);
    }
  } finally {
    if (connection) await connection.end();
  }
}

testCheckConstraint();

预期输出：

Connected to test_db.
Products table created.
Attempting to insert invalid record...
Caught expected error:
Error: Check constraint 'products_chk_1' is violated.
This happened because the stock_quantity was negative, violating the CHECK constraint.

handle_constraint.py

# 先决条件: pip install mysql-connector-python
import mysql.connector
from mysql.connector import errorcode

def test_check_constraint():
    try:
        # 假设数据库 'test_db' 已存在
        cnx = mysql.connector.connect(user='root', password='password', host='127.0.0.1', database='test_db')
        cursor = cnx.cursor()
        print("Connected to test_db.")

        # 设置表
        cursor.execute("DROP TABLE IF EXISTS Products")
        cursor.execute("CREATE TABLE Products (price DECIMAL(10,2) CHECK (price > 0))")
        print("Products table created.")

        # 尝试违反约束
        print("Attempting to insert invalid record...")
        cursor.execute("INSERT INTO Products (price) VALUES (-50.00)")
        cnx.commit()

    except mysql.connector.Error as err:
        # CHECK 约束违反的错误码是 3819
        if err.errno == 3819:
            print("Caught expected error:")
            print(f"Error: {err.msg}")
            print("This is because the price was not positive.")
        else:
            print(f"An unexpected error occurred: {err}")
    finally:
        if 'cursor' in locals(): cursor.close()
        if 'cnx' in locals() and cnx.is_connected(): cnx.close()

test_check_constraint()

预期输出：

Connected to test_db.
Products table created.
Attempting to insert invalid record...
Caught expected error:
Error: Check constraint 'products_chk_1' is violated.
This is because the price was not positive.