Skip to content
Tutorials Center
My learning

Topics

Lesson 73 of 125 ยท Designing Databases

Foreign Keys

Keep references between tables valid with SQL foreign keys. Learn REFERENCES, PRAGMA foreign_keys in SQLite, ON DELETE and ON UPDATE actions, and deferred checks.

Relational data lives in many tables that point at each other. An order points at a customer, an order line points at a product, an employee points at a manager. Each pointer is a column holding another row's key, and a foreign key constraint is the rule that keeps those pointers honest: you can't insert an order for a customer who doesn't exist, and you can't delete a customer while orders still point at them (unless you say what should happen to those orders).

Without foreign keys, mistakes pile up silently. A bug deletes a product, and suddenly some order lines point at nothing. Reports that join the tables quietly drop those rows, and totals stop adding up. With foreign keys, the database refuses the bad change the moment it happens, which is far easier to debug.

This lesson shows how to declare foreign keys, how to switch them on in SQLite, and how to choose what happens when a referenced row is deleted or changed. You met foreign keys briefly in Primary, Candidate, Composite and Foreign Keys; now you'll put them to work.

Parents and children

Every foreign key connects two tables:

  • The parent (or referenced) table holds the rows being pointed at, such as customers.
  • The child (or referencing) table holds the pointer column, such as orders.customer_id.

The rule is simple: every non-NULL value in the child column must match a key in the parent table. The parent column must be the parent's primary key or have a UNIQUE constraint, so that each value identifies exactly one row.

Declaring a foreign key

The shortest form is a column constraint with REFERENCES parent(column). For multi-column keys, or if you want to give the constraint a name, use the table form FOREIGN KEY (...) REFERENCES ...:

SQL
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id),   -- column form
  ordered_on TEXT NOT NULL
);

CREATE TABLE order_lines (
  order_id INTEGER NOT NULL,
  line_number INTEGER NOT NULL,
  product_id INTEGER NOT NULL,
  quantity INTEGER NOT NULL,
  PRIMARY KEY (order_id, line_number),
  CONSTRAINT order_lines_order_fk
    FOREIGN KEY (order_id) REFERENCES orders(id),             -- table form
  FOREIGN KEY (product_id) REFERENCES products(id)
);

The child column should have the same type as the parent key. Whether it can be NULL is a separate design question: NOT NULL means "every order must have a customer", while a nullable column means "a relationship is optional" (an employee may have no manager). A NULL foreign key value is never checked, because it doesn't point anywhere.

Turning foreign keys on in SQLite

Here's the most important SQLite fact in this lesson: foreign key enforcement is off by default. SQLite parses and stores your REFERENCES clauses, but it won't check them unless each connection runs:

SQL
PRAGMA foreign_keys = ON;

The setting lasts for that connection only, and it can't be changed in the middle of a transaction. Every SQLite application should run it right after opening the database. (PostgreSQL, MySQL with InnoDB and SQL Server always enforce foreign keys; there's nothing to switch on.)

Watch what happens without it. The shop database declares orders.customer_id REFERENCES customers(id), and the shop has customers 1 to 40:

SQL Foreign keys are off by default ยท database: shop

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

PRAGMA foreign_keys returns 0 (off), and an order for a non-existent customer goes straight in. Now switch enforcement on first. This example fails on purpose:

SQL With PRAGMA foreign_keys = ON ยท database: shop

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

The second insert stops with Error: FOREIGN KEY constraint failed. SQLite's message doesn't say which key failed; PostgreSQL's names the constraint and the missing value, which is one more reason to give constraints good names there.

Turned enforcement on late? PRAGMA foreign_key_check; lists every row that currently breaks a foreign key, without changing anything. Run it after imports or before switching enforcement on for an old database. On the shop database it returns no rows: the sample data is clean.

What happens when the parent changes?

Inserting children is the easy half. The hard question is what should happen to the children when a parent row is deleted, or when its key is updated. You answer it with ON DELETE and ON UPDATE clauses:

Action When the parent is deleted (or its key changes)
NO ACTION (default) Refuse if children still point at it (checked at the end of the statement)
RESTRICT Refuse immediately, even before deferred checks
CASCADE Delete the children too (or update their pointer to the new key)
SET NULL Set the children's pointer to NULL
SET DEFAULT Set the children's pointer to its column default

The difference between NO ACTION and RESTRICT only matters with deferred constraints (covered below); in everyday use both mean "refuse".

The default: refuse

With the default NO ACTION, you can't delete a customer who still has orders. This example fails on purpose:

SQL Deleting a referenced parent fails

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

Author 2 has no books and disappears without complaint. Author 1 still has two books, so the last delete fails with FOREIGN KEY constraint failed. To remove author 1 you'd first delete or reassign their books. This is the safest default for data that matters, such as customers with orders or products that appear on invoices.

CASCADE: delete the children too

ON DELETE CASCADE is right when the children belong to the parent and make no sense without it: the lines of an order, the comments of a post, the images of a product.

SQL ON DELETE CASCADE

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

Deleting post 1 also deleted both of its comments; only the comment on post 2 remains. Cascades can chain: if comments had their own children with CASCADE, those would go too.

