表关系
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.
动手试一试
- SQL Formatter
格式关系 and 外键定义.
- JSON 到 SQL
Convert nested JSON 关系到 SQL 表.
常见错误
错误写法
存储许多-到-许多 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 等等。
动手做项目
通过引导式项目应用所学,含起始代码与解决方案。
解读错误信息
用大白话解释常见错误——什么意思、为什么发生、如何修复。