Skip to content
Tutorials Center
My learning

Topics

Lesson 15 of 33 ยท Schema Design and Integrity

Foreign Keys

Use foreign keys in SQLite: turn on PRAGMA foreign_keys, choose ON DELETE and ON UPDATE actions, defer checks to commit time, and find orphans with foreign_key_check.

A foreign key is a promise that a value in one table points at a real row in another: every books.author_id must match an existing authors.id. The database then refuses changes that would break the promise, so you never end up with a book whose author has vanished. That is called referential integrity, and it is one of the main reasons to use a relational database at all.

SQLite supports foreign keys, but with a surprise that catches almost everyone: enforcement is off by default. You can declare perfectly good foreign keys and SQLite will ignore them until you switch them on for each connection. This lesson shows how to do that, what the ON DELETE and ON UPDATE actions do, how to postpone checks until commit time, and how to hunt for rows that already violate a key.

Foreign keys in SQLite

Off by default PRAGMA foreign_keys = ON, every connection.
Parent must exist Children cannot point at nothing.
Actions CASCADE, SET NULL, RESTRICT and more.

Declared is not the same as enforced.

1 / 2

Declaring a foreign key

You declare a foreign key either inline on a column (author_id INTEGER REFERENCES authors(id)) or as a table constraint (FOREIGN KEY (author_id) REFERENCES authors(id)). The referenced column, the parent key, must be the primary key or carry a UNIQUE constraint or index. If you omit the column list, SQLite uses the parent's primary key.

The first example proves the "off by default" claim. We declare a foreign key, insert a book whose author does not exist, and nothing complains.

SQL Declared but not enforced

Press Run (or Ctrl + Enter) to see the result here.

PRAGMA foreign_keys; prints 0, meaning enforcement is off, and the orphan row with author 99 goes in without any error. Some builds of SQLite are compiled with the default switched on, but you should never rely on that.

Turning enforcement on

Run PRAGMA foreign_keys = ON; right after you open each connection. It is a per-connection setting, not stored in the database file, so every new connection (and every connection in your pool) needs it. Most drivers or ORMs have an option for it; if yours does not, issue the pragma as the first statement.

Two details matter. The pragma does nothing inside a transaction, so run it before BEGIN. And with enforcement on, inserting or updating a child row whose parent does not exist fails with FOREIGN KEY constraint failed.

SQL Enforcement on: the orphan is refused

Press Run (or Ctrl + Enter) to see the result here.

The first two inserts succeed. A NULL foreign key is always allowed, because NULL means "no parent". Make the column NOT NULL when a parent is mandatory. The third insert raises FOREIGN KEY constraint failed and the run stops there.

ON DELETE and ON UPDATE actions

What should happen when someone deletes an author who still has books? The ON DELETE clause on the child's foreign key chooses. ON UPDATE does the same when the parent key value changes.

Action Effect on the child rows
NO ACTION (default) The change fails at the end of the statement if children would be left orphaned.
RESTRICT Fails immediately, even if you would fix it later in the same statement or transaction.
CASCADE Delete (or update) the children along with the parent.
SET NULL Set the child's key column to NULL.
SET DEFAULT Set the child's key column to its DEFAULT value (which must itself be a valid parent).

CASCADE suits true ownership (an order owns its order lines). SET NULL suits optional relationships. RESTRICT or the default suit data you never want deleted by accident.

SQL CASCADE and SET NULL

Press Run (or Ctrl + Enter) to see the result here.

Renaming team 1 to id 10 cascades to the players, so Ana and Ben now point at 10. Deleting team 10 sets their team_id to NULL (they remain in the table) and, because trophies use ON DELETE CASCADE, deletes the trophy owned by that team. Only the trophy of team 2 survives.

The default and RESTRICT

With no action written, the default is NO ACTION: deleting a parent that still has children fails.

SQL Default behaviour blocks the delete

Press Run (or Ctrl + Enter) to see the result here.

Globex has no invoices, so it is deleted. Deleting Acme fails with FOREIGN KEY constraint failed because invoice 1 still refers to it.

Index the child column of every foreign key (CREATE INDEX ... ON books (author_id)). SQLite does not create that index for you, and without it each parent delete or key change must scan the whole child table. The indexes lesson covers this.

Deferred constraints

By default a foreign key is checked at the end of each statement. A deferred constraint is checked only when the transaction commits, so inside the transaction you may temporarily break the rule, as long as it is repaired before COMMIT. This is useful for circular references, or when you load children before parents. Declare the key DEFERRABLE INITIALLY DEFERRED, or keep it immediate and call PRAGMA defer_foreign_keys = ON inside a transaction.

SQL Children first, parents later

Press Run (or Ctrl + Enter) to see the result here.

The first transaction inserts a person before their department exists, which is accepted because the check waits until COMMIT, by which time department 7 is there. The second transaction never creates department 8, so the COMMIT itself fails with FOREIGN KEY constraint failed, and the transaction stays open until you roll it back.

Finding existing violations

Foreign keys only guard future changes. If a database has orphans (because enforcement was off, or data was imported), PRAGMA foreign_key_check lists them. It works even when foreign_keys is off, which makes it the right tool before you turn enforcement on for old data.

SQL PRAGMA foreign_key_check

Press Run (or Ctrl + Enter) to see the result here.

The pragma returns one row per violation with four columns: the child table (books), the child's rowid (2 and 3), the parent table (authors) and the index of the foreign key within the child table. You can pass a table name to check just one table. The join query below it finds the same orphans in plain SQL, which also lets you see their titles and decide whether to delete them or give them a parent.

Practical rules

  • Run PRAGMA foreign_keys = ON on every connection, before any transaction.
  • Make the child column NOT NULL when a parent is required.
  • Pick ON DELETE deliberately: CASCADE for owned data, SET NULL for optional links, the default or RESTRICT to protect data.
  • Index child columns that you join or delete by.
  • When changing a table's structure, see Altering schema safely: the rebuild recipe needs foreign keys switched off temporarily.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. You declare REFERENCES authors(id) but never run any PRAGMA. What does SQLite do when you insert a book with a missing author?

    Foreign key enforcement is a per-connection setting that defaults to off in most builds. Declaring the key alone does nothing.
  2. 2. Which action makes deleting a parent also delete its child rows?

    CASCADE propagates the delete to the children. SET NULL keeps them but clears the key, and RESTRICT and NO ACTION block the delete.
  3. 3. What is true about a NULL value in a foreign key column?

    NULL means there is no parent, so the key is not checked. Add NOT NULL when a parent is mandatory.
  4. 4. What does PRAGMA foreign_key_check do?

    It only reports violations, one row each, and it works even when enforcement is off. You decide how to fix the data.
  5. 5. Why use a DEFERRABLE INITIALLY DEFERRED foreign key?

    Deferred checks run when the transaction commits, which helps with circular references and with loading children before parents.

Summary

  • Foreign keys are declared with REFERENCES but enforced only after PRAGMA foreign_keys = ON, set on every connection and outside a transaction.
  • The parent key must be a primary key or have a UNIQUE constraint or index; a NULL child value is always allowed.
  • ON DELETE and ON UPDATE choose between NO ACTION, RESTRICT, CASCADE, SET NULL and SET DEFAULT.
  • Deferred constraints are checked at COMMIT, allowing temporary violations inside a transaction.
  • PRAGMA foreign_key_check finds existing orphans; index the child columns for fast deletes and joins.

Next: Views and triggers, reusing queries and reacting to changes automatically.