PostgreSQL CHECK 约束

数据验证是数据库管理的关键环节，它能确保所存信息的准确性、一致性和可靠性。正如我们在前面章节学到的，为列定义合适的数据类型是维持数据质量的基础步骤。

然而，仅仅依靠数据类型通常不足以强制执行数据必须满足的复杂业务规则或逻辑条件。例如，一个数字列可能必须是正数，或者一个日期列必须是将来的时间。这时候 CHECK（检查）约束 就派上用场了。

CHECK 约束允许你定义特定的条件，无论是输入到单个列的数据，还是跨越多列的数据，都必须满足这些条件。它就像一个强大的守门员，从根本上防止无效数据被存储到数据库中。通过实现 CHECK 约束，你可以显著提升数据完整性，从而构建出更值得信赖的数据基础和更健壮的应用程序。

1. 什么是 CHECK 约束

在 PostgreSQL 中，CHECK 约束是一种完整性约束，它允许数据库设计者指定一个布尔表达式（Boolean expression）。任何执行 INSERT（插入）或 UPDATE（更新）操作的新行或修改行都必须满足这个表达式才能成功。

• 如果表达式对某一行计算结果为 FALSE（假），则操作被拒绝，并返回一个错误。
• 如果表达式计算结果为 TRUE（真） 或 UNKNOWN（未知，通常由于表达式中存在 NULL 值），则允许操作。

CHECK 约束的主要目的是强制执行业务规则和域完整性，这超出了简单的数据类型定义或其他基本约束（如 NOT NULL 或 UNIQUE）所能提供的范畴。例如：

• 域限制 (Domain Restriction)： 确保数值落在特定范围内（例如，年龄在 0 到 120 之间，百分比在 0 到 100 之间）。
• 条件逻辑 (Conditional Logic)： 基于同一行内其他列的值来验证数据（例如，end_date 结束日期必须晚于 start_date 开始日期）。
• 模式匹配 (Pattern Matching)： 确保文本数据符合特定的模式（虽然可以使用带有 LIKE / SIMILAR TO / ~ 的 CHECK 约束，但对于复杂模式，正则表达式函数更强大，不过也可能消耗更多性能）。

CHECK 约束对于维护数据质量至关重要，因为它们在数据库层面上强制执行规则。这意味着无论数据是如何插入或更新的（无论是通过应用程序、直接运行的 SQL 脚本，还是其他工具），都将始终检查这些指定条件。这集中了验证逻辑，降低了因应用程序 Bug 导致出现脏数据的风险，并提高了数据库的整体可靠性。

2. CHECK 约束如何处理 NULL 值

理解 NULL 值如何与 CHECK 约束交互非常重要。如果 CHECK 约束的布尔表达式中的任何操作数计算结果为 NULL，则整个表达式的结果将被视为 UNKNOWN（未知）。

在 SQL 的逻辑体系中，UNKNOWN 不等同于 FALSE。因此，如果表达式计算为 UNKNOWN，系统会认为该约束已满足，并允许进行该行的插入或更新。

这种行为在列是可选的（允许 NULL）但只要提供了值就必须符合特定规则的情况下特别有用。例如，一个 discount_percentage（折扣百分比）列可能只在应用了折扣时才需要处于 0 到 100 之间。如果 discount_percentage 为 NULL（意味着没有折扣），约束就不需要去拦截它。

3. 在建表时定义 CHECK 约束

你可以在使用 CREATE TABLE 语句创建表时直接定义 CHECK 约束。当你预先知道验证规则时，这通常是首选方法，因为它从一开始就确保了数据完整性。

在 CREATE TABLE 中定义 CHECK 约束有两种主要方式：

• 列级约束 (Column-level constraint)： 作为列定义的一部分来定义，通常只引用该列。
• 表级约束 (Table-level constraint)： 与列定义分开定义，允许它引用同一行内的多个列。

这两种方法都使用 CHECK (条件) 语法，但放置的位置不同。通常，一个好的做法是显式地为你的约束命名（特别是对于表级约束），这样以后更容易识别和管理它们。如果你不提供名称，PostgreSQL 会自动生成一个默认名称。

3.1 列级 CHECK 约束语法

CREATE TABLE table_name (
    column_name data_type CHECK (condition),
    -- 或者使用自定义名称
    another_column_name data_type CONSTRAINT constraint_name CHECK (condition),
    ...
);

3.2 表级 CHECK 约束语法

CREATE TABLE table_name (
    column1 data_type,
    column2 data_type,
    ...,
    CONSTRAINT constraint_name CHECK (condition)
);

