A foreign key says "this column points at a row over there". The ON DELETE and ON UPDATE clauses say what happens to the pointing rows when the row they point at changes or disappears. The default is to refuse. Every other action is a decision about how much work the database will do on your behalf, and each one trades a guarantee for convenience.
What do ON DELETE and ON UPDATE actions do?
The insurer sells policies through agents, and each policy is assigned to an agent. That is a foreign key from the assignment table to the agent, and the action decides what happens when an agent record is deleted. Here is a table that declares CASCADE explicitly:
CREATE TABLE agents (
agent_id INTEGER PRIMARY KEY,
code TEXT NOT NULL UNIQUE,
name TEXT NOT NULL
);
CREATE TABLE policy_assignments (
assignment_id INTEGER PRIMARY KEY,
agent_id INTEGER NOT NULL REFERENCES agents(agent_id) ON DELETE CASCADE,
policy_id INTEGER NOT NULL,
note TEXT
);
INSERT INTO agents (agent_id, code, name) VALUES
(1, 'A-100', 'North Point'),
(2, 'A-200', 'South Gate');
INSERT INTO policy_assignments (assignment_id, agent_id, policy_id, note) VALUES
(10, 1, 5, 'renewal'),
(11, 1, 6, 'renewal'),
(12, 2, 7, 'midterm');
Two assignments, both pointing at agent 1:
SELECT assignment_id, agent_id, policy_id FROM policy_assignments ORDER BY assignment_id;
assignment_id agent_id policy_id
------------- -------- ---------
10 1 5
11 1 6
12 2 7
Delete the agent, and CASCADE takes the assignments with it:
PRAGMA foreign_keys = ON;
DELETE FROM agents WHERE agent_id = 1;
SELECT COUNT(*) AS assignments_left FROM policy_assignments;
assignments_left
1
Both child rows went with their parent. The engine recorded the choice, and it can be read back:
PRAGMA foreign_key_list(policy_assignments);
id seq table from to on_update on_delete match
-- --- ------ -------- --------- --------- -------- -----
0 0 agents agent_id agent_id NO ACTION CASCADE NONE
on_update is NO ACTION and on_delete is CASCADE — the two defaults and the one explicit choice, side by side. Note that this only works when foreign keys are actually being enforced, which in SQLite means PRAGMA foreign_keys = ON for the connection.
What are the available actions?
| Action | What happens to the child rows | Use it when |
|---|---|---|
| NO ACTION | nothing — the parent change is refused | you want to be asked before anything is destroyed |
| RESTRICT | nothing, checked immediately | the same as NO ACTION, but the check happens before other work |
| CASCADE | the child rows are deleted too | the child has no meaning without the parent |
| SET NULL | the foreign key is set to NULL | the child should survive and be reattached later |
| SET DEFAULT | the foreign key is set to its default | rarely useful; the default must itself be a valid key |
NO ACTION and RESTRICT look identical in the table and behave almost identically, with one difference: NO ACTION is checked at the end of the statement, while RESTRICT is checked immediately. That distinction matters in a trigger or in a statement that changes several things at once.
What does SET NULL do in practice?
The other common choice is to keep the child and detach it. Here the assignment survives, but the agent reference is emptied:
CREATE TABLE nullable_assignments (
assignment_id INTEGER PRIMARY KEY,
agent_id INTEGER REFERENCES agents(agent_id) ON DELETE SET NULL,
policy_id INTEGER NOT NULL
);
assignment_id agent_id policy_id
-------------- -------- ---------
20 7
The agent_id column is now empty and the assignment is still there, waiting for a new agent. Note that SET NULL requires the foreign key column to be nullable — otherwise the action cannot be performed and the delete is refused instead. It is also the action that changes the shape of your reports: those rows now exist with no parent, and every query that assumed a match needs a LEFT JOIN.
What happens with RESTRICT and the default?
The same delete against a table that declares RESTRICT is refused outright:
FOREIGN KEY constraint failed
And that is also what the default does. A child table declared with no action at all behaves identically, because NO ACTION is the standard default and every engine applies it unless told otherwise. In practice this is the safe default: nothing is destroyed without somebody deciding to destroy it. The cost is that deleting a parent row becomes a two-step operation — remove or re-point the children first — and in an application that means handling the error rather than relying on a cascade to do the work silently.
What does ON UPDATE CASCADE do?
ON UPDATE governs the same relationship when the key changes rather than when the row disappears. The most common case by far is ON UPDATE CASCADE, which keeps the children pointing at the right parent when a parent key is renumbered:
CREATE TABLE cascaded_agents (
agent_id INTEGER PRIMARY KEY,
code TEXT NOT NULL UNIQUE
);
CREATE TABLE cascaded_assignments (
assignment_id INTEGER PRIMARY KEY,
agent_id INTEGER NOT NULL REFERENCES cascaded_agents(agent_id) ON UPDATE CASCADE,
policy_id INTEGER NOT NULL
);
assignment_id agent_id policy_id
-------------- -------- ---------
40 10 1
The parent was renumbered from 1 to 10 with UPDATE cascaded_agents SET agent_id = 10 WHERE agent_id = 1, and the child followed automatically. Without ON UPDATE CASCADE, that UPDATE would have been refused by the constraint, or the child would have been left pointing at a row that no longer exists.
Two warnings. CASCADE on delete is powerful and easy to regret: a single mistaken DELETE can remove a whole subtree of rows in one statement, and the constraint cannot be undone afterwards. And in SQLite, the whole mechanism is inert unless foreign keys are switched on for the connection with PRAGMA foreign_keys = ON, which is a per-connection setting and not a property of the database file.
Quick reference
- NO ACTION is the default: the parent change is refused and nothing is destroyed.
- RESTRICT refuses immediately; NO ACTION is checked at the end of the statement.
- CASCADE deletes the children with the parent; use it only where a child has no meaning alone.
- SET NULL keeps the child and detaches it, and requires a nullable foreign key column.
- ON UPDATE CASCADE keeps children correct when a parent key is renumbered.
Action drill: the insurer wants to delete a policyholder who has taken out several policies over the years. Decide what ON DELETE action each of the three child tables — policies, claims and payment records — should use, and write one sentence per table explaining what the business would lose.