Foreign Keys and One-to-Many Relationships

Objectives

By the end of this lesson, you should be able to:

• Explain what a one-to-many relationship is
• Create a foreign key with REFERENCES
• Explain what a foreign key actually enforces

💡 Why this matters: One department has many employees, but each employee belongs to exactly one department. This “one to many” shape is the most common relationship in real databases, and a foreign key is the mechanism that makes it real rather than just implied.

⚠️ A note on verification: every statement and result in this lesson was run against a real, live PostgreSQL 18 database.

The One-to-Many Shape

One departments row can relate to many employees rows, but each employees row points back to exactly one departments row. This is a one-to-many relationship, one department, many employees.

Creating the Relationship

CREATE TABLE departments (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE
);

INSERT INTO departments (name) VALUES ('Engineering'), ('Sales'), ('Marketing');

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    department_id INTEGER REFERENCES departments(id)
);

INSERT INTO employees (first_name, last_name, department_id) VALUES
  ('Erin', 'Castillo', 1),
  ('Priya', 'Desai', 1),
  ('Jordan', 'Blake', 2);

department_id INTEGER REFERENCES departments(id) is the foreign key. It declares that every value in employees.department_id must exist as an id in departments, departments is called the referenced (or parent) table, employees the referencing (or child) table. departments.id being the target of a foreign key is exactly why it needed to be a PRIMARY KEY (Module 3), a foreign key always points at a column guaranteed to be unique.

SELECT first_name, department_id FROM employees ORDER BY id;

 first_name | department_id
------------+-----------------
 Erin       |               1
 Priya      |               1
 Jordan     |               2

Erin and Priya (department_id = 1) are both in Engineering, this is the “many” side pointing at one shared department, exactly the one-to-many shape.

What a Foreign Key Enforces

INSERT INTO employees (first_name, last_name, department_id) VALUES ('Bad', 'Row', 999);

ERROR:  insert or update on table "employees" violates foreign key constraint "employees_department_id_fkey"

There is no department with id = 999, so PostgreSQL rejects the insert. This is the entire point of a foreign key: it makes an invalid reference impossible, rather than relying on every piece of application code to double-check it correctly. The default constraint name, employees_department_id_fkey, follows the same {table}_{column}_fkey pattern covered in Module 5.

Try It

1. Create a categories table (id, name) and a products table with a category_id foreign key referencing it.
2. Insert two categories and three products, at least two products sharing the same category.
3. Try inserting a product with a category_id that doesn’t exist in categories, and read the resulting error.
4. Explain, in your own words, why the referenced column (departments.id in this lesson) needs to be a primary key or otherwise guaranteed unique.

Recap

• A one-to-many relationship is one row on the “one” side related to many rows on the “many” side.
• column REFERENCES table(column) creates a foreign key, requiring every value to match an existing value in the referenced column.
• A foreign key violation is rejected outright, the same enforcement principle as NOT NULL, UNIQUE, and CHECK constraints from Module 5.

Next lesson: one-to-one relationships, and how a foreign key alone doesn’t guarantee one.