SQL FOREIGN KEY Constraint

A FOREIGN KEY is a column (or set of columns) in one table that points to the PRIMARY KEY (or a UNIQUE column) in another table. It is the mechanism that turns a pile of separate tables into a genuinely relational database: it guarantees that a value like department_id in an employees table can never point at a department that doesn’t exist. This property is called referential integrity, and it’s one of the most important guarantees a well-designed schema can offer.

Overview / How It Works

Every table that stores a foreign key is called the child table (or referencing table); the table it points to is the parent table (or referenced table). When you declare a FOREIGN KEY constraint, you’re telling the database engine: “before you accept an INSERT or UPDATE on this column, check that the value already exists as a key in the parent table.” If it doesn’t, the engine rejects the statement with a constraint-violation error instead of silently corrupting your data.

Under the hood, most database engines implement this check by looking up the value against the parent table’s primary key index (or a unique index) at write time. Because that lookup needs an index to be fast, the referenced column in the parent table must be a PRIMARY KEY or have a UNIQUE constraint — the engine cannot efficiently guarantee uniqueness against an arbitrary, unindexed column. Some engines also automatically create an index on the child table’s foreign key column for the same performance reason (this varies by product, so it’s worth checking your engine’s documentation).

Foreign keys also control what happens when a referenced parent row is deleted or its key is changed, via ON DELETE and ON UPDATE clauses:

Action	Effect when the parent row is deleted/updated
NO ACTION / RESTRICT	Reject the delete/update if child rows still reference it (this is the default in most engines).
CASCADE	Automatically delete (or update) the matching child rows too.
SET NULL	Set the child’s foreign key column to NULL.
SET DEFAULT	Set the child’s foreign key column to its declared default value.

An important SQLite-specific quirk: foreign key enforcement is off by default in SQLite unless you run PRAGMA foreign_keys = ON; for each connection. Other engines like PostgreSQL, MySQL (with InnoDB), and SQL Server enforce foreign keys by default. Always check this for whichever engine you’re using — a constraint that exists in your schema but isn’t actually enforced gives you a false sense of safety.

Syntax

A foreign key can be declared inline on a single column, or as a separate table-level constraint (required when the key spans multiple columns):

-- Column-level (single column)
CREATE TABLE employees (
    employee_id INTEGER PRIMARY KEY,
    department_id INTEGER REFERENCES departments(department_id)
);

