Foreign Key

Definition

A foreign key is a column (or set of columns) in one table that references the primary key (or a unique key) of another table, establishing a link between the two and enforcing that the referenced value actually exists.

How It Works

sql
CREATE TABLE departments (
  id INT PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  department_id INT,
  FOREIGN KEY (department_id) REFERENCES departments(id)
);

This constraint means: employees.department_id may only ever contain a value that actually exists as an id in departments (or be NULL, if the column allows it — an employee not yet assigned to a department, for example). Trying to INSERT an employee with department_id = 999 when no department 999 exists is rejected by the database itself — this guarantee is called referential integrity (5.11), and the foreign key constraint is the SQL mechanism that enforces it.

The table containing the foreign key (employees) is the "child"/"referencing" table; the table being pointed at (departments) is the "parent"/"referenced" table.

Edge Cases and Pitfalls

• Deleting a department that still has employees pointing at it raises a constraint violation by default — this is exactly why cascading actions (5.12, ON DELETE CASCADE/SET NULL/RESTRICT) exist, to say explicitly what should happen in that case instead of just failing.
• A foreign key column allowing NULL means "not yet assigned / not applicable" is a valid state — but this only works if the column doesn't ALSO need NOT NULL for business reasons (an order without a customer might be nonsensical, in which case the foreign key column should be NOT NULL too).
• A common bug class is having "orphaned" rows in a real production system that never actually had a foreign key constraint enforced (e.g. added after data already existed, or enforced only in application code) — a row's department_id value can silently point at nothing, and only an explicit query (an anti-join / LEFT JOIN ... WHERE ... IS NULL) will reveal it.

Key Takeaways

• Foreign key = a column referencing another table's primary/unique key, enforcing that the reference is valid.
• The referencing table is the child; the referenced table is the parent.
• NULL in a foreign key column (when allowed) means "no reference," not "an invalid reference."