Master Primary and Foreign Keys

You've built tables, written queries, and maybe even joined two datasets together. But deep down, you know something is missing: your tables are just unrelated piles of rows. Every time you try to connect a user to their orders, you're doing it manually, hoping the IDs line up. That's not a database — that's a spreadsheet with extra steps. The moment you master primary and foreign keys, your schema transforms from a collection of standalone tables into a cohesive, self-validating data model. This lesson gives you the mental model, the hands-on SQL, and the troubleshooting skills to make that leap — and it sets you up for the next step in your PostgreSQL journey: joins and beyond.

The problem this lesson solves

Without keys, every table is an island. You might have a users table and an orders table, but nothing ties them together inside the database. That means:

• You can insert orphaned rows — an order with a user_id that points to nobody.
• You can duplicate data — the same user appears three times because there's nothing stopping it.
• You can corrupt relationships — change a user's ID and every reference to them breaks silently.

That's not just messy; it's dangerous. In a real application, an order tied to a nonexistent user can cause invoice errors, sending you chasing phantom bugs for hours. The problem is fundamental: your schema lacks integrity.

Primary and foreign keys are the database's built-in solution to this exact pain. A primary key guarantees each row is unique and identifiable. A foreign key guarantees that every value in a column references an existing row in another table. Together, they enforce the rules that keep your data honest.

Core concept / mental model

Think of a primary key as a driver's license number — unique per person, never reused, and official. It's the single, unambiguous way to refer to that one row.

Now think of a foreign key as a badge that references that license number. When you look at a company access badge, it says "Employee #4821". Someone with a list of all licenses can look up #4821 and find the employee's name, department, and hire date. The badge itself doesn't store all that info — it just points to it.

In PostgreSQL:

• A primary key is a column (or set of columns) that uniquely identifies each row. It automatically becomes a unique index, and it can't be NULL.
• A foreign key is a column (or set of columns) that references the primary key of another table. PostgreSQL ensures that any value you put there exists in the referenced table — or the insert/update fails.

Here's the key mental shift: primary keys are about identity; foreign keys are about relationships. One declares "this is who I am," the other declares "this is who I'm connected to."

Pro tip: You'll see the shorthand PK for primary key and FK for foreign key in diagrams and tooltips. When you see a line connecting two tables in a schema diagram, it's usually a primary key pointing to a foreign key.

This model scales beautifully. You can have dozens of tables referencing a single users table, each with its own foreign key. That's the foundation of relational database design.

How it works step by step

Let's see how keys work in practice, building a tiny schema from scratch. We'll use a simple e-commerce example: users (the people) and orders (their purchases).

Step 1: Create the parent table with a primary key.

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email TEXT UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT now()
);

Here, id is the primary key. SERIAL auto-generates sequential integers, but you could use BIGSERIAL or even a UUID (more on that later). The PRIMARY KEY constraint does two things: it creates a unique index and sets NOT NULL.

Step 2: Create the child table with a foreign key.

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    amount NUMERIC(10,2) NOT NULL,
    placed_at TIMESTAMP DEFAULT now()
);

The line user_id INTEGER REFERENCES users(id) is a foreign key constraint. It tells PostgreSQL: "Every user_id must match an existing users.id."

Step 3: Insert valid data — it works.

INSERT INTO users (email) VALUES ('alice@example.com');
INSERT INTO orders (user_id, amount) VALUES (1, 49.99);

The second insert succeeds because user 1 exists.

Step 4: Try to insert an orphan — it fails.

INSERT INTO orders (user_id, amount) VALUES (999, 19.99);
-- ERROR:  insert or update on table "orders" violates foreign key constraint

That error is your database protecting you from bad data. This is the heart of referential integrity.

Step 5: Query the relationship — now you can join.

SELECT users.email, orders.amount
FROM users
JOIN orders ON users.id = orders.user_id;

This JOIN works precisely because the foreign key guarantees the relationship exists. Without the key, you'd risk matching user_id to the wrong table or missing rows.

This sequence — define PK, define FK, enforce integrity, query with confidence — is the complete workflow you'll use in every schema you design.

Hands-on walkthrough

Let's practice with a more complete example: a small library database with authors, books, and loans.

1. Set up the tables.

DROP TABLE IF EXISTS loans, books, authors;

