Skip to content

表关系

Model one-到-one, one-到-许多, and 许多-到-许多关系 in SQL.

你将学到

  • three 类型的 关系: one-到-one, one-到-许多, 许多-到-许多
  • 如何 implement 每个类型使用 foreign 键 and junction 表
  • 当到 use junction 表对比简单外键
  • 选择 owning 边的 关系
  • Self-referencing 关系如 树结构

概念讲解

一对一(1:1)

In a one-to-one relationship, each row in table A corresponds to at most one row in table B. Implement it with a foreign key plus a UNIQUE constraint on the foreign key column. Example: a 用户 table and a user_profiles table — each user has at most one profile. Splitting into two tables is useful when some columns are rarely accessed or when the secondary table has a different update frequency.

One-to-Many (1:N)

The most common relationship. One row in table A relates to many rows in table B. Implement it with a foreign key on the "许多" side pointing to the "one" side. Example: one 用户 has many 顺序 — the 顺序 table has a user_id foreign key. No junction table is needed; the foreign key lives directly on the many side.

Many-to-Many (M:N)

Many users can belong to many groups, and many groups can have many users. You cannot express this with a single foreign key. Instead, you create a junction table (also called a join table or association table) that has two foreign keys — one to each side. The user_groups table with user_id and group_id is the junction. Its primary key is typically the composite of both foreign keys.

Choosing the Owning Side

In a one-to-many relationship, the foreign key always goes on the "许多" side. Do not try to store a comma-separated list of order IDs on the user row — that breaks joins, integrity, and indexing. The foreign key on the orders table is the correct, normalized approach.

Self-Referencing Relationships

A table can reference itself. A classic example is an employees table where each employee has a manager_id that references another row in the same employees table. Another is a categories table with a parent_id for tree/hierarchy structures. Self-references use the same foreign key mechanism, just pointing to the same table.

示例代码

              -- One-to-one: user and profile
CREATE TABLE user_profiles (
  user_id    INTEGER PRIMARY KEY REFERENCES users(id),
  bio        TEXT,
  avatar_url TEXT
);

-- One-to-many: one user has many orders
CREATE TABLE orders (
  id      SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id),
  total   DECIMAL(10, 2) NOT NULL
);

-- Many-to-many: users and groups via junction table
CREATE TABLE groups (
  id    SERIAL PRIMARY KEY,
  name  VARCHAR(100) NOT NULL
);

CREATE TABLE user_groups (
  user_id  INTEGER NOT NULL REFERENCES users(id),
  group_id INTEGER NOT NULL REFERENCES groups(id),
  joined_at TIMESTAMP DEFAULT NOW(),
  PRIMARY KEY (user_id, group_id)
);

-- Self-referencing: employee -> manager
ALTER TABLE employees
  ADD COLUMN manager_id INTEGER REFERENCES employees(id);
            

所有 four 关系模式: 1:1 使用 UNIQUE FK, 1:N 使用 FK on 许多边, M:N 使用 junction 表, and self-引用 for hierarchy.

动手试一试

常见错误

错误写法

存储许多-到-许多 as comma-separated 列表

正确做法

Use junction 表使用 two foreign 键. CSV in 列 breaks 连接, prevents indexing, and violates normalization.

错误写法

Putting 外键 on 错误边 的 one-到-许多

正确做法

外键 goes on '许多' 边. For one 用户 → 许多顺序, put user_id on 顺序表, 不 order_ids on 用户表.

练习 表关系

4 个练习

练习 表关系

查阅不认识的术语

新手友好的术语表用大白话解释编程术语——变量、函数、DOM、Promise 等等。

查看术语表

动手做项目

通过引导式项目应用所学,含起始代码与解决方案。

查看项目

解读错误信息

用大白话解释常见错误——什么意思、为什么发生、如何修复。

查看错误