Skip to content

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

Practice Table Relationships

Look up unfamiliar terms

A beginner-friendly glossary explains jargon in plain English — variables, functions, DOM, Promise, and more.

View glossary

Build real projects

Apply what you learned by building guided projects with starter code and solutions.

View projects

Decode error messages

Plain-English explanations of common errors — what they mean, why they happen, and how to fix them.

View errors