Table Relationships
Model one-to-one, one-to-many, and many-to-many relationships in SQL.
What you'll learn
- The three types of relationships: one-to-one, one-to-many, many-to-many
- How to implement each type with foreign keys and junction tables
- When to use a junction table vs a simple foreign key
- Choosing the owning side of a relationship
- Self-referencing relationships like tree structures
Concept
One-to-One (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 users 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 "many" side pointing to the "one" side. Example: one user has many orders — the orders 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 "many" 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.
Example
-- 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);
All four relationship patterns: 1:1 with UNIQUE FK, 1:N with FK on the many side, M:N with a junction table, and a self-reference for a hierarchy.
Try it
- SQL Formatter
Format relationship and foreign key definitions.
- JSON to SQL
Convert nested JSON relationships into SQL tables.
Common mistakes
The mistake
Storing many-to-many as a comma-separated list
The fix
Use a junction table with two foreign keys. CSV in a column breaks joins, prevents indexing, and violates normalization.
The mistake
Putting the foreign key on the wrong side of a one-to-many
The fix
The foreign key goes on the 'many' side. For one user → many orders, put user_id on the orders table, not order_ids on the users table.
Practice Table Relationships
4 exercises
Look up unfamiliar terms
A beginner-friendly glossary explains jargon in plain English — variables, functions, DOM, Promise, and more.
Build real projects
Apply what you learned by building guided projects with starter code and solutions.
Decode error messages
Plain-English explanations of common errors — what they mean, why they happen, and how to fix them.