CREATE TABLE authors (
    id BIGSERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE books (
    id BIGSERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    author_id BIGINT REFERENCES authors(id)
);

CREATE TABLE loans (
    id BIGSERIAL PRIMARY KEY,
    book_id BIGINT REFERENCES books(id),
    borrowed_on DATE NOT NULL DEFAULT CURRENT_DATE,
    returned_on DATE
);

2. Insert sample data.

INSERT INTO authors (name) VALUES ('Ursula K. Le Guin'), ('Terry Pratchett');

INSERT INTO books (title, author_id) VALUES
    ('A Wizard of Earthsea', 1),
    ('The Left Hand of Darkness', 1),
    ('Guards! Guards!', 2);

INSERT INTO loans (book_id, borrowed_on) VALUES
    (1, '2025-04-01'),
    (3, '2025-04-02');

3. Verify the data integrity.

-- This will fail: book_id 999 doesn't exist
INSERT INTO loans (book_id, borrowed_on) VALUES (999, CURRENT_DATE);

4. Query the relationship.

SELECT a.name AS author, b.title, l.borrowed_on
FROM authors a
JOIN books b ON a.id = b.author_id
JOIN loans l ON b.id = l.book_id;

Expected output:

       author       |        title         | borrowed_on
--------------------+----------------------+-------------
 Ursula K. Le Guin  | A Wizard of Earthsea | 2025-04-01
 Terry Pratchett    | Guards! Guards!      | 2025-04-02

5. Try a delete that would break things.

-- Assuming you didn't create the FK with ON DELETE, this will fail if loans exist.
DELETE FROM books WHERE id = 1;
-- ERROR:  update or delete on table "books" violates foreign key constraint

You can handle this with ON DELETE CASCADE or ON DELETE SET NULL. We'll compare those in the next section.

This hands-on walkthrough demonstrates the full lifecycle: create constraints, insert valid data, watch invalid data get rejected, and enjoy safe queries.

Compare options / when to choose what

Not all keys are born equal. Here's a quick comparison of common choices.

When to use ON DELETE actions:

• ON DELETE CASCADE: When a parent row is deleted, all related children are deleted too. Great for orders → order_items.
• ON DELETE SET NULL: When a parent is deleted, the child's FK becomes NULL. Good for optional relationships.
• ON DELETE RESTRICT (default): Prevents deletion if any children exist. Safest if you want to avoid accidental data loss.

Composite primary keys are a third option: you can define a primary key on multiple columns. For example, a join table for students and courses might use (student_id, course_id) as a composite key to prevent duplicates.

CREATE TABLE enrollments (
    student_id INTEGER REFERENCES students(id),
    course_id INTEGER REFERENCES courses(id),
    enrolled_at DATE DEFAULT CURRENT_DATE,
    PRIMARY KEY (student_id, course_id)
);

Pro tip: If you expect rows to be referenced from other tables, stick with a single surrogate key like BIGSERIAL — it's simpler and more performant. Reserve composite keys for pure join tables where you're modeling a many-to-many relationship.

Troubleshooting & edge cases

Even with the right setup, things can go wrong. Here are the most common issues you'll hit, and how to fix them.

1. "Foreign key constraint" error on insert/update.

• Symptom: ERROR: insert or update on table "x" violates foreign key constraint.
• Cause: You're inserting a value that doesn't exist in the parent table — or the parent row was deleted.
• Fix: Check the value with a SELECT on the parent table. If the parent is gone, you either need to re-create it or adjust your data flow.

2. "Null value in column ... violates not-null constraint"

• Symptom: You're trying to insert NULL into a PK column.
• Cause: You defined a primary key, so NULL is not allowed.
• Fix: Use SERIAL or set a default value. Or intentionally allow NULL if you switch to a nullable FK — but that changes semantics.

3. Deleting a parent row is blocked.

• Symptom: ERROR: update or delete on table "parent" violates foreign key constraint.
• Cause: The default RESTRICT behavior prevents deletion.
• Fix: Decide your policy: ON DELETE CASCADE, SET NULL, or explicitly delete children first.

4. Composite key ordering matters.

• If you define PRIMARY KEY (student_id, course_id), a student can enroll in the same course only once. If you ever need duplicates, your model is wrong — use a surrogate key.

5. Performance degrades with large FKs.

• Indexes on FK columns are automatically created in modern PostgreSQL, but if you're doing heavy joins, consider composite indexes tuned to your query patterns.

What you learned & what's next

You've just mastered primary and foreign keys — the backbone of relational data integrity. Here's your checklist of what you can now do:

• Explain the core idea: primary keys are identity, foreign keys are relationships.
• Apply them in a hands-on exercise — creating tables, inserting data, and verifying integrity.
• Connect this lesson to the next step in your PostgreSQL path, which is advanced joins and query performance. With proper keys, your joins will be fast, reliable, and a joy to write.

Now it's time to put this into practice. In your own schema, go back to a table that lacks a primary key and add one. Then, identify a relationship that should be enforced with a foreign key — and add it. You'll feel the difference immediately: the database starts working for you.

Remember, keys aren't just about structure — they're about confidence. When you can trust your data's relationships, you can build applications that scale without fear of silent corruption.