Chapter 05 · Constraints, Foreign Keys, Conflict Handling, and Integrity
ON DELETE/UPDATE Actions, Deferred Constraints, and Transaction Boundaries
Model parent-row lifecycles deliberately with foreign-key actions and use deferred constraints when a transaction must pass through a temporary inconsistency before commit.
Learning outcomes
A foreign key says a relationship must be valid. An ON DELETE or ON UPDATE action says what should happen when the parent lifecycle threatens that relationship. Those actions encode business meaning, so choose them from requirements—not because one option saves application code.
Explain NO ACTION, RESTRICT, SET NULL, SET DEFAULT, and CASCADE using parent/child lifecycle scenarios.
Distinguish NO ACTION from RESTRICT timing, especially with deferred constraints.
Choose actions only when child optionality/defaults and business ownership make the resulting state valid.
Use DEFERRABLE INITIALLY DEFERRED when a transaction must pass through a temporary foreign-key violation.
Predict what happens when COMMIT sees an unresolved deferred violation and recover with fix-or-rollback.
Review FieldNotes relationships and justify each action rather than copying one global policy.
Five actions answer one lifecycle question
Imagine deleting a site that still has device rows. SQLite can reject the operation or modify the children, but the correct choice depends on whether devices are owned by the site, may be unassigned, should move to a sentinel site, or must prevent deletion.
| Action | Parent delete/update effect | Typical design question |
|---|---|---|
NO ACTION | No special transformation; the constraint must be satisfied at its normal check point. | Should the application arrange a valid state before statement/transaction completion? |
RESTRICT | Rejects the parent key change as soon as dependent children are encountered. | Must this parent be protected immediately from lifecycle changes? |
SET NULL | Sets child key columns to NULL. | Is an unassigned child a valid business state, and are those columns nullable? |
SET DEFAULT | Sets child key columns to their DEFAULT values. | Does that default itself identify a valid parent or NULL? |
CASCADE | Deletes dependent children or updates child keys with the parent. | Are children truly owned by the parent so propagation matches business semantics? |
CASCADE is an ownership decision, not a convenience switch
For an owned detail table such as a device’s ephemeral calibration samples, deleting the device may legitimately delete all samples. For maintenance history required by audit policy, deleting history automatically may be unacceptable even if it is convenient.
PRAGMA foreign_keys = ON;DROP TABLE IF EXISTS sample;DROP TABLE IF EXISTS owned_device;CREATE TABLE owned_device ( device_id INTEGER PRIMARY KEY, code TEXT NOT NULL UNIQUE);CREATE TABLE sample ( sample_id INTEGER PRIMARY KEY, device_id INTEGER NOT NULL REFERENCES owned_device(device_id) ON DELETE CASCADE, value REAL NOT NULL);INSERT INTO owned_device VALUES (1, 'PUMP-007');INSERT INTO sample(device_id, value) VALUES (1, 12.1), (1, 12.4);DELETE FROM owned_device WHERE device_id = 1;SELECT count(*) AS remaining_samples FROM sample;The count is zero because the relationship explicitly says the sample lifecycle follows the device lifecycle. That is a data-model assertion. If it is wrong, the database will enforce the wrong business rule perfectly.
SET NULL and SET DEFAULT must still produce valid child state
SET NULL only makes sense if the child key columns permit NULL. SET DEFAULT is not an escape hatch: after SQLite substitutes the default, the foreign key must still be satisfied.
PRAGMA foreign_keys = ON;DROP TABLE IF EXISTS assignment;DROP TABLE IF EXISTS technician;CREATE TABLE technician ( technician_id INTEGER PRIMARY KEY, name TEXT NOT NULL);CREATE TABLE assignment ( assignment_id INTEGER PRIMARY KEY, technician_id INTEGER DEFAULT NULL REFERENCES technician(technician_id) ON DELETE SET NULL, task TEXT NOT NULL);INSERT INTO technician VALUES (10, 'Mina');INSERT INTO assignment VALUES (1, 10, 'Inspect seal');DELETE FROM technician WHERE technician_id = 10;SELECT assignment_id, technician_id, task FROM assignment;The assignment remains and technician_id becomes NULL. If the column had been NOT NULL, this action would collide with another constraint. Likewise, a SET DEFAULT 0 action fails unless parent key 0 exists (or the default is NULL and NULL is allowed).
NO ACTION versus RESTRICT: timing can differ
Both can prevent an invalid parent change, but RESTRICT is stricter about when the failure is raised. RESTRICT fires immediately when the parent key is touched, even if the foreign key itself is deferred. NO ACTION waits until the normal foreign-key check point—end of statement for an immediate constraint or COMMIT for a deferred one.
A deferred NO ACTION relationship can temporarily be inconsistent inside a transaction and become valid before COMMIT. A RESTRICT action does not grant that temporary window for the parent operation it guards.
Deferred constraints allow temporary inconsistency inside one transaction
Foreign keys are immediate by default. An immediate violation must be resolved by the end of the statement. A foreign key declared DEFERRABLE INITIALLY DEFERRED may be violated temporarily while an explicit transaction is open, but COMMIT refuses to make the invalid state durable.
PRAGMA foreign_keys = ON;DROP TABLE IF EXISTS deferred_note;DROP TABLE IF EXISTS deferred_device;CREATE TABLE deferred_device ( device_id INTEGER PRIMARY KEY, code TEXT NOT NULL UNIQUE);CREATE TABLE deferred_note ( note_id INTEGER PRIMARY KEY, device_id INTEGER NOT NULL, text TEXT NOT NULL, FOREIGN KEY (device_id) REFERENCES deferred_device(device_id) DEFERRABLE INITIALLY DEFERRED);BEGIN;INSERT INTO deferred_note(note_id, device_id, text)VALUES (1, 500, 'Imported child first');-- Temporarily inconsistent, but still inside the transaction.PRAGMA foreign_key_check;INSERT INTO deferred_device(device_id, code)VALUES (500, 'PUMP-500');COMMIT;PRAGMA foreign_key_check;The first insert is accepted only because the explicit transaction is open and the foreign key is deferred. After the parent row arrives, COMMIT succeeds. Without an explicit transaction, the implicit transaction ends with the statement, so a deferred constraint has no useful temporary window.
If COMMIT fails, the transaction is still unresolved
A failed COMMIT caused by a deferred foreign-key violation does not magically commit the valid subset. The transaction remains open. You can add the missing parent and try COMMIT again, or explicitly ROLLBACK.
BEGIN;INSERT INTO deferred_note(note_id, device_id, text)VALUES (2, 999, 'No parent will arrive');-- This fails: FOREIGN KEY constraint failed.COMMIT;-- The transaction is still open. Choose one recovery path.ROLLBACK;SELECT count(*) AS note_2_existsFROM deferred_noteWHERE note_id = 2;After ROLLBACK, the count is zero. Production code should treat COMMIT as an operation that can fail and should have a defined rollback/error path rather than assuming all errors occur during individual INSERT/UPDATE statements.
PRAGMA defer_foreign_keys is a transaction-scoped override
SQLite also provides PRAGMA defer_foreign_keys=ON, which temporarily defers all foreign-key constraints until the outer transaction completes. It resets at COMMIT or ROLLBACK. This can be useful in carefully designed migrations, but it is broader than declaring one relationship deferred in the schema.
BEGIN;PRAGMA defer_foreign_keys = ON;-- Carefully ordered migration work here.-- All FK checks are deferred to the transaction boundary.COMMIT;Prefer schema-declared deferral when the business workflow genuinely needs it. A broad PRAGMA during ordinary application work can hide ordering bugs until COMMIT and make failures harder to localize.
Design exercise: choose the action from meaning
| Relationship | Question | Reasonable starting choice |
|---|---|---|
| site → device | Can a site record disappear while its devices still exist? | Often RESTRICT/NO ACTION; deleting a site should require an explicit migration/retirement workflow. |
| device → transient sample | Are samples meaningless without the device? | Often ON DELETE CASCADE if policy permits deletion. |
| technician → assignment | Can an assignment survive employee removal as unassigned work? | Possibly SET NULL if nullable and history requirements agree. |
| status_code → note | Should deleting a code silently rewrite notes to a generic code? | Usually restrict; SET DEFAULT only if a real sentinel parent row is deliberate. |
| natural parent code rename → child code | Should children follow a stable-key update? | ON UPDATE CASCADE can be reasonable, though stable surrogate parent keys reduce this need. |
There is no universal “best” action. Document ownership, audit retention, optionality, and deletion semantics before writing the clause.
Lifecycle checkpoint
Predict the resulting database state.
- Why can
ON DELETE SET NULLfail even though SET NULL is a valid foreign-key action? - What is the default action when no ON DELETE/UPDATE action is specified?
- Why is RESTRICT observably different from deferred NO ACTION?
- Can a transaction COMMIT while a deferred foreign-key violation still exists?
- After a deferred COMMIT failure, what should application code do?
Review the answers
SET NULL conflicts with NOT NULL if the child key is mandatory. The default is NO ACTION. RESTRICT rejects the guarded parent change immediately, whereas deferred NO ACTION can wait until COMMIT. COMMIT cannot succeed with unresolved deferred violations. The application must repair the transaction and retry commit or roll it back explicitly.
Summary and bridge
Foreign-key actions are lifecycle semantics: protect, clear, default, propagate, or defer. Deferred constraints give one transaction a controlled temporary inconsistency, but durable state must still satisfy the relationship. Lesson 4 zooms into another failure dimension: when a UNIQUE, NOT NULL, CHECK, or PRIMARY KEY conflict happens, how much of the current statement or transaction does SQLite preserve?