LinkedIn Hook

"One missing WHERE clause, and every row in the production users table was gone in 40 milliseconds."

DELETE is the most dangerous statement in SQL. Every other CRUD verb has a natural safety net -- SELECT does not mutate, INSERT creates something new, UPDATE can be re-run with the correct value. DELETE is different. DELETE removes rows, and unless you are inside a transaction, there is no undo button. The only recovery path is a backup, a point-in-time restore, or a very awkward Slack message to the on-call engineer at 2 AM.

Most engineers learn DELETE the day they forget a WHERE clause in a production shell. The tooling tries to help -- psql will ask "really?" for destructive statements, transactional DDL lets you wrap everything in BEGIN / ROLLBACK, and DELETE...RETURNING lets you see exactly what left the building. But none of that matters if you do not understand the three removal verbs -- DELETE, TRUNCATE, DROP -- and when cascades silently delete half your database through a foreign key chain.

The deeper pattern is the soft delete. In most production systems, you do not actually DELETE user data at all. You set a deleted_at timestamp, filter it out of every query, and keep the row around for auditing, undo, and analytics. Then GDPR shows up and demands a real hard delete for right-to-erasure requests, and suddenly your soft-delete architecture needs a scheduled purge job.

In Lesson 3.4, I walk through DELETE with WHERE, the missing-WHERE catastrophe, DELETE...RETURNING, the DELETE vs TRUNCATE vs DROP comparison every interview asks about, cascading foreign keys, the soft-delete pattern, and the GDPR escape hatch.

Read the full lesson -> [link]

#SQL #Database #PostgreSQL #BackendDevelopment #DataEngineering #InterviewPrep

DELETE Data thumbnail

What You'll Learn

How DELETE ... WHERE removes specific rows and why the WHERE clause is non-negotiable
The missing-WHERE catastrophe and the transactional safeguards that prevent it
Using DELETE ... RETURNING to audit exactly which rows were removed
The full comparison of DELETE, TRUNCATE, and DROP -- speed, logging, triggers, rollback, FK, sequences, permissions
Cascading deletes via ON DELETE CASCADE and how a single DELETE can walk the entire foreign key graph
The soft delete pattern with deleted_at TIMESTAMPTZ and when it is the right choice
View and partial index strategies to keep soft-deleted rows from polluting queries
The GDPR right-to-erasure requirement and why soft delete alone is not enough

The Shredder vs the Trash Can -- An Analogy for Deletion

Imagine you are cleaning out an old filing cabinet in a legal office. There are two very different ways to get rid of a document, and choosing the wrong one has real consequences.

The trash can is reversible. You drop a file in, and until the cleaners come at the end of the day, you can still fish it back out. If you realize you threw away the wrong folder at 4:45 PM, you walk back to the bin, retrieve it, and nobody knows. The downside is that the file is still physically in the office -- anyone with access to the trash can read it, auditors can find it, and the cleaning crew is not always reliable.

The shredder is permanent. You feed the document in, it becomes confetti, and there is no getting it back. Ever. The upside is certainty -- you know with absolute confidence that the information is gone and cannot be reconstructed. The downside is that a mistake is unrecoverable. Shred the wrong contract, and you are having a very unpleasant conversation with your boss and possibly a client.

Most real offices use both. Routine disposal goes in the trash -- a soft delete, reversible, easy to audit. Sensitive material gets shredded on schedule -- a hard delete, permanent, compliance-driven. And there is a formal process for moving things from the trash to the shredder once enough time has passed and nobody needs them anymore.

Database deletion works exactly the same way. DELETE is the shredder -- the row is gone, unless you wrapped it in a transaction you have not committed yet. The soft delete pattern with deleted_at is the trash can -- the row is still there, just marked as hidden, and can be restored with a single UPDATE. And a nightly purge job is the shredding truck that comes to empty the bin for rows that have been soft-deleted long enough that GDPR, compliance, or storage costs demand they actually go away.

+---------------------------------------------------------------+
|           TWO WAYS TO DELETE DATA                             |
+---------------------------------------------------------------+
|                                                                |
|  HARD DELETE (The Shredder)                                   |
|    DELETE FROM users WHERE id = 42;                           |
|    -> Row is physically removed                               |
|    -> Foreign keys may cascade                                |
|    -> Only recoverable from a backup                          |
|    -> Required for GDPR right-to-erasure                      |
|                                                                |
|  SOFT DELETE (The Trash Can)                                  |
|    UPDATE users SET deleted_at = now() WHERE id = 42;         |
|    -> Row still exists, just flagged                          |
|    -> Every query must filter WHERE deleted_at IS NULL        |
|    -> Restorable with a single UPDATE                         |
|    -> Keeps audit trail and referential integrity             |
|                                                                |
+---------------------------------------------------------------+

