PrismitaLEARN · PRACTICE · GROW

What is a foreign key, and what is referential integrity?

SQL Server Keys & Constraints Basic Fresher1–2 Yrs Conceptual 1 min read

Quick Answer

A foreign key is a column (or set) in one table that references the primary/unique key of another, enforcing that every child row points to a real parent row — that's referential integrity. It prevents orphaned records and can cascade deletes/updates to children.

A foreign key links a child table to a parent — an order's CustomerId must match an actual customer. That's referential integrity: the database guarantees you can't have an order pointing at a customer that doesn't exist, and you can't delete a customer who still has orders unless you've set up a cascade. The practical thing people forget is that SQL Server doesn't automatically index the foreign key column, and without that index, joins and cascade deletes on it do full scans and hold a lot of locks.

  • You can't insert an Order with a CustomerId that doesn't exist, and you can't delete a Customer that still has orders — unless you configure ON DELETE CASCADE (delete children) or ON DELETE SET NULL.
  • Index the FK column — SQL Server doesn't do it automatically, and joins/cascades on an unindexed FK cause scans and locking.
  • FK checks add a small write cost but catch data-corruption bugs early; disabling them (WITH NOCHECK) for bulk loads is common but re-enable and validate after.
  • Cascade chains can be surprising — a single delete removing thousands of rows across many tables.
All SQL Server questions More on Keys & Constraints