DDL 与约束

这一课你将能做到

1. 在 app schema 下写出带主键、唯一、非空、检查约束的 CREATE TABLE
2. 说清 SERIAL / IDENTITY 与 MySQL AUTO_INCREMENT 的对应关系，并会 ALTER TABLE 加列
3. 故意触发一次唯一约束失败，能读懂报错并改成正确插入

为什么要学

业务表不是「先塞数据再想规则」，而是 DDL 先定契约：哪些列必填、什么唯一、谁引用谁。PostgreSQL 对约束执行严格，CHECK、外键在日常开发里很常用。

你已在 sql-essentials 学过 DDL 概念；本课把同一套「课程表」思维改写成 PG 方言，为后面 JSONB、索引、Flyway 打地基。对照本站 schema.sql 时，记住：那是 MySQL 版，不能原样粘贴。

先建立图像

数据库 codeforge_pg
 └── schema app          ← 命名空间（像文件夹）
      ├── cf_track       ← 课程路径表
      └── cf_lesson      ← 课时表（外键 → cf_track）

约束像门禁：
  PRIMARY KEY  → 这一行的身份证
  UNIQUE       → 全表不能撞车（如 slug）
  NOT NULL      → 不能空着交卷
  CHECK        → 值必须落在允许集合
  FOREIGN KEY  → 子行必须指向真实父行

MySQL 习惯	PostgreSQL 常见写法
AUTO_INCREMENT	BIGSERIAL 或 GENERATED … AS IDENTITY
TINYINT(1) 当布尔	BOOLEAN（TRUE/FALSE）
一个 DATABASE 当命名空间	schema（app.cf_track）
反引号 `slug`	双引号仅必要时；标识符通常小写不引号
DATETIME	优先 TIMESTAMPTZ

跟着做

步骤 1：连上练习库

docker exec -it pg-dev psql -U codeforge -d codeforge_pg

若容器不在，先按 pg-install 拉起。

步骤 2：创建 schema 与课程表

-- 业务对象放进 app，避免和 public 杂物混在一起
CREATE SCHEMA IF NOT EXISTS app;

CREATE TABLE app.cf_track (
    id          BIGSERIAL PRIMARY KEY,           -- 自增 bigint 主键
    slug        VARCHAR(64) NOT NULL,            -- URL 用业务键
    title       VARCHAR(255) NOT NULL,
    description TEXT,
    category    VARCHAR(32) NOT NULL DEFAULT 'language',
    level       VARCHAR(16) NOT NULL DEFAULT 'beginner',
    sort_order  INT NOT NULL DEFAULT 0,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT uq_track_slug UNIQUE (slug),
    CONSTRAINT ck_track_level
      CHECK (level IN ('beginner', 'intermediate', 'advanced'))
);

应显示 CREATE TABLE。用 \d app.cf_track 查看列与约束。

步骤 3：插入合法行与非法行

INSERT INTO app.cf_track (slug, title, category, level, sort_order)
VALUES
  ('sql-essentials', 'SQL 与关系型数据库', 'language', 'beginner', 10),
  ('postgresql-essentials', 'PostgreSQL 入门精讲', 'language', 'intermediate', 20);

-- 应失败：重复 slug
INSERT INTO app.cf_track (slug, title)
VALUES ('sql-essentials', '重复试试');

-- 应失败：level 不在 CHECK 集合
INSERT INTO app.cf_track (slug, title, level)
VALUES ('bad-level', '坏级别', 'expert');

读报错里的约束名（uq_track_slug / ck_track_level），建立「名字有用」的直觉。

步骤 4：课时表 + 外键

CREATE TABLE app.cf_lesson (
    id          BIGSERIAL PRIMARY KEY,
    track_id    BIGINT NOT NULL
                REFERENCES app.cf_track (id) ON DELETE CASCADE,
    slug        VARCHAR(64) NOT NULL,
    title       VARCHAR(255) NOT NULL,
    order_index INT NOT NULL DEFAULT 0,
    content_md  TEXT,
    status      VARCHAR(16) NOT NULL DEFAULT 'draft',
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT uq_lesson_track_slug UNIQUE (track_id, slug)
);

