PostgreSQL DDL — 数据定义语言

DDL 是"Data Definition Language"的缩写,负责定义和修改表结构。它包括三个核心命令:CREATE 创建、ALTER 修改、DROP 删除。配合约束(constraint),DDL 让数据库帮你兜底数据正确性——业务代码就能少写一堆 if 判断。

1. CREATE TABLE — 创建表

建表是数据库设计的第一步。一个表由若干列组成,每列声明类型和约束。下面是一个完整的模板:

-- 完整的 CREATE TABLE 模板,带约束
CREATE TABLE users (
    id           SERIAL PRIMARY KEY,             -- 自增主键
    email        TEXT NOT NULL UNIQUE,           -- 邮箱唯一
    username     TEXT NOT NULL CHECK (length(username) >= 3),
    age          INT  CHECK (age >= 0 AND age <= 150),
    role         TEXT NOT NULL DEFAULT 'user',   -- 默认值
    created_at   TIMESTAMPTZ DEFAULT now(),      -- 创建时间
    updated_at   TIMESTAMPTZ DEFAULT now()
);

-- 单独命名约束(便于后续管理)
CREATE TABLE orders (
    id          SERIAL PRIMARY KEY,
    user_id     INT NOT NULL,
    total       NUMERIC(10,2) NOT NULL,
    CONSTRAINT chk_total_positive CHECK (total >= 0),
    CONSTRAINT fk_orders_user FOREIGN KEY (user_id)
        REFERENCES users(id) ON DELETE CASCADE
);

几个要点:

• SERIAL:Postgres 的自增整数主键标准写法,实际是 INTEGER + sequence + NOT NULL 的语法糖。
• 默认值:用 DEFAULT 关键字。now() 是当前时间函数,常用作 created_at 的默认值。
• 约束命名:简单约束可以直接写在列后(如 UNIQUE);复杂的建议用 CONSTRAINT 名字 ... 单独命名,方便日后管理。

2. 六大约束(constraint)

约束是数据库替你强制的"业务规则"。把规则写进表结构,应用代码就能假设数据一定满足条件,大幅简化逻辑。

-- PostgreSQL 支持的约束一览:

-- 1. NOT NULL:不允许 NULL
email TEXT NOT NULL

-- 2. UNIQUE:值不重复(NULL 除外,允许多个 NULL)
email TEXT UNIQUE
-- 多列组合唯一
CONSTRAINT uq_pair UNIQUE (user_id, product_id)

-- 3. PRIMARY KEY:主键,等价于 NOT NULL + UNIQUE
--   一张表只能有一个主键,但可以是多列组合
id SERIAL PRIMARY KEY
CONSTRAINT pk_multi PRIMARY KEY (user_id, role)

-- 4. CHECK:自定义条件(可引用其他列)
age INT CHECK (age >= 0)
CONSTRAINT chk_dates CHECK (end_date >= start_date)

-- 5. FOREIGN KEY:外键,引用另一张表的主键/唯一键
user_id INT REFERENCES users(id)
-- 加 ON DELETE 行为:删除被引用行时怎么办
user_id INT REFERENCES users(id) ON DELETE CASCADE    -- 级联删除
user_id INT REFERENCES users(id) ON DELETE SET NULL   -- 置空
user_id INT REFERENCES users(id) ON DELETE RESTRICT   -- 阻止删除(默认)
-- 加 ON UPDATE 行为同理:ON UPDATE CASCADE 主键变更时同步

-- 6. EXCLUDE 排除约束(Postgres 特色,配合范围类型)
-- 比如禁止同一房间的预约时间段重叠
CREATE TABLE room_bookings (
    room_id INT,
    during  TSTZRANGE,
    EXCLUDE USING GIST (room_id WITH =, during WITH &&)
);

约束选型建议:

• NOT NULL:能加就加,避免"满表 NULL"的灾难。代价是写数据时必须提供值。
• UNIQUE:邮箱、用户名等需要唯一的字段必加。注意 NULL 不参与唯一性检查(允许多个 NULL)。
• CHECK:数据范围、格式校验(如年龄非负、日期顺序)用它,比应用层校验更可靠。
• FOREIGN KEY:外键保证引用完整性。大型分布式项目有时会刻意去掉外键(为了性能和分库分表灵活),换由应用层保证——是否使用要看团队取舍。
• EXCLUDE:Postgres 特色,配合范围类型能实现"区间不重叠"等复杂约束,做排期/预约时极有用。

3. ALTER TABLE — 修改表

表创建后难免要改:加列、删列、改类型、加约束。ALTER TABLE 是日常运维用得最多的 DDL。

-- ALTER TABLE 修改已有表(慎用,生产环境务必先备份)

-- 加列
ALTER TABLE users ADD COLUMN phone TEXT;
ALTER TABLE users ADD COLUMN age INT DEFAULT 0;

-- 删列(连带索引/约束一起删)
ALTER TABLE users DROP COLUMN phone;
ALTER TABLE users DROP COLUMN IF EXISTS phone;  -- 不存在时不报错

