主键策略与约束设计

约束是数据库帮你维护数据完整性的机制——它比应用层的验证更可靠，因为它在数据库层面强制执行，无论哪个应用或脚本写入数据都绕不过去。

主键策略对比

方式一：SERIAL（自增整数，最常用）

CREATE TABLE users (
id SERIAL PRIMARY KEY,     -- 等价于 INTEGER + SEQUENCE + NOT NULL + UNIQUE
email TEXT NOT NULL UNIQUE,
name TEXT
);
-- BIGSERIAL 用于预期行数超过 2 亿的表
CREATE TABLE events (
id BIGSERIAL PRIMARY KEY,
...
);
-- PostgreSQL 10+ 的标准写法（等价于 SERIAL，但更规范）
id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
id BIGINT  GENERATED ALWAYS AS IDENTITY PRIMARY KEY,

优点：简单、紧凑（4/8 字节）、有序（范围查询友好） 缺点：分布式系统中 ID 可预测、不同表的 ID 空间独立

方式二：UUID（分布式 / 高隐私要求）

CREATE TABLE documents (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
...
);

优点：全局唯一、不暴露顺序信息（安全）、适合分布式系统 缺点：16 字节（是 BIGINT 的 2 倍）、随机 UUID v4 导致索引碎片化

有序 UUID 解决碎片化问题：

-- UUID v7（有时间戳前缀，有序）——需要扩展或应用层生成
-- ULID：26 字符，字典序有序，应用层生成后存为 TEXT 或 UUID

方式三：复合主键（多列联合主键）

-- 多对多关系的中间表
CREATE TABLE user_roles (
user_id BIGINT REFERENCES users(id),
role_id INTEGER REFERENCES roles(id),
assigned_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
PRIMARY KEY (user_id, role_id)   -- 复合主键
);

约束类型详解

NOT NULL

-- 字段级：最常用的约束
email TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
-- 检查现有表的 NOT NULL 状态
SELECT column_name, is_nullable
FROM information_schema.columns
WHERE table_name = 'users';

UNIQUE

-- 单列唯一
email TEXT NOT NULL UNIQUE,
-- 多列组合唯一（等价写法）
UNIQUE (tenant_id, username),   -- 在表定义中
-- 或
ALTER TABLE users ADD CONSTRAINT uq_tenant_username UNIQUE (tenant_id, username);
-- 条件唯一索引（部分唯一）：只对未删除的记录保证唯一
CREATE UNIQUE INDEX uq_active_email ON users (email) WHERE deleted_at IS NULL;

CHECK

-- 值范围约束
age INTEGER CHECK (age >= 0 AND age <= 150),
-- 枚举约束（比 ENUM 类型更灵活）
status TEXT NOT NULL CHECK (status IN ('pending', 'active', 'suspended', 'deleted')),
-- 自定义业务规则
CHECK (end_date > start_date),
CHECK (discount_rate >= 0 AND discount_rate <= 1),
-- 命名约束（便于报错时识别）
CONSTRAINT chk_positive_price CHECK (price > 0),
CONSTRAINT chk_valid_email CHECK (email ~* '^[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,}$'),

FOREIGN KEY（外键）

-- 基本外键
CREATE TABLE orders (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL REFERENCES users(id),
...
);
-- 带删除/更新行为的外键
CREATE TABLE order_items (
id BIGSERIAL PRIMARY KEY,
order_id BIGINT NOT NULL REFERENCES orders(id)
ON DELETE CASCADE          -- 删除订单时，级联删除订单项
ON UPDATE NO ACTION,       -- 更新 order_id 时不允许（默认）
product_id BIGINT NOT NULL REFERENCES products(id)
ON DELETE RESTRICT,        -- 有订单引用时，不允许删除产品
quantity INTEGER NOT NULL CHECK (quantity > 0),
unit_price NUMERIC(10, 2) NOT NULL
);

外键的删除行为对比：

完整 Schema 示例：电商核心表

-- 用户表
CREATE TABLE users (
id          BIGSERIAL PRIMARY KEY,
email       TEXT NOT NULL UNIQUE,
username    TEXT NOT NULL,
password_hash TEXT NOT NULL,
status      TEXT NOT NULL DEFAULT 'active'
CHECK (status IN ('active', 'suspended', 'deleted')),
created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
deleted_at  TIMESTAMPTZ,
CONSTRAINT uq_username UNIQUE (username)
);
-- 产品表
CREATE TABLE products (
id          BIGSERIAL PRIMARY KEY,
sku         TEXT NOT NULL UNIQUE,
name        TEXT NOT NULL,
description TEXT,
price       NUMERIC(10, 2) NOT NULL CHECK (price > 0),
stock_qty   INTEGER NOT NULL DEFAULT 0 CHECK (stock_qty >= 0),
category_id INTEGER REFERENCES categories(id) ON DELETE SET 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          BIGSERIAL PRIMARY KEY,
user_id     BIGINT NOT NULL REFERENCES users(id),
status      TEXT NOT NULL DEFAULT 'pending'
CHECK (status IN ('pending', 'paid', 'shipped', 'delivered', 'cancelled')),
total_amount NUMERIC(12, 2) NOT NULL CHECK (total_amount >= 0),
currency    CHAR(3) NOT NULL DEFAULT 'MYR',
created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
-- 订单明细表
CREATE TABLE order_items (
id          BIGSERIAL PRIMARY KEY,
order_id    BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
product_id  BIGINT NOT NULL REFERENCES products(id) ON DELETE RESTRICT,
quantity    INTEGER NOT NULL CHECK (quantity > 0),
unit_price  NUMERIC(10, 2) NOT NULL CHECK (unit_price > 0),
subtotal    NUMERIC(12, 2) GENERATED ALWAYS AS (quantity * unit_price) STORED
);

自动更新 updated_at 的触发器

-- 创建通用的 updated_at 触发器函数
CREATE OR REPLACE FUNCTION trigger_set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = NOW();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- 为每个需要自动更新 updated_at 的表添加触发器
CREATE TRIGGER set_updated_at
BEFORE UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION trigger_set_updated_at();
CREATE TRIGGER set_updated_at
BEFORE UPDATE ON products
FOR EACH ROW EXECUTE FUNCTION trigger_set_updated_at();

本节记录清单

• [ ] 主键策略：明确小表用 BIGSERIAL（或 GENERATED ALWAYS AS IDENTITY），分布式用 UUID
• [ ] 为所有文本枚举字段添加 CHECK 约束（不用枚举类型，更灵活）
• [ ] 为每个有更新业务的表添加 updated_at 触发器
• [ ] 外键 ON DELETE 行为：审查每个外键，选择合适的删除策略

下一节：规范化与反规范化决策——什么时候应该拆表（3NF）？什么时候故意合并列或用 JSONB？