sql-check-constraint

SQL CHECK 约束

什么是 SQL CHECK 约束？

SQL CHECK 约束是用于在数据库层面强制实施数据完整性的强大工具。它允许你定义一个特定的规则或条件，列（或多列）中的数据必须满足该规则或条件。

当 CHECK 约束处于活动状态时，数据库会根据此条件验证 INSERT 语句中的任何新数据或 UPDATE 语句中的修改数据。如果数据未能通过检查，数据库将拒绝该操作并返回错误。这可以防止无效数据进入你的表，从而确保更高的数据质量和一致性。

例如，在 Employees 表中，你可以为 Salary 列添加一个 CHECK 约束，以确保它始终是正值；或者为 EndDate 列添加约束，以确保它始终晚于 StartDate。

定义 CHECK 约束（列级别）

添加 CHECK 约束最直接的方法是在列级别进行，即在创建表时直接在列的定义中添加。

语法

CREATE TABLE TableName (
    ColumnName1 DataType [CONSTRAINT ConstraintName] CHECK (Condition),
    ColumnName2 DataType,
    ...
);

注意：尽管在某些数据库中命名约束是可选的，但这强烈建议作为最佳实践。一个清晰的名称可以让你在以后更容易识别和管理约束。

示例：单列约束

让我们创建一个 Users 表。我们将为 Age 列添加一个 CHECK 约束，以确保用户年龄至少为 18 岁。

-- 创建带有命名 CHECK 约束的表
CREATE TABLE Users (
    ID INT PRIMARY KEY,
    Username VARCHAR(50) NOT NULL UNIQUE,
    Age INT NOT NULL CONSTRAINT CHK_Users_Age CHECK (Age >= 18),
    Email VARCHAR(100)
);

验证

现在，让我们测试一下约束。尝试插入一个年龄小于 18 岁的用户应该会失败。

-- 这条语句将成功执行
INSERT INTO Users (ID, Username, Age, Email) VALUES (1, 'alex_morgan', 25, 'alex@example.com');

-- 这条语句将失败
INSERT INTO Users (ID, Username, Age, Email) VALUES (2, 'casey_jones', 17, 'casey@example.com');

第二个 INSERT 语句将产生一条错误消息，通常会提及被违反的约束名称，例如：

-- PostgreSQL 错误示例
ERROR: new row for relation "users" violates check constraint "chk_users_age"

-- SQL Server 错误示例
The INSERT statement conflicted with the CHECK constraint "CHK_Users_Age".

定义 CHECK 约束（表级别）

当约束需要引用多个列时，它必须在表级别定义。这对于比较同一行内值的规则很常见。

语法

CREATE TABLE TableName (
    Column1 DataType,
    Column2 DataType,
    ...,
    [CONSTRAINT ConstraintName] CHECK (Condition involving multiple columns)
);

示例：多列约束

让我们创建一个 Products 表。我们希望确保 SalePrice 始终小于或等于 ListPrice。

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100) NOT NULL,
    ListPrice DECIMAL(10, 2) NOT NULL,
    SalePrice DECIMAL(10, 2) NOT NULL,
    CONSTRAINT CHK_Products_Price CHECK (SalePrice <= ListPrice)
);

验证

尝试插入销售价格高于标价的产品现在将失败。

-- 这条语句将成功执行
INSERT INTO Products (ProductID, ProductName, ListPrice, SalePrice) VALUES (101, 'Pro Widget', 100.00, 90.00);

-- 这条语句将失败
INSERT INTO Products (ProductID, ProductName, ListPrice, SalePrice) VALUES (102, 'Faulty Widget', 50.00, 55.00);

第二次插入将被数据库拒绝，因为违反了 CHK_Products_Price 约束。

向现有表添加 CHECK 约束

你可以随时使用 ALTER TABLE 语句向表添加约束。这对于对现有数据强制实施新的业务规则很有用。

语法

ALTER TABLE TableName
ADD CONSTRAINT ConstraintName CHECK (Condition);

警告：当你向已包含数据的表添加 CHECK 约束时，大多数数据库系统会立即检查所有现有行。如果任何行违反新约束，ALTER TABLE 语句将失败。

示例

首先，让我们创建一个没有约束的 Orders 表。

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    OrderDate DATE NOT NULL,
    ShipDate DATE
);

现在，让我们添加一个约束来确保发货日期在订单日期当天或之后。

ALTER TABLE Orders
ADD CONSTRAINT CHK_Orders_ShipDate CHECK (ShipDate >= OrderDate);

命名和管理 CHECK 约束

要查看表上的现有约束，你可以查询标准的 INFORMATION_SCHEMA。这是一种可移植的方法，适用于许多数据库系统，如 PostgreSQL、SQL Server 和 MySQL。

SELECT
    constraint_name,
    check_clause
FROM information_schema.check_constraints
WHERE constraint_schema = 'public' -- 如有需要，请调整模式名称
  AND table_name = 'products';

输出

对我们的 Products 表运行上述查询将得到：

约束名称	检查子句
chk_products_price	((saleprice <= listprice))

常见陷阱和最佳实践

• CHECK 与 NULL： 如果条件中的任何列为 NULL，则 CHECK 约束条件评估为 UNKNOWN（非 TRUE 也非 FALSE）。在标准 SQL 中，UNKNOWN 允许插入或更新行。如果你想确保某列既非 NULL 又满足某个条件，你需要同时使用 NOT NULL 约束和 CHECK 约束。
• 命名至关重要： 始终为你的约束赋予一个有意义的名称（例如，CHK_TableName_ColumnName）。未命名的、系统生成的约束名称（如 products_saleprice_check 或 CK__Products__456DEA_...）难以记住和管理。
• 保持简洁： CHECK 约束应强制执行简单、稳定的业务规则。复杂逻辑通常最好在应用程序层或数据库触发器/函数中处理，因为约束可能难以调试和修改。
• 性能： CHECK 约束在受约束列的每次 INSERT 和 UPDATE 操作时都会被评估。虽然通常非常快，但极其复杂的检查可能会增加开销。

删除 CHECK 约束

如果业务规则发生变化，你可以使用 ALTER TABLE ... DROP CONSTRAINT 语句删除 CHECK 约束。这时，拥有一个清晰、可预测的名称就变得至关重要。

语法

语法在主要数据库中通常是一致的。

ALTER TABLE TableName
DROP CONSTRAINT ConstraintName;

示例

让我们从 Users 表中移除年龄检查。

-- 从 Users 表中删除约束
ALTER TABLE Users
DROP CONSTRAINT CHK_Users_Age;

验证

删除约束后，你可以成功插入一个年龄低于 18 岁的用户。

-- 这条语句现在将成功执行
INSERT INTO Users (ID, Username, Age, Email) VALUES (2, 'casey_jones', 17, 'casey@example.com');

跨数据库兼容性

CHECK 约束是 SQL 标准的一部分，并得到广泛支持。然而，存在一些细微的差异：

• MySQL： 对 CHECK 约束的支持在 8.0.16 版本中才正确实现。在早期版本中，语法被接受但约束未被强制执行。
• PostgreSQL 和 SQL Server： 两者都对 CHECK 约束提供强大、长期的支持，且行为符合标准。
• Oracle： 完全支持 CHECK 约束。
• SQLite： 完全支持 CHECK 约束。