8.6 检查约束(CHECK)

      在数据完整性的体系中，除了主键、外键、唯一和非空约束外，我们有时还需要更复杂的规则来保证数据的合理性。例如，年龄不能为负数、成绩必须在0到100之间、性别只能是“男”或“女”（但用枚举可能更合适）。这些业务规则可以通过检查约束（CHECK） 来实现。

一、什么是检查约束？

检查约束 是一种用于限定列中值的逻辑条件。它允许你在插入或更新数据时，根据一个布尔表达式来判断数据是否有效。只有满足条件（表达式结果为 TRUE）的操作才会被允许。

为什么需要检查约束？

1. 数据逻辑验证：确保字段值符合业务规则，例如年龄≥0、价格>0。

2. 减少应用层负担：将简单的数据验证下沉到数据库层，避免应用层遗漏或绕过验证。

3. 保证数据一致性：无论通过何种方式修改数据（应用程序、直接SQL、导入工具），约束都会生效。

二、CHECK 约束的语法

CHECK 约束可以在创建表时定义，也可以在表创建后添加。

1. 列级 CHECK 约束

直接在列定义后面添加 CHECK (条件)：

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    age INT CHECK (age >= 0 AND age <= 150),   -- 年龄必须在0-150之间
    gender CHAR(1) CHECK (gender IN ('M', 'F'))  -- 性别只能是 M 或 F
);

注意：列级 CHECK 只能引用本列，不能引用其他列。

2. 表级 CHECK 约束

可以在所有列定义之后，使用 [CONSTRAINT 约束名] CHECK (条件) 来定义，可以引用多个列：

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    discounted_price DECIMAL(10,2),
    CONSTRAINT chk_price_positive CHECK (price > 0),
    CONSTRAINT chk_discount_less_than_price CHECK (discounted_price < price)
);

表级约束可以为约束指定名称，便于后续管理。

3. 建表后添加 CHECK 约束

使用 ALTER TABLE 添加（MySQL 8.0.16+ 支持）：

ALTER TABLE users 
ADD CONSTRAINT chk_age_range CHECK (age >= 0 AND age <= 150);

4. 删除 CHECK 约束

ALTER TABLE users DROP CHECK chk_age_range;

三、CHECK 约束的实际应用示例

示例1：学生成绩表

CREATE TABLE scores (
    id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT NOT NULL,
    subject VARCHAR(50) NOT NULL,
    score DECIMAL(5,2),
    CONSTRAINT chk_score_range CHECK (score >= 0 AND score <= 100)
);

-- 尝试插入非法成绩
INSERT INTO scores (student_id, subject, score) VALUES (1, '数学', 105); 
-- 错误：Check constraint 'chk_score_range' is violated.

示例2：订单表，要求订单金额必须大于0，且发货日期不能早于订单日期

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_date DATE NOT NULL,
    ship_date DATE,
    amount DECIMAL(10,2) NOT NULL,
    CONSTRAINT chk_amount_positive CHECK (amount > 0),
    CONSTRAINT chk_ship_date CHECK (ship_date IS NULL OR ship_date >= order_date)
);

示例3：员工表，要求邮箱包含 '@' 符号（简单的格式验证）

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255),
    CONSTRAINT chk_email_format CHECK (email IS NULL OR email LIKE '%@%')
);

四、MySQL 中 CHECK 约束的重要说明

历史版本支持

• MySQL 8.0.16 之前：虽然可以定义 CHECK 约束，但实际上会被解析并忽略（不强制执行）。要实现类似效果，只能使用触发器或枚举。

• MySQL 8.0.16 及之后：CHECK 约束被真正支持并强制执行。这是非常重要的版本特性。

约束条件的限制

1. 不能引用其他表：CHECK 条件只能引用当前表中的列，不能包含子查询或函数调用（但可以调用确定性内置函数，如 LENGTH()）。

2. 不能使用变量或存储过程。

3. 在条件中可以使用大部分内置函数，但必须保证每次计算都是确定的（如 NOW() 不确定，不能用于 CHECK）。

4. 对于已经存在的数据：添加 CHECK 约束时，MySQL 会检查现有数据是否违反，如果违反则添加失败。

与 ENUM 的对比

对于固定值的检查，如性别、状态，可以使用 ENUM 类型，它本质上也是一种约束。但 ENUM 只能用于单列，且值集是固定的；而 CHECK 更灵活，可以定义任意表达式，包括跨列比较。

与触发器的对比

触发器也可以实现复杂的验证，但 CHECK 约束更简洁、声明式，性能通常也更好。对于简单的单行约束，优先使用 CHECK。

五、最佳实践建议

1. 在数据库层实施核心业务规则：对于关键的数据验证，使用 CHECK 约束可以确保无论应用层如何修改，数据都不会被破坏。

2. 命名规范：为 CHECK 约束起有意义的名称，如 chk_表名_列名_规则，便于识别和错误排查。

3. 尽量保持简单：CHECK 条件应保持简单易懂，过于复杂的逻辑可以考虑用触发器实现。

4. 注意版本兼容性：如果你的 MySQL 版本低于 8.0.16，CHECK 约束不会生效，需要改用触发器或应用层验证。

5. 与 NOT NULL、UNIQUE 等结合：构建完整的约束体系，确保数据质量。

六、实战练习

场景：设计一个“账户”表 accounts

要求：

• 账户余额不能为负数（balance >= 0）。

• 账户类型只能是 'SAVINGS' 或 'CHECKING'。

• 如果账户类型是 'CHECKING'，则透支额度（overdraft_limit）不能超过 1000；如果是 'SAVINGS'，则透支额度必须为 0。

• 年利率（interest_rate）必须在 0 到 10 之间。

请写出建表语句。

参考答案：

CREATE TABLE accounts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    account_no VARCHAR(20) NOT NULL UNIQUE,
    account_type ENUM('SAVINGS', 'CHECKING') NOT NULL,
    balance DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    overdraft_limit DECIMAL(10,2) DEFAULT 0.00,
    interest_rate DECIMAL(5,2) NOT NULL DEFAULT 1.00,
    CONSTRAINT chk_balance_non_negative CHECK (balance >= 0),
    CONSTRAINT chk_overdraft_limit CHECK (
        (account_type = 'SAVINGS' AND overdraft_limit = 0) OR
        (account_type = 'CHECKING' AND overdraft_limit <= 1000)
    ),
    CONSTRAINT chk_interest_rate CHECK (interest_rate BETWEEN 0 AND 10)
);

测试约束：

-- 合法插入
INSERT INTO accounts (account_no, account_type, balance, interest_rate) 
VALUES ('123456', 'SAVINGS', 1000, 2.5);

-- 非法：SAVINGS 类型设置了透支额度
INSERT INTO accounts (account_no, account_type, overdraft_limit) 
VALUES ('123457', 'SAVINGS', 500); 
-- 违反 chk_overdraft_limit

-- 非法：余额为负数
INSERT INTO accounts (account_no, account_type, balance) 
VALUES ('123458', 'CHECKING', -100); 
-- 违反 chk_balance_non_negative

-- 非法：利率超出范围
INSERT INTO accounts (account_no, account_type, interest_rate) 
VALUES ('123459', 'CHECKING', 15); 
-- 违反 chk_interest_rate

七、CHECK 约束要点总结

检查约束是数据库完整性的最后一道防线，它能确保即使应用层出现疏忽，数据库也不会接受违反业务规则的数据。在 MySQL 8.0.16 及以上版本，你可以放心使用它来强化数据质量。