SQLDatabase Designintermediate40 min read

Database Relationships: One-to-One, One-to-Many, and Many-to-Many

Understand how tables represent real-world relationships using primary keys, foreign keys, unique constraints, and junction tables in PostgreSQL.

On This Page (Table of Contents)

In this lesson, you will learn

✓Explain one-to-one, one-to-many, and many-to-many relationships using real-world examples.
✓Model one-to-one relationships using primary keys or unique foreign keys.
✓Model one-to-many relationships using foreign keys on the child table.
✓Model many-to-many relationships using junction tables and composite primary keys.
✓Use primary keys, foreign keys, UNIQUE, and NOT NULL constraints to enforce relationship rules.
✓Insert related records and retrieve them using JOIN operations.
✓Recognize how NULL values, cascading actions, and relationship optionality affect database design.

1. Introduction to Database Relationships

A database relationship describes how records in one table are associated with records in another table.

Consider a college database. It might contain students, student profiles, courses, and enrollments.

A student has one profile. A student can enroll in multiple courses. A course can contain multiple students.

These examples represent different relationship types:

1. One-to-one (1:1): one student has at most one profile, and each profile belongs to one student.
2. One-to-many (1:N): one student can have many orders, but each order belongs to one student.
3. Many-to-many (M:N): one student can enroll in many courses, and one course can have many students.

Why separate tables?

Separating related entities helps avoid duplicated information and makes it easier to enforce rules. For example, storing a student's name repeatedly in every enrollment record can cause inconsistent names when the student changes their name.

Relational databases commonly represent relationships through primary keys and foreign keys.

A primary key uniquely identifies a row. A foreign key references a primary or eligible unique key in another table, ensuring that the referenced record exists.

A foreign key does not automatically enforce every business rule. For example, a foreign key alone does not guarantee that a student has only one profile. That requires an appropriate UNIQUE constraint or a shared primary key.

2. Primary Keys and Foreign Keys

Before learning relationship types, understand the two key concepts used to implement them.

Primary key:
- Uniquely identifies a row.
- Cannot contain NULL values.
- Must be unique within its table.

Foreign key:
- References a key in another table or, where supported, an eligible key in the same table.
- Enforces referential integrity.
- Prevents a referenced parent record from being removed or changed in a way that would violate the constraint, unless the configured action permits it.

Example:

CREATE TABLE departments (
department_id INTEGER PRIMARY KEY,
department_name VARCHAR(100) NOT NULL
);

CREATE TABLE employees (
employee_id INTEGER PRIMARY KEY,
employee_name VARCHAR(100) NOT NULL,
department_id INTEGER REFERENCES departments(department_id)
);

Here, department_id in employees is a foreign key.

If department_id is nullable, an employee can have no department assigned. If it is NOT NULL, every employee must reference a department.

The foreign key prevents an employee from referencing a department ID that does not exist.

Create related tables

sql

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE departments (
    department_id INTEGER PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL
);
 
CREATE TABLE employees (
    employee_id INTEGER PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department_id INTEGER NOT NULL
        REFERENCES departments(department_id)
);

Output

Two tables are created. Each employee must reference an existing department.

This is PostgreSQL syntax. The referenced department must exist before an employee can reference it.

3. One-to-One Relationship (1:1)

A one-to-one relationship means each record on either side is associated with at most one record on the other side.

Example: students and student_profiles.

A student can have one profile, and a profile belongs to exactly one student.

A common implementation is to place a UNIQUE foreign key in the student_profiles table.

CREATE TABLE students (
student_id INTEGER PRIMARY KEY,
student_name VARCHAR(100) NOT NULL
);

CREATE TABLE student_profiles (
profile_id INTEGER PRIMARY KEY,
student_id INTEGER UNIQUE NOT NULL
REFERENCES students(student_id),
date_of_birth DATE,
bio TEXT
);

Why use UNIQUE?

The foreign key guarantees that the referenced student exists. The UNIQUE constraint prevents two profiles from referencing the same student.

