pgsql-05 约束详解

2025-09-25 约 2574 字预计阅读 6 分钟

1. 概述

**约束（Constraint）**是数据库在存储层面强制保证数据完整性的规则。把校验放在数据库层而非全靠应用层，好处是：无论数据从哪个入口写入（应用、脚本、手工 SQL）都无法绕过，是数据正确性的最后一道防线。

PostgreSQL 支持六种基础约束（主键、唯一、外键、检查、非空、默认）以及一个 MySQL 没有的强大特色——排他约束（EXCLUDE）。本文逐一讲解，并突出与 MySQL 的差异和面试点。

索引与约束关系密切（主键/唯一约束底层依赖索引），但索引本身是独立的大主题，已单独成篇，见索引原理与类型。

2. 六种基础约束

2.1 主键约束(PRIMARY KEY)

-- 方式1: 创建表时定义
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL
);

-- 方式2: 命名主键
CREATE TABLE products (
    id SERIAL,
    sku VARCHAR(50) NOT NULL,
    name VARCHAR(200),
    CONSTRAINT pk_products PRIMARY KEY (id)
);

-- 方式3: 复合主键
CREATE TABLE order_items (
    order_id INTEGER,
    product_id INTEGER,
    quantity INTEGER,
    price NUMERIC(10, 2),
    PRIMARY KEY (order_id, product_id)
);

-- 添加主键约束
ALTER TABLE categories ADD PRIMARY KEY (id);

-- 删除主键约束
ALTER TABLE categories DROP CONSTRAINT categories_pkey;

与MySQL对比:

PostgreSQL主键自动创建唯一索引
MySQL主键是聚簇索引(InnoDB)

2.2 唯一约束(UNIQUE)

-- 单列唯一
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE,
    email VARCHAR(100) UNIQUE NOT NULL
);

-- 命名唯一约束
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    sku VARCHAR(50),
    CONSTRAINT uk_products_sku UNIQUE (sku)
);

-- 复合唯一约束
CREATE TABLE user_roles (
    user_id INTEGER,
    role_id INTEGER,
    UNIQUE (user_id, role_id)
);

-- 部分唯一(PostgreSQL特色)
CREATE UNIQUE INDEX uk_active_users_email
ON users(email) WHERE is_active = TRUE;

-- 添加唯一约束
ALTER TABLE users ADD CONSTRAINT uk_users_email UNIQUE (email);

-- 删除唯一约束
ALTER TABLE users DROP CONSTRAINT uk_users_email;

2.3 外键约束(FOREIGN KEY)

-- 基本外键
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    total_amount NUMERIC(10, 2)
);

-- 命名外键
CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INTEGER,
    product_id INTEGER,
    CONSTRAINT fk_order_items_order FOREIGN KEY (order_id) REFERENCES orders(id),
    CONSTRAINT fk_order_items_product FOREIGN KEY (product_id) REFERENCES products(id)
);

-- 级联操作
CREATE TABLE comments (
    id SERIAL PRIMARY KEY,
    post_id INTEGER REFERENCES posts(id) ON DELETE CASCADE,
    user_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
    content TEXT
);

-- ON DELETE选项:
-- CASCADE: 删除父记录时,删除子记录
-- SET NULL: 删除父记录时,子记录外键设为NULL
-- SET DEFAULT: 设为默认值
-- RESTRICT: 有子记录时禁止删除(默认)
-- NO ACTION: 同RESTRICT

-- ON UPDATE选项(同上)
CREATE TABLE order_logs (
    id SERIAL PRIMARY KEY,
    order_id INTEGER REFERENCES orders(id)
        ON UPDATE CASCADE
        ON DELETE CASCADE
);

-- 添加外键
ALTER TABLE orders
ADD CONSTRAINT fk_orders_user
FOREIGN KEY (user_id) REFERENCES users(id);

-- 删除外键
ALTER TABLE orders DROP CONSTRAINT fk_orders_user;

2.4 检查约束(CHECK)

-- 单列检查
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200),
    price NUMERIC(10, 2) CHECK (price >= 0),
    stock INTEGER CHECK (stock >= 0)
);

-- 命名检查约束
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    age INTEGER,
    salary NUMERIC(10, 2),
    CONSTRAINT chk_employees_age CHECK (age >= 18 AND age <= 65),
    CONSTRAINT chk_employees_salary CHECK (salary > 0)
);

-- 多列检查
CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    start_date DATE,
    end_date DATE,
    CHECK (end_date >= start_date)
);

-- 复杂检查
CREATE TABLE discounts (
    id SERIAL PRIMARY KEY,
    discount_type VARCHAR(20),
    discount_value NUMERIC(5, 2),
    CHECK (
        (discount_type = 'percentage' AND discount_value BETWEEN 0 AND 100) OR
        (discount_type = 'fixed' AND discount_value >= 0)
    )
);

-- 添加检查约束
ALTER TABLE products
ADD CONSTRAINT chk_price_positive CHECK (price >= 0);

-- 删除检查约束
ALTER TABLE products DROP CONSTRAINT chk_price_positive;

2.5 NOT NULL约束

-- 创建表时定义
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(20)  -- 可为NULL
);

-- 添加NOT NULL
ALTER TABLE users ALTER COLUMN phone SET NOT NULL;

-- 删除NOT NULL
ALTER TABLE users ALTER COLUMN phone DROP NOT NULL;

2.6 DEFAULT约束

-- 默认值
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    status VARCHAR(20) DEFAULT 'draft',
    view_count INTEGER DEFAULT 0,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    is_published BOOLEAN DEFAULT FALSE
);

-- 表达式默认值
CREATE TABLE logs (
    id SERIAL PRIMARY KEY,
    log_date DATE DEFAULT CURRENT_DATE,
    random_id UUID DEFAULT gen_random_uuid()
);

