删除和修改数据时考虑外键

好了，我们已经知道什么是外键（FOREIGN KEY），它们怎么用，也练习过用外键建表。但如果要删除数据或者改动有关联的记录怎么办？现在我们就来搞清楚，删除和修改数据跟外键是怎么配合的，还有这里面的小技巧。

想象一下，数据库就像一个很复杂的纸牌屋。如果你抽走一张牌，可能整个结构都会塌掉。这时候外键就派上用场了，它们能防止你在删掉有关联数据时“拆掉”数据库。我们来看看它是怎么工作的。

如果尝试删除数据会发生什么？

当表里有外键时，DBMS 会检查这条记录是不是和其他表有关联。如果有关联，删除时可能会报数据完整性错误。为了避免这种“惊喜”，你可以提前给外键设置动作。这些动作就是通过 ON DELETE 选项来配置的。

用 ON DELETE 配置行为

你可以在创建外键的时候，给它设置下面这些规则之一：

1. ON DELETE CASCADE：删除父表里的记录时，所有相关的子表记录也会自动被删掉。
2. ON DELETE SET NULL：不会删掉子表记录，而是把它们的外键字段设成 NULL。
3. ON DELETE SET DEFAULT：外键字段会被设成默认值。
4. ON DELETE RESTRICT（默认行为）：如果有相关记录，不能删除，会报错。
5. ON DELETE NO ACTION：和 RESTRICT 差不多，但完整性检查会等到事务结束时才做。

例子：级联删除 ON DELETE CASCADE

-- 客户表
CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

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

-- 插入数据
INSERT INTO customers (name) VALUES ('伊万 伊万诺夫');
INSERT INTO orders (customer_id, order_date) VALUES (1, '2023-10-01');

-- 删除客户
DELETE FROM customers WHERE customer_id = 1;

-- 检查 orders 表发生了什么
SELECT * FROM orders; -- 没有记录了，都被级联删除了！

当我们从 customers 表删除一条记录时，PostgreSQL 会自动把这个客户的所有订单从 orders 表里删掉。

例子：把外键设为 NULL ON DELETE SET NULL

-- 带新行为的订单表
CREATE TABLE orders_with_null (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id) ON DELETE SET NULL,
    order_date DATE NOT NULL
);

-- 插入数据
INSERT INTO orders_with_null (customer_id, order_date) VALUES (1, '2023-10-01');

-- 删除客户
DELETE FROM customers WHERE customer_id = 1;

-- 检查 orders_with_null 表
SELECT * FROM orders_with_null;

结果：

用 ON DELETE SET NULL，我们可以保留订单，但把它们“解绑”——不再指向已经不存在的客户。

修改数据时考虑外键

除了删除，父表里的数据变动也会影响到相关记录。比如，如果客户的 customer_id 变了会怎样？这时候就要用到 ON UPDATE 选项了。

用 ON UPDATE 配置行为

你可以用下面这些策略来处理父表的变动：

1. ON UPDATE CASCADE：父表外键变了，所有相关记录也会自动跟着变。
2. ON UPDATE SET NULL：子表外键字段会被设成 NULL。
3. ON UPDATE SET DEFAULT：会被设成默认值。
4. ON UPDATE RESTRICT：如果有相关记录，禁止改外键。
5. ON UPDATE NO ACTION：检查会等到事务结束时才做。

例子：级联更新 ON UPDATE CASCADE

CREATE TABLE customers_with_cascade (
    customer_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE orders_with_cascade (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers_with_cascade(customer_id) ON UPDATE CASCADE,
    order_date DATE NOT NULL
);

-- 插入数据
INSERT INTO customers_with_cascade (name) VALUES ('伊万 伊万诺夫');
INSERT INTO orders_with_cascade (customer_id, order_date) VALUES (1, '2023-10-01');

-- 修改 customer_id
UPDATE customers_with_cascade SET customer_id = 100 WHERE customer_id = 1;

-- 检查 orders_with_cascade 表
SELECT * FROM orders_with_cascade;

结果：

当 customer_id 变了，PostgreSQL 会自动在 orders_with_cascade 表里同步更新。

实战巩固：enrollments 表

来回忆一下我们关于学生 students 和课程 courses 的例子。我们要用 enrollments 表来练习数据删除的管理。

-- 学生表
CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

-- 课程表
CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    title TEXT NOT NULL
);

-- 中间表
CREATE TABLE enrollments (
    student_id INT REFERENCES students(student_id) ON DELETE CASCADE,
    course_id INT REFERENCES courses(course_id) ON DELETE CASCADE,
    PRIMARY KEY (student_id, course_id)
);

-- 插入数据
INSERT INTO students (name) VALUES ('阿列克谢 彼得罗夫');
INSERT INTO courses (title) VALUES ('数学');
INSERT INTO enrollments (student_id, course_id) VALUES (1, 1);

-- 删除学生
DELETE FROM students WHERE student_id = 1;

-- 检查 enrollments 表
SELECT * FROM enrollments; -- 空的！记录被自动删除了。

常见错误和如何避免

常见的错误之一是你在父表删记录时，没有正确配置外键行为。比如你没给 ON DELETE 设定，默认就是 RESTRICT，这样会报错。

还要注意，太频繁用级联操作（CASCADE）可能会带来意外后果，比如你可能会不小心删掉比你想象中更多的数据。

为了避免这些坑，建议你：

• 每次都认真考虑 ON DELETE 和 ON UPDATE 的行为，结合你的业务逻辑来选。
• 对于重要操作，先做检查查询再执行删除或修改。
• 用事务，这样出错时可以回滚。