Common Mistakes When Working with Foreign Keys

We're all human, and we all make mistakes. Especially when it comes to the nitty-gritty of foreign keys in databases. In this lesson, I'll help you dodge the most frequent mistakes and hidden traps. A good database is like a solid bridge: if you mess up somewhere, the whole thing can collapse. Let's figure out how to keep your "data bridges" in shape.

Mistake 1: No Index on the Foreign Key

When you add a foreign key, you're telling the database: "Link these tables together." But if you don't explicitly create an index on that foreign key, complex queries involving those linked tables can get painfully slow.

Example of the problem:

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id)
);

Looks fine: tables are created, foreign key is there. But if you run a query like:

SELECT * 
FROM orders 
JOIN customers ON orders.customer_id = customers.customer_id;

for large amounts of data, this query can be super slow because PostgreSQL won't find a suitable index to optimize the join.

How to avoid:

PostgreSQL automatically creates an index on the parent column (the PRIMARY KEY or UNIQUE column that the FK references), but it does not create an index on the FK column in the child table. So for fast JOINs and cascading DELETE/UPDATE by FK, you need to add that index yourself:

CREATE INDEX idx_customer_id ON orders(customer_id);

Mistake 2: Wrong Order When Creating Tables

Imagine you're creating tables, but you try to add a foreign key before the table you're referencing even exists. PostgreSQL will freak out and throw errors because it can't find the target table.

Example of the problem:

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id)
);

-- Oops, where's the customers table?..
CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

Result: PostgreSQL throws an error because the customers table doesn't exist yet.

How to avoid:

Always create the tables you're referencing first, then add the foreign keys. The order matters. Here's the right way:

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id)
);

Mistake 3: Syntax Errors in Cascade Operations

Foreign keys often come with options like ON DELETE CASCADE or ON UPDATE RESTRICT. But if you mess up the syntax, your database might act weird. For example, deleting data in one table won't affect dependent tables.

Example of the problem:

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id) ON DELETE CASCADEE
);

If you look closely, you'll spot the typo — CASCADEE is spelled wrong. PostgreSQL won't let this slide.

How to avoid:

Writing it right is half the battle. If you're not sure, always check the official PostgreSQL docs.

Mistake 4: Breaking Data Integrity

Data integrity is the holy grail of any database, and foreign keys help keep it intact. But sometimes you forget to add a foreign key, and things go sideways.

Example of the problem:

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT
);

-- Inserting data
INSERT INTO orders (customer_id) VALUES (999);

Here we added an order for a non-existent customer. This breaks data integrity, and that order just "hangs" there.

How to avoid:

Always use foreign keys to prevent situations where one table references non-existent records. Let's rewrite the example the right way:

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id)
);

Now, trying to insert a "dangling" record will throw an error.

Mistake 5: Trying to Swallow Foreign Key Errors with ON CONFLICT

Sometimes devs reach for INSERT ... ON CONFLICT DO NOTHING hoping it will quietly skip rows whose foreign key doesn’t match. It won’t — and that’s a common misconception worth clearing up.

Example of the problem:

INSERT INTO orders (order_id, customer_id)
VALUES (1, 999)
ON CONFLICT DO NOTHING;
-- ERROR: insert or update on table "orders" violates foreign key constraint

ON CONFLICT only handles unique and exclusion constraint violations (as the PostgreSQL docs put it: “the ON CONFLICT clause specifies an alternative action to raising a unique violation or exclusion constraint violation error”). A foreign key violation goes right past it and raises the usual error.

How to avoid:

If your real intent is “silently skip rows whose parent doesn’t exist,” use INSERT ... SELECT ... WHERE EXISTS (...) — the same idiom used in the next section:

INSERT INTO orders (order_id, customer_id)
SELECT 1, 999
WHERE EXISTS (
    SELECT 1 FROM customers WHERE customer_id = 999
);

Mistake 6: Deleting Dependent Records Without ON DELETE

If you delete a record from a table that's referenced by a foreign key, but you didn't set up ON DELETE CASCADE, the dependent records stay in the database, breaking the whole point of the relationship.

Example of the problem:

DELETE FROM customers WHERE customer_id = 1;
-- Records in orders with customer_id = 1 are still there.

How to avoid:

Add the ON DELETE CASCADE directive so related records get deleted automatically:

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id) ON DELETE CASCADE
);

Now, when you delete a customer, their orders disappear too.

Mistake 7: Trouble with MANY-TO-MANY Relationships

When working with MANY-TO-MANY relationships, people sometimes forget to add a composite primary key or index the table.

Example of the problem:

CREATE TABLE enrollments (
    student_id INT REFERENCES students(student_id),
    course_id INT REFERENCES courses(course_id)
);

-- Oops! We forgot the PRIMARY KEY.

How to avoid:

Add a composite primary key or a unique index:

CREATE TABLE enrollments (
    student_id INT REFERENCES students(student_id),
    course_id INT REFERENCES courses(course_id),
    PRIMARY KEY (student_id, course_id)
);

Mistake 8: Cyclic References

Cyclic references happen when two tables reference each other as foreign keys. This creates a loop and causes problems when inserting data.

Example of the problem:

CREATE TABLE table_a (
    id SERIAL PRIMARY KEY,
    table_b_id INT REFERENCES table_b(id)
);

CREATE TABLE table_b (
    id SERIAL PRIMARY KEY,
    table_a_id INT REFERENCES table_a(id)
);

How to avoid:

Use DEFERRABLE INITIALLY DEFERRED so PostgreSQL can check data integrity after the transaction finishes:

CREATE TABLE table_a (
    id SERIAL PRIMARY KEY,
    table_b_id INT REFERENCES table_b(id) DEFERRABLE INITIALLY DEFERRED
);

Mistakes with foreign keys don't just slow down development — they can cause serious data headaches. Use this list as a cheat sheet to dodge the usual "gotchas." Remember: a foreign key is your buddy, not your enemy. Just treat it right, and your database will be a rock-solid foundation for your long-term project.