-- 修改默认值
ALTER TABLE posts ALTER COLUMN status SET DEFAULT 'pending';

-- 删除默认值
ALTER TABLE posts ALTER COLUMN status DROP DEFAULT;

3. 排他约束（EXCLUDE，PostgreSQL 特色）

排他约束是 PG 独有的强大能力，MySQL 无法用约束直接实现。它保证"任意两行在某种运算下不能同时成立"，最经典的用途是防止时间段/空间重叠。

-- 需要 GiST 索引支持
CREATE EXTENSION IF NOT EXISTS btree_gist;

-- 会议室预订：同一房间的时间段不能重叠
CREATE TABLE reservations (
    id       SERIAL PRIMARY KEY,
    room_id  INTEGER,
    during   TSRANGE,        -- 时间范围类型
    -- room_id 相等 且 时间段重叠(&&) 的两行不允许同时存在
    EXCLUDE USING GiST (room_id WITH =, during WITH &&)
);

INSERT INTO reservations (room_id, during)
    VALUES (1, tsrange('2026-07-01 09:00', '2026-07-01 10:00'));   -- OK
INSERT INTO reservations (room_id, during)
    VALUES (1, tsrange('2026-07-01 09:30', '2026-07-01 11:00'));   -- 报错：与上一条重叠

用普通唯一约束无法表达"重叠"，只能靠应用层加锁查询判断（有并发漏洞）。排他约束在数据库层原子地保证，是 PG 的杀手锏。范围类型见数组与范围类型。

4. 约束的高级特性

4.1 延迟约束（DEFERRABLE）

默认约束在每条语句执行后立即检查。延迟约束可推迟到事务提交时才检查，用于解决"循环外键/互相引用"或批量调整时的中间态问题。

CREATE TABLE nodes (
    id     INTEGER PRIMARY KEY,
    next_id INTEGER REFERENCES nodes(id) DEFERRABLE INITIALLY DEFERRED
);

BEGIN;
  -- 插入时互相引用，若立即检查会失败；延迟到提交才校验
  INSERT INTO nodes VALUES (1, 2), (2, 1);
COMMIT;   -- 此时才检查外键，通过

DEFERRABLE INITIALLY IMMEDIATE：可延迟但默认立即（可在事务内 SET CONSTRAINTS ... DEFERRED）。
DEFERRABLE INITIALLY DEFERRED：默认延迟到提交。

4.2 NOT VALID：给大表加约束不阻塞

给已有大量数据的表加约束时，全表校验会长时间锁表。用 NOT VALID 先只对新写入生效、跳过存量校验，之后再择机 VALIDATE（不锁写）。

-- 1) 先加约束但不校验存量数据（很快，短暂锁）
ALTER TABLE orders ADD CONSTRAINT chk_amount CHECK (amount >= 0) NOT VALID;
-- 2) 业务低峰再校验存量（只加 SHARE UPDATE EXCLUSIVE，不阻塞读写）
ALTER TABLE orders VALIDATE CONSTRAINT chk_amount;

这是生产环境给大表加约束的标准手法。

4.3 查看与管理约束

-- 查看某表所有约束
SELECT conname, contype, pg_get_constraintdef(oid)
FROM pg_constraint
WHERE conrelid = 'orders'::regclass;
-- contype: p=主键 u=唯一 f=外键 c=检查 x=排他

-- 删除约束
ALTER TABLE orders DROP CONSTRAINT chk_amount;

5. PostgreSQL vs MySQL 约束对比

特性	PostgreSQL	MySQL
CHECK 约束	一直完整支持	8.0.16 前会被静默忽略，8.0.16+ 才真正生效
外键	完整支持	InnoDB 支持，MyISAM 不支持
排他约束 EXCLUDE	✅ 独有	❌ 不支持
延迟约束 DEFERRABLE	✅ 支持	❌ 不支持（外键无法延迟）
NOT VALID 加约束	✅ 支持	❌ 无对应机制
部分唯一	✅ 部分唯一索引	❌ 需变通
主键底层	唯一索引（非聚簇）	聚簇索引

面试要点：MySQL 早期版本（8.0.16 之前）的 CHECK 约束是"语法接受但完全不生效"的，很多老项目的数据完整性其实没被保护——这是常被问到的坑；PostgreSQL 则一贯严格执行。

6. 面试高频问答

Q1：约束应该放在数据库层还是应用层？ 关键完整性约束（主键、外键、非空、唯一、值域 CHECK）应放数据库层，因为它无法被任何写入入口绕过，是最后防线；应用层校验用于提升体验和减少无效请求，两者互补而非替代。

Q2：PostgreSQL 有什么 MySQL 没有的约束能力？ 排他约束（EXCLUDE，防时间/空间重叠）、延迟约束（DEFERRABLE，提交时才校验）、NOT VALID 加约束（大表不阻塞）、部分唯一（带 WHERE 的唯一索引），这些 MySQL 都不支持。

Q3：外键有什么级联选项？ ON DELETE / ON UPDATE 可设 CASCADE（级联）、SET NULL、SET DEFAULT、RESTRICT（默认，有子记录禁止）、NO ACTION。

Q4：给一张亿级大表加 CHECK 约束，如何不长时间锁表？ 先用 ADD CONSTRAINT ... NOT VALID 只对新数据生效（短暂锁），再在低峰 VALIDATE CONSTRAINT 校验存量（不阻塞读写）。

Q5：什么场景用延迟约束？ 互相引用的外键、需要在事务中间临时违反约束、批量重排序等场景，用 DEFERRABLE INITIALLY DEFERRED 把校验推迟到事务提交时统一进行。

7. 下一步

学习基础查询语句，开始掌握数据检索。

目录