Add a Foreign Key Constraint

Enforce referential integrity by linking a column to the primary key of another table so orphaned rows cannot be inserted.

5/5 supported engines validation-green 1 example 2 scenarios

Docker-validated Not currently validation-green

Example 1

Create orders table with a foreign key to customers

Both inserts reference valid customer IDs (1 and 2), so they succeed. If you tried INSERT INTO orders VALUES (3, 99, 200) — referencing customer 99 which does not exist — the engine would raise a foreign key violation error. SQLite silently allows this violation unless PRAGMA foreign_keys = ON was set earlier in the same connection.

Source table data Rows loaded before the example query runs.

Setup

CREATE TABLE customers (id INT PRIMARY KEY, name VARCHAR(100));

INSERT INTO
  customers
VALUES
  (1, 'Alice'),
  (2, 'Bob');

Validated query Shared across supported engines.

SQL

CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT,
  total INT,
  CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers (id)
);

INSERT INTO
  orders
VALUES
  (1, 1, 150),
  (2, 2, 80);

SELECT
  o.id,
  c.name,
  o.total
FROM
  orders o
  JOIN customers c ON c.id = o.customer_id
ORDER BY
  o.id;

Expected result Returned rows for the shared example.

id	name	total
1	Alice	150
2	Bob	80

Foreign key syntax is identical across all engines. SQLite requires the PRAGMA to enable enforcement.

Useful when

Where this command helps.

preventing orders from referencing customers that do not exist
automatically deleting child rows when a parent row is deleted

Explanation

What the command is doing.

A foreign key constraint ensures every value in the referencing column (the child table) exists in the referenced column (the parent table). It can be declared inline when creating the child table with REFERENCES parent(col), or added to an existing table with ALTER TABLE child ADD CONSTRAINT fk_name FOREIGN KEY (col) REFERENCES parent(col). By default, attempting to insert a row into the child table with a value that does not exist in the parent raises an error. ON DELETE CASCADE and ON UPDATE CASCADE let you propagate changes automatically. SQLite parses foreign key syntax but only enforces it after running PRAGMA foreign_keys = ON.

Add a Foreign Key Constraint

Create orders table with a foreign key to customers

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.