-- 改列类型(可能丢数据,谨慎)
ALTER TABLE users ALTER COLUMN age TYPE BIGINT;
-- 转换需要 USING 子句处理复杂情况
ALTER TABLE users ALTER COLUMN phone TYPE bigint USING phone::bigint;

-- 改列名 / 表名
ALTER TABLE users RENAME COLUMN phone TO mobile;
ALTER TABLE users RENAME TO accounts;

-- 加 / 删默认值
ALTER TABLE users ALTER COLUMN role SET DEFAULT 'guest';
ALTER TABLE users ALTER COLUMN role DROP DEFAULT;

-- 加 / 删约束
ALTER TABLE users ADD CONSTRAINT uq_email UNIQUE (email);
ALTER TABLE users ADD CONSTRAINT chk_age CHECK (age >= 0);
ALTER TABLE users DROP CONSTRAINT chk_age;

-- 改主键(先删旧主键约束再加新的)
ALTER TABLE users DROP CONSTRAINT users_pkey;
ALTER TABLE users ADD PRIMARY KEY (email);

关键风险点:

• 改列类型可能丢数据:如 VARCHAR(100) 改 VARCHAR(50) 会截断;ALTER COLUMN ... TYPE 务必先备份。
• 大表加列默认值会重写整表:Postgres 11+ 优化了这一点,加列带"常量默认值"不再重写表,只更新元数据。
• 加 NOT NULL 约束要扫描全表:大表加约束建议先 CHECK 后 ALTER,或用 NOT VALID 异步校验。

4. DROP 与 TRUNCATE

删除操作要非常谨慎——DROP 是不可逆的(除非有备份)。

-- DROP TABLE 删除表(连带数据、索引、约束全删,谨慎!)
DROP TABLE users;

-- IF EXISTS:不存在时不报错(常用于脚本)
DROP TABLE IF EXISTS old_logs;

-- CASCADE:同时删除依赖此表的对象(视图、外键)
DROP TABLE users CASCADE;
-- RESTRICT:有依赖则拒绝删除(默认)
DROP TABLE users RESTRICT;

-- TRUNCATE:清空数据但保留表结构,比 DELETE 快得多
TRUNCATE users;
TRUNCATE users RESTART IDENTITY;   -- 同时重置 SERIAL 序列
-- 一次清空多张表
TRUNCATE users, orders, logs;

TRUNCATE 和 DELETE 的区别要记住:

• DELETE FROM users; 逐行删除,触发触发器、走事务日志,大表会很慢。
• TRUNCATE users; 直接释放数据页,几乎瞬间完成,但不能回滚(在事务外)、触发器不触发。
• 需要"清空并重置自增 ID"用 TRUNCATE ... RESTART IDENTITY。

5. 其他常用 DDL

除表外,DDL 还能创建数据库、schema、索引、视图、扩展等:

-- CREATE DATABASE:创建新数据库
CREATE DATABASE app_db OWNER app_user ENCODING 'UTF8';

-- CREATE SCHEMA:在当前数据库内创建 schema(命名空间)
--   默认有 public schema,大型项目可按业务模块分
CREATE SCHEMA billing;
CREATE TABLE billing.invoices (id INT, total NUMERIC);

-- CREATE INDEX:创建索引(详见索引章节)
CREATE INDEX idx_users_email ON users(email);
CREATE UNIQUE INDEX idx_users_username ON users(username);

-- CREATE VIEW:创建视图(虚拟表,保存查询)
CREATE VIEW active_users AS
SELECT id, email FROM users WHERE role = 'user';

-- CREATE EXTENSION:安装扩展(一行解锁强大功能)
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";   -- UUID 生成
CREATE EXTENSION IF NOT EXISTS pgcrypto;       -- 加密函数
CREATE EXTENSION IF NOT EXISTS postgis;        -- 地理信息

schema 概念要重点理解:它是数据库内的"命名空间",用于组织大型项目的表。默认所有表都在 public schema,你可以按业务模块拆分(如 billing.invoices、auth.users)。

6. 生产环境的 DDL 注意事项

• 不要手动改库结构:用 Flyway、Liquibase、Alembic、Prisma Migrate 等迁移工具,把 DDL 纳入版本控制。
• 避开锁表的高危操作:如大表加索引要加 CONCURRENTLY 关键字(不阻塞读写),详见索引章节。
• 命名规范:表名复数(users)、列名蛇形(created_at)、约束加前缀(pk_ / fk_ / uq_ / chk_)。
• 敏感字段加密:密码、身份证等用 pgcrypto 扩展的 crypt() / digest() 存储。

小结

这一章你学会了用 DDL 创建、修改、删除表结构,理解了六大约束的作用。下一篇我们看 DML(数据操作语言)——把数据真正写进表里。

← 上一篇 PostgreSQL 数据类型

下一篇 PostgreSQL DML →