在创建表时创建外键

欢迎来到我们课程中最重要的讲座之一！今天我们来聊聊怎么在PostgreSQL里创建外键。这话题在数据库设计里超级关键，因为外键就是让表之间能建立联系的桥梁。如果你觉得自己快要在未来的“SQL城市”里迷路了，那就把外键想象成连接不同区域的桥吧。

简单说，外键就是某个表里的一个字段（或者一组字段），它指向另一个表里的字段（通常是主键）。

比如说，你有两个表——students（学生）和courses（课程），那courses表里的外键就可以“指向”哪个学生选了这门课。这样，这两个表就有了联系。

为什么这很重要？

1. 外键能帮你保证数据完整性：如果某个数据在另一个表里不存在，你就不能往这边表里随便插入。
2. 它们让数据操作更简单。比如你在一个表里删掉一条记录，可以设置自动把另一个表里相关的记录也删掉。

外键的创建语法

在PostgreSQL里创建外键其实很简单——只需要一点SQL魔法。基本语法如下：

CREATE TABLE 依赖表 (
    字段_foreign_id DATA_TYPE REFERENCES 父表(字段_id)
);

我们还是来看点细节和例子吧。

例子1：students和courses表

假设我们想让学生和课程之间建立联系。每门课程都要关联到某个学生。只需要执行下面的SQL：

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    student_id INT REFERENCES students(student_id)
);

这里：

• 在students表里，我们用PRIMARY KEY创建了主键，唯一标识每个学生。
• 在courses表里，student_id字段是外键FOREIGN KEY，它指向students表里的student_id字段。

students表

courses表

重要说明

当你添加外键时，PostgreSQL会自动创建一个规则，检查外键里的值是不是在指定表里已经存在。如果你试图插入一个不存在的值，数据库会报错。

实际应用：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 (
    enrollment_id SERIAL PRIMARY KEY,
    student_id INT REFERENCES students(student_id), -- 外键
    course_id INT REFERENCES courses(course_id)		-- 外键
);

这里enrollments表用外键student_id和course_id把students和courses表连接起来。

插入数据

-- 添加学生
INSERT INTO students (name) VALUES ('伊万 伊万诺夫'), ('玛丽亚 斯米尔诺娃');

-- 添加课程
INSERT INTO courses (title) VALUES ('数学'), ('物理');

-- 学生选课
INSERT INTO enrollments (student_id, course_id) VALUES (1, 1), (1, 2), (2, 1);

查询数据

现在我们可以很容易地查出某个学生选了哪些课，或者某门课有哪些学生：

-- 伊万 伊万诺夫选的课程
SELECT c.title
FROM enrollments e
JOIN courses c ON e.course_id = c.course_id
WHERE e.student_id = 1;

-- 选了“数学”这门课的学生
SELECT s.name
FROM enrollments e
JOIN students s ON e.student_id = s.student_id
WHERE e.course_id = 1;

进阶：ON DELETE和ON UPDATE

外键还要管理当父表里的记录被修改或删除时的行为。为此可以用ON DELETE和ON UPDATE修饰符。主要选项如下：

• CASCADE：父表里的数据被修改或删除时，子表里的相关记录也会自动跟着变。
• SET NULL：子表里的外键字段会被设置为NULL。
• RESTRICT：如果子表里已经用到了这些数据，就禁止删除或修改父表里的数据。
• NO ACTION：其实和RESTRICT差不多，只不过检查是在后面才做。

例子2：用ON DELETE CASCADE

假设我们想要删除students表里的某个学生时，courses表里所有和他相关的课程也自动被删掉。可以这样写：

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    student_id INT REFERENCES students(student_id) ON DELETE CASCADE
);

现在，如果你从students表里删掉一个学生，所有和这个学生有关的courses表里的记录也会被删掉。比如：

INSERT INTO students (name) VALUES ('伊万 伊万诺夫');
INSERT INTO courses (title, student_id) VALUES ('数学', 1), ('物理', 1);

-- 删除学生伊万诺夫
DELETE FROM students WHERE student_id = 1;

-- courses表现在是空的，因为所有和伊万诺夫有关的课程都被删了

我们会在下一节讲座里更详细地讲这个话题。

用外键时的数据校验过程

当你创建外键时，PostgreSQL就像个严格的门卫，每条新记录都要检查。比如：

• 如果你插入的外键值在父表里不存在，会报错。
• 如果你删除了父表里的一条记录，而它还被别的表引用（没有ON DELETE CASCADE），就会破坏数据完整性。

例子：试图插入不合法的数据

-- 试图把课程分配给不存在的学生
INSERT INTO enrollments (student_id, course_id) VALUES (3, 1);
-- 错误：违反外键约束

创建外键时的常见错误

1. 外键没有索引。PostgreSQL会自动为主键建索引，但不会为外键建。如果你经常在WHERE条件里用外键，建议手动建个索引。
2. 建表顺序错了。你不能创建一个外键指向还不存在的表。
3. 忘了加ON DELETE或ON UPDATE修饰符。这样在编辑数据时可能会有意想不到的结果。

现在你已经知道怎么创建外键了，这可是构建结构化、数据一致的数据库的强大工具。下一节讲座我们会更详细地讲ON DELETE CASCADE和ON UPDATE RESTRICT这些操作，帮你管理关联数据。