What Is a Foreign Key Constraint? Referential Integrity Explained

A foreign key constraint is a rule on a database column that requires its value to match a value that already exists in another table — usually that table’s primary key. It’s how a relational database enforces referential integrity: the guarantee that a reference between two rows always points somewhere real. If an orders table has a customer_id column with a foreign key pointing at customers.id, the database will refuse to insert an order for a customer ID that doesn’t exist, and by default it will also refuse to delete a customer who still has orders referencing them.

The basic syntax

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  total NUMERIC NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

Once this constraint exists, INSERT INTO orders (customer_id, total) VALUES (999, 50) fails if no customer with ID 999 exists. This check happens at the database layer, not in application code, which means it holds regardless of which service, script, or ad hoc query touches the table — a guarantee that’s much harder to enforce reliably in application logic alone, especially once more than one service writes to the same database.

What happens on delete or update

A foreign key constraint has to specify what happens when the row it points to is deleted or its key changes. The common options:

• RESTRICT (or the default in many databases) — block the delete or update entirely if any referencing rows exist.
• CASCADE — automatically delete (or update) the referencing rows too. Deleting a customer would delete all their orders.
• SET NULL — set the referencing column to NULL instead of deleting the row. Requires the foreign key column to be nullable.
• SET DEFAULT — set the referencing column to a predefined default value.

FOREIGN KEY (customer_id) REFERENCES customers(id)
  ON DELETE CASCADE
  ON UPDATE RESTRICT

CASCADE is convenient but dangerous if applied casually — a single delete on a parent table can silently remove far more data than intended once cascades chain across several tables. RESTRICT is the safer default for anything where accidental data loss is costly; it forces an explicit decision (delete the children first, or reassign them) rather than letting the database make that decision for you.

Composite and self-referencing keys

A foreign key can reference more than one column at once — a composite key — when the parent table’s identity isn’t a single column:

FOREIGN KEY (order_id, product_id) REFERENCES order_items(order_id, product_id)

A table can also reference itself, which is the standard way to model hierarchical data like an org chart or a category tree:

CREATE TABLE categories (
  id INTEGER PRIMARY KEY,
  parent_id INTEGER REFERENCES categories(id)
);

Foreign keys and indexing

A foreign key constraint checks the referenced column (the primary key on the parent table), which is indexed automatically. It does not automatically index the referencing column on the child table in every database system — that’s a separate decision. Without an index on orders.customer_id, deleting a customer under RESTRICT requires a full scan of orders to check whether any rows reference it, and lookups filtering by customer become slow too. In practice, foreign key columns are almost always worth indexing explicitly, since they’re frequently used in joins as well as in the constraint check itself — see how SQL joins work for why a matching index on both sides of a join keeps it fast.

Why enforce this at the database instead of the application

Application-level validation — checking that a customer ID exists before inserting an order — can be bypassed by a bug, a direct database script, a second application writing to the same tables, or a race condition between a check and an insert. A database-enforced foreign key constraint closes all of those gaps at once, because the check happens as part of the write itself, inside the same transaction. This is the same principle behind enforcing structure through database normalization: push the rules that must always hold into the schema, rather than trusting every piece of code that touches the data to remember them.

The cost is a small amount of overhead on every write that touches a constrained column, since the database has to verify the referenced row exists. For most applications this overhead is negligible next to the cost of a corrupted reference reaching production. It’s a genuine tradeoff worth reconsidering at very high write volumes or in sharded setups where the referenced row may live on a different physical node — cross-shard foreign keys generally aren’t enforceable by the database at all, which is one reason heavily sharded systems often push referential integrity checks back into application logic.

When teams skip foreign keys

Some systems — particularly analytics warehouses, high-throughput event pipelines, or heavily sharded operational databases — deliberately omit foreign key constraints, accepting the risk of orphaned references in exchange for write throughput or the flexibility to shard tables independently. This is a legitimate tradeoff when the data is append-only, derived from a system that already enforces integrity upstream, or reconciled through some other batch process. It’s a much riskier tradeoff in a primary transactional database, where the constraint is often the only thing standing between a bug and silently corrupted relationships.

The takeaway

A foreign key constraint keeps a column’s values anchored to real rows in another table, enforced by the database itself rather than by application code that might forget to check. ON DELETE/ON UPDATE behavior — RESTRICT, CASCADE, SET NULL — decides what happens when the row it points to disappears, and that choice deserves as much scrutiny as the constraint itself, since CASCADE can quietly delete far more than expected. Index the referencing column, and think carefully before dropping the constraint entirely just for write throughput — it’s usually cheaper to keep than to debug orphaned data later.