卷 I · 起底CH 04深度 04/24

建一张会当真的表

现在建库。我们要建的不是「随便能存东西的表」，而是从第一行起就当真的表——每一列的类型、每一条主键、每一个默认值，都是我们对数据下的一道命令。这张书店库会陪你走完全书。

CREATE TABLE主键IDENTITY生成列

示例库：一个小书店

全书围绕一个小书店的数据展开：有作者、有书、有客户、有订单。先看它的骨架，再逐块拆解为什么这么写。

CREATE TABLE authors (
  id      integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name    text    NOT NULL,
  country text
);

CREATE TABLE books (
  id        integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title     text    NOT NULL,
  author_id integer NOT NULL REFERENCES authors(id),
  price     numeric(6,2) NOT NULL CHECK (price >= 0),
  year      integer,
  genre     text,
  stock     integer NOT NULL DEFAULT 0 CHECK (stock >= 0)
);

CREATE TABLE customers (
  id   integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text    NOT NULL,
  city text
);

CREATE TABLE orders (
  id          integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id integer NOT NULL REFERENCES customers(id),
  book_id     integer NOT NULL REFERENCES books(id),
  qty         integer NOT NULL CHECK (qty > 0),
  ordered_at  timestamptz NOT NULL DEFAULT now()
);

这段建表语句里，几乎每一个词都在「当真」。我们一块块看。

主键：把「行不重复」当真

上一章说过，关系模型里一个关系是集合，行不该重复。PRIMARY KEY 就是把这条规矩写下来、并强制执行：

  • 它保证 id 列唯一且非空——任何两行的 id 都不同，没有 id 是空的。
  • 它自动建了一个 B-tree 索引（卷 V 详谈），所以按 id 查一行非常快。
  • 它给了每一行一个稳定的「身份证」，让别的表能用外键指向它。

一张严肃的表，几乎都该有主键。没有主键的表，就是放弃了「这张表的行有身份、不重复」这条底线——在 PostgreSQL 的世界观里，这是很不「当真」的。

自增：IDENTITY，而不是 SERIAL

你需要让 id 自动生成、一个个往上加。PostgreSQL 有两种写法，这里要认真选一个：

-- 老写法（PostgreSQL 特有的伪类型，仍能用，但不再推荐）
id serial PRIMARY KEY

-- 新写法（SQL 标准，PG 10 起支持，推荐）
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY

两者背后都是一个序列（sequence，一个专门发号的对象）。区别在态度：

  • serial 只是「默认值取自序列」，你依然能手动往 id 里塞值，甚至塞一个和序列冲突的值，为将来埋下重复的隐患。
  • GENERATED ALWAYS AS IDENTITY 更当真：它不许你手动指定 id（除非显式 OVERRIDING SYSTEM VALUE），把「这一列由系统全权发号」这件事钉死。它还是 SQL 标准写法，可移植性更好。

所以本书一律用 IDENTITY。这是「当真」在一个小细节上的体现：能让机器负责的身份，就别留个后门让人手滑。

◆ 选对类型，就是在替未来省事

看上面几处类型的选择，每一个都不是随手写的：

  • price numeric(6,2)：钱用 numeric（精确十进制），绝不用 float——浮点数存不下精确的角分，卷 III 会给你看 0.1 + 0.2 的惨案。
  • ordered_at timestamptz：时间用带时区的 timestamptz，不用裸 timestamp——跨时区不出错，卷 III 细说。
  • title text：字符串用 text，不纠结 varchar(255) 那个从来没人想清楚的长度。PostgreSQL 里 text 和 varchar 性能一样，多数时候 text 更省心。

这些选择的道理，卷 III 会逐个讲透。这里先记住：建表时多花的这几分钟，是在替半年后的自己省下几天的排查。

约束：把「显然的规矩」写进表里

