PostgreSQL 约束

作者likuolei

PostgreSQL 约束（Constraints）完全指南

约束是数据库的“铁律”，保证 数据完整性、一致性、准确性。
本文涵盖 6 大类约束、定义方式、性能影响、级联操作、校验时机、JSON/数组约束、常见陷阱、最佳实践 等全部内容。

一、6 大类约束总览

类型作用示例
NOT NULL列不允许 NULLemail TEXT NOT NULL
UNIQUE列值唯一username TEXT UNIQUE
PRIMARY KEY唯一 + NOT NULLid BIGINT PRIMARY KEY
FOREIGN KEY外键引用manager_id BIGINT REFERENCES employees(id)
CHECK自定义条件CHECK (salary > 0)
EXCLUSION排他约束（高级）EXCLUDE USING gist (period WITH &&)

二、准备测试环境

-- 员工表
CREATE TABLE employees (
    id BIGSERIAL,
    username TEXT,
    email TEXT,
    department TEXT,
    salary NUMERIC(10,2),
    manager_id BIGINT,
    is_active BOOLEAN DEFAULT true,
    tags TEXT[] DEFAULT '{}',
    config JSONB,
    hire_date DATE,
    CONSTRAINT pk_employees PRIMARY KEY (id)
);

-- 部门表（外键引用）
CREATE TABLE departments (
    code VARCHAR(10),
    name TEXT,
    budget NUMERIC(12,2),
    CONSTRAINT pk_depts PRIMARY KEY (code)
);

三、1. NOT NULL 约束

ALTER TABLE employees 
ADD CONSTRAINT nn_email NOT NULL (email);

-- 或定义时
email TEXT NOT NULL

行为

  • 插入/更新 NULL → 报错
  • DEFAULT 可与 NOT NULL 共存
-- 推荐：NOT NULL + DEFAULT
email TEXT NOT NULL DEFAULT 'no-email@company.com'

四、2. UNIQUE 约束

-- 单列唯一
ALTER TABLE employees ADD CONSTRAINT uq_username UNIQUE (username);

-- 多列唯一（组合唯一）
ALTER TABLE employees ADD CONSTRAINT uq_email_dept UNIQUE (email, department);

索引自动创建

  • 每个 UNIQUE 约束 → 自动建 B 树索引
-- 查看索引
\d employees
-- 看到： "uq_username" UNIQUE, btree (username)

NULL 行为

  • NULL 不参与唯一性检查
  • 可插入多行 (NULL, 'IT')
INSERT INTO employees (email, department) VALUES (NULL, 'IT'), (NULL, 'IT'); -- 成功

五、3. PRIMARY KEY 约束

-- 推荐：显式定义
CONSTRAINT pk_employees PRIMARY KEY (id)

-- 等价于：id BIGINT PRIMARY KEY

特性

  • 隐含 NOT NULL + UNIQUE
  • 自动建唯一 B 树索引
  • 常被外键引用

六、4. FOREIGN KEY 外键约束

-- 基本外键
ALTER TABLE employees 
ADD CONSTRAINT fk_manager 
    FOREIGN KEY (manager_id) REFERENCES employees(id);

-- 带级联
ALTER TABLE employees 
ADD CONSTRAINT fk_dept 
    FOREIGN KEY (department) REFERENCES departments(code)
    ON DELETE SET NULL
    ON UPDATE CASCADE;

级联操作

操作说明
ON DELETE CASCADE删除父表 → 自动删子表
ON DELETE SET NULL删除父表 → 子表设 NULL
ON DELETE SET DEFAULT设为默认值
ON DELETE RESTRICT拒绝删除（默认）
ON UPDATE ...同上，更新时触发
-- 例子：删除部门 → 员工 department 设 NULL
DELETE FROM departments WHERE code = 'IT';
-- employees.department 自动变为 NULL

性能

  • 外键检查有开销
  • 批量导入建议 临时禁用
-- 临时禁用外键
ALTER TABLE employees DISABLE TRIGGER ALL;
-- ... 导入数据
ALTER TABLE employees ENABLE TRIGGER ALL;

七、5. CHECK 约束

-- 薪资 > 0
ALTER TABLE employees ADD CONSTRAINT chk_salary CHECK (salary > 0);

-- 部门合法
ALTER TABLE employees ADD CONSTRAINT chk_dept 
    CHECK (department IN ('IT', 'HR', 'Finance', 'Sales'));

-- 复杂条件
ALTER TABLE employees ADD CONSTRAINT chk_email_format
    CHECK (email ~* '^[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,}$');

表达式索引支持

-- 为 CHECK 加速（可选）
CREATE INDEX idx_emp_salary_pos ON employees(salary) WHERE salary > 0;

八、6. EXCLUSION 约束（高级）

防止重叠，常用于 时间段、范围、空间

-- 防止员工排班时间重叠
ALTER TABLE schedules 
ADD CONSTRAINT excl_schedule 
    EXCLUDE USING gist (employee_id WITH =, period WITH &&);

九、JSON 与数组约束

1. JSON 键存在

-- config 必须包含 'level'
ALTER TABLE employees ADD CONSTRAINT chk_config_level
    CHECK (config ? 'level');

2. 数组非空

-- tags 至少 1 个
ALTER TABLE employees ADD CONSTRAINT chk_tags_not_empty
    CHECK (array_length(tags, 1) > 0);

