Should You Use Foreign Keys? Performance, Consistency, and When to Simulate Them

When persisting data, relational databases are the safest choice, and foreign keys (FK) are a key concept that links tables by ensuring a column in one table references a candidate key in another.

Foreign keys not only store an integer; they also provide referential integrity, guaranteeing that referenced data exists. However, many production systems rarely use FK constraints because they add extra workload to the database.

Referential integrity means every reference points to an existing row. In MySQL, NO ACTION and RESTRICT have the same semantics.

ALTER TABLE posts
ADD CONSTRAINT FOREIGN KEY (author_id)
REFERENCES authors(id);

The above SQL adds a foreign‑key constraint, requiring the posts table to have an author_id column that references authors.id.

FK types include RESTRICT, CASCADE, and SET NULL. The most common are RESTRICT (default) and CASCADE. RESTRICT checks existence on INSERT/UPDATE/DELETE, while CASCADE propagates changes.

Using RESTRICT triggers consistency checks when updating or deleting records.

Using CASCADE triggers automatic updates or deletions of dependent rows.

Note: In MySQL, NO ACTION and RESTRICT are equivalent.

Consistency checks add overhead. A benchmark using Go code compared a baseline table without FK to a table with FK ( foreign_key_posts) under the same workload. Results showed the FK case was slower, with overhead ranging from ~0.02% to ~11.5% across four runs.

BenchmarkBaseline-8      3770    309503 ns/op
BenchmarkForeignKey-8   3331    317162 ns/op
... (additional runs omitted for brevity) ...

To simulate FK behavior in the application layer, follow these guidelines:

Before INSERT or UPDATE, execute a SELECT to ensure the referenced row exists.

Before DELETE, execute a SELECT to check for existing references.

These checks should be performed inside a transaction to maintain atomicity. Example transaction for inserting/updating a post:

BEGIN
SELECT * FROM authors WHERE id = <post.author_id> FOR UPDATE;
-- INSERT INTO posts ... / UPDATE posts ...
END

When deleting an author, you must query all tables that reference it; with many tables, this becomes cumbersome and adds network overhead.

Consistency Check

Using RESTRICT ensures that any INSERT, UPDATE, or DELETE operation validates the foreign‑key relationship, preventing orphaned rows.

Cascading Operations

Defining a foreign key with ON UPDATE CASCADE ON DELETE CASCADE makes the database automatically propagate changes or deletions to dependent rows.

ALTER TABLE posts
ADD CONSTRAINT FOREIGN KEY (author_id)
REFERENCES authors(id)
ON UPDATE CASCADE
ON DELETE CASCADE;

Updating authors.id updates all matching posts.author_id values.

Deleting an author removes all related posts.

While cascade deletes maintain consistency, they can cause massive data removal in large databases, leading to performance spikes.

Manually implementing cascade deletes in application code is possible but usually incurs higher overhead than built‑in FK cascades.

DELETE FROM posts WHERE author_id = 1 LIMIT 100;
-- repeat until all rows are removed
DELETE FROM authors WHERE id = 1;

Summary

Foreign keys provide consistency guarantees but add computational cost to the database. In high‑concurrency, horizontally scalable services, using FK may limit throughput. Simulating FK in the application shifts work out of the database, increasing maintenance and network traffic, while dropping FK sacrifices data integrity for performance.

Not using FK reduces DB load but loses consistency.

Simulating FK trades some consistency for higher availability at the cost of extra code and network calls.

Using FK keeps data consistent and offloads work to the DB.

Choose the strategy based on consistency requirements, performance constraints, and team resources.