PostgreSQL的表关系和约束，咋帮你搞定用户订单不混乱、学生选课不重复？

kknone
120 阅读15分钟

表关系：为什么需要它？

在数据库设计中，表关系是连接不同表的“桥梁”，核心目的是避免数据冗余和保证数据一致性 。比如，如果你把用户的“用户名”“邮箱”和“地址”“生日”都放在一个表，当用户修改地址时，你需要更新所有包含该用户地址的行——这不仅麻烦，还容易出错。而把“用户基本信息”和“用户详细信息”拆成两个表，通过关系关联，修改地址只需要更新“详细信息表”的一行，既高效又安全。

表关系的三种类型

PostgreSQL支持三种常见的表关系，我们用“用户-订单-商品”的例子逐一解释：

1. 一对一关系（One-to-One）

定义：两个表中的行一一对应（一个用户对应一个详细信息，一个详细信息属于一个用户）。
实现方式：从表（详细信息表）的外键同时作为主键（或添加唯一约束），确保一个主表行只能对应一个从表行。

示例：用户表（users）和用户详细信息表（user_profiles）

-- 主表：用户基本信息
CREATE TABLE users
(
    id       SERIAL PRIMARY KEY,           -- 主键（自增整数），唯一标识用户
    username VARCHAR(50)  NOT NULL UNIQUE, -- 用户名：非空+唯一
    email    VARCHAR(100) NOT NULL UNIQUE  -- 邮箱：非空+唯一
);

-- 从表：用户详细信息（一对一关联）
CREATE TABLE user_profiles
(
    user_id   INT PRIMARY KEY,       -- 主键=外键，保证一对一
    full_name VARCHAR(100) NOT NULL, -- 真实姓名：非空
    bio       TEXT,                  -- 个人简介：可空
    -- 外键约束：user_id引用users表的id
    FOREIGN KEY (user_id) REFERENCES users (id)
        ON DELETE CASCADE            -- 主表（users）行删除时，从表（user_profiles）对应行也删除
);

关键逻辑：user_profiles的user_id既是主键（唯一）又是外键（关联users.id），因此每个用户只能有一个详细信息，反之亦然。

2. 一对多关系（One-to-Many）

定义：主表中的一行对应从表中的多行（一个用户有多个订单，一个订单属于一个用户）。
实现方式：从表（订单表）添加外键，指向主表（用户表）的主键。

示例：订单表（orders）和订单项表（order_items）

-- 主表：订单信息
CREATE TABLE orders
(
    id         SERIAL PRIMARY KEY,                  -- 订单主键
    user_id    INT NOT NULL,                        -- 关联用户表的id
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- 订单时间：默认当前时间
    -- 外键约束：user_id引用users表的id
    FOREIGN KEY (user_id) REFERENCES users (id)
        ON DELETE CASCADE                           -- 用户删除时，其所有订单也删除
);

-- 从表：订单项（一对多关联）
CREATE TABLE order_items
(
    id         SERIAL PRIMARY KEY,                           -- 订单项主键
    order_id   INT            NOT NULL,                      -- 关联订单表的id
    product_id INT            NOT NULL,                      -- 关联商品表的id（假设存在products表）
    quantity   INT            NOT NULL CHECK (quantity > 0), -- 数量：非空+必须>0
    price      DECIMAL(10, 2) NOT NULL CHECK (price > 0),    -- 单价：非空+必须>0
    -- 外键约束：order_id引用orders表的id
    FOREIGN KEY (order_id) REFERENCES orders (id)
        ON DELETE CASCADE                                    -- 订单删除时，其所有订单项也删除
);

关键逻辑：order_items的order_id指向orders.id，一个订单可以有多个订单项，但一个订单项只能属于一个订单——这就是“一对多”。

3. 多对多关系（Many-to-Many）

定义：两个表中的行互相对应多行（一个学生选多门课，一门课有多个学生）。
实现方式：创建中间表，包含两个外键（分别指向两个主表的主键），并将这两个外键设为组合主键（保证唯一）。

示例：学生表（students）和课程表（courses）

-- 主表1：学生信息
CREATE TABLE students
(
    id   SERIAL PRIMARY KEY,  -- 学生主键
    name VARCHAR(50) NOT NULL -- 学生姓名：非空
);

-- 主表2：课程信息
CREATE TABLE courses
(
    id          SERIAL PRIMARY KEY,          -- 课程主键
    course_name VARCHAR(100) NOT NULL UNIQUE -- 课程名称：非空+唯一
);

-- 中间表：学生-课程关联（多对多）
CREATE TABLE student_courses
(
    student_id INT NOT NULL, -- 关联学生表的id
    course_id  INT NOT NULL, -- 关联课程表的id
    -- 组合主键：保证学生-课程组合唯一（一个学生不能重复选同一门课）
    PRIMARY KEY (student_id, course_id),
    -- 外键约束1：student_id引用students表的id
    FOREIGN KEY (student_id) REFERENCES students (id) ON DELETE CASCADE,
    -- 外键约束2：course_id引用courses表的id
    FOREIGN KEY (course_id) REFERENCES courses (id) ON DELETE CASCADE
);

关键逻辑：中间表student_courses的student_id和course_id 组合成主键，确保“学生A选课程B”的记录唯一。当学生或课程被删除时，中间表的关联记录也会自动删除（ON DELETE CASCADE）。

约束：数据的“安全护栏”

表关系依赖约束（Constraint）来保证数据的完整性和一致性。约束是数据库的“规则”，违反规则的数据会被直接拒绝。PostgreSQL支持5种核心约束：

1. 主键约束（Primary Key）

作用：唯一标识表中的每一行，非空且唯一。
语法：创建表时用PRIMARY KEY，或用ALTER TABLE添加。
示例：

