表之间的 ONE-TO-MANY 关系建模

现在是时候升级下我们的技能了，来搞搞关系型数据库里最常见的关系之一——ONE-TO-MANY。

想象一下有个小公司。一个员工只能在一个部门工作，但一个部门可以有很多员工。现实世界里我们有两个对象：员工和部门。它们的关系可以描述为“一个部门可以有很多员工”，或者正式点说就是“一对多”（ONE-TO-MANY）。

其实 ONE-TO-MANY 这种关系到处都是：

• 一个客户可以下很多订单；
• 一个作者可以写很多书；
• 一个老师可以教很多学生。

在关系型数据库里，ONE-TO-MANY 关系是通过外键（FOREIGN KEY）实现的。“多”的那张表里有个字段指向“1”的那张表的主键。

怎么创建 ONE-TO-MANY 关系

我们来看个经典例子：客户和订单的关系。一个客户可以下很多订单，但每个订单只属于一个客户。我们要建两张表：customers（客户）和 orders（订单）。

customers 表

这是我们的“1”表。它用来存客户信息。

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY, -- 客户唯一标识
    name TEXT NOT NULL              -- 客户名
);

orders 表

这是“多”表。它存订单，每个订单有个外键 customer_id，指向 customers 表里的 customer_id。

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,           -- 订单唯一标识
    order_date DATE NOT NULL,              -- 订单日期
    customer_id INT REFERENCES customers(customer_id) -- 外键
);

实战操作

插入数据

现在我们往表里加点数据，看看关系是不是OK。

先往 customers 表加客户：

INSERT INTO customers (name)
VALUES
    ('Ada Lovelace'),
    ('Grace Hopper'),
    ('Linus Torvalds');

结果：

再往 orders 表加订单：

INSERT INTO orders (order_date, customer_id)
VALUES
    ('2023-10-01', 1),  -- Ada 的订单
    ('2023-10-02', 2),  -- Grace 的订单
    ('2023-10-03', 1);  -- Ada 的另一个订单

orders 表：

注意：插入订单时一定要填存在的 customer_id。如果你写了个不存在的ID，数据库会报错。这就是数据完整性的保护。

检查关系

现在来看看表之间的关系。比如问：Ada Lovelace 下了哪些订单？

SELECT orders.order_id, orders.order_date, customers.name
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id
WHERE customers.name = 'Ada Lovelace';

结果：

这里我们用 JOIN 把两张表通过外键连起来。又方便又优雅——而且没有重复数据！

为什么要这样做

ONE-TO-MANY 关系在现实里超级常见又实用。想象下电商网站，有成千上万个客户和几百万个订单。如果每条订单都重复存客户信息，数据量会爆炸。我们把客户信息单独放一张表，订单放另一张表，数据量就小多了，结构也更清晰。

而且，能把数据连起来就能写很强的分析 SQL。比如问：“每个客户下了多少订单？”或者“哪些客户最近一个月下过单？”

常见坑和注意点

新手最容易踩的坑：

没加外键。 如果你忘了在“多”表加外键，这关系就只存在你脑子里，数据库根本管不了。这样你很容易搞出“坏掉”的库，比如订单指向了根本不存在的客户。

删“1”表的记录。 比如你把 customers 里的客户删了，他在 orders 里的订单就成了“孤儿”。为了避免这种情况，可以用 ON DELETE CASCADE，这样删客户时他的订单也会自动删掉。

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    order_date DATE NOT NULL,
    customer_id INT REFERENCES customers(customer_id) ON DELETE CASCADE
);

现在如果你删了客户：

DELETE FROM customers WHERE customer_id = 1;

他的所有订单也会被删掉。数据库就像新泡的咖啡一样干净。

插入数据出错。 如果你插入了一个不存在 customer_id 的订单，会报错，比如：

ERROR:  insert or update on table "orders" violates foreign key constraint