Cascades are powerful and silent. One DELETE can remove thousands of rows in tables you weren't thinking about. Use CASCADE only for true ownership. Never cascade from something like customers to orders or invoices: those are business records you're usually required to keep. Many apps "soft delete" such rows instead, with a deleted_at column.

ON DELETE SET NULL keeps the child rows and clears their pointer. It suits optional relationships: when a manager leaves, their reports stay but temporarily have no manager. The column must allow NULL.

SQL ON DELETE SET NULL and ON UPDATE CASCADE

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

Renaming the team's code from RED to CRIMSON updated Ana's and Ben's rows automatically (ON UPDATE CASCADE), and deleting the Blue Herons left Chloe without a team (ON DELETE SET NULL). ON UPDATE CASCADE is mainly useful with natural keys like these codes; surrogate integer ids should never change, so they rarely need it.

Seeing a table's foreign keys

PRAGMA foreign_key_list (or the table-valued pragma_foreign_key_list()) shows every foreign key of a table, including its actions:

SQL Listing foreign keys ยท database: shop

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

order_items has two foreign keys, one to products and one to orders, both with the default NO ACTION.

Self-referencing and composite foreign keys

A foreign key can point at its own table. The company database stores the org chart this way: employees.manager_id REFERENCES employees(id). The CEO has NULL as manager, and everyone else points at another employee. You'll query this structure with Self Joins and Recursive CTEs.

A foreign key can also span several columns, when the parent's key is composite. Both lists must have the same number of columns in the same order:

SQL
CREATE TABLE grade_appeals (
  id INTEGER PRIMARY KEY,
  student_id INTEGER NOT NULL,
  course_id INTEGER NOT NULL,
  term_id INTEGER NOT NULL,
  reason TEXT NOT NULL,
  FOREIGN KEY (student_id, course_id, term_id)
    REFERENCES enrollments (student_id, course_id, term_id)
);

An appeal must refer to an enrollment that really exists: that student, in that course, in that term.

Deferred constraints

Normally a foreign key is checked at the end of each statement. Sometimes you need to insert rows in an order that's temporarily invalid, for example two rows that point at each other, or a child row before its parent. Declaring the constraint DEFERRABLE INITIALLY DEFERRED postpones the check to COMMIT. This example fails on purpose in its second transaction:

SQL A deferred foreign key

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

The first transaction is valid by the time it commits, so it succeeds. The second never creates department 99, so COMMIT fails with FOREIGN KEY constraint failed. PostgreSQL supports the same syntax; MySQL doesn't support deferred foreign keys at all. You'll learn more about BEGIN and COMMIT in Transactions.

Foreign keys and indexes

To check ON DELETE rules, the database must find all children of a parent row, which means searching the child column. PostgreSQL, SQLite and SQL Server don't index foreign key columns automatically (MySQL's InnoDB does). Without an index, every parent delete scans the whole child table. As a rule, create an index on every foreign key column:

SQL
CREATE INDEX order_items_order_id_idx ON order_items (order_id);
CREATE INDEX order_items_product_id_idx ON order_items (product_id);

The index also speeds up the joins you'll run along that key, as you'll see in Indexes.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. In a fresh SQLite connection, a child table declares REFERENCES parent(id). What happens when you insert a child pointing at a missing parent?

    SQLite stores foreign key definitions but only enforces them on connections that run PRAGMA foreign_keys = ON.
  2. 2. comments.post_id has ON DELETE CASCADE. Post 7 has three comments. What does DELETE FROM posts WHERE id = 7 do (with enforcement on)?

    CASCADE propagates the delete to every child row that references the deleted parent.
  3. 3. Which ON DELETE action fits employees.manager_id, where staff should stay when their manager leaves?

    SET NULL keeps the employee rows and clears the link, so the reports remain and can be assigned a new manager. CASCADE would delete them.
  4. 4. Which value can a nullable foreign key column team_code hold without matching any parent row?

    A NULL foreign key doesn't point anywhere, so it isn't checked. Every other value must match a parent key.
  5. 5. Why should you usually index foreign key columns in PostgreSQL and SQLite?

    These databases don't create the index automatically, so without one every parent delete scans the whole child table.

Summary

  • A foreign key makes every non-NULL value in a child column match a key in the parent table.
  • Declare it with REFERENCES parent(col) or FOREIGN KEY (...) REFERENCES parent(...); the parent column must be a primary key or UNIQUE.
  • SQLite enforces foreign keys only after PRAGMA foreign_keys = ON, per connection. PRAGMA foreign_key_check finds existing violations.
  • ON DELETE / ON UPDATE choose what happens to children: NO ACTION/RESTRICT (refuse), CASCADE, SET NULL or SET DEFAULT.
  • Use CASCADE only for rows the parent truly owns; refuse by default for business records.
  • Foreign keys can reference their own table or span several columns; DEFERRABLE INITIALLY DEFERRED moves the check to COMMIT.
  • Index your foreign key columns.

Next: ALTER TABLE and DROP TABLE, where you'll change tables that already hold data.