Chapter 10 · Changing Data Safely
DELETE, TRUNCATE, and Data-Retention Decisions
Removing data is not only a syntax decision. It is a lifecycle decision involving references, auditability, legal retention, recovery, storage, and downstream consumers.
Learning outcomes
DELETE removes rows; TRUNCATE, where supported, removes all rows through a distinct command with engine-specific locking, logging, identity, and rollback behavior. Retention design determines whether removal is appropriate at all.
Delete a reviewed row set with precise predicates and RETURNING.
Predict foreign-key RESTRICT, CASCADE, and SET NULL effects.
Compare DELETE and TRUNCATE without assuming cross-vendor equivalence.
Design hard-delete, soft-delete, and archive-then-delete workflows.
Connect retention decisions to recovery, audit, and downstream systems.
Removal is a lifecycle decision
Delete row
The logical row no longer exists in the table.
Mark deleted
The row stays but normal queries exclude it.
Move then delete
A separate store preserves selected history.
Retention job
Rows are removed according to a documented age and status policy.
Preview and delete the same set
SELECT order_id, external_ref, status, ordered_atFROM sales_orderWHERE order_id = 101 AND status = 'draft';DELETE FROM sales_orderWHERE order_id = 101 AND status = 'draft'RETURNING order_id, external_ref, status;The state predicate prevents deleting the row if it became paid between the operator’s initial assumption and the statement.
Referential actions change the blast radius
| Foreign-key action | Parent deletion result |
|---|---|
| RESTRICT / NO ACTION | Reject the parent deletion while referencing children remain. |
| CASCADE | Delete referencing child rows automatically. |
| SET NULL | Preserve children but clear the reference when NULL is allowed. |
| SET DEFAULT | Replace the reference with its default when the default remains valid. |
BEGIN;SELECT COUNT(*) AS child_rows_beforeFROM order_itemWHERE order_id = 100;DELETE FROM sales_orderWHERE order_id = 100;SELECT COUNT(*) AS child_rows_afterFROM order_itemWHERE order_id = 100;ROLLBACK;The schema declares ON DELETE CASCADE for order items. A safe operator reviews both the parent target and dependent rows before committing.
DELETE all rows versus TRUNCATE
| Question | DELETE FROM table | TRUNCATE TABLE |
|---|---|---|
| Row filter | Supports WHERE | Removes the whole table only. |
| Row triggers | Normally participate | Behavior varies by engine. |
| Identity/sequence | Usually unchanged | May reset or optionally restart identity. |
| Foreign-key restrictions | Checked row deletion semantics | Often stricter or structurally different. |
| Availability in SQLite | Supported | No TRUNCATE statement. |
| Rollback and logging | Engine-specific | Engine-specific; never assume non-transactional or transactional universally. |
SQLite has no TRUNCATE statement. A DELETE FROM table with no WHERE and no RETURNING may use an internal truncate optimization when no trigger prevents it, but the SQL command remains DELETE.
Archive before deletion
BEGIN;INSERT INTO order_archive ( order_id, external_ref, customer_id, status, total_amount, ordered_at, archived_at)SELECT order_id, external_ref, customer_id, status, total_amount, ordered_at, CURRENT_TIMESTAMPFROM sales_orderWHERE status IN ('cancelled', 'refunded') AND ordered_at < '2026-01-01';DELETE FROM sales_orderWHERE order_id IN ( SELECT order_id FROM order_archive);COMMIT;The archive table has a primary key, so rerunning the insert exposes duplicates instead of silently creating multiple history copies. In a production workflow, verify archive counts before deletion and keep both operations in one transaction when the engine and storage design permit it.
Soft deletion
UPDATE customerSET status = 'closed', deleted_at = CURRENT_TIMESTAMP, updated_at = CURRENT_TIMESTAMPWHERE customer_id = 5 AND deleted_at IS NULLRETURNING customer_id, status, deleted_at;Soft deletion helps recovery and audit, but it increases query complexity. Unique keys, foreign keys, reports, caches, and privacy obligations must all account for deleted rows.
Retention policy checklist
| Decision | Question |
|---|---|
| Purpose | Why is the data retained, and who uses it? |
| Clock | Which timestamp starts the retention period? |
| Eligibility | Which statuses or legal holds block deletion? |
| Dependencies | What children, aggregates, files, caches, and exports depend on it? |
| Evidence | How are candidate counts, deletion counts, and approvals recorded? |
| Recovery | Can the data be restored, and for how long? |
Reusable Chapter 10 practice database
Run this setup once in a disposable SQLite database. Every lesson uses the same constrained tables so that write behavior, references, conflicts, and verification can be compared consistently.
PRAGMA foreign_keys = ON;DROP TABLE IF EXISTS change_request;DROP TABLE IF EXISTS order_archive;DROP TABLE IF EXISTS order_item;DROP TABLE IF EXISTS sales_order;DROP TABLE IF EXISTS product_feed;DROP TABLE IF EXISTS customer_stage;DROP TABLE IF EXISTS product;DROP TABLE IF EXISTS customer;CREATE TABLE customer ( customer_id INTEGER PRIMARY KEY, email TEXT NOT NULL UNIQUE, full_name TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'paused', 'closed')), loyalty_points INTEGER NOT NULL DEFAULT 0 CHECK (loyalty_points >= 0), deleted_at TEXT, updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP) STRICT;CREATE TABLE product ( product_id INTEGER PRIMARY KEY, sku TEXT NOT NULL UNIQUE, product_name TEXT NOT NULL, unit_price REAL NOT NULL CHECK (unit_price >= 0), stock_qty INTEGER NOT NULL DEFAULT 0 CHECK (stock_qty >= 0), discontinued_at TEXT, updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP) STRICT;CREATE TABLE sales_order ( order_id INTEGER PRIMARY KEY, external_ref TEXT NOT NULL UNIQUE, customer_id INTEGER NOT NULL REFERENCES customer(customer_id), status TEXT NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'paid', 'cancelled', 'refunded')), total_amount REAL NOT NULL DEFAULT 0 CHECK (total_amount >= 0), ordered_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP) STRICT;CREATE TABLE order_item ( order_id INTEGER NOT NULL REFERENCES sales_order(order_id) ON DELETE CASCADE, line_no INTEGER NOT NULL, product_id INTEGER NOT NULL REFERENCES product(product_id), quantity INTEGER NOT NULL CHECK (quantity > 0), unit_price REAL NOT NULL CHECK (unit_price >= 0), PRIMARY KEY (order_id, line_no)) STRICT;CREATE TABLE customer_stage ( email TEXT NOT NULL UNIQUE, full_name TEXT NOT NULL, status TEXT NOT NULL CHECK (status IN ('active', 'paused', 'closed'))) STRICT;CREATE TABLE product_feed ( sku TEXT NOT NULL UNIQUE, product_name TEXT NOT NULL, unit_price REAL NOT NULL CHECK (unit_price >= 0), stock_qty INTEGER NOT NULL CHECK (stock_qty >= 0), source_time TEXT NOT NULL) STRICT;CREATE TABLE order_archive ( order_id INTEGER PRIMARY KEY, external_ref TEXT NOT NULL, customer_id INTEGER NOT NULL, status TEXT NOT NULL, total_amount REAL NOT NULL, ordered_at TEXT NOT NULL, archived_at TEXT NOT NULL) STRICT;CREATE TABLE change_request ( request_key TEXT PRIMARY KEY, operation TEXT NOT NULL, applied_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP) STRICT;INSERT INTO customer (customer_id, email, full_name, status, loyalty_points, updated_at)VALUES (1, 'nadia@example.com', 'Nadia Rahimi', 'active', 120, '2026-08-01 09:00:00'), (2, 'omar@example.com', 'Omar Haddad', 'active', 80, '2026-08-01 09:00:00'), (3, 'lina@example.com', 'Lina Chen', 'paused', 40, '2026-08-01 09:00:00'), (4, 'ava@example.com', 'Ava Morgan', 'active', 0, '2026-08-01 09:00:00'), (5, 'noah@example.com', 'Noah Silva', 'closed', 15, '2026-08-01 09:00:00');INSERT INTO product (product_id, sku, product_name, unit_price, stock_qty, updated_at)VALUES (10, 'SQL-FOUND', 'Database Foundations', 49.00, 35, '2026-08-01 09:00:00'), (11, 'SQL-QUERY', 'SQL Query Practice', 69.00, 18, '2026-08-01 09:00:00'), (12, 'SQL-CARD', 'SQL Reference Card', 15.00, 80, '2026-08-01 09:00:00'), (13, 'SQL-LAB', 'SQLite Lab Bundle', 29.00, 22, '2026-08-01 09:00:00'), (14, 'DQ-WORK', 'Data Quality Workbook', 24.50, 0, '2026-08-01 09:00:00');INSERT INTO sales_order (order_id, external_ref, customer_id, status, total_amount, ordered_at)VALUES (100, 'WEB-100', 1, 'paid', 79.00, '2026-07-01 10:00:00'), (101, 'WEB-101', 2, 'draft', 0.00, '2026-07-10 11:00:00'), (102, 'WEB-102', 3, 'cancelled', 29.00, '2025-12-01 12:00:00'), (103, 'WEB-103', 4, 'paid', 69.00, '2026-08-01 13:00:00'), (104, 'WEB-104', 5, 'refunded', 15.00, '2025-11-15 14:00:00');INSERT INTO order_item VALUES (100, 1, 10, 1, 49.00), (100, 2, 13, 1, 30.00), (102, 1, 13, 1, 29.00), (103, 1, 11, 1, 69.00), (104, 1, 12, 1, 15.00);INSERT INTO customer_stage VALUES ('marta@example.com', 'Marta Costa', 'active'), ('reza@example.com', 'Reza Nouri', 'active'), ('lina@example.com', 'Lina Chen', 'active');INSERT INTO product_feed VALUES ('SQL-FOUND', 'Database Foundations', 52.00, 31, '2026-08-05 06:00:00'), ('SQL-CARD', 'SQL Reference Card', 15.00, 75, '2026-08-05 06:00:00'), ('SQL-OPS', 'Database Operations', 89.00, 12, '2026-08-05 06:00:00');The setup drops tables. Never execute training reset scripts against a production database.
Practice lab
- Find terminal orders older than 2026-01-01.
- Count their dependent order-item rows.
- Archive the eligible orders and delete them in one transaction.
- Roll back and verify that both source and archive return to their original state.
- Compare hard deletion with the customer soft-delete pattern.
SELECT (SELECT COUNT(*) FROM order_archive) AS archived_orders, (SELECT COUNT(*) FROM sales_order WHERE status IN ('cancelled', 'refunded') AND ordered_at < '2026-01-01') AS eligible_source_orders;Checkpoint
Choose a retention action
- Why is DELETE not only a syntax decision?
- What can ON DELETE CASCADE remove beyond the visible parent row?
- Does SQLite implement TRUNCATE TABLE?
- What must be verified between archiving and deleting?
- What complexity does soft deletion introduce?
Review the answers
Deletion affects audit, recovery, dependencies, and policy. CASCADE removes referencing children. SQLite uses DELETE, not TRUNCATE. Archive and source counts must reconcile. Soft deletion requires every normal query and uniqueness rule to understand deleted state.
Summary and references
- Prove the deletion target and its dependency graph before executing.
- DELETE and TRUNCATE are not portable synonyms.
- Archival and soft deletion preserve different kinds of history.
- A retention workflow needs policy, evidence, verification, and recovery.