Foreign Key

LinkedIn Hook

"Your orders table has 1.2 million rows pointing to customers that no longer exist. Nobody noticed for a year."

Foreign keys are the single most important tool a relational database gives you for keeping data honest, and they are also the most commonly skipped. Teams disable them "for performance," forget to add them during a migration, or blindly slap ON DELETE CASCADE on every reference and wonder why deleting one user just wiped out half the database.

The truth is that a foreign key is not overhead — it is a contract. It tells the database "this column must always point to a real row in that other table, or the write fails." Without that contract, orphaned rows accumulate silently, reports lie, joins return nonsense, and the bug surfaces months later when a finance team asks why the numbers do not add up.

Get foreign keys right and your data stays consistent by construction. Get them wrong and you are one DELETE away from a production incident.

In Lesson 2.2, I break down foreign keys in PostgreSQL: inline vs table-level declarations, every ON DELETE and ON UPDATE action, self-referencing hierarchies, composite foreign keys, deferred constraints, and why you must index every FK column.

Read the full lesson -> [link]

#Database #PostgreSQL #SQL #DataModeling #BackendDevelopment #InterviewPrep

---

---

What You'll Learn

• What a foreign key is and how it enforces referential integrity between tables
• Inline vs table-level FK declarations and when each is preferred
• Every ON DELETE action: CASCADE, SET NULL, SET DEFAULT, RESTRICT, NO ACTION
• The ON UPDATE clause and when it matters (rarely, but it matters)
• Self-referencing foreign keys for trees and hierarchies like employee -> manager
• Composite foreign keys that reference multi-column primary keys
• Deferring constraint checks to end-of-transaction with DEFERRABLE INITIALLY DEFERRED
• Why every foreign key column should almost always have its own index

---

The Library Card Analogy — A Pointer That Must Resolve

Imagine a library's loan ledger. Every row in the ledger says "member #4812 borrowed book #2217 on April 3." The ledger does not store the member's name, address, or phone number — it just writes down the member ID. To find out who member #4812 actually is, you walk over to the members file and look them up.

Now imagine someone rips a page out of the members file — member #4812 is gone. But the loan ledger still says "member #4812 borrowed a book." That row is now a lie. The pointer no longer resolves. If a librarian runs a report of "all outstanding loans by member," member #4812's loan either vanishes or shows up as a ghost entry with no owner.

A foreign key is the rule that prevents this. It tells the database: "every value in loans.member_id must correspond to a real row in members.id. If you try to insert a loan with a member that does not exist, reject the write. If you try to delete a member who still has loans, either reject the delete or cascade the cleanup — but never leave a dangling pointer." The database enforces this on every write, so it is impossible for orphaned rows to exist in the first place.

That property — the guarantee that every reference resolves — is called referential integrity, and it is the single most important correctness property a relational database gives you.

+---------------------------------------------------------------+
|           PARENT - CHILD RELATIONSHIP                         |
+---------------------------------------------------------------+
|                                                                |
|   PARENT TABLE: customers                                      |
|   +------+-----------+-------------------+                     |
|   |  id  |   name    |       email       |                     |
|   +------+-----------+-------------------+                     |
|   |  1   |  Alice    |  alice@acme.io    |                     |
|   |  2   |  Bob      |  bob@acme.io      |                     |
|   |  3   |  Carol    |  carol@acme.io    |                     |
|   +------+-----------+-------------------+                     |
|                 ^          ^         ^                         |
|                 |          |         |                         |
|                 | FK: orders.customer_id -> customers.id       |
|                 |          |         |                         |
|   CHILD TABLE:  |  orders  |         |                         |
|   +------+-------------+----------+----------+                 |
|   | id   | customer_id |  total   |  status  |                 |
|   +------+-------------+----------+----------+                 |
|   | 100  |      1      |  49.00   | paid     |                 |
|   | 101  |      1      |  12.00   | paid     |                 |
|   | 102  |      2      |  99.00   | pending  |                 |
|   | 103  |      3      |  27.00   | paid     |                 |
|   +------+-------------+----------+----------+                 |
|                                                                |
|   Every customer_id in orders MUST exist in customers.id       |
|   or the INSERT/UPDATE is rejected by the database.            |
|                                                                |
+---------------------------------------------------------------+

