SQLite Foreign Keys: Enable, Enforce and Cascade (Tested Examples)

Ninad Pathak

October 7, 2026

Foreign keys in SQLite aren’t enforced until you turn them on, so a delete can leave child rows pointing at nothing even with a foreign key declared. In this article I’ll walk you through turning enforcement on and making foreign keys do what you expect, even on a table you already have.

TLDR

A foreign key ties a column in one table (the child) to a key in another (the parent).
SQLite ignores foreign keys until you run PRAGMA foreign_keys = ON;, and you have to run it on every new connection.

Declare the key with REFERENCES parent(id), and index the child column.
Add ON DELETE CASCADE or ON DELETE SET NULL to choose what happens to child rows.
ALTER TABLE can’t add a foreign key constraint to an existing column. Add a new column with REFERENCES, or rebuild the table.

What is a foreign key in SQLite?

A foreign key is a constraint that says a value in the child table must exist as a key in the parent table. SQLite supports it, but only when you ask: “Foreign key constraints are disabled by default (for backwards compatibility), so must be enabled separately for each database connection” (enabling foreign key support).

A new connection reports enforcement as off (0):

sqlite> PRAGMA foreign_keys;
foreign_keys
------------
0

SQLite foreign key syntax

Declare the key on the child column, then pick the actions:

column_name INTEGER REFERENCES parent_table(parent_column)
  [ON DELETE {CASCADE | SET NULL | RESTRICT | NO ACTION}]
  [ON UPDATE {CASCADE | SET NULL | RESTRICT | NO ACTION}]
  [DEFERRABLE INITIALLY DEFERRED]

Piece	What it does
`REFERENCES authors(id)`	The child value must exist in `authors.id`
`ON DELETE CASCADE`	Deleting the parent deletes its children
`ON DELETE SET NULL`	Deleting the parent sets the child key to NULL
No `ON DELETE` clause	NO ACTION: deleting a parent that still has children fails
`ON UPDATE CASCADE`	Changing the parent key changes the child key too
`PRAGMA foreign_keys = ON;`	Turns enforcement on for this connection

How to create and enforce a foreign key

To show you how this works, I will use an authors table and a books table. Each block below is one sqlite3 connection, which matters because the pragma is per connection.

Step 1: Create the tables with REFERENCES

I index the child column too, because “Indices are not required for child key columns but they are almost always beneficial” (SQLite Foreign Key Support):

sqlite> CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
sqlite> CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT NOT NULL, author_id INTEGER REFERENCES authors(id) ON DELETE CASCADE);
sqlite> CREATE INDEX idx_books_author ON books(author_id);
sqlite> INSERT INTO authors (id, name) VALUES (1, 'Ada'), (2, 'Grace');
sqlite> INSERT INTO books (title, author_id) VALUES ('Notes', 1), ('Compilers', 2), ('Letters', 1);

Step 2: Turn enforcement on

With the pragma on, a book can’t point at an author who doesn’t exist:

sqlite> PRAGMA foreign_keys = ON;
sqlite> INSERT INTO books (title, author_id) VALUES ('Ghost', 99);
Runtime error near line 2: FOREIGN KEY constraint failed (19)

Step 3: See what happens without it

In a new connection without the pragma, the same insert succeeds and leaves an orphan row:

sqlite> INSERT INTO books (title, author_id) VALUES ('Ghost', 99);
sqlite> SELECT id, title, author_id FROM books WHERE author_id = 99;
id  title  author_id
--  -----  ---------
4   Ghost  99

Step 4: Find and fix violations already in the database

PRAGMA foreign_key_check lists child rows whose parent is missing. It works whether or not enforcement is on:

sqlite> PRAGMA foreign_key_check;
table  rowid  parent   fkid
-----  -----  -------  ----
books  4      authors  0

Delete or fix the orphan, and the check comes back empty:

sqlite> DELETE FROM books WHERE author_id = 99;
sqlite> PRAGMA foreign_key_check;

Examples

Each example below turns enforcement on first. The action you choose decides what happens to child rows when their parent changes (ON DELETE and ON UPDATE actions).

Delete children with ON DELETE CASCADE

“For an ‘ON DELETE CASCADE’ action, this means that each row in the child table that was associated with the deleted parent row is also deleted.” Deleting Ada deletes her two books:

sqlite> PRAGMA foreign_keys = ON;
sqlite> DELETE FROM authors WHERE id = 1;
sqlite> SELECT id, title, author_id FROM books;
id  title      author_id
--  ---------  ---------
2   Compilers  2

Block the delete (the default, NO ACTION)

With no ON DELETE clause, the action is NO ACTION. Deleting a parent that still has a child fails:

sqlite> PRAGMA foreign_keys = ON;
sqlite> CREATE TABLE publishers (id INTEGER PRIMARY KEY, name TEXT);
sqlite> CREATE TABLE editions (id INTEGER PRIMARY KEY, publisher_id INTEGER REFERENCES publishers(id));
sqlite> INSERT INTO publishers VALUES (1, 'Acme');
sqlite> INSERT INTO editions VALUES (1, 1);
sqlite> DELETE FROM publishers WHERE id = 1;
Runtime error near line 6: FOREIGN KEY constraint failed (19)

