核心 · 数据库第 6 课 / 共 6 课免费阅读

键、约束与索引 · SQL 与数据库

怎么设计不会出错的表？

课程目录

为什么需要它

坏数据比慢查询代价大得多。如果一张表允许给不存在的顾客下单，迟早会出现这样的订单。好的设计让错误的数据根本存不进去，索引则让常用的查找变快。

直觉理解

设计时问三个问题：

有哪几种东西？ 每种东西一张表（顾客、商品、订单）。
每一行靠什么区分？ 一个 PRIMARY KEY（主键），通常是整数 id。
它们怎么连起来？ 在「多」的那一边放外键（REFERENCES）：一个顾客有很多订单，所以订单表里存 customer_id。

每个事实只存一次。如果城市写在每一条订单里，顾客一搬家，就得改很多地方，总会漏掉一处。

索引就像书后面的索引：数据库不用一页页翻，就能找到匹配的行。

定义

主键 PRIMARY KEY

A column whose value is unique and never NULL. It identifies one row.
值唯一且不为 NULL 的列，用来唯一确定一行。

索引 Index

An extra lookup structure on one or more columns. It speeds up WHERE and JOIN on those columns and costs a little extra space and write time.
建在一列或几列上的额外查找结构。能加快这些列上的 WHERE 和 JOIN，代价是多占一点空间、写入稍慢一点。

示例

约束挡住坏数据

在互动课里运行

这张表不接受重复的邮箱。第二个 Ada 的 INSERT 失败了，错误信息会告诉你原因。

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    age INTEGER CHECK (age >= 0)
);
INSERT INTO users (email, age) VALUES ('ada@example.com', 36);
INSERT INTO users (email, age) VALUES ('ada@example.com', 20);

这个例子故意以报错结束：重点是学会读报错信息。

逐步执行

L3每个用户都要有邮箱，而且不能两个人用同一个。
L4负数年龄会被拒绝。
L7又是同一个邮箱：数据库拒绝。

外键和索引

在互动课里运行

这笔订单指向一个不存在的顾客，数据库拒绝了它。

1
2

CREATE INDEX idx_orders_customer ON orders (customer_id);
INSERT INTO orders (id, customer_id, product_id, qty, day) VALUES (9, 42, 1, 1, '2025-03-09');

这个例子故意以报错结束：重点是学会读报错信息。

逐步执行

L1按顾客查订单现在很快了。
L2没有 42 号顾客：外键约束失败。

常见错误

一个格子里存一串值

tags = 'red,blue,green' 看起来省事，但没法正常连接和统计。另建一张表，每个标签一行。

什么都加索引

每次 INSERT 和 UPDATE 都要更新每一个索引。只给经常查找和连接的列建索引，不是每列都建。

小测验

1. 一个学生选很多门课，一门课也有很多学生。「谁选了什么」怎么存？

A在 students 表里加一个 course_id 列
B建第三张表 enrolments(student_id, course_id)
C加一个文字列，把课程名列出来

查看答案

答案：B

多对多关系需要一张中间表，每对一行。students 里的一个列只能存一门课。

2. `CREATE INDEX idx ON orders (customer_id)` 对哪条查询帮助最大？

ASELECT * FROM orders WHERE customer_id = 3
BSELECT * FROM orders WHERE qty > 2
CSELECT COUNT(*) FROM products

查看答案

答案：A

索引建在 customer_id 上，所以按 customer_id 查找可以直接跳到匹配的行。

动手练习

3 道练习，分三层：先预测程序的输出，再按新需求改程序，最后从零写一个。代码在浏览器里运行，提交马上判题。

预测输出留下了什么？
准确写出这段代码的结果。
改需求让坏数据存不进去
修改 reviews 表：stars 不能为 NULL，且必须在 1 到 5 之间（CHECK (stars BETWEEN 1 AND 5)）；product_id 必须引用 products(id)。测试会检查表上的规则。
从零写给标签建一张表
商品可以有很多标签，一个标签也可以在很多商品上。创建表 product_tags：product_id（引用 products(id)）、tag（TEXT，不能为 NULL），主键由两列一起组成：PRIMARY KEY (product_id, tag)。给商品 4 插入标签 gift 和 sale，然后 SELECT tag FROM product_tags ORDER BY tag;。

在互动课里练这一课

打开互动课，这些例子都能直接改、直接运行，练习题提交马上判题。免费开始，不用注册。

打开互动课课程地图

互动课按顺序解锁：前面的课还没学，会先带你到课程地图。

本课术语

术语	中文	英文定义
Primary key	主键	The column (or columns) that uniquely identifies a row.
Index	索引	A lookup structure that makes searches on a column fast.
Schema	表结构	The design of a database: its tables, columns and rules.