I’ve used foreign keys for quite some time, and somewhere back of the mind I used to think that the database stores some kind of pointer or reference to the parent row — similar to how references work in code. But it doesn’t.
It doesn’t. It’s just a plain value. And that one realization led me down a rabbit hole that changed a few things about Foreign Keys.
The Common Misconception
When you define a foreign key, most people, including me previously, assume that the database stores some kind of pointer or reference to the parent row. It doesn’t.
When you have:
CREATE TABLE product (
id SERIAL PRIMARY KEY,
name TEXT,
category_id INT REFERENCES category(id),
brand_id INT REFERENCES brand(id)
The `category_id` column stores just the plain integer value — `1`, `2`, `42`. No memory address, no reference object. Just the number.
The relationship only comes alive at query time when you JOIN or preload. The database matches values in memory and stitches the result together. The two tables are completely separate on disk.
So What Does a Foreign Key Actually Do?
A foreign key constraint is purely a data integrity rule.
Before writing your row, the database verifies that the referenced value exists in the parent table. If it doesn’t, the insert is rejected. That’s it.
No pointer. No physical link. Just a pre-write verification. But obviously, don’t trust my words. Let’s prove this part.
CREATE TABLE category (
id SERIAL PRIMARY KEY,
name TEXT
);
CREATE TABLE brand (
id SERIAL PRIMARY KEY,
name TEXT
);
CREATE TABLE product (
id SERIAL PRIMARY KEY,
name TEXT,
category_id INT REFERENCES category(id),
brand_id INT REFERENCES brand(id)
);
INSERT INTO category (name) VALUES ('Electronics');
INSERT INTO brand (name) VALUES ('Nike');
Now enable auto_explain to expose internal operations:
SET log_min_duration_statement = 0;
SET client_min_messages = LOG;
LOAD 'auto_explain';
SET auto_explain.log_min_duration = 0;
SET auto_explain.log_analyze = true;
SET auto_explain.log_nested_statements = true;
Now insert a valid product row:
INSERT INTO product (name, category_id, brand_id)
VALUES ('Shoes', 1, 1);
Here’s what PostgreSQL actually logged:
duration: 0.309 ms execute <unnamed>: SET client_min_messages = LOG
duration: 0.468 ms parse <unnamed>: SET client_min_messages = LOG
duration: 0.054 ms bind <unnamed>: SET client_min_messages = LOG
duration: 0.321 ms execute <unnamed>: SET client_min_messages = LOG
duration: 14.537 ms parse <unnamed>: INSERT INTO product (name, category_id, brand_id)
VALUES ('Shoes', 1, 1)
duration: 7.120 ms bind <unnamed>: INSERT INTO product (name, category_id, brand_id)
VALUES ('Shoes', 1, 1)
duration: 1.941 ms plan:
Query Text: SELECT 1 FROM ONLY "public"."category" x WHERE "id" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x
Query Parameters: $1 = '1'
LockRows (cost=0.15..8.18 rows=1 width=10) (actual time=1.937..1.938 rows=1 loops=1)
-> Index Scan using category_pkey on category x (cost=0.15..8.17 rows=1 width=10) (actual time=0.742..0.742 rows=1 loops=1)
Index Cond: (id = $1)
duration: 0.597 ms plan:
Query Text: SELECT 1 FROM ONLY "public"."brand" x WHERE "id" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x
Query Parameters: $1 = '1'
LockRows (cost=0.15..8.18 rows=1 width=10) (actual time=0.446..0.447 rows=1 loops=1)
-> Index Scan using brand_pkey on brand x (cost=0.15..8.17 rows=1 width=10) (actual time=0.432..0.432 rows=1 loops=1)
Index Cond: (id = $1)
duration: 24.123 ms plan:
Query Text: INSERT INTO product (name, category_id, brand_id)
VALUES ('Shoes', 1, 1)
Insert on product (cost=0.00..0.01 rows=0 width=0) (actual time=13.145..13.146 rows=0 loops=1)
-> Result (cost=0.00..0.01 rows=1 width=44) (actual time=4.637..4.639 rows=1 loops=1)
duration: 31.248 ms execute <unnamed>: INSERT INTO product (name, category_id, brand_id)
VALUES ('Shoes', 1, 1)
If you read the above logs carefully, you’d find two hidden SELECTs fired before the INSERT.
I knew FK constraints did some checks, but I assumed it was happening as part of the write, not as separate lookups before it. Seeing the actual query logs made it click instantly. Good to have proof of how FK values are just values and not some kinda pointers.
The Myth of Foreign Key References in PostgreSQL was originally published in Level Up Coding on Medium, where people are continuing the conversation by highlighting and responding to this story.