Why use NOT NULL?

It requires every profile to reference a student. Without it, a profile could have a NULL student_id and would not reference a student.

Important distinction:
- UNIQUE plus a foreign key ensures each student has at most one profile.
- It does not ensure every student has a profile.
- A separate student record can exist without any profile.

This design is useful when profile information is optional or when it is appropriate to keep optional or less frequently accessed attributes in a separate table.

Another implementation uses the same primary key in both tables:

CREATE TABLE student_profiles (
student_id INTEGER PRIMARY KEY
REFERENCES students(student_id),
date_of_birth DATE,
bio TEXT
);

This prevents more than one profile row for a student and makes student_id both the profile's primary key and a foreign key.

Choose the design based on the domain and whether a separate profile identifier is useful.

Enforce one profile per student

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
CREATE TABLE students (
    student_id INTEGER PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL
);
 
CREATE TABLE student_profiles (
    profile_id INTEGER PRIMARY KEY,
    student_id INTEGER UNIQUE NOT NULL
        REFERENCES students(student_id),
    bio TEXT
);
 
INSERT INTO students (student_id, student_name)
VALUES (1, 'Asha');
 
INSERT INTO student_profiles (profile_id, student_id, bio)
VALUES (101, 1, 'Computer science student');

Output

The tables and both rows are created successfully. Inserting another profile with student_id = 1 would violate the UNIQUE constraint.

The sample allows a student to have no profile, but a student cannot have two profiles.

4. One-to-Many Relationship (1:N)

A one-to-many relationship means one parent record can be associated with multiple child records.

Example: one department has many employees, but each employee belongs to one department.

The foreign key belongs on the many side: the employees table.

CREATE TABLE departments (
department_id INTEGER PRIMARY KEY,
department_name VARCHAR(100) NOT NULL
);

CREATE TABLE employees (
employee_id INTEGER PRIMARY KEY,
employee_name VARCHAR(100) NOT NULL,
department_id INTEGER NOT NULL
REFERENCES departments(department_id)
);

The relationship looks like this:

departments
------------
department_id (PK)
department_name
|
| one department
|
+--------------------+
| |
v v
employees employees
employee_id (PK) employee_id (PK)
department_id (FK) department_id (FK)

A more useful table view is:

Department 10: Engineering
- Employee 1: Asha
- Employee 2: Ravi
- Employee 3: Meera

All three employee rows reference department_id = 10.

Why is the foreign key on employees?

Each employee row stores the ID of its department. The department does not need to store a list of employee IDs in a single field.

This is easier to query, constrain, index, and maintain.

If department_id is nullable, an employee can exist without an assigned department. If it is NOT NULL, every employee must belong to an existing department.

The foreign key does not require every department to have an employee. A department can exist with zero employees.

Create a one-to-many relationship

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
CREATE TABLE departments (
    department_id INTEGER PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL
);
 
CREATE TABLE employees (
    employee_id INTEGER PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department_id INTEGER NOT NULL
        REFERENCES departments(department_id)
);
 
INSERT INTO departments
VALUES (10, 'Engineering');
 
INSERT INTO employees
VALUES
    (1, 'Asha', 10),
    (2, 'Ravi', 10),
    (3, 'Meera', 10);
 
SELECT employee_name, department_id
FROM employees
ORDER BY employee_id;

Output

employee_name | department_id --------------+--------------- Asha | 10 Ravi | 10 Meera | 10

Three employees can reference the same department. The foreign key ensures department 10 exists.

5. Many-to-Many Relationship (M:N)

A many-to-many relationship means each record in one table can be associated with many records in another table, and vice versa.

Example: students and courses.

- A student can enroll in several courses.
- A course can contain several students.

A single foreign key in students cannot represent all course enrollments. A single foreign key in courses cannot represent all students enrolled in each course.

The standard relational solution is a junction table, also called a bridge or associative table.

The design is:

