DELETE, Cascades, Soft Delete, and Data Lifecycle

Learning outcomes

DELETE is simple syntactically and potentially large operationally. A one-row parent DELETE can remove many child rows through CASCADE, and deleting rows usually creates reusable free pages rather than immediately shrinking the database file.

DELETE targets rows exactly like a SELECT predicate

A DELETE without WHERE removes every row in the table. With WHERE, only rows for which the predicate is true are targeted. Preview first.

DROP TABLE IF EXISTS cleanup_note;CREATE TABLE cleanup_note(  note_id INTEGER PRIMARY KEY,  device_code TEXT NOT NULL,  status TEXT NOT NULL,  occurred_at TEXT NOT NULL);INSERT INTO cleanup_note(device_code,status,occurred_at) VALUES('PUMP-007','closed','2025-01-01'),('PUMP-007','open','2026-08-12'),('FAN-014','closed','2024-05-01');SELECT note_id, device_code, status, occurred_atFROM cleanup_noteWHERE status='closed' AND occurred_at<'2026-01-01';

Only after the SELECT result matches the retention rule should the DELETE use that same predicate.

Cascades expand lifecycle scope

Chapter 5 established that ON DELETE CASCADE is a business ownership rule. It means deleting the parent causes SQLite to delete referencing child rows when foreign keys are enabled.

PRAGMA foreign_keys=ON;DROP TABLE IF EXISTS child_note;DROP TABLE IF EXISTS parent_device;CREATE TABLE parent_device(device_id INTEGER PRIMARY KEY, code TEXT UNIQUE NOT NULL);CREATE TABLE child_note(  note_id INTEGER PRIMARY KEY,  device_id INTEGER NOT NULL REFERENCES parent_device(device_id) ON DELETE CASCADE,  body TEXT NOT NULL);INSERT INTO parent_device VALUES (1,'PUMP-007'),(2,'FAN-014');INSERT INTO child_note(device_id,body) VALUES (1,'a'),(1,'b'),(2,'c');SELECT count(*) AS child_before FROM child_note; -- 3DELETE FROM parent_device WHERE device_id=1;SELECT changes() AS direct_parent_rows;          -- 1SELECT count(*) AS child_after FROM child_note;  -- 1

The SQL changes() value reports direct rows changed by the top-level statement, not auxiliary rows changed by foreign-key actions. Therefore a count of 1 does not mean the operation affected only one stored row.

Hard delete versus soft delete

A hard delete removes the row. A soft-delete design usually keeps the row and records lifecycle state such as deleted_at or is_deleted. Neither is universally superior.

ALTER TABLE cleanup_note ADD COLUMN deleted_at TEXT;UPDATE cleanup_noteSET deleted_at=CURRENT_TIMESTAMPWHERE note_id=1;-- Application-visible rows must deliberately filter:SELECT * FROM cleanup_note WHERE deleted_at IS NULL;

If users expect data to be erased, a soft-delete flag may be the wrong compliance behavior. If audit/history must be retained, immediate hard deletion may be wrong. Model the lifecycle from requirements.

Deleting rows does not necessarily shrink the file

SQLite stores tables and indexes in pages. Deleting rows can make pages reusable inside the database; it does not imply the operating-system file immediately becomes smaller. PRAGMA freelist_count reports currently unused pages in the database file.

PRAGMA page_count;PRAGMA freelist_count;DELETE FROM cleanup_note WHERE status='closed';PRAGMA page_count;PRAGMA freelist_count;

For a tiny lab the values may not visibly change because pages still contain other records. On larger deletions, free pages can grow. Chapter 18 will cover maintenance and VACUUM choices. Do not schedule VACUUM merely because rows were deleted; it rewrites the database and has operational costs.

Transaction-protected cleanup lab

Build counts before and after the candidate deletion, then decide whether to commit.

DROP TABLE IF EXISTS retention_note;CREATE TABLE retention_note(  note_id INTEGER PRIMARY KEY,  status TEXT NOT NULL,  occurred_at TEXT NOT NULL);INSERT INTO retention_note(status,occurred_at) VALUES('closed','2024-01-01'),('closed','2025-06-01'),('open','2024-02-01'),('open','2026-08-12');SELECT count(*) AS total_before FROM retention_note;SELECT count(*) AS candidatesFROM retention_noteWHERE status='closed' AND occurred_at<'2026-01-01'; -- expect 2BEGIN;DELETE FROM retention_noteWHERE status='closed' AND occurred_at<'2026-01-01';SELECT changes() AS direct_deleted;                 -- expect 2SELECT count(*) AS total_after_candidate FROM retention_note; -- expect 2-- Lab choice: verify then commit.COMMIT;SELECT count(*) AS final_total FROM retention_note; -- 2

The important behavior is not the exact date rule; it is that candidate count, direct delete count, and final count agree with the intended lifecycle policy before COMMIT.

Failure cases and production judgment

Summary and bridge

Safe deletion combines target verification, transaction protection, relationship knowledge, and lifecycle intent. Next comes a different write problem: you have an incoming logical entity and need to insert it if new, but update or ignore it when a uniqueness rule identifies an existing row. That is UPSERT—not REPLACE.

Authoritative references

• SQLite — DELETE.
• SQLite — Foreign Keys.
• SQLite — PRAGMA freelist_count.
• SQLite — VACUUM.