EP05. “建库建表与约束条件”

• 建表时少设一个约束条件，脏数据迟早会混进去——等到发现价格是负数、订单指向不存在的用户，回头清理的成本比一开始设计表结构贵得多。
• 从「查询已有数据」切到「从零设计并建立」的角度：数据库层级操作（CREATE/DROP DATABASE）、表结构操作（CREATE/DROP/ALTER TABLE），加六种约束条件（NOT NULL、UNIQUE、PRIMARY KEY、FOREIGN KEY、CHECK、DEFAULT）全部覆盖到。
• 例子延续 EP01/EP02 已经用过的 users/orders/products/order_items/categories 电商场景，这次把这几张表真正建出来。
• AUTO INCREMENT（自增主键）在不同数据库系统写法差异很大，这篇会摆一张对照表，换数据库不用重学一遍。
• 前置知识：先看过 EP01、EP02，知道 SELECT/JOIN 怎么写、知道索引和事务是什么概念。

重点内容

---

数据库层级操作：CREATE DATABASE / DROP DATABASE

建数据库需要管理员权限，不是随便哪个账号都能执行：

-- 建一个新数据库
CREATE DATABASE ecommerce_db;

-- 删除数据库（⚠️ 不可逆，见下面「常见错误」）
DROP DATABASE ecommerce_db;

简单提一下备份：备份数据库怎么做，每个数据库系统差异很大，不是标准 SQL 的一部分。

• SQL Server：有专属的 T-SQL 语法，BACKUP DATABASE dbname TO DISK = 'filepath';，还能加 WITH DIFFERENTIAL 做差异备份（只备份上次完整备份之后变动的部分，比每次全量备份快）。这是 SQL Server 专属命令，不是通用标准 SQL，搬到 PostgreSQL/MySQL 不能用。
• PostgreSQL：用命令行工具 pg_dump / pg_restore，不是写在 SQL 语句里执行的。
• MySQL：用命令行工具 mysqldump，或者 SELECT ... INTO OUTFILE 导出部分数据。

---

建表：CREATE TABLE

基本语法：每个栏位写「栏位名 数据类型 约束条件」，栏位之间用逗号分隔。

CREATE TABLE 表名 (
  栏位1 数据类型 约束条件,
  栏位2 数据类型 约束条件,
  ...
);

常见栏位类型（PostgreSQL）：

• int / bigint：整数
• varchar(n)：长度上限 n 的变长字符串
• text：不限长度的文本
• numeric(p,s)：精确小数，p 是总位数、s 是小数位数（金额栏位一定要用这个，不要用 float，会有精度误差）
• boolean：真/假
• timestamp：日期+时间
• date：只有日期

完整例子——把 EP01/EP02 一直在用的电商场景真正建出来：

CREATE TABLE categories (
  id serial PRIMARY KEY,
  name varchar(100) NOT NULL
);

CREATE TABLE users (
  id serial PRIMARY KEY,
  email varchar(255) NOT NULL UNIQUE,
  name varchar(100) NOT NULL,
  role varchar(20) NOT NULL DEFAULT 'user',
  active boolean NOT NULL DEFAULT true,
  login_count int NOT NULL DEFAULT 0,
  created_at timestamp NOT NULL DEFAULT NOW()
);

CREATE TABLE products (
  id serial PRIMARY KEY,
  name varchar(150) NOT NULL,
  price numeric(10,2) NOT NULL CHECK (price >= 0),
  category_id int,
  description text,
  CONSTRAINT fk_category FOREIGN KEY (category_id) REFERENCES categories(id)
);

