2. PostgreSQL 数据模型基础：表、字段、主键、外键、索引、约束的概念与设计规范

在 API 契约治理体系中，数据库是数据的最终存储与一致性保障层。PostgreSQL 作为关系型数据库的标杆，其数据模型设计直接决定了 API 接口的查询效率、数据完整性与扩展能力。本章将系统梳理 PostgreSQL 的核心数据对象及其设计规范，为后续的接口映射奠定基础。

2.1 表（Table）与字段（Column）

表是关系型数据库的基本存储单元，对应现实世界中的实体或关系。字段是表的列，定义实体的属性。PostgreSQL 支持丰富的数据类型，包括数值、字符、日期/时间、JSON、数组、几何类型等。

2.1.1 字段设计规范

规范项 推荐做法 反例
字段命名 小写蛇形命名（snake_case），如 user_id userId、UserID
数据类型选择 优先使用精确类型，如 numeric(10,2) 而非 float 金额字段使用 double precision
长度限制 字符字段必须指定 VARCHAR(N)，避免无限制 TEXT VARCHAR 不指定长度
默认值 业务字段应设置合理默认值，如 status 默认 'active' 允许 NULL 且无默认值
非空约束 业务必填字段必须加 NOT NULL 依赖应用层校验
API 映射提示： 字段名与 API 请求/响应中的 JSON 键名应保持映射关系。建议在 API 契约中显式声明字段映射规则，避免因命名风格差异导致接口歧义。

2.2 主键（Primary Key）

主键是表中唯一标识每一行记录的字段或字段组合。PostgreSQL 推荐使用 UUID 或 BIGSERIAL 作为主键类型。

2.2.1 主键设计规范

  • 唯一性： 主键值必须在全表范围内唯一，且不允许 NULL。
  • 稳定性： 主键一旦赋值，不应被更新。业务字段（如身份证号）不适合做主键。
  • 简洁性： 优先使用单列主键；复合主键仅用于多对多关联表。
  • 性能： 自增整数主键写入性能高，但分布式场景推荐 UUID 避免冲突。
-- 推荐：使用 UUID 主键
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) NOT NULL UNIQUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- 推荐：使用自增 BIGSERIAL 主键
CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    user_id UUID NOT NULL,
    total_amount NUMERIC(10,2) NOT NULL
);

2.3 外键（Foreign Key）

外键用于建立表与表之间的引用关系，保证参照完整性。在 API 契约中，外键对应资源之间的关联关系。

2.3.1 外键设计规范

规范项 说明
必须建立索引 外键列应创建索引，避免级联操作导致全表扫描
级联策略明确 根据业务选择 ON DELETE CASCADE、SET NULL 或 RESTRICT
避免循环依赖 表之间不应形成外键环，否则会导致数据插入死锁
API 关联映射 外键关系应映射为 API 中的嵌套资源或引用 ID
常见陷阱： 在微服务架构中，跨服务的外键约束会引入紧耦合。建议仅在单体应用或同一限界上下文内使用数据库外键，服务间依赖通过 API 契约保证。

2.4 索引（Index）

索引是加速数据检索的数据库对象。PostgreSQL 支持 B-tree、Hash、GiST、GIN、BRIN 等多种索引类型。

2.4.1 索引设计规范

  • B-tree 索引： 默认类型，适用于等值查询和范围查询（=、>、BETWEEN、LIKE 'abc%'）。
  • 复合索引： 将查询条件中频繁同时使用的列组合创建索引，注意列顺序（最左前缀原则）。
  • 唯一索引： 用于保证业务唯一性，如 email、order_no。
  • 部分索引： 只对满足条件的行建立索引，节省空间，如 WHERE status = 'active'。
  • 避免过度索引： 索引会降低写入性能，每张表索引数建议不超过 5-8 个。
-- 复合索引示例：按 user_id 和 created_at 查询订单
CREATE INDEX idx_orders_user_created ON orders (user_id, created_at DESC);

-- 部分索引示例：只索引活跃用户
CREATE INDEX idx_users_active ON users (email) WHERE status = 'active';

2.5 约束（Constraint）

约束是数据库保证数据完整性的规则。PostgreSQL 提供五种主要约束类型：NOT NULL、UNIQUE、PRIMARY KEY、FOREIGN KEY、CHECK。

2.5.1 约束设计规范

约束类型 用途 API 映射场景
NOT NULL 禁止字段为空 API 请求中必填参数
UNIQUE 保证字段值唯一 用户名、邮箱等不可重复字段
CHECK 自定义业务规则 年龄范围、状态枚举值校验
EXCLUSION 排除重叠或冲突 时间区间不重叠、地理空间不重叠 
-- CHECK 约束示例：保证订单金额为正数
CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    total_amount NUMERIC(10,2) NOT NULL CHECK (total_amount > 0),
    status VARCHAR(20) NOT NULL CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled'))
);

2.6 数据模型与 API 契约的映射关系

下图展示了 PostgreSQL 数据模型各要素与 API 契约（OpenAPI 3.x）之间的典型映射关系：

PostgreSQL 数据模型 → API 契约映射 PostgreSQL 数据模型 表（Table）→ 资源（Resource） 字段（Column）→ 属性（Property） 主键（PK）→ 资源 ID 外键（FK）→ 资源引用 / 嵌套 约束（Constraint）→ 校验规则 索引（Index）→ 查询性能保障 API 契约（OpenAPI） /users, /orders 路径 schema.properties 路径参数 {id} $ref 引用 / 嵌套 schema schema.minLength, enum, pattern API 网关缓存 / 查询优化

2.7 设计规范总结

核心原则：
  1. 命名一致性： 表名、字段名、约束名采用统一的小写蛇形命名，与 API 契约中的属性名保持映射。
  2. 类型精确性： 选择最合适的数据类型，避免隐式类型转换带来的性能损耗和精度丢失。
  3. 约束前置： 数据完整性约束优先在数据库层实现，API 层仅做格式校验和业务逻辑校验。
  4. 索引策略化： 基于 API 查询模式设计索引，定期使用 pg_stat_user_indexes 分析索引使用率。
  5. 外键显式化： 所有外键关系必须在数据库层声明，并在 API 契约中通过 $ref 或 foreignKey 扩展字段体现。

遵循以上规范，可以构建出既满足数据库性能要求、又能与 API 契约无缝对齐的数据模型。下一层我们将深入探讨如何将 PostgreSQL 的数据类型与 OpenAPI 的 schema 类型进行精确映射。

← 上一章 📖 返回目录 下一章 →