除非另外命名了方言,否则 SQL 示例使用 PostgreSQL。简短的代码片段假定其引用的表已存在。在应用 DDL 之前,请在目标数据库中验证 DDL。
什么是 SQL 约束?
约束 是数据库引擎强制执行的规则,以保证表中数据的准确性和可靠性。它们充当护栏——在数据库边界阻止无效的插入、更新和删除。它们保护声明的规则;应用程序验证和事务设计仍然很重要。
SQL 定义了几种约束类型。在本指南中,我们将深入探讨每种约束,展示何时使用它,并强调常见的陷阱。
NOT NULL
这 NOT NULL 约束确保列始终有值。任何 INSERT 或 UPDATE 尝试将列设置为 NULL 将被拒绝。空字符串仍然是一个值;当禁止空文本时,添加一个 CHECK。
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name VARCHAR(150) NOT NULL, -- every product MUST have a name
price NUMERIC(10,2) NOT NULL -- every product MUST have a price
);
-- This will fail:
INSERT INTO products (name, price) VALUES (NULL, 9.99);
-- ERROR: null value in column "name" violates not-null constraint提示: 默认为 NOT NULL 对于每一列,只有在有明确的业务理由允许 null 时才删除它。Null 会将三值逻辑引入查询,并且是细微错误的常见来源。
唯一
A UNIQUE 约束保证受约束的列(或列组合)中没有两行包含相同的值。与主键不同, UNIQUE 列 可以 包含 NULL。PostgreSQL 和 MySQL 通常允许唯一列中有多个空值;SQL Server 在单列唯一约束中允许一个空值。这是一条唯一性规则,而不是评估 NULL = NULL 的结果。
CREATE TABLE users (
id SERIAL PRIMARY KEY,
email VARCHAR(255) NOT NULL UNIQUE,
username VARCHAR(50) NOT NULL UNIQUE
);
-- Multi-column unique constraint
CREATE TABLE reservations (
id SERIAL PRIMARY KEY,
room_id INT NOT NULL,
date DATE NOT NULL,
CONSTRAINT uq_room_date UNIQUE (room_id, date)
);多列 UNIQUE 上面的约束确保了同一个房间不能在同一天被预订两次。
CHECK
CHECK 约束用于在值存储之前验证它是否满足布尔表达式。它们强制执行仅靠类型系统无法表达的行级规则。CHECK 接受 TRUE 或 UNKNOWN;当需要值时,将其与 NOT NULL 结合使用。
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
age INT NOT NULL CHECK (age >= 18 AND age <= 120),
salary NUMERIC(12,2) NOT NULL CHECK (salary > 0)
);
-- Named CHECK constraint with multiple columns
CREATE TABLE events (
id SERIAL PRIMARY KEY,
start_date DATE NOT NULL,
end_date DATE NOT NULL,
CONSTRAINT chk_dates CHECK (end_date >= start_date)
);注意事项: 在 8.0.16 之前的 MySQL 版本中,CHECK 约束已被解析,但 被静默忽略。请务必验证您的数据库版本是否实际强制执行了它们。
DEFAULT
这 DEFAULT 在从一个 INSERT 或者显式请求 DEFAULT。显式 NULL 不会触发默认值。默认值减少了应用程序代码中的样板文件,并使架构具有自文档化能力。
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
status VARCHAR(20) NOT NULL DEFAULT 'pending',
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
quantity INT NOT NULL DEFAULT 1
);
-- Inserting without specifying status, created_at, or quantity
INSERT INTO orders DEFAULT VALUES;
-- Result: status='pending', created_at=<current time>, quantity=1带表达式的 DEFAULT
PostgreSQL 和 SQL Server 允许在 defaults 中使用表达式和函数调用。MySQL 自 8.0.13 版本以来支持此功能(针对某些类型)。
-- PostgreSQL: UUID default
CREATE TABLE tokens (
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
value TEXT NOT NULL
);PRIMARY KEY 和 FOREIGN KEY 约束
这些是关系数据库中最结构化的约束。一个 PRIMARY KEY 唯一标识每一行并隐式添加 NOT NULL + UNIQUE。一个 FOREIGN KEY 链接一个表到另一个表并强制执行引用完整性。
CREATE TABLE departments (
id SERIAL PRIMARY KEY,
name VARCHAR(80) NOT NULL UNIQUE
);
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
department_id INT NOT NULL,
CONSTRAINT fk_emp_dept
FOREIGN KEY (department_id)
REFERENCES departments (id)
ON DELETE RESTRICT
ON UPDATE CASCADE
);请参阅我们关于的深入指南 主键与外键 用于级联操作、复合键和最佳实践。您还可以在其中以可视化方式建模这些关系 Erd Go 编辑器.
列级与表级约束
可以定义约束 内联 (列级) 或作为单独的子句 所有列之后 (表级)。结果是相同的——但当约束跨越多个列时,表级是必需的。
-- Column-level (single column)
CREATE TABLE items (
id SERIAL PRIMARY KEY,
code VARCHAR(20) NOT NULL UNIQUE,
price NUMERIC(10,2) NOT NULL CHECK (price >= 0)
);
-- Table-level (multi-column constraint)
CREATE TABLE shipments (
id SERIAL PRIMARY KEY,
origin VARCHAR(3) NOT NULL,
destination VARCHAR(3) NOT NULL,
CONSTRAINT chk_different_locations CHECK (origin <> destination),
CONSTRAINT uq_route UNIQUE (origin, destination)
);命名约束及其重要性
始终使用 CONSTRAINT 关键字。当违反约束时,数据库会在错误消息中返回约束名称——一个有意义的名称,如 chk_positive_salary 比自动生成的哈希值更有帮助。
-- Good: named constraint
ALTER TABLE products
ADD CONSTRAINT chk_positive_price CHECK (price > 0);
-- You can also drop a constraint by name
ALTER TABLE products
DROP CONSTRAINT chk_positive_price;一致的命名规范使迁移、调试和团队上手更加顺畅。常见的模式是:
pk_<table>— 主键fk_<table>_<column>— 外键uq_<table>_<column(s)>— 唯一约束chk_<table>_<rule>— 检查约束
组合约束与常用模式
在实践中,列同时带有多个约束。以下是你将反复使用的模式:
CREATE TABLE accounts (
id SERIAL PRIMARY KEY,
email VARCHAR(255) NOT NULL,
role VARCHAR(20) NOT NULL DEFAULT 'user'
CHECK (role IN ('user', 'admin', 'moderator')),
balance NUMERIC(12,2) NOT NULL DEFAULT 0.00
CHECK (balance >= 0),
is_active BOOLEAN NOT NULL DEFAULT TRUE,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
deleted_at TIMESTAMPTZ -- nullable: NULL means "not deleted"
);软删除模式
这 deleted_at 上面的列实现了 软删除。您无需删除行,而是设置 deleted_at = now()。将其与部分唯一索引配对,以防止出现“活动”重复项,同时仍允许具有相同电子邮箱的已删除行存在。该表刻意没有在电子邮箱上设置全局 UNIQUE 约束;保留这样的约束也会拒绝已删除行之间的重复项:
-- PostgreSQL: unique email only among active accounts
CREATE UNIQUE INDEX uq_accounts_active_email
ON accounts (email)
WHERE deleted_at IS NULL;常见陷阱
- NULL 和 UNIQUE: 大多数数据库允许多个
NULL中的值UNIQUE列,因为NULL不等于任何东西——包括它自己。SQL Server 是个例外,它将NULL默认情况下作为重复项;请使用过滤索引来解决此问题。 - 旧版本 MySQL 中的 CHECK: 如上所述,MySQL 8.0.16 之前的版本会解析但忽略
CHECK约束。验证您的 MySQL 版本或在应用程序代码中强制执行规则。 - 延迟约束: PostgreSQL 支持对声明的外键、唯一、主键和排除约束进行延迟检查
DEFERRABLE INITIALLY DEFERRED。CHECK 和 NOT NULL 不能延迟。这对于循环引用很有用,但可能会增加调试的难度。 - CHECK 的性能: 简单
CHECK约束的开销可以忽略不计。避免在其中嵌入子查询或函数调用CHECK— 它们甚至可能不受支持,并且会隐藏缓慢的逻辑。 - 更改大型表上的约束: 添加
NOT NULLPostgreSQL 中的约束可能需要进行表扫描,除非现有经过验证的 CHECK 证明该列不包含空值。对于非常大的表,请考虑添加CHECK (column IS NOT NULL) NOT VALID首先,然后单独验证。
在以下位置同时审查 SQL 和图表中的约束: SQL转ERD编辑器, 然后在目标数据库中验证它们的行为。图表不强制执行数据完整性。