---

Declaring a Foreign Key — Inline and Table-Level

PostgreSQL accepts two syntaxes for foreign keys: inline with the column definition, or as a separate table-level constraint. Both compile to the same constraint — the choice is stylistic, except when the FK spans multiple columns (composite), in which case only the table-level form works.

-- Parent table: customers
CREATE TABLE customers (
    id         BIGSERIAL PRIMARY KEY,
    name       TEXT NOT NULL,
    email      TEXT NOT NULL UNIQUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Child table with INLINE foreign key
-- The REFERENCES clause attaches directly to the column definition.
CREATE TABLE orders (
    id          BIGSERIAL PRIMARY KEY,
    customer_id BIGINT NOT NULL REFERENCES customers(id),
    total_cents INTEGER NOT NULL CHECK (total_cents >= 0),
    status      TEXT NOT NULL DEFAULT 'pending',
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Equivalent TABLE-LEVEL form
-- Preferred when you want to name the constraint explicitly, or when
-- the FK spans multiple columns.
CREATE TABLE order_items (
    id         BIGSERIAL PRIMARY KEY,
    order_id   BIGINT NOT NULL,
    product_id BIGINT NOT NULL,
    qty        INTEGER NOT NULL CHECK (qty > 0),

    CONSTRAINT fk_order_items_order
        FOREIGN KEY (order_id) REFERENCES orders(id)
        ON DELETE CASCADE
);

Naming matters. When PostgreSQL generates a constraint name for an inline FK, you get something like orders_customer_id_fkey. That is fine for small projects but becomes painful when error messages reference opaque names during a migration. Use the table-level form and name the constraint fk_<child>_<parent> so errors are self-explanatory.

Now let us prove that the constraint actually works.

-- Insert a valid customer and order
INSERT INTO customers (name, email) VALUES ('Alice', 'alice@acme.io');
-- customers: (1, 'Alice', ...)

INSERT INTO orders (customer_id, total_cents) VALUES (1, 4900);
-- orders: (1, 1, 4900, 'pending', ...)   OK

-- Try to insert an order pointing to a non-existent customer
INSERT INTO orders (customer_id, total_cents) VALUES (999, 1200);
-- ERROR:  insert or update on table "orders" violates foreign key
--         constraint "orders_customer_id_fkey"
-- DETAIL: Key (customer_id)=(999) is not present in table "customers".

The database refused the write. There is no way for orders.customer_id to ever hold a value that does not exist in customers.id — not even for a millisecond.

---

ON DELETE — What Happens When the Parent Row Disappears

The most important decision you make when declaring an FK is what happens if someone deletes the parent row. PostgreSQL supports five actions. Each has a legitimate use; the trick is knowing which one fits the relationship you are modeling.

-- Demonstration schema: one parent, five children, each with a different action
CREATE TABLE parents (
    id   BIGSERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

-- 1. CASCADE — delete children when parent is deleted
CREATE TABLE child_cascade (
    id        BIGSERIAL PRIMARY KEY,
    parent_id BIGINT NOT NULL REFERENCES parents(id) ON DELETE CASCADE
);

-- 2. SET NULL — null out the FK, keep the child row
CREATE TABLE child_set_null (
    id        BIGSERIAL PRIMARY KEY,
    parent_id BIGINT REFERENCES parents(id) ON DELETE SET NULL
    -- Note: column must be nullable for SET NULL to work
);

-- 3. SET DEFAULT — set the FK to its column default
CREATE TABLE child_set_default (
    id        BIGSERIAL PRIMARY KEY,
    parent_id BIGINT NOT NULL DEFAULT 0
        REFERENCES parents(id) ON DELETE SET DEFAULT
);

-- 4. RESTRICT — reject the parent delete if children exist (checked immediately)
CREATE TABLE child_restrict (
    id        BIGSERIAL PRIMARY KEY,
    parent_id BIGINT NOT NULL REFERENCES parents(id) ON DELETE RESTRICT
);

-- 5. NO ACTION — reject the parent delete if children exist (checked at end of statement;
-- can be deferred with DEFERRABLE). This is the default if you omit ON DELETE.
CREATE TABLE child_no_action (
    id        BIGSERIAL PRIMARY KEY,
    parent_id BIGINT NOT NULL REFERENCES parents(id) ON DELETE NO ACTION
);

Now let us demonstrate each one against real data.

-- Seed data
INSERT INTO parents (id, name) VALUES (0, 'SYSTEM'), (1, 'Alice'), (2, 'Bob');
INSERT INTO child_cascade     (parent_id) VALUES (1), (1), (2);
INSERT INTO child_set_null    (parent_id) VALUES (1), (2);
INSERT INTO child_set_default (parent_id) VALUES (1), (2);

-- CASCADE: deleting parent 1 removes all children with parent_id = 1
DELETE FROM parents WHERE id = 1;
-- child_cascade rows with parent_id = 1 are GONE
-- SELECT * FROM child_cascade;
--   id | parent_id
--    3 |     2

-- But wait — we also had child_set_null and child_set_default referencing parent 1.
-- The DELETE above would actually have failed because multiple FKs pointed at id=1
-- with different actions. Let us restart the demo per-table for clarity.

-- SET NULL: deleting the parent leaves the child but nulls the FK column
TRUNCATE parents CASCADE;
INSERT INTO parents (id, name) VALUES (0, 'SYSTEM'), (1, 'Alice');
INSERT INTO child_set_null (parent_id) VALUES (1), (1);
DELETE FROM parents WHERE id = 1;
-- SELECT * FROM child_set_null;
--   id | parent_id
--    1 |   NULL
--    2 |   NULL

-- SET DEFAULT: deleting the parent sets the child FK to its column default (0)
-- The default value must itself exist in the parent table, or the action fails.
INSERT INTO child_set_default (parent_id) VALUES (1);
DELETE FROM parents WHERE id = 1;
-- child_set_default row now has parent_id = 0 (the SYSTEM sentinel)

-- RESTRICT: reject the delete immediately
INSERT INTO parents (id, name) VALUES (5, 'Carol');
INSERT INTO child_restrict (parent_id) VALUES (5);
DELETE FROM parents WHERE id = 5;
-- ERROR:  update or delete on table "parents" violates foreign key
--         constraint "child_restrict_parent_id_fkey" on table "child_restrict"
-- DETAIL: Key (id)=(5) is still referenced from table "child_restrict".

The critical nuance between RESTRICT and NO ACTION: both reject the delete, but RESTRICT checks immediately and cannot be deferred, while NO ACTION checks at the end of the statement (or end of transaction if the constraint is DEFERRABLE). For simple use cases they behave identically. For complex transactions where you temporarily break referential integrity and fix it before commit, you need NO ACTION with deferring enabled.

---

ON UPDATE — When the Parent Key Changes

ON UPDATE mirrors ON DELETE but fires when the referenced column's value changes. In practice, you should almost never need it, because primary keys should be immutable. If customers.id is a surrogate key like BIGSERIAL or UUID, its value never changes, so ON UPDATE never fires.

ON UPDATE CASCADE becomes relevant only when your primary key is a natural key that can legitimately change — for example, a country code that gets reassigned, or a SKU that gets renamed. In that case, ON UPDATE CASCADE propagates the rename to every child row automatically.

CREATE TABLE countries (
    code CHAR(2) PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE addresses (
    id           BIGSERIAL PRIMARY KEY,
    country_code CHAR(2) NOT NULL
        REFERENCES countries(code)
        ON UPDATE CASCADE   -- rename propagates
        ON DELETE RESTRICT  -- but do not allow deleting a country still in use
);

INSERT INTO countries VALUES ('UK', 'United Kingdom');
INSERT INTO addresses (country_code) VALUES ('UK'), ('UK');

-- Rename UK -> GB; the cascade updates both addresses automatically
UPDATE countries SET code = 'GB' WHERE code = 'UK';
-- addresses.country_code is now 'GB' for both rows

Rule of thumb: use surrogate primary keys and you will never need ON UPDATE. If you find yourself reaching for it, ask whether your "primary key" should actually have been a surrogate with the natural key stored as a separate UNIQUE column.

---

Self-Referencing Foreign Keys — Trees and Hierarchies

A foreign key does not have to point at a different table. It can reference the same table it lives in. This is how you model trees: org charts, category hierarchies, comment threads, file system directories.

-- Employee tree: every employee has an optional manager who is also an employee
CREATE TABLE employees (
    id         BIGSERIAL PRIMARY KEY,
    name       TEXT NOT NULL,
    title      TEXT NOT NULL,
    manager_id BIGINT REFERENCES employees(id) ON DELETE SET NULL
    -- NULL manager_id means this employee is the root (CEO)
);

INSERT INTO employees (id, name, title, manager_id) VALUES
    (1, 'Alice',  'CEO',       NULL),
    (2, 'Bob',    'VP Eng',    1),
    (3, 'Carol',  'VP Sales',  1),
    (4, 'Dan',    'EM',        2),
    (5, 'Eve',    'Senior SWE',4),
    (6, 'Frank',  'SWE',       4);

The resulting tree:

                Alice (CEO, id=1)
                /              \
          Bob (VP Eng, 2)    Carol (VP Sales, 3)
               |
           Dan (EM, 4)
           /         \
    Eve (Sr SWE, 5)  Frank (SWE, 6)

You can walk the tree with a recursive CTE — the canonical way to query hierarchies in SQL.

-- Find every descendant of Bob (id = 2)
WITH RECURSIVE subtree AS (
    -- Anchor: start with Bob himself
    SELECT id, name, title, manager_id, 0 AS depth
    FROM employees
    WHERE id = 2

    UNION ALL

    -- Recursive step: join children onto the current frontier
    SELECT e.id, e.name, e.title, e.manager_id, s.depth + 1
    FROM employees e
    JOIN subtree s ON e.manager_id = s.id
)
SELECT repeat('  ', depth) || name AS tree, title
FROM subtree
ORDER BY depth, id;

-- Expected output:
--          tree          |    title
-- -----------------------+------------
--  Bob                   | VP Eng
--    Dan                 | EM
--      Eve               | Senior SWE
--      Frank             | SWE

The self-referencing FK with ON DELETE SET NULL is the right default for org charts: if a manager leaves, their direct reports are not deleted — they just become temporarily unassigned until HR updates the manager_id. Using CASCADE here would be catastrophic: deleting one middle manager would delete their entire subtree.

---

Composite Foreign Keys — Multi-Column References

When the parent's primary key is composite, the FK must reference all columns. This only works with the table-level syntax.

-- Parent: a tenant-scoped products table
CREATE TABLE products (
    tenant_id BIGINT NOT NULL,
    sku       TEXT   NOT NULL,
    name      TEXT   NOT NULL,
    price_cents INTEGER NOT NULL,
    PRIMARY KEY (tenant_id, sku)
);

-- Child: order lines must reference a product within the SAME tenant
CREATE TABLE order_lines (
    id         BIGSERIAL PRIMARY KEY,
    order_id   BIGINT NOT NULL,
    tenant_id  BIGINT NOT NULL,
    sku        TEXT   NOT NULL,
    qty        INTEGER NOT NULL,

    CONSTRAINT fk_order_lines_product
        FOREIGN KEY (tenant_id, sku) REFERENCES products(tenant_id, sku)
        ON DELETE RESTRICT
);

Composite FKs are how you enforce multi-tenant isolation at the schema level: there is no way for a child row in tenant A to accidentally reference a product in tenant B, because (tenant_id, sku) must match as a pair.

---

Deferring Constraints — DEFERRABLE INITIALLY DEFERRED

Sometimes you need to temporarily break referential integrity inside a transaction and fix it before commit. Classic case: inserting rows into two tables that reference each other, or renumbering IDs in a batch. PostgreSQL supports this with deferrable constraints.

CREATE TABLE nodes (
    id   BIGINT PRIMARY KEY,
    next BIGINT,

    CONSTRAINT fk_nodes_next
        FOREIGN KEY (next) REFERENCES nodes(id)
        DEFERRABLE INITIALLY DEFERRED
);

-- Inside a transaction we can insert two rows that point at each other
BEGIN;
    INSERT INTO nodes (id, next) VALUES (1, 2);  -- references row 2 which doesn't exist yet
    INSERT INTO nodes (id, next) VALUES (2, 1);  -- references row 1
    -- Neither insert fails immediately because the constraint is deferred.
COMMIT;
-- At COMMIT time, PostgreSQL verifies both FKs resolve. Both do, so the commit succeeds.

Without DEFERRABLE INITIALLY DEFERRED, the first INSERT would fail immediately because next = 2 does not yet exist. With deferring, the check is postponed to the end of the transaction, and as long as the final state is consistent, the writes succeed.

Use deferring sparingly. It is a real tool for genuine chicken-and-egg problems, but it also makes reasoning about data integrity harder — a constraint violation can now surface at COMMIT instead of at the offending statement.

---

Indexing Foreign Keys — The Silent Performance Killer

PostgreSQL does not automatically index foreign key columns. This trips up everyone eventually. The parent's primary key is indexed (it is a PK), but the FK column on the child side is not, unless you create an index yourself.

Why it matters:

1. Joins. Every JOIN orders ON orders.customer_id = customers.id scans all of orders unless customer_id is indexed.
2. Parent deletes. When you delete a customer, PostgreSQL must find every row in every child table that references that customer to apply the ON DELETE action. Without an index on orders.customer_id, that is a full table scan. Delete 100 customers and you have scanned orders 100 times.
3. Foreign key checks on updates. Updating a parent's PK (if mutable) or moving a child's FK requires looking up rows — all of which benefit from an index on the FK side.

-- Always create this explicitly alongside the FK
CREATE INDEX idx_orders_customer_id ON orders(customer_id);

-- For composite FKs, index the same column order as the FK declaration
CREATE INDEX idx_order_lines_tenant_sku ON order_lines(tenant_id, sku);

The rule: every foreign key column should have an index, unless you have measured and proven otherwise. The overhead of the index is almost always dwarfed by the cost of not having it the first time you delete a parent row.

---

Napkin AI Visual Prompt: "Dark gradient (#0a0f1f -> #111a2e). Center: two white monospace table rectangles labeled 'customers' (parent) and 'orders' (child). A glowing sky blue (#4fc3f7) arrow labeled 'FK: customer_id -> id' connects them. Below, five color-coded branches labeled CASCADE, SET NULL, SET DEFAULT, RESTRICT, NO ACTION — each showing a miniature before/after of a parent delete. On the right, a self-referencing rose (#ff5c8a) loop on an 'employees' table labeled 'manager_id -> id'. White monospace labels throughout, subtle grid overlay."

---

Common Mistakes

1. Forgetting to index foreign key columns. PostgreSQL indexes primary keys automatically, but not FK columns on the child side. The first time you DELETE FROM customers WHERE id = 42, the database sequentially scans every child table that references customers(id) to enforce the FK action. On a table with millions of rows and no index, a single parent delete can take minutes. The fix is a one-line CREATE INDEX for every FK column. Make it part of your migration template so you never forget.

2. Using ON DELETE CASCADE blindly. Cascading deletes feel convenient — "clean up the child rows automatically, done." The problem is that cascades compose. If users -> sessions -> events -> audit_log all cascade, deleting one user can recursively wipe millions of rows across a dozen tables, locking them for the duration and potentially crashing replication. Worse, a mistaken DELETE FROM users in a console becomes an irrecoverable disaster. Prefer RESTRICT or soft deletes (a deleted_at timestamp) for anything user-facing. Reserve CASCADE for tightly coupled containment relationships — order lines belonging to an order, tags belonging to a post — where the child has no meaning without its parent.

3. No foreign keys at all, resulting in orphaned rows. "We'll enforce it in the application layer" is the most common excuse for skipping FKs, and it is always a lie. Sooner or later a bulk import, a manual console fix, a racing request, or a buggy ORM will write a child row whose parent does not exist, and the database will happily accept it because there is no constraint saying otherwise. Months later, a join returns nonsense and a finance report is wrong. A FK is one line in a migration; an orphaned-row incident takes days to clean up.

4. Type mismatch between FK and referenced column. PostgreSQL requires the FK column and the referenced column to be of compatible types. A classic bug is declaring customers.id BIGSERIAL (which is BIGINT) and orders.customer_id INTEGER — the FK declaration will either fail or force silent type coercion on every check. Worse, mismatched types prevent index use in joins, so a query that should be a 0.1ms index lookup becomes a 500ms hash join. Always mirror types exactly: if the parent is BIGINT, the child is BIGINT; if the parent is UUID, the child is UUID.

5. Circular foreign keys you cannot insert into. Two tables that reference each other (table A has an FK to table B, and table B has an FK to table A) create a chicken-and-egg problem: you cannot insert into either without the other existing first. Beginners try to fix this by making one side nullable and inserting in two steps, which works but is fragile. The right fix is either (a) rethink the schema — usually one of the FKs is actually a one-to-many that belongs elsewhere, or (b) declare one of the constraints as DEFERRABLE INITIALLY DEFERRED so both inserts happen inside a transaction and the check runs at commit.

---

Interview Questions

1. "What is referential integrity, and how does a foreign key enforce it?"

Referential integrity is the guarantee that every reference from one row to another resolves to a real, existing row. In relational terms: if orders.customer_id contains the value 42, there must be a row in customers with id = 42. A foreign key is the constraint that enforces this guarantee at the database level. On every INSERT or UPDATE to the child table, the database checks that the new FK value exists in the parent. On every DELETE or key UPDATE in the parent, the database checks that no child rows are left dangling, and applies the ON DELETE / ON UPDATE action to either cascade, null out, or reject the operation. The key property is that this enforcement happens inside the database, so no application bug, no manual SQL, and no bulk import can ever produce orphaned rows — it is impossible for the constraint to be violated and the transaction to still commit.

2. "Walk me through every ON DELETE action and when you would use each."

PostgreSQL supports five actions. CASCADE deletes child rows when the parent is deleted — use it for tightly coupled containment relationships like order lines belonging to an order, where the child has no meaning without the parent. SET NULL nulls out the FK in the child while keeping the child row — use it when the relationship is optional and losing the parent should not delete the child, like an employee whose manager has left. SET DEFAULT sets the FK to the column's default value, which must itself reference a real parent row — use it with a sentinel parent like a SYSTEM user, rarely useful. RESTRICT rejects the parent delete immediately if any child rows reference it — use it as the safe default for most user-facing entities where you want an explicit decision before cleanup. NO ACTION is similar to RESTRICT but checks at the end of the statement rather than immediately, which allows it to be deferred to end-of-transaction with DEFERRABLE INITIALLY DEFERRED — use it when you need to break and re-establish integrity inside a single transaction. My rule of thumb: default to RESTRICT for anything important, CASCADE only when the child literally cannot exist without the parent, and SET NULL for optional relationships.

3. "Why should every foreign key column have an index? PostgreSQL doesn't create it automatically, right?"

Correct — PostgreSQL indexes primary keys automatically but does not index FK columns on the child side. This matters for three reasons. First, joins: every query that joins child to parent on the FK column will benefit from an index on the child side; without one, the join degrades to a hash or sequential scan. Second, and more importantly, parent deletes and key updates: when you delete a row from the parent, PostgreSQL has to find every child row that references it to apply the ON DELETE action, and without an index that becomes a full table scan of the child — catastrophic on large tables. I have seen a one-row DELETE FROM users take minutes because the sessions table had a hundred million rows and no index on user_id. Third, constraint checking on writes benefits from the index when the database verifies that a referenced row still exists. The fix is one line — CREATE INDEX idx_<table>_<fk_column> ON <table>(<fk_column>) — and it should be in your migration alongside every FK declaration.

4. "How would you model an organizational hierarchy with foreign keys, and how would you query it?"

I would use a self-referencing foreign key on a single employees table: each row has a manager_id that references employees.id, with ON DELETE SET NULL so that departing managers leave their reports temporarily unassigned rather than cascading deletions. The root of the tree (the CEO) has manager_id = NULL. To query the hierarchy — for example, "find every descendant of this employee" — I would use a recursive CTE: the anchor clause selects the starting employee, and the recursive clause joins employees on manager_id = <previous row's id>, walking the tree level by level. This is the canonical SQL pattern for hierarchies and works in PostgreSQL, SQL Server, Oracle, and modern MySQL. For very deep trees or heavy read workloads, alternative representations exist — nested sets, materialized path, closure tables — but the self-referencing FK with a recursive CTE is the default and handles the vast majority of use cases cleanly.

5. "What is a deferrable constraint, and when would you use one?"

A deferrable constraint lets PostgreSQL postpone the FK check from "immediately after each statement" to "at the end of the transaction." You declare it with DEFERRABLE INITIALLY DEFERRED on the constraint itself. The use case is transactions that temporarily break referential integrity and fix it before commit — classic examples include inserting two rows that reference each other (a circular FK), renumbering primary keys in a batch update, or restoring data from a dump where insertion order cannot satisfy every FK immediately. With deferred checking, each individual INSERT or UPDATE succeeds without running the FK check, and PostgreSQL verifies all deferred constraints at COMMIT. If the final state is consistent, the commit succeeds; if not, the commit fails and the whole transaction rolls back. The tradeoff is that errors surface later and are harder to trace to a specific statement, so deferring should be a deliberate choice for specific operations, not a global default. Most production schemas never need it.

---

Quick Reference — Foreign Key Cheat Sheet

+---------------------------------------------------------------+
|           ON DELETE / ON UPDATE ACTIONS                       |
+---------------------------------------------------------------+
|                                                                |
|  CASCADE      Child rows are deleted / updated with parent    |
|               Use for: containment (order_items in orders)    |
|                                                                |
|  SET NULL     Child FK becomes NULL, row stays                |
|               Use for: optional links (employee.manager_id)   |
|               Requires: FK column is nullable                 |
|                                                                |
|  SET DEFAULT  Child FK becomes the column DEFAULT value      |
|               Use for: sentinel parent (SYSTEM user)          |
|               Requires: default value exists in parent        |
|                                                                |
|  RESTRICT     Reject parent change immediately                |
|               Use for: safe default, user-facing entities     |
|               Cannot be deferred                              |
|                                                                |
|  NO ACTION    Reject parent change at end of statement        |
|               Use for: same as RESTRICT but deferrable        |
|               Default if ON DELETE clause is omitted          |
|                                                                |
+---------------------------------------------------------------+

+---------------------------------------------------------------+
|           KEY RULES                                            |
+---------------------------------------------------------------+
|                                                                |
|  1. Every FK column should have an index (manual)             |
|  2. FK type must match parent PK type EXACTLY                 |
|  3. Default to RESTRICT; use CASCADE only for containment     |
|  4. Name constraints: fk_<child>_<parent>                     |
|  5. Self-referencing FKs + recursive CTEs model trees         |
|  6. Composite FKs need table-level syntax                     |
|  7. Use DEFERRABLE INITIALLY DEFERRED for circular writes     |
|  8. Immutable surrogate PKs make ON UPDATE unnecessary        |
|                                                                |
+---------------------------------------------------------------+

Scenario	Wrong Way	Right Way
Order lines under order	No FK, "trust the app"	FK ON DELETE CASCADE
User deletion	CASCADE everywhere	RESTRICT + soft delete
Manager leaves company	CASCADE (deletes team!)	ON DELETE SET NULL
Parent PK type BIGINT	Child FK INTEGER	Child FK BIGINT
Join on child.parent_id	No index on FK column	CREATE INDEX on FK
Circular reference	Two-step insert + NULL hack	DEFERRABLE INITIALLY DEFERRED
Multi-tenant isolation	Single-column FK	Composite (tenant_id, sku) FK
Renaming a country code	Update child rows manually	ON UPDATE CASCADE

---

Prev: Lesson 2.1 -- Primary Key Next: Lesson 2.3 -- Unique and Candidate Key

---

This is Lesson 2.2 of the Database Interview Prep Course -- 12 chapters, 58 lessons.