3. JSON 值范围

-- level 在 1-10
ALTER TABLE employees ADD CONSTRAINT chk_config_level_range
    CHECK ((config->>'level')::INT BETWEEN 1 AND 10);

十、约束命名与管理

推荐命名规范

-- 表名_列_约束类型
employees_email_nn
employees_username_uq
employees_manager_fk
employees_salary_chk

查看约束

-- 查看表所有约束
\d employees

-- 查询信息视图
SELECT conname, contype, pg_get_constraintdef(oid)
FROM pg_constraint 
WHERE conrelid = 'employees'::regclass;

删除约束

ALTER TABLE employees DROP CONSTRAINT uq_username;

十一、性能影响与优化

约束性能影响优化建议
NOT NULL几乎无必须
UNIQUE建索引，写慢读快必要时用
PRIMARY KEY必须优先 BIGSERIAL
FOREIGN KEY检查开销批量操作禁用
CHECK轻微简单表达式
EXCLUSION高（GIST）仅必要时
-- 批量导入优化
BEGIN;
ALTER TABLE employees DISABLE TRIGGER ALL;
COPY employees FROM '/data.csv' CSV HEADER;
ALTER TABLE employees ENABLE TRIGGER ALL;
COMMIT;

十二、常见陷阱与解决方案

陷阱说明解决方案
NULL 绕过 UNIQUE多 NULL 允许用 UNIQUE NULLS NOT DISTINCT（PG15+）
外键死锁并发更新按固定顺序更新
CHECK 复杂性能差拆分或用触发器
忘记 ON DELETE数据孤岛显式指定
约束名冲突迁移报错统一命名
-- PG15+：NULL 不重复
ALTER TABLE employees ADD CONSTRAINT uq_email UNIQUE NULLS NOT DISTINCT (email);

十三、最佳实践 Checklist

项目建议
所有列加 NOT NULL + DEFAULT避免 NULL 混乱
主键用 BIGSERIAL性能 + 自动
外键显式命名 + 级联可维护
唯一约束建索引自动完成
CHECK 简单有效避免复杂逻辑
批量操作禁用外键性能
约束统一命名便于管理

十四、一键完整建表脚本（生产级）

CREATE TABLE employees (
    id BIGSERIAL PRIMARY KEY,
    username TEXT NOT NULL,
    email TEXT NOT NULL DEFAULT 'no-email@company.com',
    department VARCHAR(20) NOT NULL,
    salary NUMERIC(10,2) NOT NULL DEFAULT 0,
    manager_id BIGINT,
    is_active BOOLEAN NOT NULL DEFAULT true,
    tags TEXT[] NOT NULL DEFAULT '{}',
    config JSONB NOT NULL DEFAULT '{}',
    hire_date DATE NOT NULL DEFAULT CURRENT_DATE,

    -- 约束
    CONSTRAINT uq_username UNIQUE (username),
    CONSTRAINT uq_email_dept UNIQUE (email, department),
    CONSTRAINT fk_manager 
        FOREIGN KEY (manager_id) REFERENCES employees(id)
        ON DELETE SET NULL,
    CONSTRAINT fk_dept 
        FOREIGN KEY (department) REFERENCES departments(code)
        ON DELETE RESTRICT,
    CONSTRAINT chk_salary CHECK (salary >= 0),
    CONSTRAINT chk_tags_not_empty CHECK (array_length(tags, 1) > 0),
    CONSTRAINT chk_config_level CHECK (config ? 'level'),
    CONSTRAINT chk_email_format CHECK (email ~* '^[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,}$')
);

-- 索引（UNIQUE 已自动建）
CREATE INDEX idx_emp_dept ON employees(department);
CREATE INDEX idx_emp_manager ON employees(manager_id);

十五、快速实战：5 分钟掌握约束

-- 1. 添加 NOT NULL
ALTER TABLE employees ALTER COLUMN email SET NOT NULL;

-- 2. 添加唯一约束
ALTER TABLE employees ADD CONSTRAINT uq_username UNIQUE (username);

-- 3. 添加外键 + 级联
ALTER TABLE employees ADD CONSTRAINT fk_dept 
    FOREIGN KEY (department) REFERENCES departments(code)
    ON DELETE SET NULL;

-- 4. 添加 CHECK
ALTER TABLE employees ADD CONSTRAINT chk_salary CHECK (salary > 0);

-- 5. 查看约束
\d employees

现在就动手：
在 employees 表中：

  1. 为 email 添加 NOT NULL + DEFAULT
  2. 为 (username, department) 添加组合 UNIQUE
  3. 添加外键 manager_id → employees.id，ON DELETE SET NULL
  4. 添加 CHECK (salary BETWEEN 30000 AND 500000)
  5. 用 \d 查看所有约束和索引

十六、约束状态检查

-- 查看无效约束
SELECT conname, convalidated 
FROM pg_constraint 
WHERE conrelid = 'employees'::regclass AND NOT convalidated;

需要我生成：

  • 1000 条带约束测试数据？回复 测试数据
  • 自动生成约束 DDL（Python）？回复 Python 约束
  • 软删除 vs 外键约束设计？回复 软删除设计
  • 约束迁移与版本控制？回复 约束迁移

随时告诉我！

类似文章

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注