建表语句里还藏着几条约束，它们把你脑子里「这显然不该发生」的想法，变成数据库当真执行的规则：

  • NOT NULL：书必须有标题，订单必须有数量——这些列不许为空。
  • CHECK (price >= 0)：价格不能是负数；CHECK (qty > 0)：订单数量必须是正数。
  • REFERENCES authors(id)：外键——一本书的 author_id 必须真的对应 authors 表里一个存在的作者，不许指向一个不存在的人。
  • DEFAULT 0 / DEFAULT now()：不写就用默认值，库存默认 0，下单时间默认「现在」。

这些约束是卷 III 第 12 章的主角，那一章我们会用一台真的校验器，看它们怎么把违规的写入当场挡在门外。现在你只要感受到：建表不只是「声明有哪些列」，更是在「立规矩」。

塞点数据进去

建完表，灌一点种子数据，方便后面每一章都有东西可查：

INSERT INTO authors (name, country) VALUES
  ('Ursula', 'US'), ('Liu', 'CN'), ('Borges', 'AR'), ('Tolkien', NULL);

INSERT INTO books (title, author_id, price, year, genre, stock) VALUES
  ('Left Hand',    1, 45, 1969, 'scifi',   3),
  ('Dispossessed', 1, 52, 1974, 'scifi',   0),
  ('Three-Body',   2, 39, 2008, 'scifi',   7),
  ('Ficciones',    3, 30, 1944, 'fiction', 2),
  ('Aleph',        3, 28, 1949, 'fiction', 0),
  ('Hobbit',       4, 25, 1937, 'fantasy', 5);

注意我们没有手动写 id——因为 id 是 GENERATED ALWAYS AS IDENTITY，系统自动从 1 发号。也注意 Tolkien 的 country 是 NULL：他的国籍我们「不知道」，这个 NULL 会在第 6 章讲三值逻辑时派上大用场。

灌完用 \d books 和一句查询确认：

serious=# SELECT count(*) FROM books;
 count
-------
     6
(1 row)

生成列：让数据库替你算派生值

再看一个「当真」的小能力。假设你常要按「书名 + 年份」显示，与其每次查询都拼一遍，不如让表自己算好：

ALTER TABLE books
  ADD COLUMN label text
  GENERATED ALWAYS AS (title || ' (' || year || ')') STORED;

label 是一个生成列（generated column）：它的值由别的列算出来，你不能直接写它，改了 title 或 year 它会自动跟着变。STORED 表示算好的值真的存到磁盘上。这让「派生数据永远和源数据一致」这件事，由数据库当真保证，而不是靠应用每处都记得同步。

⇄ 对照 MySQL

这张 schema 里，几处和 MySQL 建表习惯不同的地方：

  • 自增：MySQL 是 id INT AUTO_INCREMENT PRIMARY KEY；PG 是 GENERATED ALWAYS AS IDENTITY（或老式 serial）。两者背后机制不同——MySQL 的 AUTO_INCREMENT 是列属性，PG 的是独立的序列对象，可以被多张表共享、可以单独取号（nextval）。
  • 没有 ENGINE=InnoDB：PG 只有一种表存储，不需要你选引擎，也就没有「不小心建成 MyISAM 结果没事务」这种坑。
  • 字符串别再纠结长度：MySQL 里 VARCHAR(n) 的 n 有历史包袱（索引长度、行大小），PG 里 text 就是最省心的选择，长度不影响性能。
  • 没有字符集 / 排序规则的建表噩梦：不用在每张表上写 CHARSET=utf8mb4——PG 的库在 createdb 时就定好了编码（默认 UTF-8），干净利落。
  • 生成列：两家都支持（MySQL 5.7+ 也有 GENERATED 列），语法接近，这一处是相通的。

这一章的一句话

建表不是「声明有哪些列」，而是给数据立规矩：主键立身份、类型立形状、约束立底线、默认值立缺省——从第一行起就当真。

地基打好了。卷 II 开始，我们回到你熟悉的 SELECT，但这次要挖到你没见过的深度。第一站：SELECT 本身，和它背后的集合去重。