4. 定义 CHECK 约束的实战示例

让我们考虑一个管理员工数据和产品库存的公司的实际场景。

4.1 示例 1：基本的范围验证（员工年龄）

假设我们正在创建一个 employees（员工）表。一条业务规则规定，员工的年龄必须在 18 到 65 岁之间（含 18 和 65）。

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    age INT CHECK (age >= 18 AND age <= 65), -- 无名称的列级 CHECK 约束
    hire_date DATE DEFAULT CURRENT_DATE
);

代码解析：age INT CHECK (age >= 18 AND age <= 65): 这定义了一个列级 CHECK 约束。任何试图将 age 设置在 18-65 范围之外的 INSERT 或 UPDATE 操作都将失败。

让我们测试一下：

-- 这条会成功插入
INSERT INTO employees (first_name, last_name, email, age)
VALUES ('Alice', 'Smith', 'alice.s@example.com', 30);

-- 这条会失败，因为年龄超出了有效范围
INSERT INTO employees (first_name, last_name, email, age)
VALUES ('Bob', 'Johnson', 'bob.j@example.com', 17);
-- 报错：关系 "employees" 的新行违反了检查约束 "employees_age_check"
-- 详细信息：失败的行包含 (2, Bob, Johnson, bob.j@example.com, 17, 2023-10-27)。

-- 这条也会失败
INSERT INTO employees (first_name, last_name, email, age)
VALUES ('Charlie', 'Brown', 'charlie.b@example.com', 70);
-- 报错：关系 "employees" 的新行违反了检查约束 "employees_age_check"

-- 这条会成功插入 (age 是 NULL，它满足约束，因为 18 <= NULL AND NULL <= 65 的结果是 UNKNOWN)
INSERT INTO employees (first_name, last_name, email, age)
VALUES ('Diana', 'Prince', 'diana.p@example.com', NULL);

请注意 PostgreSQL 是如何自动将约束命名为 employees_age_check 的。一般来说，自己提供一个更具描述性的名称会更好。

4.2 示例 2：为产品库存命名 CHECK 约束

考虑一个 products（产品）表，其中 stock_quantity（库存数量）必须始终为非负数，且 price（价格）必须大于零。我们将为了清晰起见使用命名约束。

CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL CONSTRAINT chk_product_price_positive CHECK (price > 0), -- 列级，带名称
    stock_quantity INT NOT NULL CONSTRAINT chk_product_stock_nonnegative CHECK (stock_quantity >= 0), -- 列级，带名称
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

代码解析：

• CONSTRAINT chk_product_price_positive CHECK (price > 0): 确保价格始终为正数。
• CONSTRAINT chk_product_stock_nonnegative CHECK (stock_quantity >= 0): 确保库存数量永远不为负数。

测试这些约束：

-- 这条会成功插入
INSERT INTO products (product_name, price, stock_quantity)
VALUES ('Laptop', 1200.50, 50);

-- 这条会失败，因为价格不是正数
INSERT INTO products (product_name, price, stock_quantity)
VALUES ('Mouse', 0.00, 100);
-- 报错：关系 "products" 的新行违反了检查约束 "chk_product_price_positive"

-- 这条会失败，因为库存数量是负数
INSERT INTO products (product_name, price, stock_quantity)
VALUES ('Keyboard', 75.00, -5);
-- 报错：关系 "products" 的新行违反了检查约束 "chk_product_stock_nonnegative"

4.3 示例 3：用于日期范围的表级 CHECK 约束

我们需要存储项目详细信息，包括 start_date（开始日期）和 end_date（结束日期）。一个关键的业务规则是 end_date 必须始终在 start_date 当天或之后。这需要一个表级约束，因为它涉及比较两个不同的列。

CREATE TABLE projects (
    project_id SERIAL PRIMARY KEY,
    project_name VARCHAR(255) NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    budget DECIMAL(12, 2) CONSTRAINT chk_project_budget_positive CHECK (budget > 0),
    CONSTRAINT chk_project_dates_valid CHECK (end_date >= start_date) -- 表级，带名称
);

代码解析：CONSTRAINT chk_project_dates_valid CHECK (end_date >= start_date): 此约束确保 end_date 的值始终大于或等于 start_date。

测试 chk_project_dates_valid 约束：

-- 这条会成功插入
INSERT INTO projects (project_name, start_date, end_date, budget)
VALUES ('Website Redesign', '2023-01-15', '2023-06-30', 25000.00);