-- 创建表时指定主键
CREATE TABLE products
(
    id   SERIAL PRIMARY KEY, -- SERIAL：自增整数（PostgreSQL特有）
    name VARCHAR(100) NOT NULL
);

-- 修改表添加主键（如果表已存在）
ALTER TABLE products
    ADD PRIMARY KEY (id);

注意：一个表只能有一个主键，可以是单个列（如id）或多个列（复合主键，如student_courses 的student_id+course_id）。

2. 外键约束（Foreign Key）

作用：维护表之间的引用完整性，确保从表中的值必须存在于主表中（比如订单项的order_id必须是订单表中存在的id）。
语法：FOREIGN KEY (从表列) REFERENCES 主表(主表列) [ON DELETE/UPDATE 动作]
常见动作（重点！）：

  • CASCADE：主表行删除/更新时，从表关联行自动删除/更新（比如删除用户时，其订单也删除）。
  • SET NULL：主表行删除/更新时，从表关联列设为NULL（比如产品下架时，订单项的product_id设为NULL，需从表列允许NULL）。
  • RESTRICT：阻止主表行删除/更新，如果从表有关联行（比如有订单项的订单不能删除）。

示例：

ALTER TABLE order_items
ADD FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE SET NULL;

3. 唯一约束（Unique Constraint）

作用：保证列或列组合的值唯一，可以为空，但最多一个空值（因为NULL不等于任何值，包括自身）。
语法：创建表时用UNIQUE，或用ALTER TABLE添加。
示例：

-- 用户表的邮箱和用户名都唯一
CREATE TABLE users
(
    id       SERIAL PRIMARY KEY,
    username VARCHAR(50)  NOT NULL UNIQUE, -- 用户名唯一
    email    VARCHAR(100) NOT NULL UNIQUE  -- 邮箱唯一
);

4. 非空约束（Not Null Constraint）

作用：保证列不能为NULL，必须有值。
语法：创建表时用NOT NULL，或用ALTER TABLE修改。
示例：

-- 创建表时指定非空
CREATE TABLE users
(
    id       SERIAL PRIMARY KEY,
    password VARCHAR(255) NOT NULL -- 密码非空
);

-- 修改表添加非空约束
ALTER TABLE users
    ALTER COLUMN username SET NOT NULL;

-- 取消非空约束（如果业务允许）
ALTER TABLE users
    ALTER COLUMN username DROP NOT NULL;

5. 检查约束（Check Constraint）

作用：自定义条件，保证列值满足特定规则（比如“价格>0”“年龄≥18”）。
语法：创建表时用CHECK (条件)，或用ALTER TABLE添加。
示例：

-- 商品表：价格>0，库存≥0
CREATE TABLE products
(
    id    SERIAL PRIMARY KEY,
    name  VARCHAR(100)   NOT NULL,
    price DECIMAL(10, 2) NOT NULL CHECK (price > 0),           -- 价格必须>0
    stock INT            NOT NULL DEFAULT 0 CHECK (stock >= 0) -- 库存≥0，默认0
);

-- 修改表添加检查约束：年龄在18-60之间
ALTER TABLE users
    ADD CHECK (age BETWEEN 18 AND 60);

约束的“管理技巧”

  • 命名约束：默认情况下，PostgreSQL会自动生成约束名（如users_email_key），但你可以自定义名称，方便后续管理： 
    CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(100) NOT NULL,
    -- 自定义唯一约束名
    CONSTRAINT unique_user_email UNIQUE (email)
);
  • 删除约束：用ALTER TABLE删除不再需要的约束： 
    -- 删除用户表的唯一约束
ALTER TABLE users DROP CONSTRAINT unique_user_email;

课后Quiz：巩固你的知识

  1. 问题：设计一个“博客系统”，用户表（users）和文章表（posts ）的关系是“一个用户写多篇文章，一篇文章属于一个用户”。这是什么关系？如何实现？
    答案：一对多关系。在文章表添加user_id外键指向用户表的id：

    CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    content TEXT NOT NULL,
    user_id INT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

  2. 问题：外键的ON DELETE CASCADE和ON DELETE RESTRICT有什么区别？
    答案：CASCADE会自动删除从表关联行；RESTRICT会阻止主表行删除（如果从表有关联行）。

  3. 问题：如何给用户表的age列添加检查约束，确保年龄在18到60之间？
    答案：

    ALTER TABLE users ADD CHECK (age BETWEEN 18 AND 60);

常见报错与解决办法

1. ERROR: there is no unique constraint matching given keys for referenced table "orders"

原因：创建外键时，主表的被引用列没有主键或唯一约束（比如order_items的order_id引用orders的id，但orders.id 不是主键）。
解决：给主表添加主键约束：

ALTER TABLE orders
    ADD PRIMARY KEY (id);

2. ERROR: duplicate key value violates unique constraint "users_email_key"

原因：插入/更新数据时违反唯一约束（比如用户表的email重复）。
解决：检查重复数据（SELECT * FROM users WHERE email = 'test@example.com'），或修改唯一约束（如添加组合唯一）。

3. ERROR: null value in column "username" violates not-null constraint

原因：插入数据时，非空列（如username）未赋值。
解决：插入时提供非空值（INSERT INTO users (username, email) VALUES ('test', 'test@example.com')），或取消非空约束（如果业务允许）。

参考链接

余下文章内容请点击跳转至 个人博客页面 或者 扫码关注或者微信搜一搜：编程智域 前端至全栈交流与成长 ，阅读完整的文章：PostgreSQL的表关系和约束，咋帮你搞定用户订单不混乱、学生选课不重复？

往期文章归档
免费好用的热门在线工具
avatar
文章
阅读
粉丝