这是《PostgreSQL 零基础实战指南》的独立章节版。本章从概念、实操和生产排查三个视角展开，代码块保留了原书可直接运行的版本。 约束是数据库兜底的数据质量能力。应用校验、数据库约束和审计规则共同构成可靠模型。
6.1 建表模板
CREATE TABLE orders (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
order_no TEXT NOT NULL UNIQUE,
user_id BIGINT NOT NULL REFERENCES users(id),
status order_status NOT NULL DEFAULT 'CREATED',
amount NUMERIC(12,2) NOT NULL CHECK (amount >= 0),
paid_at TIMESTAMPTZ,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
6.2 主键
ALTER TABLE orders ADD PRIMARY KEY (id);
复合主键：
CREATE TABLE order_items (
order_id BIGINT NOT NULL,
line_no INT NOT NULL,
sku_id BIGINT NOT NULL,
PRIMARY KEY (order_id, line_no)
);
主键会自动创建唯一 B-Tree 索引。
6.3 唯一约束
ALTER TABLE users
ADD CONSTRAINT uq_users_email UNIQUE (email);
部分唯一索引：
CREATE UNIQUE INDEX uq_users_active_email
ON users(email)
WHERE deleted_at IS NULL;
适合软删除场景。
6.4 外键
ALTER TABLE orders
ADD CONSTRAINT fk_orders_user
FOREIGN KEY (user_id)
REFERENCES users(id)
ON DELETE RESTRICT;
策略：
| 策略 | 含义 |
|---|---|
| NO ACTION / RESTRICT | 禁止删除被引用行 |
| CASCADE | 级联删除或更新 |
| SET NULL | 置空引用列 |
| SET DEFAULT | 设置默认值 |
高写入系统要评估外键索引和锁开销，但不能只因为“麻烦”而放弃数据完整性。
6.5 CHECK 约束
ALTER TABLE orders
ADD CONSTRAINT ck_orders_paid_at
CHECK (status <> 'PAID' OR paid_at IS NOT NULL);
命名约束便于排障和迁移：
pk_ / uq_ / fk_ / ck_ 前缀
6.6 默认值与触发器
默认值：
ALTER TABLE orders
ALTER COLUMN created_at SET DEFAULT now();
更新时间触发器：
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS trigger AS $$
BEGIN
NEW.updated_at = now();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_orders_updated_at
BEFORE UPDATE ON orders
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();
触发器逻辑隐蔽，应有清单和审计。
6.7 表继承与分区
传统表继承较少用于新业务，分区表是更常见的物理拆分方式：
CREATE TABLE events (
id bigint,
event_date date NOT NULL
) PARTITION BY RANGE (event_date);
分区细节在第 13 章展开。
6.8 Schema 管理
CREATE SCHEMA sales;
ALTER TABLE sales.orders RENAME TO orders_2026;
COMMENT ON TABLE orders IS '订单主表';
多租户可用独立数据库、独立 Schema 或共享表 + RLS，成本和隔离性不同。
6.9 DDL 迁移
安全原则：
- 先在测试环境验证；
- 大表加列避免长时间锁；
- 建索引使用 CONCURRENTLY；
- 修改类型评估重写成本；
- 迁移可回滚或可补偿；
- 与应用发布顺序明确。
并发建索引：
CREATE INDEX CONCURRENTLY idx_orders_user_created
ON orders(user_id, created_at);
6.10 约束排查
查看约束：
SELECT
conname,
contype,
conrelid::regclass AS table_name
FROM pg_constraint
WHERE conrelid = 'orders'::regclass;
常见错误：
| 错误 | 含义 |
|---|---|
| duplicate key | 违反唯一约束 |
| foreign key violation | 外键不存在或被引用 |
| check constraint | 条件不满足 |
| not-null violation | 必填列空 |
本章小结
表结构设计的核心是明确业务键、状态流、时间口径和引用关系。约束让非法数据难以进入数据库，迁移则必须考虑锁时间和回滚方案。
思考题
- 唯一约束和部分唯一索引有什么差异？
- 外键删除策略如何选择？
- 大表建索引应注意什么？
- 什么时候使用触发器？
- 软删除如何影响唯一性？