students
- student_id (PK)
- student_name

courses
- course_id (PK)
- course_name

enrollments
- student_id (FK)
- course_id (FK)
- enrolled_at

The enrollments table connects the other two tables.

For example:

Student 1 -> Course 101
Student 1 -> Course 102
Student 2 -> Course 101

Student 1 is enrolled in two courses. Course 101 has two students.

A composite primary key on (student_id, course_id) prevents the same student-course pair from being inserted more than once.

The junction table can also contain relationship-specific attributes, such as enrollment date, grade, role, or quantity.

These attributes belong in the junction table because they describe the relationship, not just the student or the course.

Create a many-to-many relationship

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
CREATE TABLE students (
    student_id INTEGER PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL
);
 
CREATE TABLE courses (
    course_id INTEGER PRIMARY KEY,
    course_name VARCHAR(100) NOT NULL
);
 
CREATE TABLE enrollments (
    student_id INTEGER NOT NULL
        REFERENCES students(student_id),
    course_id INTEGER NOT NULL
        REFERENCES courses(course_id),
    enrolled_at DATE NOT NULL DEFAULT CURRENT_DATE,
    PRIMARY KEY (student_id, course_id)
);

Output

Three tables are created. The enrollments table connects students and courses and prevents duplicate student-course pairs.

The composite primary key also makes both student_id and course_id non-NULL. Each foreign key must reference an existing parent row.

6. Insert Data into Related Tables

When tables have foreign-key dependencies, insert the referenced records before the records that depend on them.

For a student-course relationship:

1. Insert students.
2. Insert courses.
3. Insert enrollments that reference those students and courses.

The following example inserts two students, two courses, and three enrollments.

If you try to insert an enrollment for a student or course that does not exist, the foreign-key constraint rejects the row.

The composite primary key also prevents a student from being enrolled in the same course twice.

For a multi-step operation that must succeed as a unit, use a transaction. If one statement fails, roll back the transaction rather than leaving a partially completed operation.

Insert students, courses, and enrollments

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
BEGIN;
 
INSERT INTO students (student_id, student_name)
VALUES
    (1, 'Asha'),
    (2, 'Ravi');
 
INSERT INTO courses (course_id, course_name)
VALUES
    (101, 'SQL'),
    (102, 'Python');
 
INSERT INTO enrollments (student_id, course_id)
VALUES
    (1, 101),
    (1, 102),
    (2, 101);
 
COMMIT;
 
SELECT student_id, course_id
FROM enrollments
ORDER BY student_id, course_id;

Output

student_id | course_id -----------+---------- 1 | 101 1 | 102 2 | 101

The example assumes these IDs are unused in a fresh test database. In an existing database, choose appropriate IDs or use generated identity columns.

7. Retrieve Related Data with JOIN

Relationships become especially useful when you retrieve information from multiple tables.

To list employees with their department names, join employees to departments using the foreign key:

SELECT
e.employee_name,
d.department_name
FROM employees AS e
JOIN departments AS d
ON e.department_id = d.department_id;

The ON condition tells SQL which rows are related.

For the students and courses example, use two joins:

SELECT
s.student_name,
c.course_name,
e.enrolled_at
FROM enrollments AS e
JOIN students AS s
ON e.student_id = s.student_id
JOIN courses AS c
ON e.course_id = c.course_id
ORDER BY s.student_name, c.course_name;

The junction table supplies the connection between students and courses.

An INNER JOIN returns matching combinations. If you need to include students who have no enrollments, start from students and use LEFT JOIN.

Relationships define how records are connected; JOIN specifies how to retrieve related records in a query.

List students and their courses

sql

1
2
3
4
5
6
7
8
9
SELECT
    s.student_name,
    c.course_name
FROM enrollments AS e
JOIN students AS s
    ON e.student_id = s.student_id
JOIN courses AS c
    ON e.course_id = c.course_id
ORDER BY s.student_id, c.course_id;

Output

