Foreign keys and ON DELETE actions

A foreign key ties a child row to a parent row: orders.customer_id must name a customer who exists. Foreign keys refuse two things, a child that points to a missing parent and a parent that disappears while children still point to it, and the second rule is where the ON DELETE actions come in. They decide what happens to the children when their parent is deleted: CASCADE deletes them too, SET NULL empties their link, SET DEFAULT puts the column’s default in it, and RESTRICT or NO ACTION refuse the delete while children exist. Choosing one per relationship is real design work: an order’s items belong to the order and can go with it, a customer who has orders must not vanish, and an optional link, such as the agent who delivered an order, can simply be forgotten. Every example on this page runs in sql.js 1.14.2, which packages SQLite 3.49.1, and like any SQLite connection it checks foreign keys only after PRAGMA foreign_keys = ON.

Three links, three actions

-- One shop, three links, three ON DELETE actions.
PRAGMA foreign_keys = ON;   -- SQLite checks foreign keys only after this, on every new connection

CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE agents    (agent_id    INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE orders (
  order_id    INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers (customer_id) ON DELETE RESTRICT,
  agent_id    INTEGER          REFERENCES agents (agent_id)       ON DELETE SET NULL
);
CREATE TABLE order_items (
  order_id INTEGER NOT NULL REFERENCES orders (order_id) ON DELETE CASCADE,
  product  TEXT    NOT NULL,
  qty      INTEGER NOT NULL,
  PRIMARY KEY (order_id, product)
);

INSERT INTO customers VALUES (1, 'Asha Kulkarni'), (2, 'Vikram Nair');
INSERT INTO agents    VALUES (7, 'Ravi'), (8, 'Fatima');
INSERT INTO orders    VALUES (101, 1, 7), (102, 1, 8), (103, 2, 7);
INSERT INTO order_items VALUES
  (101, 'Basmati rice 1 kg', 2), (101, 'Toor dal 500 g', 1), (102, 'Masala chai 250 g', 2), (103, 'Coconut oil 1 L', 1);

DELETE FROM orders    WHERE order_id = 101;     -- CASCADE: the order's two items go with it
DELETE FROM agents    WHERE agent_id = 7;       -- SET NULL: order 103 stays, without an agent
DELETE FROM customers WHERE customer_id = 1;    -- RESTRICT: refused, Asha still has order 102

SELECT order_id, customer_id, agent_id FROM orders;
SELECT order_id, product, qty FROM order_items;

┌──────────┬─────────────┬──────────┐
│ order_id │ customer_id │ agent_id │
├──────────┼─────────────┼──────────┤
│ 102      │ 1           │ 8        │
│ 103      │ 2           │          │
└──────────┴─────────────┴──────────┘
┌──────────┬───────────────────┬─────┐
│ order_id │      product      │ qty │
├──────────┼───────────────────┼─────┤
│ 102      │ Masala chai 250 g │ 2   │
│ 103      │ Coconut oil 1 L   │ 1   │
└──────────┴───────────────────┴─────┘

Runtime error near line 26: FOREIGN KEY constraint failed

Each delete did what its action promises. Deleting order 101 took its two items with it, because order_items declares CASCADE. Deleting agent Ravi kept order 103 but set its agent_id to NULL, because the link is optional and declares SET NULL. Deleting Asha failed, because she still has order 102 and the link from orders declares RESTRICT; SQLite’s message, “FOREIGN KEY constraint failed”, does not say which key, while PostgreSQL names the constraint and the key value, and MySQL prints the whole foreign key definition with error 1451.

Writing a foreign key

A foreign key can follow its column, as in the example, or stand among the table constraints, where it can span several columns and take a name: CONSTRAINT items_order_fk FOREIGN KEY (order_id) REFERENCES orders (order_id) ON DELETE CASCADE. The parent’s columns must be its primary key or carry a UNIQUE constraint, so that each value points to exactly one row. The types must match as well: PostgreSQL 18.6 refused a text column pointing to an integer key because the types are incompatible, and MySQL 8.4.11 refused it with error 3780. A child column that allows NULL makes the link optional, because a NULL points nowhere and is never checked. Name the constraint as you would a CHECK: PostgreSQL and MySQL print the name in every error about it, and changing the action later means dropping the constraint by that name and adding it again.

Choosing an action for each link

Ask one question per link: do the children mean anything without their parent? Items of a deleted order mean nothing, so CASCADE. A delivery means something without its agent, so SET NULL. An order is a financial record, so a customer who has orders must not be deleted at all, and RESTRICT makes the database refuse; such long-lived records are usually marked inactive instead of deleted. NO ACTION, the default when you name no action, refuses the same deletes as RESTRICT and differs only in when it looks, which matters inside transactions. ON UPDATE takes the same actions for a parent key that changes, which surrogate keys never do. MySQL’s InnoDB does not carry out SET DEFAULT, so keep that action for PostgreSQL and SQLite.

Turning the checks on

PostgreSQL always enforces foreign keys. SQLite enforces them only on a connection that has run PRAGMA foreign_keys = ON, and sql.js starts with it off, as SQLite does unless it was compiled otherwise, so every program that opens the database must switch it on, and data written by one that did not may already hold orphans. MySQL’s InnoDB enforces foreign keys written as a FOREIGN KEY clause, but ignores a REFERENCES written after a column, as the lesson on keys and relationships showed, and a session can switch every check off with SET FOREIGN_KEY_CHECKS = 0, which dumps and bulk loads often do. Switching the checks back on in SQLite or MySQL does not look at the rows that are already there.

Your turn

PRAGMA foreign_keys = ON;

-- Add the three foreign keys, each with its ON DELETE action.
CREATE TABLE recruiters (recruiter_id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE drives (
  drive_id     INTEGER PRIMARY KEY,
  recruiter_id INTEGER NOT NULL,
  held_on      TEXT    NOT NULL
);
CREATE TABLE students (student_id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE applications (
  drive_id   INTEGER NOT NULL,
  student_id INTEGER NOT NULL,
  PRIMARY KEY (drive_id, student_id)
);

-- Leave this as it is: the foreign keys SQLite now knows about, one row per link.
SELECT m.name AS child_table, f."from" AS child_column, f."table" AS parent_table, f.on_delete
FROM sqlite_schema AS m, pragma_foreign_key_list(m.name) AS f
WHERE m.type = 'table'
ORDER BY child_table, child_column;

# Sample test: your script creates the tables, and its last query lists their foreign keys from SQLite's catalog.
tests:
  - name: three foreign keys, with RESTRICT for recruiters and CASCADE for applications
    ordered: true
    setup: ''
    expected:
      columns: [child_table, child_column, parent_table, on_delete]
      rows:
        - ['applications', 'drive_id', 'drives', 'CASCADE']
        - ['applications', 'student_id', 'students', 'CASCADE']
        - ['drives', 'recruiter_id', 'recruiters', 'RESTRICT']

Each action in depth: what it removes, when it checks, how it fails Pro: opens with a Pro, Premium or Ultimate pass

Deferred checks, data loads and the index every foreign key needs, with numbers Pro: opens with a Pro, Premium or Ultimate pass

Interview questions

Key takeaways

• Foreign keys refuse a child without a parent; the ON DELETE actions decide what deleting a parent does to its children.
• CASCADE for children that belong to the parent, SET NULL for optional links, RESTRICT or NO ACTION for history that must stay.
• NO ACTION is the default; it refuses the same deletes as RESTRICT and differs only in timing.
• SQLite checks foreign keys only after PRAGMA foreign_keys = ON, on every connection; MySQL ignores a column-level REFERENCES.