-- Table-level, with an explicit constraint name and actions
CREATE TABLE employees (
    employee_id INTEGER PRIMARY KEY,
    department_id INTEGER,
    CONSTRAINT fk_employee_department
        FOREIGN KEY (department_id)
        REFERENCES departments(department_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

• FOREIGN KEY (column) — the column in the current (child) table.
• REFERENCES parent_table(parent_column) — the parent table and the primary/unique key it points to.
• CONSTRAINT name — optional, but naming your constraints makes error messages and later ALTER TABLE ... DROP CONSTRAINT calls much easier to work with (note: SQLite itself has very limited ALTER TABLE support for constraints — see Common Mistakes).
• ON DELETE / ON UPDATE — optional; defaults to NO ACTION if omitted.

Examples

Example 1: A basic foreign key relationship

CREATE TABLE departments (
    department_id INTEGER PRIMARY KEY,
    department_name TEXT NOT NULL
);

CREATE TABLE employees (
    employee_id INTEGER PRIMARY KEY,
    employee_name TEXT NOT NULL,
    department_id INTEGER,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

INSERT INTO departments (department_id, department_name) VALUES
    (1, 'Engineering'),
    (2, 'Sales');

INSERT INTO employees (employee_id, employee_name, department_id) VALUES
    (101, 'Alice', 1),
    (102, 'Bob', 2),
    (103, 'Carla', 1);

SELECT e.employee_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
ORDER BY e.employee_id;

Result:

employee_name	department_name
Alice	Engineering
Bob	Sales
Carla	Engineering

The department_id column in employees is declared as a foreign key referencing departments.department_id. Every value we inserted (1, 2, 1) already exists in departments, so all three inserts succeed and the join resolves cleanly.

Example 2: The enforcement trap (SQLite default behavior)

CREATE TABLE departments (
    department_id INTEGER PRIMARY KEY,
    department_name TEXT NOT NULL
);

CREATE TABLE employees (
    employee_id INTEGER PRIMARY KEY,
    employee_name TEXT NOT NULL,
    department_id INTEGER,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

INSERT INTO departments (department_id, department_name) VALUES (1, 'Engineering');

-- No PRAGMA foreign_keys = ON was run, so this is NOT checked
INSERT INTO employees (employee_id, employee_name, department_id) VALUES (201, 'Ghost Employee', 99);

SELECT employee_name, department_id FROM employees;

Result:

employee_name	department_id
Ghost Employee	99

Even though department 99 was never inserted into departments, the row was accepted without error. This is because SQLite does not enforce foreign keys unless PRAGMA foreign_keys = ON; is explicitly set for the connection. The constraint exists in the schema, but it’s inert — a common source of confusion for developers coming from PostgreSQL or MySQL, where enforcement is on by default.

Example 3: ON DELETE CASCADE in action

PRAGMA foreign_keys = ON;

CREATE TABLE authors (
    author_id INTEGER PRIMARY KEY,
    author_name TEXT NOT NULL
);

CREATE TABLE books (
    book_id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    author_id INTEGER,
    FOREIGN KEY (author_id) REFERENCES authors(author_id)
        ON DELETE CASCADE
);

INSERT INTO authors (author_id, author_name) VALUES (1, 'Jane Austen'), (2, 'Mark Twain');

INSERT INTO books (book_id, title, author_id) VALUES
    (1, 'Emma', 1),
    (2, 'Persuasion', 1),
    (3, 'Huckleberry Finn', 2);

DELETE FROM authors WHERE author_id = 1;

SELECT * FROM books;

Result:

book_id	title	author_id
3	Huckleberry Finn	2

With enforcement turned on and ON DELETE CASCADE declared, deleting Jane Austen automatically deletes every book that referenced her (Emma and Persuasion). Only Mark Twain’s book survives. Without CASCADE, that DELETE would instead have been rejected with a foreign key constraint error, because orphaned books rows would otherwise remain.

Example 4: Joining across an implied foreign key relationship

SELECT o.order_id, c.customer_name, o.amount
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
ORDER BY o.order_id;

Result: three rows, one per order, each showing the order’s id, amount, and the name of the customer it belongs to (only customers who have at least one order appear, since this is an inner join). This mirrors how a foreign key is used day to day: orders.customer_id is designed to reference customers.customer_id, and that relationship is exactly what a JOIN reconstructs.

How It Works Step by Step / Under the Hood

• At CREATE TABLE time: the engine records the constraint’s parent table, parent column, and any ON DELETE/ON UPDATE actions as metadata attached to the child table.
• At INSERT/UPDATE time on the child table: before the row is written, the engine checks whether the new foreign key value exists in the parent table’s key (or is NULL, which is always allowed unless the column is also NOT NULL). This lookup is why the parent column needs an index — it’s typically its primary key, so the lookup is fast.
• At DELETE/UPDATE time on the parent table: before the parent row is removed or its key changed, the engine checks the child table for matching rows. If any exist and no ON DELETE/ON UPDATE action was specified (or it’s RESTRICT/NO ACTION), the statement is rejected. If CASCADE, SET NULL, or SET DEFAULT was specified, the engine performs that action on the child rows automatically, as part of the same transaction.
• Transactional consistency: all of this happens within the same transaction as your statement, so a foreign key violation causes the whole statement (and, if you’re inside an explicit transaction, everything not yet committed) to roll back — the database never ends up in a half-updated state.

Common Mistakes

Mistake 1: Assuming ALTER TABLE can add a foreign key the same way in every engine

CREATE TABLE customers2 (
    id INTEGER PRIMARY KEY,
    name TEXT
);

CREATE TABLE orders2 (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER
);

ALTER TABLE orders2 ADD FOREIGN KEY (customer_id) REFERENCES customers2(id);

This works in MySQL, PostgreSQL, and SQL Server, but SQLite’s ALTER TABLE only supports adding a column, renaming, or dropping a column — it cannot add a table-level constraint after the fact. In SQLite you must recreate the table with the foreign key included from the start (or use a workaround: create a new table with the constraint, copy the data over, drop the old table, and rename the new one).

CREATE TABLE orders2_new (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    FOREIGN KEY (customer_id) REFERENCES customers2(id)
);

Mistake 2: Forgetting that enforcement must be turned on (and then relying on it)

PRAGMA foreign_keys = ON;

CREATE TABLE departments3 (
    department_id INTEGER PRIMARY KEY,
    department_name TEXT
);

CREATE TABLE employees3 (
    employee_id INTEGER PRIMARY KEY,
    department_id INTEGER,
    FOREIGN KEY (department_id) REFERENCES departments3(department_id)
);

INSERT INTO departments3 VALUES (1, 'Engineering');

INSERT INTO employees3 (employee_id, department_id) VALUES (1, 99);

With enforcement on, this correctly fails with a foreign key constraint error because department 99 doesn’t exist — which is exactly what should happen. The mistake in practice is the opposite: writing code that assumes this protection exists, without ever issuing PRAGMA foreign_keys = ON; on every new connection (as shown in Example 2). Always verify enforcement is actually active rather than trusting the schema definition alone.

Best Practices

• Always index foreign key columns on the child table (many engines do this automatically for the parent’s primary key, but the child-side index often needs to be created explicitly) — it dramatically speeds up both the constraint check and typical JOIN queries.
• Decide your ON DELETE behavior deliberately. Silently accepting the default (usually RESTRICT/NO ACTION) is often correct for safety, but CASCADE is appropriate for true parent-owns-child relationships (like authors and their books) and SET NULL suits optional relationships.
• Match data types exactly between the foreign key column and the referenced column — mismatched types (e.g. TEXT referencing an INTEGER primary key) cause subtle bugs even in engines that don’t strictly enforce type checking.
• Name your constraints (CONSTRAINT fk_name FOREIGN KEY ...) so that error messages and later schema changes are easier to reason about.
• In SQLite specifically, enable PRAGMA foreign_keys = ON; at the start of every connection/session — it is not persisted or inherited automatically.
• Don’t over-cascade: chains of ON DELETE CASCADE across many tables can delete far more data than intended from a single statement. Trace the chain before enabling it in production schemas.

Practice Exercises

• Exercise 1: Create two tables, teams(team_id INTEGER PRIMARY KEY, team_name TEXT) and players(player_id INTEGER PRIMARY KEY, player_name TEXT, team_id INTEGER), with players.team_id as a foreign key referencing teams.team_id. Insert two teams and three players, then write a query joining players to their team names.
• Exercise 2: Modify your players table’s foreign key to use ON DELETE SET NULL instead of the default. Delete one team and confirm (via a SELECT) that its players now have NULL in team_id rather than being deleted or blocking the delete.
• Exercise 3: Using the shared orders and customers sample tables, write a query that lists every customer along with the total amount of their orders, using a LEFT JOIN so that customers with zero orders still appear with a total of 0 or NULL. (Hint: you’ll need GROUP BY and an aggregate function.)

Summary

• A FOREIGN KEY constraint links a column in a child table to the primary key (or a unique column) of a parent table, enforcing referential integrity.
• The referenced column must be a PRIMARY KEY or have a UNIQUE constraint so the engine can efficiently verify matches.
• ON DELETE/ON UPDATE clauses (CASCADE, SET NULL, SET DEFAULT, RESTRICT/NO ACTION) control what happens to child rows when a parent row is deleted or its key changes.
• SQLite does not enforce foreign keys by default — you must run PRAGMA foreign_keys = ON; per connection, unlike PostgreSQL, MySQL, and SQL Server.
• SQLite’s ALTER TABLE cannot add a foreign key constraint to an existing table; the table must be recreated.
• Index your foreign key columns for both performance and correctness of the enforcement check.