CREATE TABLE orders (
  id serial PRIMARY KEY,
  user_id int NOT NULL,
  status varchar(20) NOT NULL DEFAULT 'pending',
  total_amount numeric(10,2) NOT NULL CHECK (total_amount >= 0),
  created_at timestamp NOT NULL DEFAULT NOW(),
  CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE order_items (
  id serial PRIMARY KEY,
  order_id int NOT NULL,
  product_id int NOT NULL,
  quantity int NOT NULL CHECK (quantity > 0),
  unit_price numeric(10,2) NOT NULL,
  CONSTRAINT fk_order FOREIGN KEY (order_id) REFERENCES orders(id),
  CONSTRAINT fk_product FOREIGN KEY (product_id) REFERENCES products(id)
);

这就是 EP01 的 JOIN users u JOIN orders o JOIN order_items oi JOIN products p 那条查询背后，实际建表时长什么样。

还有一种简化写法——用现有表的查询结果直接建一张新表（复制数据，常用来做临时的数据快照或子集）：

-- 把已完成订单另外存一份，方便离线分析、不影响正式表
CREATE TABLE completed_orders_snapshot AS
SELECT * FROM orders WHERE status = 'completed';

---

删表与清空数据：DROP TABLE vs TRUNCATE TABLE

两者常被搞混，但差别很大：

-- DROP TABLE：连表结构一起永久删除
DROP TABLE sessions;
DROP TABLE IF EXISTS sessions;   -- 表不存在也不报错，migration 脚本常用这个写法

-- TRUNCATE TABLE：只清空数据，表结构、栏位、约束都保留
TRUNCATE TABLE order_items;

---

改表结构：ALTER TABLE

上线之后需求变了，不用整张表重建，用 ALTER TABLE 改：

-- 加栏位
ALTER TABLE products ADD COLUMN stock_quantity int NOT NULL DEFAULT 0;

-- 删栏位
ALTER TABLE users DROP COLUMN login_count;

-- 改栏位名
ALTER TABLE orders RENAME COLUMN total_amount TO order_total;

-- 改表名
ALTER TABLE orders RENAME TO customer_orders;

-- 改栏位数据类型（PostgreSQL 写法）
ALTER TABLE products ALTER COLUMN price TYPE numeric(12,2);

-- 事后补一个约束条件
ALTER TABLE products ADD CONSTRAINT chk_stock CHECK (stock_quantity >= 0);

改栏位数据类型，几个数据库写法不一样：

改栏位名，SQL Server 语法完全不同（不是标准 RENAME COLUMN）：

-- SQL Server
EXEC sp_rename 'orders.total_amount', 'order_total', 'COLUMN';

---

约束条件（Constraints）总览

约束条件是加在栏位或表上的规则，数据库会拿它来挡掉不符合规则的写入，不是靠应用层代码自己把关。可以在 CREATE TABLE 建表当下就加，也可以之后用 ALTER TABLE 补加。

常见的写法分两种：

• 栏位级：直接写在栏位后面，只管这一个栏位，例如 price numeric(10,2) NOT NULL
• 表级：栏位定义完之后另起一行，用 CONSTRAINT 约束名 约束类型 (栏位) 的格式，可以同时管好几个栏位，也方便之后用约束名去 DROP 掉

---

NOT NULL

栏位不准是 NULL（空值）。EP01 讲过「WHERE email = NULL 永远查不到东西」，从源头用 NOT NULL 挡住不该是空的栏位，比事后用 WHERE 去补更可靠。

CREATE TABLE users (
  id serial NOT NULL,
  email varchar(255) NOT NULL,
  name varchar(100) NOT NULL
);

事后补 NOT NULL，三个数据库写法都不一样：

---

UNIQUE

栏位（或几个栏位组合）的值不能重复，但跟 PRIMARY KEY 不同，同一张表可以有很多个 UNIQUE 约束。

-- 栏位级：email 不能重复
CREATE TABLE users (
  id serial PRIMARY KEY,
  email varchar(255) NOT NULL UNIQUE
);

-- 表级 + 命名：多个栏位组合起来不能重复
CREATE TABLE user_settings (
  user_id int NOT NULL,
  device_id varchar(100) NOT NULL,
  theme varchar(20) DEFAULT 'light',
  CONSTRAINT uc_user_device UNIQUE (user_id, device_id)
);

事后补加、拿掉：

ALTER TABLE users ADD CONSTRAINT uc_email UNIQUE (email);

---

PRIMARY KEY

等于 NOT NULL + UNIQUE 的组合，用来唯一识别表里的每一行——一张表只能有一个 PRIMARY KEY，但可以是好几个栏位组成的「复合主键」（composite key）。

-- 单栏位主键（EP01/EP02 一直在用的写法）
CREATE TABLE users (
  id serial PRIMARY KEY,
  email varchar(255) NOT NULL UNIQUE
);

-- 复合主键：order_items 换一种设计，不用独立 id，改用两个外键组合当主键
CREATE TABLE order_items (
  order_id int NOT NULL,
  product_id int NOT NULL,
  quantity int NOT NULL CHECK (quantity > 0),
  unit_price numeric(10,2) NOT NULL,
  PRIMARY KEY (order_id, product_id),
  CONSTRAINT fk_order FOREIGN KEY (order_id) REFERENCES orders(id),
  CONSTRAINT fk_product FOREIGN KEY (product_id) REFERENCES products(id)
);

事后补加、拿掉：

ALTER TABLE users ADD PRIMARY KEY (id);

---

FOREIGN KEY

让一张表的栏位「参照」另一张表的 PRIMARY KEY，用来维持表跟表之间的关联完整性——挡住两种脏数据：往外键栏位塞一个父表里根本不存在的值，或者删掉父表里还有子表在参照的那一行。

对应 EP01 一直在用的 orders.user_id → users.id 关联：

CREATE TABLE orders (
  id serial PRIMARY KEY,
  user_id int NOT NULL,
  status varchar(20) NOT NULL DEFAULT 'pending',
  CONSTRAINT fk_user
    FOREIGN KEY (user_id)
    REFERENCES users(id)
);

事后补加、拿掉：

ALTER TABLE orders
  ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id);

