DELETE — Removing Rows

DELETE is a DML (Data Manipulation Language) command that removes specific rows based on a WHERE condition, or all rows if no WHERE is given. DELETE is fully transactional in all databases — it can be rolled back if inside an uncommitted transaction. DELETE fires triggers, respects foreign key constraints, and logs each row deletion to the transaction log. On large tables DELETE is slow because every row removal is individually logged.

TRUNCATE is a DDL (Data Definition Language) command that removes all rows from a table at once. It does not use a WHERE clause — it is all-or-nothing. TRUNCATE is much faster than DELETE because it deallocates data pages rather than removing rows one by one. In PostgreSQL, TRUNCATE is transactional and can be rolled back. In MySQL, TRUNCATE is NOT transactional and auto-commits — it cannot be rolled back. TRUNCATE does not fire row-level triggers and bypasses foreign key cascade rules (you must truncate in dependency order or use CASCADE).

DROP TABLE removes the entire table — both all the data and the table structure (column definitions, constraints, indexes). It is irreversible without a backup. Use DROP TABLE when decommissioning a feature or cleaning up a table that is no longer needed. The choice in practice: DELETE for removing specific rows in production, TRUNCATE for resetting test/staging environments, DROP TABLE for schema changes during development or migrations.

Soft delete is a pattern where rows are never physically removed from the database — instead a column (typically is_deleted BOOLEAN or deleted_at TIMESTAMP) is set to mark the row as logically deleted. All normal application queries include WHERE is_deleted = false (or WHERE deleted_at IS NULL) to exclude soft-deleted rows. The actual data remains in the database indefinitely or until a scheduled hard delete after a retention period.

Four reasons production applications prefer soft delete: First, auditability — regulators, auditors, and compliance teams need to see the full history of what happened to every record. A hard-deleted row leaves no trace. A soft-deleted row is permanently visible to audit queries. Second, recovery — users accidentally delete things daily. With soft delete, recovery is UPDATE is_deleted = false — instant. With hard delete, recovery requires a database backup restore, potentially losing hours of other changes. Third, referential integrity — a hard-deleted customer still has orders in the orders table that now reference a non-existent customer. Soft delete keeps the customer row, preserving all relationships. Fourth, analytics — churned customers, removed products, and cancelled subscriptions have analytical value. Their historical data answers questions about why users leave and which products fail.

The trade-offs: soft delete means the table grows indefinitely unless a periodic hard delete runs for very old soft-deleted rows. All queries must include the is_deleted filter — missing it returns deleted rows, which is a bug. Indexes must be designed to efficiently filter on is_deleted. And UNIQUE constraints become complex — if an email is soft-deleted, can a new user register with the same email? These are solvable problems, but they require deliberate design. Most US tech companies (DoorDash, Stripe, Brex) use soft delete for user-facing data and compliance-sensitive tables, and use hard delete only for truly ephemeral data like session tokens and temporary processing records.

When a foreign key is defined with ON DELETE CASCADE, deleting a row from the parent table automatically deletes all rows in child tables that reference the deleted parent. This cascades through multiple levels — if table A references B with CASCADE, and table C references A with CASCADE, deleting from B automatically deletes from A and then from C.

The mechanism: the database engine, after deleting the parent row, searches all FK relationships on that table for CASCADE constraints. For each found FK with CASCADE, it deletes the referencing rows from the child table — which may trigger further cascades in tables that reference the child. This all happens atomically within the same transaction. The entire cascade either succeeds or rolls back together.

The risks: a CASCADE delete from a parent table can silently remove far more rows than you intended. DELETE FROM orders WHERE order_id = 1007 might also delete 5 rows from order_items via CASCADE — if you did not know about the CASCADE relationship, the order_items deletion is invisible and surprising. Before deleting from any table, always check which FK relationships have CASCADE and estimate how many rows will be affected across all cascading tables. Count them with SELECT queries before the DELETE. Another risk: poorly designed CASCADE chains can make a single DELETE catastrophically broad — a DELETE from a top-level table cascades through 5 child tables, removing thousands of rows you did not intend to touch. Restrict CASCADE to genuinely containment relationships (order_items to orders) and use RESTRICT for association relationships (orders to customers).

Deleting duplicates requires identifying which rows to keep and which to delete. The standard approach uses a CTE (Common Table Expression) with ROW_NUMBER() to assign a rank to each duplicate group — keeping the row with rank 1 and deleting the rest.

The pattern in PostgreSQL: WITH duplicates AS (SELECT customer_id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY customer_id ASC) AS rn FROM customers) DELETE FROM customers WHERE customer_id IN (SELECT customer_id FROM duplicates WHERE rn > 1);. The PARTITION BY email groups rows by email address. ROW_NUMBER() assigns 1 to the first occurrence (by customer_id order) and 2, 3, etc. to subsequent duplicates. The DELETE removes all rows where rn > 1 — keeping exactly one row per email.

Choosing which duplicate to keep: ORDER BY customer_id ASC keeps the oldest (lowest ID, first inserted). ORDER BY customer_id DESC keeps the newest. ORDER BY updated_at DESC keeps the most recently modified. The choice depends on which duplicate has the most complete or most current data. Always run the CTE as a SELECT first — SELECT * FROM duplicates WHERE rn > 1 — to see exactly which rows will be deleted before converting it to a DELETE statement. For tables with hundreds of millions of rows, batch the duplicate deletion by adding a LIMIT to avoid locking the table for too long.