student_name | course_name -------------+------------ Asha | SQL Asha | Python Ravi | SQL

The output corresponds to the sample enrollment records. Each enrollment produces a student-course combination.

8. Relationship Optionality and NULL

Cardinality describes how many records can be related. Optionality describes whether a relationship is required.

Consider an employee's department_id.

With:
department_id INTEGER REFERENCES departments(department_id)

The relationship is optional because department_id can be NULL.

With:
department_id INTEGER NOT NULL REFERENCES departments(department_id)

The relationship is mandatory for each employee because the employee must have a department.

The foreign key ensures that a non-NULL department_id references an existing department.

For a one-to-one relationship, UNIQUE limits each referenced student to one profile. However, NULL handling depends on the database's uniqueness rules. In PostgreSQL, a standard UNIQUE constraint permits multiple NULL values by default.

If the relationship must always exist on both sides, the schema may need additional design choices. Two separate tables with a foreign key do not automatically guarantee that every row in the first table has a corresponding row in the second.

Choose constraints according to the actual business requirement rather than assuming that the relationship label alone defines all rules.

10. Indexes and Relationship Performance

Primary keys and UNIQUE constraints create indexes in PostgreSQL to enforce their uniqueness. Foreign-key declarations, however, do not automatically create an index on the referencing columns.

For example, if employees.department_id is frequently used in joins or department-based lookups, an index may help:

CREATE INDEX idx_employees_department_id
ON employees(department_id);

For a junction table with:
PRIMARY KEY (student_id, course_id)

the composite primary-key index supports lookups beginning with student_id. It is not generally equivalent to a separate index beginning with course_id.

If queries often retrieve all students in a course, consider:

CREATE INDEX idx_enrollments_course_id
ON enrollments(course_id);

Indexes consume storage and add work to inserts, updates, and deletes. Add them based on query patterns and validate their usefulness with representative workloads and EXPLAIN or EXPLAIN ANALYZE where appropriate.

Do not create an index on every column automatically. Design indexes around actual access patterns.

11. Choosing the Correct Relationship

Use these questions to identify the relationship type.

Question 1: Can one record on the first side relate to multiple records on the second side?

Question 2: Can one record on the second side relate to multiple records on the first side?

Interpretation:

- Neither side can have more than one related record: likely one-to-one.
- One side can have many related records, but the other side has at most one: one-to-many.
- Both sides can have many related records: many-to-many.

Examples:

One-to-one:
A user and a separately stored optional profile, where each user may have at most one profile.

One-to-many:
A customer and orders, where each order belongs to one customer and a customer can place multiple orders.

Many-to-many:
Students and courses, where students can enroll in multiple courses and courses can have multiple students.

These are modeling examples, not universal business rules. For example, an order might contain multiple products, so an order-products junction table may be needed to represent that relationship.

Relationship pattern reference

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- One-to-one: UNIQUE foreign key
CREATE TABLE profiles (
    profile_id INTEGER PRIMARY KEY,
    user_id INTEGER UNIQUE NOT NULL REFERENCES users(user_id)
);
 
-- One-to-many: foreign key on the many side
CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(customer_id)
);
 
-- Many-to-many: junction table
CREATE TABLE student_courses (
    student_id INTEGER NOT NULL REFERENCES students(student_id),
    course_id INTEGER NOT NULL REFERENCES courses(course_id),
    PRIMARY KEY (student_id, course_id)
);

Output

These are illustrative schema patterns, not a complete standalone script. The referenced users, customers, students, and courses tables must exist before their foreign keys can be created.

The appropriate relationship depends on the real business rules. The referenced tables must be created first.

12. Common Mistakes

Mistake 1: Putting a single department_id on a department to represent all its employees.
Why it is wrong: One department can have many employees. Store department_id on each employee row.

Mistake 2: Assuming a foreign key alone creates a one-to-one relationship.
Why it is wrong: Multiple child rows can reference the same parent unless uniqueness is enforced.

