如何在 SQLite 中创建 CHECK 约束

数据完整性是数据库设计的核心要素，而 CHECK 约束就是维护数据质量的守门人。想象一下，如果没有规则限制，数据库就可能存入员工年龄为负数或订单日期来自未来的荒谬数据。SQLite 的 CHECK 约束功能让我们能够为数据设定合理的业务规则，从源头上杜绝这些异常情况的发生。

为什么需要 CHECK 约束

CHECK 约束就像数据的智能过滤器，它在数据写入前自动验证信息的有效性。与应用程序层面的验证相比，数据库层面的 CHECK 约束具有三大优势：

可靠性：即使应用程序存在漏洞，数据库依然能坚守数据规则
一致性：所有数据修改操作（插入、更新）都遵循相同规则
高效性：验证过程在数据库引擎内部完成，性能更高

创建表时定义 CHECK 约束

最基本的用法是在建表语句中直接嵌入约束条件。

单列约束示例

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    age INTEGER CHECK (age >= 18 AND age <= 65),
    salary REAL CHECK (salary > 0)
);

这里我们确保员工年龄在 18-65 岁之间，且工资必须为正数。

多列联合约束

CREATE TABLE reservations (
    id INTEGER PRIMARY KEY,
    room_number INTEGER,
    check_in_date TEXT,
    check_out_date TEXT,
    CHECK (check_out_date > check_in_date)
);

这个约束保证退房日期必须晚于入住日期。

修改表添加 CHECK 约束

对于已存在的表，我们可以通过修改表结构来增加约束条件。

添加新列时附带约束

ALTER TABLE products
ADD COLUMN discount_price REAL
CHECK (discount_price < regular_price);

为现有列添加约束

-- SQLite 需要重建表来实现这个操作
CREATE TABLE new_users (
    id INTEGER PRIMARY KEY,
    username TEXT NOT NULL,
    password TEXT CHECK (length(password) >= 8)
);

INSERT INTO new_users
SELECT * FROM users;

DROP TABLE users;
ALTER TABLE new_users RENAME TO users;

复杂业务规则实现

CHECK 约束可以表达相当复杂的业务逻辑，远超简单的范围检查。

正则表达式验证

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    email TEXT CHECK (email LIKE '%_@__%.__%')
);

虽然 SQLite 不支持完整正则，但可以用 LIKE 实现基础格式检查。

枚举值约束

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    status TEXT CHECK (status IN ('pending', 'processing', 'shipped', 'delivered'))
);

条件组合约束

CREATE TABLE bank_accounts (
    id INTEGER PRIMARY KEY,
    account_type TEXT,
    balance REAL,
    CHECK (
        (account_type = 'savings' AND balance >= 0) OR
        (account_type = 'credit' AND balance <= 10000)
    )
);

约束命名与错误处理

为约束命名可以让我们更清晰地处理违反约束的情况。

命名约束示例

CREATE TABLE students (
    id INTEGER PRIMARY KEY,
    score INTEGER,
    CONSTRAINT score_range CHECK (score BETWEEN 0 AND 100)
);

获取约束错误信息

当约束被违反时，SQLite 会返回类似这样的错误：

CHECK constraint failed: score_range

命名的约束能让我们快速定位问题所在。

实际应用案例

让我们看几个 CHECK 约束在实际业务中的典型应用。

电商库存管理

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price REAL CHECK (price > 0),
    stock INTEGER CHECK (stock >= 0),
    weight REAL CHECK (weight > 0 AND weight < 1000),
    CHECK (NOT (stock > 0 AND discontinued = 1))
);

医院预约系统

CREATE TABLE appointments (
    id INTEGER PRIMARY KEY,
    patient_id INTEGER NOT NULL,
    doctor_id INTEGER NOT NULL,
    appointment_time TEXT CHECK (
        strftime('%H:%M', appointment_time) BETWEEN '08:00' AND '18:00' AND
        strftime('%w', appointment_time) NOT IN ('0', '6')
    ),
    duration INTEGER CHECK (duration IN (15, 30, 45, 60)),
    CHECK (doctor_id != patient_id)
);

智能家居设备日志

CREATE TABLE device_logs (
    id INTEGER PRIMARY KEY,
    device_id INTEGER NOT NULL,
    event_time TEXT CHECK (event_time <= datetime('now')),
    temperature REAL CHECK (temperature BETWEEN -40 AND 100),
    power_consumption REAL CHECK (power_consumption >= 0),
    event_type TEXT CHECK (event_type IN ('on', 'off', 'alert', 'normal'))
);

总结

SQLite 的 CHECK 约束是维护数据完整性的强大工具：

基础检查：确保数值在合理范围内，日期顺序正确
复杂规则：实现多列关联验证和业务逻辑约束
灵活应用：支持正则模式、枚举值和条件组合
错误预防：在数据写入前拦截无效信息

在实际开发中，建议将最基本的业务规则通过 CHECK 约束实现，这样即使应用程序逻辑变更或出现漏洞，核心数据质量仍能得到保障。同时要注意，过于复杂的约束可能影响性能，对于特别复杂的业务规则，可能需要考虑在应用层实现部分验证逻辑。