Keep the child with ON DELETE SET NULL

The child key is set to NULL, and the row stays:

sqlite> PRAGMA foreign_keys = ON;
sqlite> CREATE TABLE teams (id INTEGER PRIMARY KEY, name TEXT);
sqlite> CREATE TABLE players (id INTEGER PRIMARY KEY, name TEXT, team_id INTEGER REFERENCES teams(id) ON DELETE SET NULL);
sqlite> INSERT INTO teams VALUES (1, 'Red');
sqlite> INSERT INTO players VALUES (1, 'Kim', 1);
sqlite> DELETE FROM teams WHERE id = 1;
sqlite> SELECT * FROM players;
id  name  team_id
--  ----  -------
1   Kim

Follow a renamed key with ON UPDATE CASCADE

When the parent key changes, the child key changes with it:

sqlite> PRAGMA foreign_keys = ON;
sqlite> CREATE TABLE countries (code TEXT PRIMARY KEY);
sqlite> CREATE TABLE cities (name TEXT NOT NULL, country TEXT REFERENCES countries(code) ON UPDATE CASCADE);
sqlite> INSERT INTO countries VALUES ('UK'); INSERT INTO cities VALUES ('London', 'UK');
sqlite> UPDATE countries SET code = 'GB' WHERE code = 'UK';
sqlite> SELECT name, country FROM cities;
name    country
------  -------
London  GB

Insert a child before its parent with a deferred key

A DEFERRABLE INITIALLY DEFERRED key is checked at commit, not after each statement: “Deferred foreign key constraints are not checked until the transaction tries to COMMIT” (SQLite Foreign Key Support). That lets you insert a child before its parent inside one transaction:

sqlite> PRAGMA foreign_keys = ON;
sqlite> CREATE TABLE parents (id INTEGER PRIMARY KEY);
sqlite> CREATE TABLE kids (id INTEGER PRIMARY KEY, parent_id INTEGER REFERENCES parents(id) DEFERRABLE INITIALLY DEFERRED);
sqlite> BEGIN;
sqlite> INSERT INTO kids VALUES (1, 10);
sqlite> INSERT INTO parents VALUES (10);
sqlite> COMMIT;
sqlite> SELECT * FROM kids;
id  parent_id
--  ---------
1   10

Edge cases

Adding a foreign key to a table that already exists works differently in SQLite than in other databases.

ALTER TABLE can’t add a constraint

SQLite has no ALTER TABLE … ADD CONSTRAINT. “The only schema altering commands directly supported by SQLite are the ‘rename table’, ‘rename column’, ‘add column’, ‘drop column’ commands” (ALTER TABLE):

sqlite> CREATE TABLE reviews (id INTEGER PRIMARY KEY, book_id INTEGER);
sqlite> ALTER TABLE reviews ADD CONSTRAINT fk_book FOREIGN KEY (book_id) REFERENCES books(id);
Parse error near line 2: near "CONSTRAINT": syntax error
  ALTER TABLE reviews ADD CONSTRAINT fk_book FOREIGN KEY (book_id) REFERENCES bo
                          ^--- error here

Add a new column with REFERENCES

ADD COLUMN accepts a REFERENCES clause. With enforcement on, “the column must have a default value of NULL”:

sqlite> ALTER TABLE reviews ADD COLUMN author_id INTEGER REFERENCES authors(id);
sqlite> SELECT "table", "from", "to" FROM pragma_foreign_key_list('reviews');
sqlite> INSERT INTO reviews (book_id, author_id) VALUES (2, 2), (2, 2);
table    from       to
-------  ---------  --
authors  author_id  id

Rebuild the table to add a key to an existing column

For an existing column, create a new table with the key and copy the rows across. Then drop the old table and rename the new one. The table already holds two reviews. I turned enforcement off for the copy and ran foreign_key_check before committing. It printed nothing, so neither copied row broke the new keys:

sqlite> PRAGMA foreign_keys = OFF;
sqlite> BEGIN;
sqlite> CREATE TABLE reviews_new (id INTEGER PRIMARY KEY, book_id INTEGER REFERENCES books(id), author_id INTEGER REFERENCES authors(id));
sqlite> INSERT INTO reviews_new (id, book_id, author_id) SELECT id, book_id, author_id FROM reviews;
sqlite> DROP TABLE reviews;
sqlite> ALTER TABLE reviews_new RENAME TO reviews;
sqlite> PRAGMA foreign_key_check;
sqlite> COMMIT;
sqlite> SELECT "table", "from", "to" FROM pragma_foreign_key_list('reviews');
sqlite> SELECT id, book_id, author_id FROM reviews;
table    from       to
-------  ---------  --
authors  author_id  id
books    book_id    id
id  book_id  author_id
--  -------  ---------
1   2        2
2   2        2

The docs describe the full procedure under Making Other Kinds Of Table Schema Changes. It also covers recreating indexes and triggers.

Conclusion

SQLite enforces foreign keys only after PRAGMA foreign_keys = ON;, so put that line wherever your code opens a connection. Declare keys with REFERENCES and index the child column. Then pick an ON DELETE action deliberately. To add a key to an existing column, rebuild the table.