Mistake 3: Storing a list of course IDs in one text column.
Why it is wrong: Individual relationships become difficult to constrain, query, and maintain. Use a junction table.

Mistake 4: Assuming every parent must have a child.
Why it is wrong: A foreign key usually enforces the validity of child references, not the existence of at least one child for every parent.

Mistake 5: Using ON DELETE CASCADE without understanding its scope.
Why it is wrong: A parent deletion may remove many dependent records.

Mistake 6: Assuming foreign keys automatically index the referencing columns in PostgreSQL.
Why it is wrong: Indexes on referencing columns may need to be created separately for performance.

Mistake 7: Ignoring duplicate relationships in a junction table.
Why it is wrong: Without a suitable unique or primary-key constraint, the same pair may be inserted more than once.

13. Practice Exercises

Exercise 1: One-to-one
Create a users table and a profiles table where each user can have at most one profile. Make the profile's user_id a unique, non-NULL foreign key.

Exercise 2: One-to-many
Create an orders table where every order references an existing customer. Allow one customer to have many orders.

Exercise 3: Many-to-many
Create authors and books tables. Then create a book_authors junction table so a book can have multiple authors and an author can write multiple books.

Exercise 4: Integrity
Explain what happens if you insert a junction-table row that references a nonexistent book.

Exercise 5: Querying
Write a query that lists each student's name and the courses they are enrolled in.

Exercise 6: Optionality
Explain the difference between a nullable foreign key and a NOT NULL foreign key.

Exercise 7: Cascades
Explain one situation where ON DELETE CASCADE is appropriate and one where it could be dangerous.

Practice answer: authors and books

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
CREATE TABLE authors (
    author_id INTEGER PRIMARY KEY,
    author_name VARCHAR(100) NOT NULL
);
 
CREATE TABLE books (
    book_id INTEGER PRIMARY KEY,
    title VARCHAR(200) NOT NULL
);
 
CREATE TABLE book_authors (
    book_id INTEGER NOT NULL REFERENCES books(book_id),
    author_id INTEGER NOT NULL REFERENCES authors(author_id),
    PRIMARY KEY (book_id, author_id)
);
 
SELECT
    a.author_name,
    b.title
FROM book_authors AS ba
JOIN authors AS a
    ON a.author_id = ba.author_id
JOIN books AS b
    ON b.book_id = ba.book_id
ORDER BY a.author_name, b.title;

Output

The query returns one row per author-book relationship. Exact results depend on which authors, books, and relationships have been inserted.

The composite primary key prevents duplicate author-book pairs, and the foreign keys prevent references to nonexistent authors or books.

14. Knowledge Check

Test whether you can identify relationship types and the constraints used to enforce them.

15. Interview Questions

Q1. What is a database relationship?
Answer: It describes how records in different tables are associated, usually through primary keys and foreign keys.

Q2. How do you implement a one-to-one relationship?
Answer: Use a foreign key with a UNIQUE constraint, or use the referenced key as the dependent table's primary key.

Q3. How do you implement a one-to-many relationship?
Answer: Put a foreign key in the child table, allowing multiple child rows to reference the same parent.

Q4. Why is a junction table needed for many-to-many relationships?
Answer: It stores individual associations between records in the two tables and can hold relationship-specific attributes.

Q5. Does a foreign key ensure that every parent has a child?
Answer: No. It ensures that a non-NULL child reference points to a valid parent. Additional business rules may be needed to require children.

Q6. What is referential integrity?
Answer: It is the consistency of references between related tables, enforced by foreign-key constraints.

Q7. What is the difference between ON DELETE CASCADE and ON DELETE SET NULL?
Answer: CASCADE deletes dependent rows. SET NULL keeps the dependent rows but clears their foreign-key values, provided the columns allow NULL.

Q8. Why might a junction table have a composite primary key?
Answer: It can prevent duplicate pairs of related IDs while identifying each association.

Q9. Why might you index a foreign-key column?
Answer: An index can improve joins and lookups and can help some parent-row updates or deletions that must check referencing rows.