---

CHECK

验证栏位的值是否符合特定条件，条件不成立就拒绝写入。

-- 栏位级：价格不能是负数
CREATE TABLE products (
  id serial PRIMARY KEY,
  price numeric(10,2) NOT NULL CHECK (price >= 0)
);

-- 表级 + 命名：同时管好几个栏位的组合条件
CREATE TABLE orders (
  id serial PRIMARY KEY,
  status varchar(20) NOT NULL DEFAULT 'pending',
  total_amount numeric(10,2) NOT NULL,
  CONSTRAINT chk_order
    CHECK (total_amount >= 0 AND status IN ('pending', 'paid', 'shipped', 'completed', 'cancelled'))
);

事后补加、拿掉：

ALTER TABLE orders ADD CONSTRAINT chk_order_total CHECK (total_amount >= 0);

---

DEFAULT

栏位没给值的时候，自动填上预设值。

CREATE TABLE orders (
  id serial PRIMARY KEY,
  status varchar(20) NOT NULL DEFAULT 'pending',
  created_at timestamp NOT NULL DEFAULT NOW()
);

「取现在时间当默认值」这个函数名，几个数据库不一样：

事后补加、拿掉 DEFAULT：

---

AUTO INCREMENT：跨数据库对照

自增主键（新增一行，主键自动往上加）几乎每个系统都要用到，但写法差异是这篇笔记里最大的一个坑：

Oracle 没有栏位级的自增关键字，要自己建一个 SEQUENCE 对象，插入数据时手动取下一个值：

CREATE SEQUENCE seq_user_id START WITH 1 INCREMENT BY 1;

INSERT INTO users (id, email, name)
VALUES (seq_user_id.nextval, 'test@example.com', 'Test User');

---

语法速查

---

适用版本

主要例子以 PostgreSQL 语法为主（延续 EP01/EP02），自增主键用 serial。DDL（数据定义语言，也就是 CREATE/ALTER/DROP 这类改变表结构的语句）比查询语句的跨数据库差异大得多，尤其是这几个地方，换数据库前务必对照：

---

学完你会

• ✅ 看懂并写出 CREATE DATABASE / CREATE TABLE / ALTER TABLE / DROP TABLE 的基本语法
• ✅ 知道 NOT NULL、UNIQUE、PRIMARY KEY、FOREIGN KEY、CHECK、DEFAULT 六种约束条件分别挡什么脏数据、怎么加
• ✅ 换一个数据库系统时，知道该去查哪几个语法点（自增主键、改栏位类型、拿掉约束）大概率要重写

常见错误

• ❌ 把 DROP TABLE / DROP DATABASE 当成能后悔的操作——它们没有回收站，一执行就是永久删除，正式环境的 DDL 操作最好先在测试环境跑过、或者用有审核流程的 migration 工具，不要手动对正式库执行
• ❌ 建表图省事，NOT NULL / CHECK / FOREIGN KEY 全部跳过不加——短期看起来省事，长期会让脏数据（空邮箱、负数价格、指向不存在用户的订单）混进正式数据库，回头清理比一开始设计好约束贵得多
• ❌ 把 TRUNCATE TABLE 当成安全操作乱用——虽然比 DROP TABLE 温和（保留表结构），但同样不可回滚、通常不能只清空部分行，而且如果有其他表用 FOREIGN KEY 参照这张表，直接 TRUNCATE 可能会被挡下来
• ❌ FOREIGN KEY 参照的两个栏位数据类型没对齐——比如子表用 int、父表主键是 bigint，PostgreSQL 建表时会直接报错，务必两边类型一致
• 💡 团队协作、多环境（开发/测试/正式）同步表结构时，不建议手写裸 SQL DDL 脚本——用 Prisma、Flyway、Django migrations 这类 migration 工具，能把 AUTO INCREMENT / ALTER COLUMN / DROP CONSTRAINT 这几个跨数据库差异点抽象掉，也能追踪每次表结构变动的历史

Sources

Blog / Website：

1. SQL CREATE DATABASE Statement
2. SQL DROP DATABASE Statement
3. SQL Backup Database
4. SQL CREATE TABLE Statement
5. SQL DROP TABLE Statement
6. SQL ALTER TABLE Statement
7. SQL Constraints
8. SQL NOT NULL Constraint
9. SQL UNIQUE Constraint
10. SQL PRIMARY KEY Constraint
11. SQL FOREIGN KEY Constraint
12. SQL CHECK Constraint
13. SQL DEFAULT Constraint
14. SQL Auto Increment