How should SQLite foreign keys handle a deleted parent row?

Decide what the child rows mean before choosing a foreign-key action. SQLite offers three relevant policies: RESTRICT blocks the parent deletion while dependent child rows exist; CASCADE deletes those child rows; SET NULL keeps them but clears their foreign-key values. The action is declared on the child table’s REFERENCES constraint. [S1]

A practical selection guide:

1. Choose RESTRICT when a child record must remain linked and should not be removed as a side effect.
2. Choose CASCADE when child records have no purpose without that particular parent and should be removed with it.
3. Choose SET NULL when child records should remain, but their association with the deleted parent should be removed. The child foreign-key column must allow NULL. [S1]

These policies represent different data-retention decisions; none is automatically the right choice for every relationship.

Here are three alternatives for a hypothetical parent and child relationship. Treat each as a separate disposable example; create the tables in a test database rather than changing important data.

CREATE TABLE parent (
  id INTEGER PRIMARY KEY
);

-- Keep children linked; block deletion while children exist.
CREATE TABLE child_restrict (
  id INTEGER PRIMARY KEY,
  parent_id INTEGER NOT NULL
    REFERENCES parent(id) ON DELETE RESTRICT
);

-- Delete dependent children when their parent is deleted.
CREATE TABLE child_cascade (
  id INTEGER PRIMARY KEY,
  parent_id INTEGER NOT NULL
    REFERENCES parent(id) ON DELETE CASCADE
);

-- Keep children but allow their parent reference to be cleared.
CREATE TABLE child_detachable (
  id INTEGER PRIMARY KEY,
  parent_id INTEGER
    REFERENCES parent(id) ON DELETE SET NULL
);

The action appears on the child-side REFERENCES constraint. In the detachable example, parent_id is not declared NOT NULL, which allows it to hold NULL as required by SET NULL. [S1] These snippets illustrate the constraint choices; they do not establish what policy suits your application.

Ask whether the child has meaning on its own after the parent is gone. For example, suppose parent represents a project and child represents a project-specific note. If the note is useful only as part of that project, CASCADE may match the intended relationship. If it should remain available but no longer belong to a project, SET NULL may match instead—provided the reference column can hold NULL. SQLite’s documented actions support those respective outcomes. [S1]

Before choosing, write the intended result in plain language: “When this parent is deleted, I expect these child rows to ___.” Then check any other application requirements about retaining, displaying, or cleaning up those records. The SQLite action alone does not determine your product’s retention policy.

Use this short test plan with disposable sample data:

1. Add one parent and a child that references it.
2. Confirm which outcome your chosen policy is meant to produce: blocked deletion, deleted child, or retained child with a cleared reference.
3. Try deleting that parent in the test database and inspect the child table afterward.
4. For SET NULL, verify the child foreign-key column is permitted to hold NULL; SQLite requires that for this action. [S1]
5. Repeat with a parent that has no child, and record whether the observed behavior matches your expectation.

The SQLite documentation establishes the three action outcomes and the SET NULL requirement. It does not decide which records your application should preserve, nor does this guide claim the examples have been run against your database. Use your own disposable test data before applying a schema change.