DELETE, TRUNCATE & Data-Loss Safety — SQL Course

What you'll learn

Remove data with explicit scope, understand cascades and TRUNCATE, and use transactions and backups to reduce irreversible mistakes. The lab uses PostgreSQL while identifying the semantics that transfer to other relational systems.

By the end of this lesson, you'll be able to:

Apply DELETE to a realistic data question
Apply DELETE USING to a realistic data question
Apply Foreign-key actions to a realistic data question
Apply TRUNCATE to a realistic data question

Core mental model

SQL is declarative: describe the result or invariant you need, then let the database choose a physical execution strategy. Use this table to connect syntax to design decisions.

Concept	What it means	Decision rule
DELETE	Row removal with a predicate and trigger behavior	Use when scope or row-level effects matter
TRUNCATE	Fast whole-table removal with stronger locking semantics	Reserve for deliberate administrative workflows
Cascade	Automatic dependent action through foreign keys	Select it only when child lifecycle truly follows the parent

Professional workflow

Work from a defined question and result grain, then verify correctness before performance.

State the data-removal operation question and the exact grain of the expected result.
Inspect table definitions, keys, constraints, representative values, and row counts.
Write the smallest correct query with explicit columns, aliases, and predicates.
Test missing, duplicate, boundary, and NULL cases before trusting the result.
Inspect the execution plan or affected rows when cost or data change matters.
Save the query with its assumptions, parameters, verification, and recovery notes.

Make results explainable

Keep each query in a saved SQL file with a short statement of its purpose, expected grain, assumptions, and verification query.

Guided SQL lab

Archive before deletion

The transaction captures exactly the rows selected for removal and allows verification before commit.

remove_discontinued.sql

BEGIN;

WITH removed AS (
  DELETE FROM inventory.products
  WHERE discontinued_at < current_date - INTERVAL '2 years'
  RETURNING *
)
INSERT INTO inventory.product_archive
SELECT * FROM removed;

-- Verify counts, then COMMIT; otherwise ROLLBACK.

Production practice

Contract

Define the expected row grain, inputs, output columns, invariants, and failure or empty-result behavior before writing SQL.

Verification

Use representative fixtures and independent row-count, uniqueness, NULL, and boundary checks; compare plans when cost matters.

Operations

Save reviewed SQL with explicit schema names where appropriate, bounded scope, least privilege, observability, and a recovery path for changes.

Common failure mode

A cascade can turn one delete into a large graph removal. Inspect foreign-key actions and estimate affected rows first.

Independent workshop

Build a review-ready data-removal operation lab against the course commerce dataset.

Your finished workshop must include:

DELETE
DELETE USING
Foreign-key actions
TRUNCATE
Transactions
Verification notes and edge-case evidence

Definition of done

Run the expected case and at least two edge cases, verify row counts and grain, and add comments explaining any vendor-specific behavior.

Recap & quick check

Key takeaways

DELETE: Use when scope or row-level effects matter
TRUNCATE: Reserve for deliberate administrative workflows
Cascade: Select it only when child lifecycle truly follows the parent

Quick check

1. Which rule best applies to DELETE?

2. Which rule best applies to TRUNCATE?

3. Which rule best applies to Cascade?

Next: String, Numeric & Date-Time Functions