PostgreSQL: PostgreSQL约束与数据完整性

最后更新：2026-08-26

1. 你将学到

PRIMARY KEY（单列/复合主键）
FOREIGN KEY（CASCADE / SET NULL / SET DEFAULT / NO ACTION / RESTRICT）
UNIQUE 约束
NOT NULL 约束
CHECK 约束（PostgreSQL 特色：可引用其他行）
EXCLUSION 排他约束（PostgreSQL 特色：如时间范围不重叠）
DEFERRABLE 延迟约束（PostgreSQL 特色）
约束命名与管理

2. 故事

Charlie 是 SaaS 平台的数据库架构师。他需要确保以下业务规则在数据库层面强制执行：

每笔订单金额必须 > 0（CHECK）
会议室预订的时间范围不能重叠（EXCLUSION）
删除客户时自动清理其订单，但关键订单必须保护（FOREIGN KEY 级联策略）
批量导入数据时，行之间的引用可能暂时违反约束，导入完成后再检查（DEFERRABLE）

Charlie 用约束代替应用层校验，让数据库成为数据完整性的最后防线。

3. Concept：PRIMARY KEY

(1) 主键的作用

主键唯一标识表中的每一行，兼具 UNIQUE + NOT NULL 特性。每张表只能有一个主键。

特性	说明
唯一性	不允许重复值
非空	不允许 NULL
自动创建索引	PostgreSQL 自动为主键创建 B-Tree 唯一索引
每表仅一个	只能定义一个 PRIMARY KEY

▶ 示例：单列主键

SQL

CREATE TABLE customers (
  customer_id BIGSERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) UNIQUE
);

TEXT 📖 仅展示

CREATE TABLE

▶ 示例：复合主键

SQL

CREATE TABLE order_items (
  order_id   BIGINT NOT NULL,
  product_id BIGINT NOT NULL,
  quantity   INT NOT NULL CHECK (quantity > 0),
  PRIMARY KEY (order_id, product_id)
);

TEXT 📖 仅展示

CREATE TABLE

(2) 主键列类型对比

类型	存储	范围	适用场景
SERIAL / BIGSERIAL	4/8 字节	2B / 9.2×10¹⁸	大多数业务表
UUID	16 字节	全局唯一	分布式系统
自然键（如 email）	可变	—	少用，业务变化风险
复合主键	多列	—	关联表、多对多中间表

▶ 示例：UUID 主键

SQL

