数据建模与约束

好的 PostgreSQL 模型不是“先建几列，规则以后再补”，而是尽量让数据库知道哪些状态合法。

一份可工作的订单模型

CREATE TABLE customers (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text NOT NULL,
  display_name text NOT NULL CHECK (length(trim(display_name)) > 0),
  created_at timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT customers_email_unique UNIQUE (email)
);

CREATE TABLE orders (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint NOT NULL REFERENCES customers(id),
  status text NOT NULL DEFAULT 'pending'
    CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled')),
  total_cents bigint NOT NULL CHECK (total_cents >= 0),
  placed_at timestamptz NOT NULL DEFAULT now()
);

COMMENT ON COLUMN orders.total_cents IS
  'Order total in the smallest currency unit; never a floating-point amount.';

类型选择

需求	建议类型	避免
主键	`bigint GENERATED ... AS IDENTITY` 或 `uuid`	新设计继续依赖 `serial` 的隐式行为
金额	最小货币单位的 `bigint`，或明确精度的 `numeric(p,s)`	`real` / `double precision`
时间点	`timestamptz`	把带时区的现实时间存成字符串
文本	`text` + 业务约束	没有业务意义的任意 `varchar(255)`
状态	小而稳定时用 `CHECK`；独立生命周期时用引用表	无约束自由文本
文档数据	`jsonb`	把核心关系和外键藏进 JSON

timestamptz 存储绝对时间点，显示时按会话时区转换。它不保存原始输入的时区名称；若业务需要“Europe/Paris”这类规则，另存时区标识。

约束的职责

NOT NULL：值必须存在。
CHECK：单行必须满足谓词。
UNIQUE：候选键唯一；默认允许多个 NULL。
PRIMARY KEY：唯一且非空的行标识。
FOREIGN KEY：引用目标必须存在；删除策略要显式设计。

外键删除策略不是语法偏好

ON DELETE CASCADE 表示父记录消失时子记录也应消失。只有生命周期确实从属时才使用；账单、审计记录通常不应级联删除。

Schema 与命名

为应用对象使用明确 schema，并收紧默认权限：

CREATE SCHEMA app;
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
ALTER ROLE app_runtime SET search_path = app, pg_catalog;

对 AI 和人都友好的命名应完整、稳定、少缩写：customer_id 优于 cid，created_at 优于 ctime。用 COMMENT ON 记录单位、状态转换和隐私等级，而不是复述列名。

验证模型

INSERT INTO customers (email, display_name)
VALUES ('ada@example.com', 'Ada')
RETURNING id, created_at;

-- 应失败：金额不能为负
INSERT INTO orders (customer_id, total_cents)
VALUES (1, -100);

设计完成的标准不只是“合法数据能写入”，还包括“典型非法数据被正确拒绝”。

一份可工作的订单模型

类型选择

约束的职责

Schema 与命名

验证模型

On this page