Napkin AI Visual Prompt: "Dark gradient (#0a0f1f -> #111a2e). Split comparison: LEFT side labeled 'Hard Delete' shows a rose (#ff5c8a) shredder consuming a document table row with paper fragments falling. RIGHT side labeled 'Soft Delete' shows a sky blue (#4fc3f7) trash can holding a row tagged with deleted_at: 2026-04-13, with a small undo arrow indicating restore. White monospace labels. Sky blue arrow at the bottom showing the lifecycle: active -> soft-deleted -> purged."

DELETE Basics -- Removing Rows by Predicate

The DELETE statement removes rows from a table that match a predicate. Conceptually it is the inverse of INSERT -- INSERT produces rows, DELETE destroys them. Unlike UPDATE, DELETE has no SET clause because there is nothing to assign; the whole row leaves.

-- Setup: a simple orders table we can delete from
CREATE TABLE orders (
    id          SERIAL PRIMARY KEY,
    customer_id INT         NOT NULL,
    status      TEXT        NOT NULL,
    total_cents INT         NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

INSERT INTO orders (customer_id, status, total_cents) VALUES
    (1, 'pending',   1200),
    (1, 'cancelled',  800),
    (2, 'shipped',   4500),
    (3, 'cancelled', 2200),
    (3, 'pending',    600);
-- 5 rows inserted.

-- Remove all cancelled orders
DELETE FROM orders
WHERE status = 'cancelled';
-- DELETE 2

-- Verify what is left
SELECT id, customer_id, status FROM orders ORDER BY id;
--  id | customer_id |  status
-- ----+-------------+---------
--   1 |           1 | pending
--   3 |           2 | shipped
--   5 |           3 | pending

The WHERE clause is not optional in spirit, even though it is optional in syntax. Every DELETE you write in real code should have one, and you should read it twice before pressing enter.

The Missing-WHERE Catastrophe

This is the single most famous SQL accident. Forget the WHERE clause, and DELETE removes every row in the table -- not some, not most, all of them.

-- DO NOT RUN THIS IN PRODUCTION
DELETE FROM orders;
-- DELETE 3
-- Every remaining row is gone. The table still exists, but it is empty.

SELECT count(*) FROM orders;
--  count
-- -------
--      0

+---------------------------------------------------------------+
|           WHAT A MISSING WHERE LOOKS LIKE                     |
+---------------------------------------------------------------+
|                                                                |
|   DELETE FROM users;                                          |
|              ^^^^^^                                            |
|              |                                                 |
|              no WHERE -> every row matches -> entire table     |
|                                                                |
|   Before:  users  = 2,400,000 rows                            |
|   After:   users  =         0 rows                            |
|   Recovery path: restore from backup, replay WAL              |
|   Time to panic: approximately 5 seconds                      |
|                                                                |
+---------------------------------------------------------------+

The two defenses every engineer should build into muscle memory:

1. Wrap destructive statements in a transaction. Start with BEGIN, run the DELETE, check the row count and a SELECT, and only then COMMIT. If anything looks wrong, ROLLBACK and nothing happened. This works in PostgreSQL, SQL Server, and Oracle. MySQL with InnoDB supports it too, as long as you are not inside autocommit mode.

BEGIN;

DELETE FROM orders WHERE status = 'cancelled';
-- DELETE 2

-- Sanity check before committing
SELECT count(*) FROM orders;
--  count
-- -------
--      3

-- Looks right -> commit. Looks wrong -> ROLLBACK.
COMMIT;

2. Write the SELECT first. Before running DELETE FROM X WHERE Y, run SELECT * FROM X WHERE Y with the exact same predicate. Count the rows. If the count matches your expectation, swap SELECT for DELETE. This habit alone catches 90% of predicate typos.

DELETE...RETURNING -- Audit What You Removed

PostgreSQL, Oracle, and SQL Server (via OUTPUT) support returning the deleted rows in the same statement. This is invaluable for audit logs, undo buffers, and any workflow where "what did I just delete" matters.

-- Delete and capture the removed rows in one statement
DELETE FROM orders
WHERE status = 'cancelled'
RETURNING id, customer_id, total_cents;
--  id | customer_id | total_cents
-- ----+-------------+-------------
--   2 |           1 |         800
--   4 |           3 |        2200
-- DELETE 2

Common uses of RETURNING on a DELETE:

Audit logging. Pipe the deleted rows into an audit_log table in the same transaction. If the DELETE rolls back, so does the audit insert, and the two stay consistent.
Confirmation for the user. An API endpoint can return "we deleted these 3 orders" with the actual IDs, instead of a generic success message.
Move-to-archive. Combined with a CTE, you can atomically delete from one table and insert into another -- a poor-man's move operation.

-- Atomically move cancelled orders into an archive table
WITH removed AS (
    DELETE FROM orders
    WHERE status = 'cancelled'
    RETURNING *
)
INSERT INTO orders_archive
SELECT *, now() AS archived_at FROM removed;
-- INSERT 0 2

DELETE vs TRUNCATE vs DROP -- The Comparison

SQL has three verbs for "make data go away," and they do very different things. This is one of the most reliable interview questions in the CRUD section.

+---------------------------------------------------------------+
|           THREE WAYS TO REMOVE DATA                           |
+---------------------------------------------------------------+
|                                                                |
|  DELETE  -> remove some or all ROWS, table stays              |
|  TRUNCATE -> remove ALL rows fast, table stays                |
|  DROP    -> remove the TABLE ITSELF, schema and all           |
|                                                                |
+---------------------------------------------------------------+

Concern	DELETE	TRUNCATE	DROP
What it removes	Rows matching WHERE	All rows in the table	The entire table
DML or DDL	DML	DDL (in most engines)	DDL
Speed	Slow -- row by row	Very fast -- page deallocation	Very fast -- drops metadata
WAL / log volume	Per-row entries, heavy	Minimal, metadata only	Minimal, metadata only
Triggers fire	Yes -- row level BEFORE/AFTER	No in MySQL, yes in Postgres*	No triggers fire
Rollback support	Yes, inside a transaction	Yes in Postgres/SQL Server, NO in MySQL/Oracle	Yes in Postgres, NO in MySQL
Foreign keys	Respects FK + CASCADE	Blocked if referenced, unless CASCADE	Blocked if table is referenced
Sequence / identity	Keeps current value	Resets to start (with RESTART)	Destroys the sequence
Permission required	DELETE on the table	TRUNCATE (or DROP on some engines)	DROP / owner
Use case	Delete a subset of rows	Wipe a staging / test table	Remove a table permanently

* PostgreSQL fires statement-level triggers on TRUNCATE but not row-level triggers.

A few subtleties worth internalizing:

MySQL TRUNCATE does not roll back. In MySQL, TRUNCATE is implemented as DROP + CREATE under the hood for InnoDB, which means it is an implicit commit. Wrapping it in START TRANSACTION ... ROLLBACK will not save you. PostgreSQL, by contrast, is fully transactional -- you can TRUNCATE inside a transaction and ROLLBACK.
TRUNCATE bypasses row-level triggers. If you depend on an AFTER DELETE trigger to write audit rows, TRUNCATE silently skips it. Use DELETE when triggers matter.
Sequences. TRUNCATE ... RESTART IDENTITY resets the auto-increment counter to 1. Plain DELETE leaves the sequence at its current value -- the next insert gets the next number, even if the table is empty.

Cascading Deletes -- When One DELETE Walks the Graph

Foreign keys were introduced in Chapter 2. The piece that matters for deletion is the ON DELETE action, which tells the database what to do when the parent row a child points to goes away.

-- Parent: customers. Child: orders with ON DELETE CASCADE.
CREATE TABLE customers (
    id    SERIAL PRIMARY KEY,
    email TEXT UNIQUE NOT NULL
);

CREATE TABLE orders (
    id          SERIAL PRIMARY KEY,
    customer_id INT NOT NULL REFERENCES customers(id)
                ON DELETE CASCADE,
    total_cents INT NOT NULL
);

INSERT INTO customers (email) VALUES ('a@x.com'), ('b@x.com');
INSERT INTO orders (customer_id, total_cents) VALUES
    (1, 100), (1, 200), (2, 300);

-- Deleting the parent also deletes every child row that points to it
DELETE FROM customers WHERE id = 1;
-- DELETE 1   (but 2 orders went with it)

SELECT id, customer_id FROM orders;
--  id | customer_id
-- ----+-------------
--   3 |           2

The five ON DELETE actions every engineer should know, in order of aggressiveness:

NO ACTION / RESTRICT -- the DELETE fails if any child row still points to the parent. Safest default.
CASCADE -- children are deleted too. Powerful but dangerous when graphs are deep.
SET NULL -- the child's FK column is set to NULL. The column must be nullable.
SET DEFAULT -- the child's FK column is set to its DEFAULT value. The default must still be a valid parent.

+---------------------------------------------------------------+
|           CASCADE WALKS THE GRAPH                             |
+---------------------------------------------------------------+
|                                                                |
|   users  ---(CASCADE)-->  posts  ---(CASCADE)-->  comments    |
|                              |                                 |
|                              +---(CASCADE)-->  post_tags       |
|                                                                |
|   DELETE FROM users WHERE id = 7;                              |
|     -> deletes 1 user                                          |
|     -> deletes 214 posts                                       |
|     -> deletes 18,400 comments                                 |
|     -> deletes 642 post_tags                                   |
|   Total affected rows: 19,257 from a single DELETE.           |
|                                                                |
+---------------------------------------------------------------+

Cascading deletes are a correctness feature and a foot-gun in equal measure. They guarantee referential integrity -- you cannot end up with orphan rows pointing at a deleted parent. But they also mean a single DELETE can silently remove thousands of rows across many tables, and a mistaken cascade path can take out data you never meant to touch. The discipline is to plan cascade paths at schema-design time, not when you are three SELECTs deep at 2 AM. Reference Chapter 2 for the full foreign-key design discussion.

The Soft Delete Pattern -- deleted_at TIMESTAMPTZ

In most production systems you do not actually want to destroy user data the first time a user clicks "delete." You want to hide it, keep it auditable, allow a support agent to restore it, and purge it later on a schedule. The standard implementation is a nullable deleted_at column.

-- Add a deleted_at column -- NULL means "active", a timestamp means "soft-deleted"
ALTER TABLE users
ADD COLUMN deleted_at TIMESTAMPTZ;

-- Soft delete: an UPDATE, not a DELETE
UPDATE users
SET deleted_at = now()
WHERE id = 42;
-- UPDATE 1

-- Restore: null out the timestamp
UPDATE users
SET deleted_at = NULL
WHERE id = 42;
-- UPDATE 1

Every query in the application now needs to filter out soft-deleted rows. This is the biggest pitfall of the pattern -- forget the filter once, and you leak deleted data back into the UI.

-- Every read query must filter
SELECT id, email
FROM users
WHERE deleted_at IS NULL
  AND id = 42;

Two strategies keep the filter consistent across a large codebase:

1. A view that hides soft-deleted rows. Create a users_active view that applies the filter once, and have the application read from the view instead of the base table. Direct access to the base table is reserved for admin tooling and the purge job.

CREATE VIEW users_active AS
SELECT * FROM users
WHERE deleted_at IS NULL;

-- Application queries use the view
SELECT * FROM users_active WHERE id = 42;

2. A partial index. Indexes on soft-delete tables waste space on rows nobody queries. A partial index only covers active rows, keeping the index small and the read path fast.

-- Only index active rows -- much smaller, much faster
CREATE INDEX users_active_email_idx
    ON users (email)
    WHERE deleted_at IS NULL;

-- A unique constraint that only applies to active rows
CREATE UNIQUE INDEX users_active_email_uniq
    ON users (email)
    WHERE deleted_at IS NULL;
-- This lets a deleted user's email be reused by a new account.

+---------------------------------------------------------------+
|           SOFT DELETE LIFECYCLE                               |
+---------------------------------------------------------------+
|                                                                |
|   [active]                                                     |
|       |                                                        |
|       |  UPDATE SET deleted_at = now()                         |
|       v                                                        |
|   [soft-deleted]  <--- filtered out of all queries             |
|       |                                                        |
|       |  nightly purge job after N days                        |
|       v                                                        |
|   [hard-deleted]  <--- row is physically gone                  |
|                                                                |
+---------------------------------------------------------------+

Soft delete is a great pattern for user experience and operational safety, but it is not a legal strategy. Under GDPR Article 17 (and equivalent laws like CCPA, LGPD, and PIPEDA), a user has the right to request erasure of their personal data, and the controller must comply within 30 days. A deleted_at timestamp does not count -- the data still exists, and a breach would still expose it.

The production pattern is layered: soft delete first for safety and undo, then a scheduled purge job that hard-deletes rows where deleted_at < now() - INTERVAL '30 days' (or whatever your retention policy says). For erasure requests specifically, you run an immediate hard delete or anonymization instead of waiting for the scheduled purge. Design the schema so that personal fields can be anonymized in place (email = NULL, name = 'deleted user') while keeping foreign-key relationships intact for analytics and financial records you are legally required to retain.

Common Mistakes

1. DELETE without a WHERE clause. The original sin. DELETE FROM users; removes every row in the table, no prompt, no undo. Defenses: wrap destructive statements in BEGIN ... COMMIT so you can ROLLBACK, run the predicate as a SELECT first and count the rows, use psql \set to enable safe-update mode, and never paste DELETE statements into production shells without double-checking. Some teams ban raw shell access to production databases entirely for exactly this reason.

2. TRUNCATE inside a transaction, expecting rollback. In PostgreSQL this works -- TRUNCATE is transactional and ROLLBACK restores the rows. In MySQL InnoDB, TRUNCATE is an implicit commit; START TRANSACTION; TRUNCATE t; ROLLBACK; does not roll back, because the TRUNCATE already committed. If you need transactional wiping on MySQL, use DELETE FROM t; and accept the WAL volume. Know your engine before you assume rollback semantics.

3. Forgetting the WHERE deleted_at IS NULL filter on soft-delete tables. A single query without the filter can leak deleted users, deleted posts, and deleted messages into the UI -- exactly the data you promised the user was gone. The fix is to enforce the filter centrally: use a view (users_active), a query builder scope, an ORM default scope, or a row-level security policy. Never rely on every developer remembering to add the filter to every query.

4. No FK cascade planning. Two failure modes here. First, you forget to set ON DELETE CASCADE, and the DELETE fails with a FK violation every time, forcing you to manually delete children first. Second, you set CASCADE too aggressively, and a user deletion quietly takes out 200,000 rows across 15 tables you forgot were linked. The fix is to draw the FK graph at design time, mark each edge with the intended ON DELETE action explicitly, and review cascade paths in code review.

5. Forgetting to reset sequences after a full wipe. DELETE FROM orders; empties the table but leaves the sequence at its current value. The next insert gets id = 9,401 even though the table is empty. If you are resetting a test or staging table and want ids to restart at 1, use TRUNCATE orders RESTART IDENTITY (PostgreSQL) or ALTER TABLE orders AUTO_INCREMENT = 1 (MySQL). For production tables with foreign keys, never reset sequences -- doing so can cause the next insert to collide with an existing id or with a referenced child row.

Interview Questions

1. "What is the difference between DELETE, TRUNCATE, and DROP, and when would you use each?"

DELETE is a DML statement that removes rows matching a WHERE clause. It is row-by-row, fires triggers, writes per-row entries to the WAL, is fully transactional in every major engine, respects foreign keys, leaves auto-increment sequences untouched, and can be rolled back inside a transaction. Use it when you need to remove a subset of rows -- it is the only option that takes a predicate. TRUNCATE is a DDL statement that wipes every row in a table by deallocating pages rather than deleting rows individually. It is orders of magnitude faster than DELETE on large tables, writes minimal WAL, skips row-level triggers (in MySQL), and can reset the identity sequence with RESTART IDENTITY. It is transactional in PostgreSQL and SQL Server, but in MySQL InnoDB it implicitly commits and cannot be rolled back. Use it to wipe a staging, test, or cache table quickly. DROP removes the table itself -- the rows, the schema, the indexes, the constraints, and the sequence. It is DDL, very fast, transactional in PostgreSQL, and fails if other tables have foreign keys referencing the dropped table unless you use CASCADE. Use it when you truly want the table gone. The rule of thumb: DELETE for partial, TRUNCATE for full wipe, DROP for schema removal.

2. "What is a soft delete, and when would you choose it over a hard delete?"

A soft delete marks a row as deleted by setting a timestamp column (typically deleted_at) instead of physically removing the row. The row stays in the table, and every query must filter WHERE deleted_at IS NULL to exclude soft-deleted rows. You use soft delete when you want to preserve audit history, support undo operations, keep referential integrity (child rows still point to the soft-deleted parent and stay valid), enable admin-side restore without pulling from backups, and run analytics on historical activity including deleted accounts. The trade-offs are that every query needs the filter (forget it once and you leak deleted data), indexes get larger unless you use partial indexes, unique constraints behave oddly unless you scope them to active rows, and the rows still take up storage and still contain personal data. You choose hard delete when the data is transient and has no audit value (expired sessions, cache rows), when storage costs dominate, or when compliance requires actual erasure. In practice, mature systems use both -- soft delete as the default for safety and UX, then a scheduled purge job that hard-deletes rows after a retention period, plus an immediate hard-delete or anonymization path for GDPR right-to-erasure requests.

3. "What are the risks of cascading deletes, and how do you mitigate them?"

Cascading deletes via ON DELETE CASCADE are a correctness feature -- they guarantee you never end up with orphan child rows pointing to a deleted parent. The risk is that a single DELETE can silently walk the entire foreign key graph and remove thousands or millions of rows across many tables. A user deletion might cascade into posts, then comments, then likes, then notifications, then attachments -- and if you did not plan the cascade paths carefully, you lose data you never meant to delete. The second risk is performance: a cascade DELETE holds locks on every affected row across every affected table, which can block other transactions and blow up long-running migrations. Mitigations: draw the foreign key graph explicitly at schema-design time and mark each edge with its intended ON DELETE action; prefer NO ACTION / RESTRICT as the default and opt in to CASCADE only where it is clearly correct; use SET NULL when the child should survive its parent; run destructive DELETEs inside an explicit transaction on a non-production replica first and check affected row counts; for very large cascades, delete in batches or during maintenance windows; and layer a soft-delete pattern on top so that cascading a soft delete just sets deleted_at on children without physically removing anything.

Quick Reference -- DELETE Cheat Sheet

+---------------------------------------------------------------+
|           DELETE CHEAT SHEET                                  |
+---------------------------------------------------------------+
|                                                                |
|  BASIC DELETE:                                                 |
|    DELETE FROM t WHERE col = value;                           |
|                                                                |
|  DELETE WITH RETURNING (Postgres):                             |
|    DELETE FROM t WHERE col = value RETURNING *;               |
|                                                                |
|  SAFE DESTRUCTIVE PATTERN:                                     |
|    BEGIN;                                                      |
|    DELETE FROM t WHERE col = value;                           |
|    SELECT count(*) FROM t;  -- sanity check                   |
|    COMMIT;   -- or ROLLBACK                                    |
|                                                                |
|  FULL WIPE (fast):                                             |
|    TRUNCATE t RESTART IDENTITY;                               |
|                                                                |
|  SOFT DELETE:                                                  |
|    UPDATE t SET deleted_at = now() WHERE id = ?;              |
|    SELECT ... WHERE deleted_at IS NULL;                        |
|                                                                |
|  PARTIAL INDEX FOR SOFT DELETE:                                |
|    CREATE INDEX t_active_idx ON t (col)                       |
|        WHERE deleted_at IS NULL;                               |
|                                                                |
+---------------------------------------------------------------+

+---------------------------------------------------------------+
|           KEY RULES                                            |
+---------------------------------------------------------------+
|                                                                |
|  1. Never write DELETE without WHERE                           |
|  2. Wrap destructive statements in a transaction               |
|  3. Run the SELECT first, then swap it for DELETE              |
|  4. Use DELETE...RETURNING for audit and confirmation          |
|  5. TRUNCATE is fast but not always transactional (MySQL)      |
|  6. Plan ON DELETE CASCADE paths at schema design time         |
|  7. Soft delete for UX, hard delete for GDPR                   |
|  8. Every query on a soft-delete table needs the filter        |
|                                                                |
+---------------------------------------------------------------+

Concern	DELETE	TRUNCATE	DROP
Removes	Rows matching WHERE	All rows	The whole table
Speed	Slow	Very fast	Very fast
Logging	Per-row WAL	Minimal	Minimal
Triggers	Yes, row-level	Statement only (Postgres)	No
Rollback	Yes	Postgres yes, MySQL no	Postgres yes, MySQL no
FK behavior	Respects + CASCADE	Blocked unless CASCADE	Blocked unless CASCADE
Sequence reset	No	Yes (RESTART IDENTITY)	Destroyed
Permission	DELETE	TRUNCATE / DROP	DROP / owner
Typical use	Partial removal	Full wipe of staging table	Schema removal

Prev: Lesson 3.3 -- Update Data Next: Lesson 4.1 -- WHERE Clause

This is Lesson 3.4 of the Database Interview Prep Course -- 12 chapters, 58 lessons.

DELETE Data