-- 这条会失败，因为结束日期早于开始日期
INSERT INTO projects (project_name, start_date, end_date, budget)
VALUES ('Mobile App Development', '2023-07-01', '2023-06-30', 50000.00);
-- 报错：关系 "projects" 的新行违反了检查约束 "chk_project_dates_valid"
-- 详细信息：失败的行包含 (2, Mobile App Development, 2023-07-01, 2023-06-30, 50000.00)。

-- 这条会成功插入 (开始日期等于结束日期)
INSERT INTO projects (project_name, start_date, end_date, budget)
VALUES ('Database Migration', '2024-01-01', '2024-01-01', 10000.00);

5. 向现有表添加 CHECK 约束

你可以使用 ALTER TABLE 语句将 CHECK 约束添加到已经存在的表中。当业务规则演变，或者当你最初创建表时没有包含所有必要的验证逻辑时，这非常有用。

当你向现有表添加 CHECK 约束时，PostgreSQL 会立即扫描表中的现有数据，以验证所有现有行是否满足新约束。如果任何现有行违反了该约束，ALTER TABLE 语句将失败，并且约束不会被添加。这种行为确保了约束对所有数据（无论是过去的还是现在的）都是有效的。

5.1 添加 CHECK 约束的语法

ALTER TABLE table_name
ADD CONSTRAINT constraint_name CHECK (condition);

5.2 示例：向现有表添加约束

假设我们最初创建 employees 表时没有加入年龄约束，或者现在出台了一项新政策需要进行薪资验证。

首先，让我们创建一个简化的 employees_v2 表并插入一些数据：

CREATE TABLE employees_v2 (
    employee_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    salary DECIMAL(10, 2)
);

-- 插入一些数据，其中一些可能会违反未来的约束
INSERT INTO employees_v2 (first_name, last_name, salary) VALUES
('John', 'Doe', 60000.00),
('Jane', 'Smith', 75000.00),
('Peter', 'Jones', -5000.00), -- 这将违反要求正数薪资的约束
('Maria', 'Garcia', NULL);

现在，让我们添加一个名为 chk_employee_salary_positive 的检查约束，以确保 salary（薪资）始终为正值（如果不为 NULL）。

ALTER TABLE employees_v2
ADD CONSTRAINT chk_employee_salary_positive CHECK (salary > 0);

代码解析：

• ALTER TABLE employees_v2: 指定要修改的表。
• ADD CONSTRAINT ...: 添加新约束，条件 salary > 0 将被强制执行。

现有数据会发生什么？
在我们的示例中，'Peter Jones' 的行中 salary 为 -5000.00，这违反了 salary > 0。因此，ALTER TABLE 语句将会失败：

ERROR:  check constraint "chk_employee_salary_positive" is violated by some row
-- 报错：某行违反了检查约束 "chk_employee_salary_positive"

要成功添加该约束，你首先需要使用 UPDATE 或 DELETE 修正违规数据：

-- 首先修正违规数据
UPDATE employees_v2
SET salary = 50000.00
WHERE employee_id = 3; -- Peter Jones 的 employee_id

-- 现在，约束可以成功添加了
ALTER TABLE employees_v2
ADD CONSTRAINT chk_employee_salary_positive CHECK (salary > 0);

更新数据后，ALTER TABLE 命令就能成功执行。salary 为 NULL 的行 (Maria Garcia) 不会违反约束，因为 NULL > 0 计算结果为 UNKNOWN，而 UNKNOWN 不是 FALSE。

6. 删除 CHECK 约束

如果相关的业务规则发生变化或不再适用，你可能需要移除 CHECK 约束。要删除约束，你必须知道它的名称。

删除 CHECK 约束的语法：

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

示例：删除 CHECK 约束
让我们从 projects 表（来自前面的示例 3）中删除 chk_project_budget_positive 约束。

ALTER TABLE projects
DROP CONSTRAINT chk_project_budget_positive;

代码解析：

• ALTER TABLE projects: 指定要修改的表。
• DROP CONSTRAINT chk_project_budget_positive: 移除名为该名字的约束。

执行此操作后，你就可以向 projects 表插入或更新 budget 为 0 或负数的数据了，而这在以前是被约束阻止的。

7. 复杂的 CHECK 约束表达式

CHECK 约束可以使用各种 SQL 运算符和函数，从而允许执行更复杂的业务规则。你可以使用逻辑运算符（AND、OR、NOT）组合条件，创建精密的验证规则。

7.1 示例 1：条件折扣验证