The first action depends on whether the statement is still running or has already completed. If it is still running — interrupt it immediately. In PostgreSQL, find the process with SELECT pid, query FROM pg_stat_activity WHERE state = 'active' and terminate it with SELECT pg_terminate_backend(pid). If the DELETE was running inside an explicit transaction and has not been committed, terminating the connection automatically rolls back the transaction — all rows are restored. This is why wrapping destructive operations in explicit transactions is critical.

If the DELETE has already committed: assess the damage immediately. Determine when the DELETE ran (check database logs or application logs) and how old the most recent backup is. In PostgreSQL, point-in-time recovery (PITR) using WAL (write-ahead logs) can restore the database to the exact state immediately before the DELETE — this is the fastest recovery path and preserves all changes made after the last base backup. Contact your DBA immediately — this is not a situation to handle alone.

Prevention for the future: enable query review requirements for destructive operations on production (require a second engineer to approve the SQL before execution), implement a "safe mode" in your SQL client that prevents DELETE/UPDATE without WHERE, use row-level security policies that limit which users can run DELETE, add automated backups with short RPO (Recovery Point Objective) so the maximum data loss window is small, and make soft delete the default for all user-facing tables so hard delete is rarely necessary. This incident should trigger a blameless post-mortem focused on system improvements rather than individual punishment — the system should make it impossible to cause this level of damage with a single command.

Cause: You tried to delete a customer who has orders in the orders table. The FK constraint on orders.customer_id → customers.customer_id with ON DELETE RESTRICT (the default) prevents the deletion because deleting the customer would orphan their orders. The database is correctly protecting referential integrity.

Fix: Three options: (1) Delete the child rows first, then the parent: DELETE FROM order_items WHERE order_id IN (SELECT order_id FROM orders WHERE customer_id = 42); DELETE FROM orders WHERE customer_id = 42; DELETE FROM customers WHERE customer_id = 42. (2) Use soft delete instead: UPDATE customers SET is_deleted = true WHERE customer_id = 42. (3) Change the FK to ON DELETE CASCADE — but only if child rows are genuinely meaningless without the parent, which orders usually are not (they are financial records).

Cause: The WHERE clause was less specific than intended — it matched far more rows than expected. Common causes: a missing AND condition, using OR when AND was intended, a date range that was too broad, or a condition that matched on a non-unique column when a unique identifier should have been used. This is the most catastrophic DELETE error class because it is silent — no error, just too much data gone.

Fix: If inside a transaction: ROLLBACK immediately. If already committed: check the most recent database backup and begin point-in-time recovery. Going forward: always run SELECT COUNT(*) with the identical WHERE before DELETE, verify the count matches expectations, wrap in a transaction, and check the affected row count reported by the DELETE statement before COMMIT. Many teams require a second person to verify the SELECT output before any DELETE affecting more than 100 rows in production.

Cause: You tried to TRUNCATE a parent table that has child tables with FK references to it. TRUNCATE cannot check and cascade FK relationships the same way DELETE does — it would leave the child table with orphaned rows pointing to a now-empty parent. PostgreSQL blocks this by default.

Fix: Use TRUNCATE TABLE parent_table CASCADE to also truncate all FK-referencing child tables simultaneously. WARNING: this will empty ALL child tables too — verify this is what you want before running. Alternatively, TRUNCATE in dependency order: TRUNCATE the child tables first, then the parent. TRUNCATE order_items; TRUNCATE orders; TRUNCATE customers; — child tables first, parent tables last. In MySQL, disable foreign key checks temporarily: SET FOREIGN_KEY_CHECKS = 0; TRUNCATE ...; SET FOREIGN_KEY_CHECKS = 1; — but only in controlled environments, never in production with live application traffic.

Cause: The WHERE clause matched no rows. Common causes: the filter value does not match what is stored (case difference, leading/trailing whitespace, wrong ID), the rows were already deleted by another process, a date filter using the wrong format, or a subquery that returns no rows making the IN condition match nothing.

Fix: Run the equivalent SELECT: SELECT * FROM table WHERE [identical WHERE condition]. If it returns 0 rows, investigate the filter. Check actual stored values: SELECT DISTINCT status FROM table to see what values exist. Check for case issues: WHERE LOWER(column) = LOWER('value'). Check for whitespace: WHERE TRIM(column) = 'value'. For subquery-based deletes, run the subquery alone first to confirm it returns the expected rows.

Cause: Deleting millions of rows from a large table is slow for two reasons: the database logs every row deletion individually to the transaction log (for rollback capability), and every deleted row requires updating all indexes on that table. On a table with 10 indexes, deleting one row updates 10 indexes. Deleting 50 million rows updates 10 indexes × 50 million times. Additionally, a long-running DELETE holds locks that block other queries.

Fix: Three approaches in order of preference: (1) Batch delete: add LIMIT 10000 and loop until 0 rows affected — each batch commits quickly, releases locks, and keeps the operation visible to other queries. (2) If deleting most rows, consider creating a new table with only the rows you want to keep, swapping table names, then dropping the old table — faster than deleting the majority. (3) If indexes are the bottleneck, drop non-essential indexes before the bulk delete and recreate them after — index creation on a static table is faster than maintaining indexes during deletion. For truly massive tables, discuss with a DBA before attempting any approach.

FreshCart's data team needs to clean up test data that was inserted during a recent load test. All test data has customer emails ending in '@test.freshmart.com'. Write: (1) A SELECT to find all test customers and how many orders they have each. (2) The DELETE to remove test order_items first (for orders belonging to test customers). (3) The DELETE to remove test orders. (4) The DELETE to remove the test customers themselves. Show the correct order.