Q10. What is the difference between cardinality and optionality?
Answer: Cardinality describes how many records may be related. Optionality describes whether the relationship is required or may be absent.

16. Lesson Summary

Database relationships help represent real-world data accurately without unnecessary duplication.

One-to-one:
Use a unique foreign key or a shared primary key to ensure that each record is associated with at most one record on the other side.

One-to-many:
Place a foreign key on the many side so several child rows can reference one parent.

Many-to-many:
Create a junction table containing foreign keys to both tables. Use a composite primary key or suitable UNIQUE constraint to prevent duplicate associations when duplicates are not allowed.

Also remember:
- Primary keys uniquely identify records.
- Foreign keys enforce referential integrity.
- NOT NULL and UNIQUE help enforce relationship rules.
- Nullable foreign keys represent optional relationships.
- Cascading actions must match the business rules.
- JOIN retrieves related data across tables.
- Indexes should be designed for real query and workload patterns.

The goal is not merely to connect tables. It is to model the real business rules so that the database can preserve valid, consistent data.

Common pitfalls to avoid

Real debugging lessons and exact syntax contrasts to avoid runtime bugs

#1 Assuming a foreign key alone enforces a one-to-one relationship

Why it happens: A foreign key validates the referenced row but does not prevent multiple child rows from referencing the same parent.

Incorrect Approach

sql

CREATE TABLE student_profiles (
    profile_id INTEGER PRIMARY KEY,
    student_id INTEGER REFERENCES students(student_id)
);

Correct Solution

sql

CREATE TABLE student_profiles (
    profile_id INTEGER PRIMARY KEY,
    student_id INTEGER UNIQUE NOT NULL
        REFERENCES students(student_id)
);

Key takeaway: UNIQUE prevents multiple profiles from referencing the same non-NULL student_id. NOT NULL requires every profile to reference a student.

#2 Trying to store a many-to-many relationship in a single comma-separated column

Why it happens: Storing several IDs in one field may appear simpler at first, but it prevents ordinary foreign-key constraints from validating each individual relationship.

Incorrect Approach

sql

CREATE TABLE students (
    student_id INTEGER PRIMARY KEY,
    course_ids TEXT
);
 
-- Example value: '101,102,103'

Correct Solution

sql

CREATE TABLE student_courses (
    student_id INTEGER NOT NULL REFERENCES students(student_id),
    course_id INTEGER NOT NULL REFERENCES courses(course_id),
    PRIMARY KEY (student_id, course_id)
);

Key takeaway: A junction table stores one relationship per row, making constraints, joins, and updates more reliable.

#3 Putting the one-to-many foreign key on the wrong side

Why it happens: The developer tries to store multiple child IDs on the parent instead of letting each child reference its parent.

Incorrect Approach

sql

CREATE TABLE departments (
    department_id INTEGER PRIMARY KEY,
    employee_ids TEXT
);

Correct Solution

sql

CREATE TABLE employees (
    employee_id INTEGER PRIMARY KEY,
    department_id INTEGER NOT NULL
        REFERENCES departments(department_id)
);

Key takeaway: The foreign key belongs on the many side. Each employee references one department, while many employees can share that department ID.

#4 Using ON DELETE CASCADE without considering dependent data

Why it happens: The developer assumes related records should always be deleted with their parent.

Incorrect Approach

sql

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL
        REFERENCES customers(customer_id)
        ON DELETE CASCADE
);

Correct Solution

sql

-- Choose the deletion policy based on business rules.
-- Default NO ACTION behavior can prevent deletion
-- while dependent orders still reference the customer.
CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL
        REFERENCES customers(customer_id)
);

Key takeaway: Cascade is appropriate only when dependent rows should be deleted with the parent. Financial, audit, or historical records may require a different policy.

Ready for hands-on challenges?

Test your skills with shuffled coding challenges in the Practice Arena.

Practice Arena →