When Should You Use Foreign Keys? Benefits, Risks, and Performance Impact

Foreign Key Basics

A foreign key is a constraint that links a column (or set of columns) in one table to a candidate key—usually the primary key—of another table. It enforces referential integrity , ensuring that every referenced value exists in the parent table.

Common foreign‑key actions are: RESTRICT (or NO ACTION in MySQL): prevents INSERT, UPDATE or DELETE when the referenced row would become invalid. CASCADE: automatically propagates UPDATE or DELETE operations to dependent rows. SET NULL: sets the foreign‑key column to NULL when the parent row is deleted.

Creating a Foreign Key

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

Referential‑Integrity Checks (RESTRICT)

When a foreign key uses the default RESTRICT behavior, the database performs the following checks:

On INSERT into posts, it verifies that the supplied author_id exists in authors.

On UPDATE of posts.author_id, it re‑checks the existence of the referenced author.

On DELETE of an authors row, it aborts the operation if any posts still reference that author.

Performance Impact

A simple Go benchmark comparing operations on a table with and without a foreign‑key constraint shows a measurable overhead:

BenchmarkBaseline-8      3770    309503 ns/op
BenchmarkForeignKey-8    3331    317162 ns/op

BenchmarkBaseline-8      3192    315506 ns/op
BenchmarkForeignKey-8    3381    315577 ns/op

BenchmarkBaseline-8      3298    312761 ns/op
BenchmarkForeignKey-8    3829    345342 ns/op

BenchmarkBaseline-8      3753    291642 ns/op
BenchmarkForeignKey-8    3948    325239 ns/op

The extra cost ranged from ~0.02 % to over 11 % depending on the run, confirming that referential checks add CPU and I/O overhead.

Simulating Foreign Keys in Application Code

If you choose to omit database‑enforced foreign keys, you can enforce the same rules in the application layer:

Before INSERT or UPDATE of a child row, execute a SELECT to confirm the parent row exists.

Before deleting a parent row, execute a SELECT to ensure no child rows reference it.

Wrap the check(s) and the subsequent data modification in a single transaction to guarantee atomicity.

Typical transaction pattern:

BEGIN;
SELECT 1 FROM authors WHERE id = ? FOR UPDATE;  -- verify author exists
-- INSERT INTO posts ...  or  UPDATE posts ...
COMMIT;

Cascade Operations

To let the database handle propagation automatically, define the foreign key with ON UPDATE CASCADE and/or ON DELETE CASCADE:

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

With CASCADE, updating an author's primary key updates all referencing posts.author_id values, and deleting an author removes all related posts rows. In large tables, cascade deletes can cause massive I/O spikes.

When you need to limit the impact, perform manual batch deletes:

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

Manual batching reduces instantaneous load but incurs more round‑trips and overall overhead compared to built‑in cascade handling.

Conclusion

Foreign keys provide strong data consistency guarantees but introduce additional CPU and I/O work. They are advisable when strict referential integrity is required and the workload can tolerate the modest performance penalty. In highly concurrent, horizontally‑scaled services where latency is critical, developers may opt to enforce lightweight checks in the application layer, accepting the trade‑off of reduced consistency and higher maintenance effort.