Home

深色模式

PostgreSQL 建表与约束 ​

概述 ​

到了建表这一步，重点已经不是“PostgreSQL 能不能做”，而是“数据库层到底替业务兜住哪些规则”。表设计写得清楚，应用代码会轻很多；约束没建，很多问题最后都会变成线上脏数据。

这一篇只讲最常见的表设计动作：主键、默认值、非空、唯一约束、检查约束、外键，还有几个建表时特别容易偷懒的地方。中小项目把这些先写进数据库，后面排障会省心很多。

表结构骨架 ​

很多业务表都可以从这类骨架开始：

sql
CREATE TABLE users (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    email TEXT NOT NULL,
    nickname TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

这几个字段看起来朴素，但意义很明确：

  • id：主键
  • email、nickname：业务字段
  • created_at、updated_at：记录创建和修改时间

先把骨架写完整，再补业务约束，通常比一边开发一边随手加列更稳。

更新时间列 ​

很多人第一次建表时会写上：

sql
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()

然后以为以后每次 UPDATE 都会自动刷新。其实不会。这个默认值只在 INSERT 时生效。

后面想让它跟着更新，常见做法有两种：

  • 由应用层在 UPDATE 时显式写 updated_at = NOW()
  • 用触发器统一维护

中小项目起步时，先由应用层显式更新通常已经够用，也更容易看懂。

非空默认值 ​

数据库最怕“这个字段按理说应该有值，但库里偏偏留了一堆空”。所以在能明确判断的地方，NOT NULL 应该尽早加上。

例如：

sql
CREATE TABLE users (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    email TEXT NOT NULL,
    nickname TEXT NOT NULL,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

这里的几个判断都很实际：

  • 邮箱不能为空
  • 昵称不能为空
  • 激活状态默认是 TRUE
  • 创建时间默认取当前时间

如果字段业务上允许缺省，再留 NULL。但别因为一开始懒得想，就把整张表都做成“全字段可空”。

唯一约束 ​

只在应用层判断唯一性是不够的。并发请求一多，很容易出现应用层看起来没重复，最终却插进两条相同数据。

例如邮箱应该唯一：

sql
CREATE TABLE users (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    nickname TEXT NOT NULL
);

如果是多字段组合唯一，也可以直接写：

sql
UNIQUE (tenant_id, slug)

像邮箱、用户名、订单号、租户内唯一 slug 这种约束，尽量直接落到数据库层，不要只靠应用自己“保证”。

检查约束 ​

有些字段不是简单的真/假，也不是靠唯一性约束，而是值必须落在一小组可接受范围里。这时候 CHECK 很顺手。

例如订单状态：

sql
status TEXT NOT NULL CHECK (status IN ('pending', 'paid', 'cancelled'))

再比如金额非负：

sql
amount NUMERIC(12, 2) NOT NULL CHECK (amount >= 0)

这种规则如果只放在应用层，总会有漏掉的时候。数据库再兜一层，脏数据会少很多。

外键约束 ​

很多项目一提外键就先怕性能问题，但在中小项目里，外键带来的数据一致性收益往往更实际。

例如订单表引用用户表：

sql
CREATE TABLE orders (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id),
    status TEXT NOT NULL CHECK (status IN ('pending', 'paid', 'cancelled')),
    amount NUMERIC(12, 2) NOT NULL CHECK (amount >= 0),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

这样至少能保证：

  • orders.user_id 不能指向不存在的用户
  • 删除、更新用户时，数据库会按外键规则保护关联关系

至于 ON DELETE CASCADE、ON DELETE RESTRICT、ON DELETE SET NULL 用哪种，要看业务语义，不要图省事全写成级联删除。

还有一个常被忽略的点：外键不会自动给引用端列创建索引。例如 orders.user_id REFERENCES users(id) 建完后，users.id 这边有主键索引，但 orders.user_id 不会自动多出一个索引。后面如果常按用户查订单，通常还要手动补索引。

建表示例 ​

下面这组表已经覆盖了很多后台系统的常见需求：

sql
CREATE TABLE users (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    nickname TEXT NOT NULL,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE orders (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    order_no TEXT NOT NULL UNIQUE,
    user_id BIGINT NOT NULL REFERENCES users(id),
    status TEXT NOT NULL CHECK (status IN ('pending', 'paid', 'cancelled')),
    amount NUMERIC(12, 2) NOT NULL CHECK (amount >= 0),
    paid_at TIMESTAMPTZ,
    metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

这个定义里有几个值得保留的点：

  • 订单号数据库层唯一
  • 用户和订单的关联由外键兜底
  • 金额和状态都有限制条件
  • metadata 允许放少量扩展字段，但核心字段依然拆成普通列

软删除 ​

很多人建每张表都会顺手加一个 deleted_at，但软删除不是默认真理。

适合加软删除的情况通常是：

  • 业务要求保留历史记录
  • 恢复删除记录是常见操作
  • 审计需要看到删除时间

如果业务就是要彻底删除，或者删完不会恢复，也没有审计要求，硬塞一个 deleted_at 只会让查询条件和唯一约束变复杂。

常见失误 ​

该 NOT NULL 的字段全留成可空 ​

后面应用里就会出现一堆额外判空逻辑，数据库里也容易留下模糊数据。

唯一性只放在应用层 ​

并发插入一来，应用层检查很容易被绕过。该唯一的字段，数据库必须知道。

外键一开始全不建 ​

怕的是未来可能很大，结果今天的数据一致性先没了。大多数中小项目，外键完全值得先用起来。

核心字段塞进 JSONB ​

扩展字段用 JSONB 没问题，稳定的业务字段还是应该拆成列。建模图省事，后面通常会加倍补回来。

参考 ​