Foreign keys

Backend Fundamentals · Databases · 10 min read

1. What problem does this solve?

Motivation

When building relational data models you often have entities that belong to other entities.
For example, a Post belongs to a User. In a database this relationship is represented by a foreign key column in the posts table that references the primary key of the users table.

The problem

• Orphan records – a post could exist without a corresponding user if the link is not enforced.
• Inconsistent data – deleting a user could leave dangling posts, or updating a user’s ID could break references.
• Business rule violations – you might want to prevent deleting a user who still has posts, or automatically delete all posts when a user is removed.

Why it matters

• Data integrity – guarantees that relationships are valid at the database level, not just in application code.
• Simplified application logic – you can rely on the database to enforce rules, reducing bugs.
• Performance – the database can use indexes on foreign keys to speed up joins and lookups.

Consequences of not using foreign keys

• Manual checks in code become error‑prone.
• Harder to maintain consistency across services or micro‑services.
• Increased risk of data corruption, especially under concurrent writes.

Where it appears in real backend systems

• E‑commerce platforms (orders → customers, order_items → orders)
• Social networks (posts → users, comments → posts)
• Content management systems (articles → authors)
• Financial systems (transactions → accounts)

2. Mental model

Simple analogy

Library books and shelves
Think of a library where each book must be placed on a shelf that exists.
Shelf → users table (primary key: user_id)
Book → posts table (foreign key: author_id referencing user_id)

If a shelf is removed, you decide whether to:

• Keep the books (orphaned) – not allowed in a well‑managed library.
• Move the books to another shelf – handled automatically by the system.
• Delete the books – cascade delete.

Precise engineering model

• Primary key – unique identifier for a row (e.g., user_id).
• Foreign key – a column that references a primary key in another table.
• Referential integrity – the database guarantees that every foreign key value either matches a primary key or is NULL (if nullable).
• Actions – ON DELETE / ON UPDATE clauses define what happens when the referenced row changes.

3. Deep explanation

Internals

• The database stores a constraint that links two columns.
• On INSERT/UPDATE, the engine checks that the foreign key value exists in the referenced table.
• On DELETE/UPDATE of the referenced row, the engine enforces the defined action (restrict, cascade, set null, set default).

Important terminology

Trade‑offs

Failure modes

• Deadlocks – cascading deletes can lock many rows; careful ordering or batching helps.
• Long transactions – deferred constraints can delay errors until commit, making debugging harder.
• Partial failures – if a cascade delete fails midway, you may end up with orphaned rows unless wrapped in a transaction.

Limitations

• Cross‑database references – most RDBMS do not support foreign keys across databases.
• Distributed systems – enforcing referential integrity across services requires additional coordination (e.g., two‑phase commit, sagas).
• No support in NoSQL – MongoDB, Redis, etc., don’t enforce foreign keys natively; you must handle it in application logic.

Where it fits in a backend architecture

• Data layer – the database enforces constraints.
• Service layer – may rely on database guarantees to simplify business logic.
• API layer – can expose errors like 23503 (foreign key violation) to clients.
• Cache layer – must invalidate or update cached data when foreign key changes occur.

Interaction with related concepts

• ORMs – map foreign keys to object relationships; can generate constraints automatically.
• Transactions – foreign key checks happen inside transactions; use DEFERRED for complex scenarios.
• Indexes – foreign key columns should be indexed for join performance.
• Replication – foreign key constraints are replicated to read replicas; consistency must be maintained.

4. How it works

Step‑by‑step flow

1. Create tables – define primary key and foreign key constraints.
2. Insert parent row – e.g., insert a user.
3. Insert child row – insert a post referencing the user’s id.
4. Attempt invalid insert – insert a post with a non‑existent author_id; database rejects.
5. Delete parent row – depending on ON DELETE action:
   • RESTRICT – fails if child rows exist.
   • CASCADE – deletes all child rows automatically.
   • SET NULL – sets author_id to NULL on child rows.
6. Update parent key – if ON UPDATE CASCADE, child rows’ foreign keys are updated automatically.

sequenceDiagram
    participant App
    participant DB
    App->>DB: INSERT INTO users (id, name) VALUES (1, 'Alice')
    DB-->>App: Success
    App->>DB: INSERT INTO posts (id, title, author_id) VALUES (10, 'Hello', 1)
    DB-->>App: Success
    App->>DB: DELETE FROM users WHERE id = 1
    DB->>App: (ON DELETE CASCADE) Deletes posts with author_id=1
    DB-->>App: Success

5. Practical implementation

Below is a minimal Node.js example using the pg library (PostgreSQL).
It demonstrates creating tables, inserting data, and observing cascade delete.