考虑一个 orders（订单）表，其中商品可以有 discount_percentage（折扣百分比）。仅当 is_discounted（是否折扣）标志设置为 TRUE 时才应应用此百分比，并且该百分比本身必须在 0 到 100 之间。如果 is_discounted 为 FALSE，则 discount_percentage 应为 NULL 或 0。

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL,
    is_discounted BOOLEAN DEFAULT FALSE,
    discount_percentage DECIMAL(5, 2),
    CONSTRAINT chk_discount_valid CHECK (
        (NOT is_discounted AND discount_percentage IS NULL) OR -- 如果未折扣，百分比必须为 NULL
        (is_discounted AND discount_percentage >= 0 AND discount_percentage <= 100) -- 如果有折扣，百分比必须在 0-100 之间
    )
);

代码解析：chk_discount_valid 约束使用 OR 来定义两个互斥的有效状态：

1. NOT is_discounted AND discount_percentage IS NULL: 如果未打折，则折扣百分比必须为空。
2. is_discounted AND discount_percentage >= 0 AND discount_percentage <= 100: 如果已打折，则百分比必须介于 0 到 100 之间。

测试这个复杂的约束：

-- 有效：无折扣，百分比为 NULL
INSERT INTO orders (customer_id, order_date, total_amount, is_discounted, discount_percentage)
VALUES (101, '2023-10-27', 150.00, FALSE, NULL);

-- 有效：有折扣，百分比在合理范围内
INSERT INTO orders (customer_id, order_date, total_amount, is_discounted, discount_percentage)
VALUES (102, '2023-10-27', 200.00, TRUE, 15.50);

-- 无效：有折扣，但百分比超出范围
INSERT INTO orders (customer_id, order_date, total_amount, is_discounted, discount_percentage)
VALUES (103, '2023-10-27', 500.00, TRUE, 101.00);
-- 报错：关系 "orders" 的新行违反了检查约束 "chk_discount_valid"

-- 无效：无折扣，但百分比不是 NULL
INSERT INTO orders (customer_id, order_date, total_amount, is_discounted, discount_percentage)
VALUES (104, '2023-10-27', 300.00, FALSE, 10.00);
-- 报错：关系 "orders" 的新行违反了检查约束 "chk_discount_valid"

此示例展示了使用 CHECK 约束强制执行复杂逻辑规则的强大能力。

7.2 示例 2：确保有效的类似枚举的值

虽然 PostgreSQL 原生提供了 ENUM 枚举类型，但你也可以使用 CHECK 约束将 VARCHAR 或 TEXT 列限制为一组预定义的值，从而模仿枚举的行为。如果你需要 VARCHAR 的灵活性但又想限制其内容，这将非常有用。

考虑一个 employee_status（员工状态）表，其中的 status 列只能是 'Active'（在职）、'Inactive'（离职）或 'On Leave'（休假）。

CREATE TABLE employee_status (
    status_id SERIAL PRIMARY KEY,
    employee_id INT NOT NULL,
    status VARCHAR(20) NOT NULL CONSTRAINT chk_valid_status CHECK (status IN ('Active', 'Inactive', 'On Leave')),
    status_date DATE DEFAULT CURRENT_DATE
);

代码解析：status IN ('Active', 'Inactive', 'On Leave'): 这确保了 status 列只能包含这三个指定的字符串值之一。

测试一下：

-- 有效插入
INSERT INTO employee_status (employee_id, status) VALUES (1, 'Active');
INSERT INTO employee_status (employee_id, status) VALUES (2, 'On Leave');

-- 无效插入：'Retired' 不在允许的列表中
INSERT INTO employee_status (employee_id, status) VALUES (3, 'Retired');
-- 报错：关系 "employee_status" 的新行违反了检查约束 "chk_valid_status"

8. CHECK 约束的错误处理

当 INSERT 或 UPDATE 语句试图以违反 CHECK 约束的方式创建或修改数据行时，PostgreSQL 将引发错误。错误消息通常会指出：

1. 错误类型：check constraint is violated (违反检查约束)。
2. 被违反的约束名称。
3. 有时会有一个 DETAIL（详细）消息，显示导致问题的具体行数据。

理解这些错误消息对于调试和在应用程序中实现正确的错误处理机制至关重要。例如，应用程序可以捕获这些特定的数据库错误，并向用户提供友好的提示信息，解释需要纠正哪些数据。

我们在前面看到的错误示例：

ERROR:  new row for relation "employees" violates check constraint "employees_age_check"
DETAIL:  Failing row contains (2, Bob, Johnson, bob.j@example.com, 17, 2023-10-27).

这非常清楚地告诉了我们哪个表 (employees)，哪个约束 (employees_age_check) 以及导致违规的具体数据 (age 是 17)。