Foreign Keys Done Right: ON DELETE Rules, Indexing and Pitfalls

Declare every relationship as a foreign key, choose the referential action deliberately rather than accepting the default, and index the referencing column yourself on every engine except MySQL. Those three habits prevent most of the orphaned rows, lock storms and slow deletes that foreign keys get blamed for. The details, including deferrable constraints and adding keys to large tables, follow.

What a foreign key guarantees#

A foreign key states that every non-null value in the referencing column exists in the referenced column, and it makes the engine enforce that on every insert, update and delete. The referenced column must be a primary key or carry a unique constraint; it does not have to be the primary key, and it can be several columns. What you get in return is a database that cannot hold a pets.owner_id pointing at nobody, no matter which service, script or hurried engineer wrote the row.

ALTER TABLE pets
    ADD CONSTRAINT fk_pets_owners
    FOREIGN KEY (owner_id) REFERENCES owners (id)
    ON DELETE NO ACTION
    ON UPDATE CASCADE;

Relationships enforced only in application code are invisible to everyone else: to the reporting team, to the next service that writes to the table, and to any tool that draws the schema from the catalog. A diagram of such a schema shows tables floating with no arrows between them, which is a diagnosis in itself.

Referential actions#

The action decides what happens to child rows when the parent row is deleted or its key updated. All five exist in PostgreSQL; MySQL, SQL Server and Oracle support subsets, noted below.

The difference between NO ACTION and RESTRICT only shows when the check is deferred or when a single statement deletes the parent and the children together; for everyday schemas they behave the same, and NO ACTION is the portable spelling.

Cascades deserve respect. A cascade from customers to orders to order_items to payments turns one DELETE into the destruction of a financial history, and it does so silently. The rule that has aged well: cascade into rows that are part of the parent, never into rows that record something that happened. Deleting a product must not delete past order items; it should be blocked, or the product marked inactive instead.

Index the referencing column yourself#

Creating a foreign key creates no index on the referencing column in PostgreSQL, SQL Server or Oracle. The unique index on the referenced side already exists; the child side is your job. Without it, two things happen. Every delete or key update on the parent scans the child table to check for references, so deleting one owner scans all of pets, and joins from parent to child fall back to sequential scans. On Oracle the missing index additionally causes a full table lock on the child during parent updates, which is the classic cause of unexplained lock waits.

MySQL and MariaDB behave differently: InnoDB requires an index on the referencing column and creates one automatically if none exists. This is why schemas migrated from MySQL to PostgreSQL often become slow on deletes; the safety net is gone.

PostgreSQL can list the foreign keys that have no index starting with their first column:

SELECT c.conrelid::regclass AS table_name,
       c.conname            AS constraint_name,
       a.attname            AS column_name
FROM pg_constraint c
JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = c.conkey[1]
WHERE c.contype = 'f'
  AND NOT EXISTS (SELECT 1
                  FROM pg_index i
                  WHERE i.indrelid = c.conrelid
                    AND i.indkey[0] = c.conkey[1])
ORDER BY 1, 2;

Then add what is missing, named after the table and column:

CREATE INDEX CONCURRENTLY ix_pets_owner_id ON pets (owner_id);

Not every foreign key needs an index. A reference to a tiny lookup table that is never deleted from and rarely joined in the child-to-parent direction can live without one. The default should still be to index and to remove consciously, not the reverse.

Deferrable constraints#

By default a foreign key is checked at the end of each statement. PostgreSQL and Oracle can defer the check to the end of the transaction, which makes it possible to insert a parent and child in either order, swap two rows' references, or load a circular pair:

ALTER TABLE employees
    ADD CONSTRAINT fk_employees_manager
    FOREIGN KEY (manager_id) REFERENCES employees (id)
    DEFERRABLE INITIALLY IMMEDIATE;

BEGIN;
SET CONSTRAINTS fk_employees_manager DEFERRED;
INSERT INTO employees (id, manager_id) VALUES (2, 1);
INSERT INTO employees (id, manager_id) VALUES (1, 2);
COMMIT;

MySQL and SQL Server do not support deferrable constraints; there you either make one side nullable and update it in a second statement, or disable checks for the load, which is a sharp tool best kept for migrations run by hand.

Adding a foreign key to a large table#

Adding a constraint validates every existing row while holding a lock that blocks writes. On a table with hundreds of millions of rows that is an outage. PostgreSQL splits the two steps:

ALTER TABLE visits
    ADD CONSTRAINT fk_visits_pets FOREIGN KEY (pet_id) REFERENCES pets (id) NOT VALID;

-- later, without blocking reads or writes:
ALTER TABLE visits VALIDATE CONSTRAINT fk_visits_pets;

New rows are checked from the first statement on; existing rows are checked by the second, which takes a lock that does not block reads or writes. SQL Server has the equivalent WITH NOCHECK, with the caveat that an untrusted constraint is ignored by the query optimizer until it is re-checked. MySQL has no split; adding a foreign key to a large InnoDB table copies or rebuilds the table unless foreign key checks are disabled for the operation, so plan it.

Type mismatches and other pitfalls#

• Match the types exactly. InnoDB refuses a foreign key between INT and BIGINT or between signed and unsigned columns; PostgreSQL accepts more, but a mismatch still costs a cast on every check and join.
• Match the collation and character set for text keys in MySQL, or the constraint will not be created.
• Do not drop foreign keys to speed up bulk loads and forget to add them back. Add them back with NOT VALID and validate afterwards.
• Give constraints explicit names, so that the migration that later alters them works on every engine. See database naming conventions.
• Remember that ON UPDATE CASCADE is only useful when keys change, which with surrogate keys they should not. Its presence often signals a natural key that should not have been one.

Where SchemaDrawer fits#

Foreign keys are the lines on an ER diagram, and the fastest audit of a schema's integrity is to look for lines that should exist and do not. SchemaDrawer draws that diagram from the catalog itself: it runs your migrations in Docker against the engine and version you pick, reads tables, columns, primary keys and foreign keys back over JDBC and renders an SVG in which every foreign key is an arrow labelled with its column. A customer_id column with no arrow is a relationship someone forgot to declare. It draws constraints, not indexes, so the reverse-index check above remains yours. The getting started guide shows how to get a diagram in a few minutes.

Key takeaways#

• Every relationship is a foreign key; code-only relationships are invisible and unenforced.
• Choose NO ACTION, CASCADE or SET NULL per relationship; cascade only into rows that belong to the parent.
• Index referencing columns on PostgreSQL, SQL Server and Oracle; InnoDB does it for you.
• Use deferrable constraints for circular data where the engine offers them.
• Add keys to big tables with NOT VALID and validate in a second step.