// db.ts
import { Pool } from 'pg';

export const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
});

// schema.ts
import { pool } from './db';

export async function initSchema() {
  // Create users table
  await pool.query(`
    CREATE TABLE IF NOT EXISTS users (
      id SERIAL PRIMARY KEY,
      name TEXT NOT NULL
    );
  `);

  // Create posts table with foreign key
  await pool.query(`
    CREATE TABLE IF NOT EXISTS posts (
      id SERIAL PRIMARY KEY,
      title TEXT NOT NULL,
      author_id INT NOT NULL,
      CONSTRAINT fk_author
        FOREIGN KEY (author_id)
        REFERENCES users(id)
        ON DELETE CASCADE   -- change to RESTRICT / SET NULL as needed
    );
  `);
}

// demo.ts
import { pool } from './db';
import { initSchema } from './schema';

async function runDemo() {
  await initSchema();

  // Insert a user
  const { rows: userRows } = await pool.query(
    `INSERT INTO users (name) VALUES ($1) RETURNING *`,
    ['Alice']
  );
  const user = userRows[0];
  console.log('Created user:', user);

  // Insert a post referencing the user
  const { rows: postRows } = await pool.query(
    `INSERT INTO posts (title, author_id) VALUES ($1, $2) RETURNING *`,
    ['My first post', user.id]
  );
  console.log('Created post:', postRows[0]);

  // Attempt to insert a post with a non‑existent author
  try {
    await pool.query(
      `INSERT INTO posts (title, author_id) VALUES ($1, $2)`,
      ['Invalid', 9999]
    );
  } catch (err: any) {
    console.error('Foreign key violation:', err.code); // 23503
  }

  // Delete the user – cascade deletes posts
  await pool.query(`DELETE FROM users WHERE id = $1`, [user.id]);
  console.log('Deleted user; posts should be gone.');

  // Verify posts table is empty
  const { rows: remainingPosts } = await pool.query(`SELECT * FROM posts`);
  console.log('Remaining posts:', remainingPosts);
}

runDemo().catch(console.error).finally(() => pool.end());

Key lines explained

6. Production considerations

7. Common mistakes

8. Senior engineer perspective

What changes at scale?

• Cross‑service data – In micro‑services you often keep data in separate databases; foreign keys become logical constraints enforced by the application or via event streams.
• Partitioning / sharding – Foreign key constraints cannot span shards; you must design sharding keys that keep related rows together or accept eventual consistency.
• Bulk operations – Large cascade deletes can become a bottleneck; consider soft deletes or background jobs.
• Monitoring – Track lock wait times, deadlocks, and constraint violation rates as early indicators of schema issues.

Design decisions

When NOT to use foreign keys

• When you need high write throughput and the overhead of constraint checks outweighs the benefit.
• In distributed micro‑service architectures where data lives in separate services; enforce consistency via events instead.
• With NoSQL stores that do not support constraints; rely on application logic or secondary indexes.

9. Interview questions

10. Hands‑on task

Task: Build a small blog API that enforces foreign key constraints between users and posts.

1. Create a PostgreSQL database (use Docker or local install).
2. Write a Node.js/Express server with TypeScript.
3. Define users and posts tables with a foreign key (author_id → users.id).
4. Implement endpoints:
   • POST /users – create a user.
   • POST /posts – create a post (must provide a valid author_id).
   • DELETE /users/:id – delete a user; observe cascade delete.
5. Add error handling to return a 400 status when a foreign key violation occurs.
6. Write a simple script that attempts to insert a post with an invalid author_id and logs the error.

Goal: Verify that the database enforces referential integrity and that your API correctly surfaces constraint violations.

11. Quick revision

• Foreign key links a child column to a parent primary key.
• Enforces referential integrity at the database level.
• ON DELETE / ON UPDATE actions: RESTRICT, CASCADE, SET NULL, SET DEFAULT.
• Must index foreign key columns for performance.
• Deferred constraints delay checks until transaction commit.
• In micro‑services, foreign keys become logical constraints; enforce via events.
• Common pitfalls: missing ON DELETE action, nullable FK without intent, no indexing.
• Production concerns: cascade delete performance, lock contention, monitoring constraint violations.
• Use pg or an ORM (Prisma, TypeORM) to map relationships; let the ORM generate constraints.
• Error code 23503 in PostgreSQL indicates a foreign key violation.

12. References

• PostgreSQL documentation – Foreign Key Constraints
• Node.js pg library – Official Docs
• Prisma – Foreign Key Relations
• TypeORM – Relations
• SQL standard – Foreign Key
• Docker – PostgreSQL Image