INSERT INTO app.cf_lesson (track_id, slug, title, order_index, status)
SELECT id, 'pg-intro', 'PostgreSQL 是什么', 1, 'published'
FROM app.cf_track WHERE slug = 'postgresql-essentials';

再试插入一个不存在的 track_id（如 999999），应被外键拒绝。

步骤 5：ALTER 与布尔列

ALTER TABLE app.cf_track
  ADD COLUMN IF NOT EXISTS is_member BOOLEAN NOT NULL DEFAULT FALSE;

UPDATE app.cf_track
SET is_member = TRUE
WHERE slug = 'postgresql-essentials';

SELECT slug, is_member FROM app.cf_track ORDER BY sort_order;

PG 用真正的布尔，不要写 1/0 当习惯（虽常可强制转换，风格上应 TRUE/FALSE）。

步骤 6（可选）：IDENTITY 写法对照

-- PG 10+ 更「标准 SQL」的自增写法（新表可用）
CREATE TABLE app.demo_identity (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name TEXT NOT NULL
);

BIGSERIAL 仍广泛出现在教程与存量库；新项目可优先 IDENTITY。二者都依赖序列对象。

讲清楚要点

1. 因为 多个业务域共用一个数据库，所以 用 schema（如 app）分区，比全堆在 public 清晰。
2. 主键稳定、业务键可暴露：id 给关联与 ORM；slug 给 URL，并加 UNIQUE。
3. 约束要命名：CONSTRAINT uq_… 让报错可读，迁移与排错更快。
4. 外键 ON DELETE CASCADE 表示删课程时级联删课时——适合强归属；若要保留历史，改用 RESTRICT 或软删。
5. CHECK 把非法枚举挡在门外：比只靠应用层校验多一道防线。
6. DDL 也是事务的一部分（多数情况）：失败可回滚未提交的结构变更；生产变更仍走迁移工具（见 Flyway 课）。

你可能会懵的地方

• public 和 app 哪个才是「默认」？
  新连接 search_path 通常含 public。写 app.cf_track 最稳；或 SET search_path TO app, public。

• SERIAL 和 IDENTITY 选哪个？
  入门两者都能用。看存量：老库多 SERIAL；新规范文档常推 IDENTITY。本课示例用 BIGSERIAL 贴近许多教程。

• 为什么 \d cf_track 找不到表？
  表在 app 下。用 \d app.cf_track 或先设 search_path。

• TEXT 和 VARCHAR 差在哪？
  PG 中 TEXT 与无长限制的 VARCHAR 性能实质接近；需要长度上限时用 VARCHAR(n) 表达业务规则。

• 删表很危险吗？
  是。DROP TABLE 不可轻易在共享库执行；练习库可重建，业务库走迁移与备份。

错法对照

错法	问题	正解
粘贴 MySQL 的 `id` BIGINT AUTO_INCREMENT	语法错误	BIGSERIAL 或 IDENTITY
用 TINYINT(1) 表示是否会员	类型别扭、语义不清	BOOLEAN
不写 schema，全建在 public	后期难治理	CREATE SCHEMA app + app.表
外键不加，只靠应用保证	脏数据	REFERENCES …
插入时省略 NOT NULL 且无 DEFAULT 的列	报错	提供值或改列定义

自测三问

1. UNIQUE (track_id, slug) 允许两个不同课程有相同课时 slug 吗？为什么？
2. ON DELETE CASCADE 删掉某 cf_track 行后，相关 cf_lesson 会怎样？
3. 为何本站生产 schema.sql 不能直接拿到本课执行？

小结一句话

PostgreSQL DDL 用 schema + 标准类型 + 可命名约束把数据契约写清楚；自增用 SERIAL/IDENTITY，布尔用 BOOLEAN。

官方延伸

• PostgreSQL：CREATE TABLE
• PostgreSQL：约束