CREATE TABLE global_events (
  event_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  event_name VARCHAR(200) NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

TEXT 📖 仅展示

CREATE TABLE

4. Concept：FOREIGN KEY

(1) 外键与五种级联策略

外键保证引用完整性：子表的引用列值必须存在于父表的主键/唯一键中。

策略	ON DELETE 行为	ON UPDATE 行为	典型场景
CASCADE	级联删除子行	级联更新子行	订单明细随订单删除
SET NULL	子行设 NULL	子行设 NULL	可选关联，删除不影响
SET DEFAULT	子行设默认值	子行设默认值	较少使用
RESTRICT	拒绝删除（立即）	拒绝更新（立即）	保护关键数据
NO ACTION	拒绝删除（可延迟）	拒绝更新（可延迟）	默认行为

▶ 示例：CASCADE 级联删除

SQL

CREATE TABLE orders (
  order_id    BIGSERIAL PRIMARY KEY,
  customer_id BIGINT NOT NULL
    REFERENCES customers(customer_id) ON DELETE CASCADE,
  total_amount DECIMAL(12,2) NOT NULL
);

TEXT 📖 仅展示

CREATE TABLE

删除客户时，其所有订单自动删除。

▶ 示例：RESTRICT 保护关键数据

SQL

CREATE TABLE invoices (
  invoice_id  BIGSERIAL PRIMARY KEY,
  order_id    BIGINT NOT NULL
    REFERENCES orders(order_id) ON DELETE RESTRICT,
  invoice_date DATE NOT NULL
);

TEXT 📖 仅展示

CREATE TABLE

如果订单已有发票，删除订单会被拒绝。

(2) CASCADE vs RESTRICT 对比

维度	CASCADE	RESTRICT
删除父行	子行一起删除	拒绝删除
数据安全	方便但有风险	安全但需手动清理
适用	附属数据	关键/财务数据

▶ 示例：多级级联

SQL

CREATE TABLE customers (
  customer_id BIGSERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

CREATE TABLE orders (
  order_id    BIGSERIAL PRIMARY KEY,
  customer_id BIGINT REFERENCES customers(customer_id) ON DELETE CASCADE
);

CREATE TABLE order_items (
  item_id     BIGSERIAL PRIMARY KEY,
  order_id    BIGINT REFERENCES orders(order_id) ON DELETE CASCADE,
  product_id  BIGINT NOT NULL
);

TEXT 📖 仅展示

CREATE TABLE

删除 customer → 自动删除 orders → 自动删除 order_items，三级级联。

▶ 示例：SET NULL

SQL

CREATE TABLE reviews (
  review_id   BIGSERIAL PRIMARY KEY,
  product_id  BIGINT REFERENCES products(product_id) ON DELETE SET NULL,
  content     TEXT NOT NULL
);

TEXT 📖 仅展示

CREATE TABLE

产品删除后，评论的 product_id 设为 NULL，评论保留。

5. Concept：UNIQUE 与 NOT NULL

(1) UNIQUE 约束

UNIQUE 保证列值（或列组合）不重复，允许 NULL（多个 NULL 不算重复）。

维度	PRIMARY KEY	UNIQUE
每表数量	仅一个	可多个
NULL 允许	不允许	允许（多个 NULL 不冲突）
自动索引	是	是
语义	标识行	保证唯一性

▶ 示例：多列 UNIQUE 约束

SQL

CREATE TABLE user_accounts (
  user_id BIGSERIAL PRIMARY KEY,
  email   VARCHAR(255) UNIQUE,
  phone   VARCHAR(20),
  UNIQUE (email, phone)
);

TEXT 📖 仅展示

CREATE TABLE

email 单列唯一 + (email, phone) 组合唯一。

(2) NOT NULL 约束

NOT NULL 禁止列存储 NULL 值，是最基本的完整性约束。

写法	说明
`col TYPE NOT NULL`	列级约束
`CONSTRAINT nn_col CHECK (col IS NOT NULL)`	等价写法

▶ 示例：NOT NULL 组合

SQL

CREATE TABLE products (
  product_id   BIGSERIAL PRIMARY KEY,
  name         VARCHAR(200) NOT NULL,
  unit_price   DECIMAL(10,2) NOT NULL,
  description  TEXT
);

TEXT 📖 仅展示

CREATE TABLE

description 允许 NULL，其余关键列不允许。

6. Concept：CHECK 约束

(1) CHECK 约束基础

CHECK 约束要求列值必须满足指定布尔表达式。PostgreSQL 的 CHECK 可以引用同一行中其他列（SQL 标准也支持，但很多数据库不支持）。

特性	说明
行级检查	只能引用当前行的列
PostgreSQL 特色	可引用其他行（通过子查询，但有限制）
表级 CHECK	可同时约束多列
NO INHERIT	不传递到子表（PG 特色）

▶ 示例：金额必须为正

SQL

CREATE TABLE orders (
  order_id    BIGSERIAL PRIMARY KEY,
  total_amount DECIMAL(12,2) NOT NULL CHECK (total_amount > 0),
  discount    DECIMAL(12,2) DEFAULT 0 CHECK (discount >= 0)
);

TEXT 📖 仅展示

CREATE TABLE

▶ 示例：多列 CHECK 约束

SQL

CREATE TABLE campaigns (
  campaign_id BIGSERIAL PRIMARY KEY,
  start_date  DATE NOT NULL,
  end_date    DATE NOT NULL,
  budget      DECIMAL(12,2) NOT NULL CHECK (budget > 0),
  CONSTRAINT chk_date_range CHECK (end_date >= start_date),
  CONSTRAINT chk_budget_limit CHECK (budget <= 1000000)
);

TEXT 📖 仅展示

CREATE TABLE

(2) 常见 CHECK 模式

业务规则	CHECK 表达式
金额为正	`CHECK (amount > 0)`
折扣范围	`CHECK (discount BETWEEN 0 AND 1)`
日期先后	`CHECK (end_date >= start_date)`
枚举值	`CHECK (status IN ('active', 'inactive', 'pending'))`
字符串长度	`CHECK (LENGTH(phone) >= 10)`
百分比	`CHECK (rate >= 0 AND rate <= 100)`

▶ 示例：命名约束便于管理

SQL

CREATE TABLE subscriptions (
  sub_id    BIGSERIAL PRIMARY KEY,
  plan      VARCHAR(50) NOT NULL,
  price     DECIMAL(10,2) NOT NULL,
  CONSTRAINT chk_price_positive CHECK (price > 0),
  CONSTRAINT chk_plan_valid CHECK (plan IN ('free', 'basic', 'pro', 'enterprise'))
);

TEXT 📖 仅展示

CREATE TABLE

▶ 示例：修改现有表添加 CHECK

SQL

ALTER TABLE orders
ADD CONSTRAINT chk_amount_positive CHECK (total_amount > 0);

TEXT 📖 仅展示

-- SQL 语句执行成功

7. Concept：EXCLUSION 排他约束

(1) EXCLUSION 约束原理

EXCLUSION 约束保证：如果两行在指定列上"相等"（用 = 运算符比较），则在指定维度上不"重叠"（用重叠运算符比较）。这是 PostgreSQL 特色功能。

场景	约束	运算符
时间范围不重叠	同一资源的时间不能交叉	`=, &&`
座位不重复	同一场次的座位号不重复	`=, =`（等价 UNIQUE）

▶ 示例：会议室预订时间不重叠

SQL

CREATE TABLE room_bookings (
  booking_id BIGSERIAL PRIMARY KEY,
  room_id    INT NOT NULL,
  time_range TSTZRANGE NOT NULL,
  booked_by  VARCHAR(100) NOT NULL,
  CONSTRAINT excl_room_no_overlap
    EXCLUDE USING GiST (room_id WITH =, time_range WITH &&)
);

TEXT 📖 仅展示

CREATE TABLE

▶ 示例：插入冲突

SQL

INSERT INTO room_bookings (room_id, time_range, booked_by)
VALUES (1, '[2025-07-13 09:00, 2025-07-13 11:00)', 'Alice');

INSERT INTO room_bookings (room_id, time_range, booked_by)
VALUES (1, '[2025-07-13 10:00, 2025-07-13 12:00)', 'Bob');

TEXT 📖 仅展示

ERROR: conflicting key value violates exclusion constraint "excl_room_no_overlap"
DETAIL: Key (room_id, time_range)=(1, [2025-07-13 10:00,2025-07-13 12:00))
conflicts with existing key (room_id, time_range)=(1, [2025-07-13 09:00,2025-07-13 11:00)).

(2) EXCLUSION vs UNIQUE 对比

维度	UNIQUE	EXCLUSION
比较运算符	仅 `=`	自定义（=、&&、<->等）
范围重叠	不支持	支持
索引类型	B-Tree	GiST / SP-GiST
灵活性	低	高
适用	离散值唯一	范围不重叠、距离约束

▶ 示例：价格区间不重叠

SQL

CREATE TABLE discount_tiers (
  tier_id    BIGSERIAL PRIMARY KEY,
  product_id INT NOT NULL,
  price_range NUMRANGE NOT NULL,
  discount   DECIMAL(5,4) NOT NULL,
  CONSTRAINT excl_price_no_overlap
    EXCLUDE USING GiST (product_id WITH =, price_range WITH &&)
);

TEXT 📖 仅展示

CREATE TABLE

8. Concept：DEFERRABLE 延迟约束

(1) 延迟约束机制

默认情况下，约束在每条语句执行后立即检查。DEFERRABLE 约束允许将检查推迟到事务提交时，解决批量操作中的循环引用问题。

模式	检查时机	语法
IMMEDIATE	每条语句后	默认行为
DEFERRABLE INITIALLY IMMEDIATE	每条语句后（可切换）	DEFERRABLE INITIALLY IMMEDIATE
DEFERRABLE INITIALLY DEFERRED	事务提交时	DEFERRABLE INITIALLY DEFERRED

▶ 示例：循环引用的解决方案

SQL

CREATE TABLE departments (
  dept_id   INT PRIMARY KEY,
  name      VARCHAR(100) NOT NULL,
  manager_id INT
);

ALTER TABLE departments
ADD CONSTRAINT fk_dept_manager
  FOREIGN KEY (manager_id) REFERENCES departments(dept_id)
  DEFERRABLE INITIALLY DEFERRED;

TEXT 📖 仅展示

CREATE TABLE

▶ 示例：事务内操作

SQL

BEGIN;

INSERT INTO departments (dept_id, name, manager_id)
VALUES (1, 'Engineering', 2);

INSERT INTO departments (dept_id, name, manager_id)
VALUES (2, 'QA', 1);

COMMIT;

TEXT 📖 仅展示

INSERT 0 1

单独执行第一条 INSERT 会失败（manager_id=2 还不存在），但 DEFERRABLE 让检查推迟到 COMMIT，两条 INSERT 都完成后验证通过。

(2) 动态切换约束检查时机

▶ 示例：SET CONSTRAINTS

SQL

BEGIN;
SET CONSTRAINTS fk_dept_manager DEFERRED;

INSERT INTO departments (dept_id, name, manager_id) VALUES (3, 'Sales', 4);
INSERT INTO departments (dept_id, name, manager_id) VALUES (4, 'Marketing', 3);

SET CONSTRAINTS fk_dept_manager IMMEDIATE;
COMMIT;

TEXT 📖 仅展示

INSERT 0 1

(3) IMMEDIATE vs DEFERRED 对比

维度	IMMEDIATE	DEFERRED
检查时机	每条语句后	事务提交时
性能	更快（即时反馈）	稍慢（批量检查）
循环引用	无法解决	可解决
风险	低	事务末尾才发现违规
适用	大多数约束	循环引用、批量导入

9. Concept：约束命名与管理

(1) 约束命名规范

好命名便于定位问题和运维操作。

约束类型	推荐前缀	示例
PRIMARY KEY	pk_	pk_orders
FOREIGN KEY	fk_	fk_orders_customer
UNIQUE	uq_	uq_users_email
CHECK	chk_	chk_amount_positive
EXCLUSION	excl_	excl_room_no_overlap

▶ 示例：显式命名所有约束

SQL

CREATE TABLE orders (
  order_id     BIGSERIAL      CONSTRAINT pk_orders PRIMARY KEY,
  customer_id  BIGINT NOT NULL CONSTRAINT fk_orders_customer
    REFERENCES customers(customer_id) ON DELETE CASCADE,
  total_amount DECIMAL(12,2)  CONSTRAINT chk_amount_positive CHECK (total_amount > 0),
  status       VARCHAR(20)    CONSTRAINT chk_status_valid
    CHECK (status IN ('pending', 'shipped', 'delivered', 'cancelled')),
  order_date   TIMESTAMPTZ    NOT NULL DEFAULT NOW()
);

TEXT 📖 仅展示

CREATE TABLE

(2) 约束管理操作

操作	语法
添加约束	`ALTER TABLE t ADD CONSTRAINT name CHECK (...)`
删除约束	`ALTER TABLE t DROP CONSTRAINT name`
查看约束	`SELECT * FROM pg_constraint WHERE conrelid = 't'::regclass`
禁用触发器（间接禁用约束）	`ALTER TABLE t DISABLE TRIGGER ALL`

▶ 示例：查看表的所有约束

SQL

SELECT
  conname AS constraint_name,
  contype AS type,
  pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE conrelid = 'orders'::regclass;

TEXT 📖 仅展示

 constraint_name       | type | definition
-----------------------+------+--------------------------------------------
 pk_orders             | p    | PRIMARY KEY (order_id)
 fk_orders_customer    | f    | FOREIGN KEY (customer_id) REFERENCES ...
 chk_amount_positive   | c    | CHECK ((total_amount > 0))
 chk_status_valid      | c    | CHECK ((status = ANY (ARRAY['pending'::...

10. Comprehensive Example

Charlie 的 SaaS 平台约束方案——涵盖主键、外键级联、CHECK、EXCLUSION、DEFERRABLE：

SQL

CREATE TABLE customers (
  customer_id BIGSERIAL    CONSTRAINT pk_customers PRIMARY KEY,
  name        VARCHAR(100) NOT NULL,
  email       VARCHAR(255) CONSTRAINT uq_customers_email UNIQUE,
  credit_limit DECIMAL(12,2) DEFAULT 0
    CONSTRAINT chk_credit_non_negative CHECK (credit_limit >= 0)
);

CREATE TABLE orders (
  order_id     BIGSERIAL      CONSTRAINT pk_orders PRIMARY KEY,
  customer_id  BIGINT NOT NULL CONSTRAINT fk_orders_customer
    REFERENCES customers(customer_id) ON DELETE RESTRICT,
  total_amount DECIMAL(12,2) NOT NULL
    CONSTRAINT chk_amount_positive CHECK (total_amount > 0),
  status       VARCHAR(20) DEFAULT 'pending'
    CONSTRAINT chk_status CHECK (status IN ('pending','shipped','delivered','cancelled')),
  created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE room_bookings (
  booking_id BIGSERIAL   CONSTRAINT pk_bookings PRIMARY KEY,
  room_id    INT NOT NULL,
  time_range TSTZRANGE NOT NULL,
  booked_by  VARCHAR(100) NOT NULL,
  CONSTRAINT excl_room_no_overlap
    EXCLUDE USING GiST (room_id WITH =, time_range WITH &&)
);

ALTER TABLE orders
ADD CONSTRAINT fk_orders_customer_deferred
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
  ON DELETE RESTRICT
  DEFERRABLE INITIALLY IMMEDIATE;

11. 执行流程

约束检查在 DML 语句执行中的位置：

flowchart TD
    A[INSERT/UPDATE/DELETE] --> B{NOT NULL 检查}
    B -->|通过| C{CHECK 检查}
    B -->|失败| Z[报错回滚]
    C -->|通过| D{UNIQUE 检查}
    C -->|失败| Z
    D -->|通过| E{PRIMARY KEY 检查}
    D -->|失败| Z
    E -->|通过| F{EXCLUSION 检查}
    E -->|失败| Z
    F -->|通过| G{约束时机?}
    F -->|失败| Z
    G -->|IMMEDIATE| H[立即检查 FK]
    G -->|DEFERRED| I[推迟到 COMMIT 时检查 FK]
    H -->|通过| J[语句完成]
    I --> K[COMMIT]
    K --> L[检查所有 DEFERRED FK]
    L -->|通过| M[事务提交成功]
    L -->|失败| Z

    style Z fill:#ffccbc
    style J fill:#c8e6c9
    style M fill:#c8e6c9

❓ 常见问题

Q RESTRICT 和 NO ACTION 有什么区别？

A 功能几乎相同，都拒绝删除/更新。区别是 NO ACTION 可以配合 DEFERRABLE 推迟检查，RESTRICT 总是立即检查。

Q UNIQUE 约束允许多个 NULL 吗？

A PostgreSQL 中 UNIQUE 列允许多个 NULL，因为 NULL != NULL。如需限制 NULL 也唯一，加 NOT NULL 约束。

Q CHECK 约束能引用其他表的行吗？

A 可以写子查询，但 PostgreSQL 的 CHECK 只保证当前行的约束，不保证跨行一致性（其他会话可能修改引用数据）。跨表一致性用外键。

Q EXCLUSION 约束需要什么索引？

A 需要 GiST 或 SP-GiST 索引支持。PostgreSQL 自动为 EXCLUSION 约束创建对应索引。

Q DEFERRABLE 能用于 CHECK 约束吗？

A 不能。DEFERRABLE 只适用于 FOREIGN KEY 和 UNIQUE 约束。CHECK 和 NOT NULL 总是立即检查。

Q 大量 CASCADE 删除会影响性能吗？

A 会。级联删除逐行执行，可能产生大量锁和 I/O。大批量删除建议先手动删子表数据，再删父表行，避免长事务。

📖 小节

PRIMARY KEY = UNIQUE + NOT NULL，每表仅一个，自动创建索引
FOREIGN KEY 五种级联策略：CASCADE / SET NULL / SET DEFAULT / RESTRICT / NO ACTION
CASCADE 方便但有风险，RESTRICT 安全但需手动清理
UNIQUE 允许多个 NULL，PRIMARY KEY 不允许任何 NULL
CHECK 约束可引用同行其他列，适合多列逻辑校验
EXCLUSION 排他约束（PG 特色）保证范围不重叠，如会议室预订
DEFERRABLE 延迟约束（PG 特色）解决循环引用和批量导入问题
约束显式命名便于管理和运维，推荐 pk_/fk_/uq_/chk_/excl_ 前缀

📝 作业

⭐ 创建 customers 和 orders 表，定义主键和外键（ON DELETE CASCADE），并添加 CHECK 约束确保订单金额 > 0
⭐ 创建一个 products 表，包含 UNIQUE 的 SKU 列和 CHECK 约束确保 price > 0 且 discount <= price
⭐⭐ 使用 EXCLUSION 约束创建 room_bookings 表，确保同一会议室的时间范围不重叠，并测试冲突插入
⭐⭐ 创建两个互相引用的表（employees 引用 departments，departments 的 manager_id 引用 employees），用 DEFERRABLE 解决循环引用
⭐⭐⭐ 设计一个完整的 SaaS 租户数据模型，包含：租户表、用户表（外键级联）、订阅表（CHECK 验证日期和金额）、资源预订表（EXCLUSION 防止时间重叠），并为所有约束命名

Web-Tutorial 技术团队

由多位开发者共同维护的编程教程平台。每篇教程由对应领域的开发者编写和审核，确保内容准确可靠。如发现任何问题，欢迎向我们反馈。

关于我们

PostgreSQL: PostgreSQL约束与数据完整性

1. 你将学到

2. 故事

3. Concept：PRIMARY KEY

(1) 主键的作用

▶ 示例：单列主键

▶ 示例：复合主键

(2) 主键列类型对比

▶ 示例：UUID 主键

4. Concept：FOREIGN KEY

(1) 外键与五种级联策略

▶ 示例：CASCADE 级联删除

▶ 示例：RESTRICT 保护关键数据

(2) CASCADE vs RESTRICT 对比

▶ 示例：多级级联

▶ 示例：SET NULL

5. Concept：UNIQUE 与 NOT NULL

(1) UNIQUE 约束

▶ 示例：多列 UNIQUE 约束

(2) NOT NULL 约束

▶ 示例：NOT NULL 组合

6. Concept：CHECK 约束

(1) CHECK 约束基础

▶ 示例：金额必须为正

▶ 示例：多列 CHECK 约束

(2) 常见 CHECK 模式

▶ 示例：命名约束便于管理

▶ 示例：修改现有表添加 CHECK

7. Concept：EXCLUSION 排他约束

(1) EXCLUSION 约束原理

▶ 示例：会议室预订时间不重叠

▶ 示例：插入冲突

(2) EXCLUSION vs UNIQUE 对比

▶ 示例：价格区间不重叠

8. Concept：DEFERRABLE 延迟约束

(1) 延迟约束机制

▶ 示例：循环引用的解决方案

▶ 示例：事务内操作

(2) 动态切换约束检查时机

▶ 示例：SET CONSTRAINTS

(3) IMMEDIATE vs DEFERRED 对比

9. Concept：约束命名与管理

(1) 约束命名规范

▶ 示例：显式命名所有约束

(2) 约束管理操作

▶ 示例：查看表的所有约束

10. Comprehensive Example

11. 执行流程

❓ 常见问题

📖 小节

📝 作业

🙏 帮我们做得更好