DELETE removes rows. It's the simplest operation in the module and the one that demands the most judgement, because it raises a question INSERT and UPDATE never raise: does it really have to be deleted?

GreenStore already gave you the answer without telling you. Its products and suppliers tables have an active column. That boolean exists because, in a real system, a product that has been sold is never deleted: it's withdrawn from the catalogue. Deleting it would destroy the billing history, and in fact the foreign key order_lines.product_id → products.id is declared ON DELETE RESTRICT precisely to prevent it. That whole discussion —soft delete against hard delete— has its lesson here.

Before that we'll get to the concrete stuff: the reinforced safety protocol, the difference between DELETE and TRUNCATE, and exactly what happens when you delete a row other rows depend on, demonstrated with counts before and after for the three ON DELETE actions GreenStore declares.

⚠️ Safety warning

DELETE is a destructive and irreversible operation once committed. Unlike UPDATE, which replaces one value with another, DELETE makes the entire row disappear — and, with ON DELETE CASCADE, rows in other tables you never mentioned.

• Run all the examples on your practice database (greenstore), never in production.
• Take a backup beforehand: pg_dump -U sql_course -d greenstore -f backup.sql. To get back to the initial state, just run greenstore.sql again.
• Always work inside BEGIN … ROLLBACK / COMMIT while you're experimenting.
• On a real system, a deletion over live data should be reviewed by another person, and personal-data deletion policies require legal review (section 10).

1. The syntax, and the reinforced protocol
2. DELETE with no WHERE against TRUNCATE
3. Deleting a referenced row: RESTRICT
4. Deleting a referenced row: CASCADE
5. Deleting a referenced row: SET NULL
6. The danger of CASCADE and the professional alternative
7. DELETE ... USING: deleting according to another table
8. RETURNING: keeping what you delete
9. Soft delete against hard delete
10. Personal data and the right to erasure
11. Recovery after an accidental deletion
12. Common Mistakes and Tips
13. Exercises
14. Conclusion

Only two pieces: which table and which rows. There's no SET, there are no values. All the responsibility falls on the WHERE.

Just like UPDATE N, the number tells you how many rows it removed. And here it matters even more, because there's no new value you can inspect afterwards: if the number isn't the one you expected, it's already too late.

05-03's five-step protocol applies just the same, with two reinforcements:

Step 3 is the new one, and it's what separates a safe DELETE from a catastrophe. Before deleting an order, look at how many lines and returns it has:

Now you know that DELETE 1 is going to remove four rows across three tables. Section 4 demonstrates it.

And the complete flow:

With no WHERE, DELETE empties the table:

All twelve reviews, gone. Exactly the same problem as the UPDATE with no WHERE, with the same absence of any warning.

To empty a table there's a specific statement, TRUNCATE:

They do the same thing and they're nothing alike:

That last row deserves a demonstration:

TRUNCATE doesn't run the ON DELETE actions: either you empty the whole tree at once, or you empty nothing.

And if you also want the sequences to start again at 1:

When to use each. TRUNCATE for emptying working tables, staging tables or test tables, where you want to start from scratch and speed matters. DELETE for everything else, and always when there's a WHERE involved. A DELETE FROM table; with no condition on a large table is the worst of both worlds: slow, with the WAL going wild and without freeing any space.

Here's where it gets interesting. When the row you delete is the "parent" of a foreign key, the engine applies the action declared in the ON DELETE. GreenStore declares all three.

RESTRICT is the barrier: it forbids deletion. order_lines.product_id carries it, among others.

Product 1 (Extra virgin olive oil) appears in 5 order lines and has 2 reviews:

Read it slowly, because it's the module's most frequent error:

Compare it with the INSERT error from 05-02: there it said is not present in table (you were referencing something that doesn't exist); here it says is still referenced from table (something depends on what you want to delete). The two halves of referential integrity.

Now a product that can be deleted. Number 19 (Natural stick deodorant) is one of the three that have never been sold, and it has no reviews:

Of the catalogue of 20, 19 are left. The RESTRICT constraint isn't an obstacle: it's a function. It's telling you "this product has a history, don't destroy it". And product 19 doesn't have one.

CASCADE propagates the deletion to the child rows. In GreenStore four foreign keys carry it: order_lines.order_id, returns.order_id, reviews.product_id and reviews.customer_id.

Order 6 is the perfect case: it's cancelled, it has 2 lines and 1 return.

And what hangs off order 6:

Now the deletion:

DELETE 1. One single row, says PostgreSQL. Let's look at the counts:

Four rows have disappeared across three tables, and the counter reported only one. There it is, in all its rawness, the danger of CASCADE: DELETE N counts the rows you deleted, not the ones the engine deleted down the chain.

And cascades can chain. If order_lines in turn had a child table with CASCADE, the deletion would keep going down. In a large schema, a single DELETE can propagate through half the database with nothing telling you.

The way to avoid surprises is to ask the system catalogue which foreign keys point at a table:

Quicker still, inside psql, the Referenced by section of \d orders:

Tip: run \d table before any DELETE on a table you don't know inside out. The Referenced by section tells you in five seconds whether you're about to trigger a cascade.

SET NULL doesn't delete the child: it takes the reference away. GreenStore declares it on orders.employee_id, customers.referred_by_id and employees.manager_id.

Sales rep Óscar Peris Blasco (employee 4) leaves the company. He has 4 orders assigned:

The 20 orders are still there. What has changed is that Óscar's four now have employee_id at NULL: we've gone from 10 orders with no sales rep to 14.

And there's a lesson from 04-03 here worth underlining. Before, employee_id IS NULL unambiguously meant "order that came in through the web". Now it means two different things: "web order" or "the sales rep who handled it is no longer at the company". The NULL has lost semantic precision without anyone deciding it should. It's a classic side effect of SET NULL, and the reason many teams prefer to keep the employee with a leaver flag instead of deleting them.

A more spectacular case: deleting Andrés Company Talens (employee 2), Sales manager, who handles no orders but has three subordinates.

Three employees have been left with no manager, and the org chart 01-06 drew so neatly now has four roots instead of one. The company hasn't reorganised: an intermediate node of the tree has simply disappeared and SET NULL has done the only thing it knows how to do.

CASCADE is convenient. One DELETE and the whole tree disappears cleanly, with no orphan rows. And that's exactly why it's dangerous:

That's why many teams adopt a more conservative policy: RESTRICT on every FK, and explicit deletion in the correct order, inside a transaction.

Compare the two outputs. With CASCADE: DELETE 1, and four rows gone. Explicitly: DELETE 1, DELETE 2, DELETE 1 — four rows, and you see all of them. You write three lines instead of one and you gain complete visibility over what you're destroying. In a system with real data, that trade is worth far more than it costs.

GreenStore uses CASCADE in four places and all four are genuine composition: an order line, a return and a review mean nothing without their parent. That's the correct use. 01-05's rule still stands: ask yourself whether the child row makes sense on its own, and if you're in doubt, RESTRICT.

Just as UPDATE has FROM, DELETE has USING: it lets you decide what to delete based on another table.

Cleaning up the catalogue: removing the lines of the cancelled orders.

USING's rules, all inherited from UPDATE ... FROM:

• You can put several tables, separated by commas or joined with JOIN.
• The target table isn't repeated in the USING. If you do it, silent Cartesian product.
• Unlike in UPDATE ... FROM, a 1:N matching isn't a problem here: a row that matches three others is deleted just once. DELETE is idempotent by nature.

Dialect note: USING is PostgreSQL's own. MySQL writes DELETE ol FROM order_lines ol JOIN orders o ON ... WHERE ... (notice the alias repeated after DELETE). SQL Server uses DELETE t FROM target t JOIN other o ON .... Oracle has neither and forces you into a correlated subquery. The portable form is always a subquery: DELETE FROM order_lines WHERE order_id IN (SELECT id FROM orders WHERE status = 'cancelled') — module 7.

In DELETE, RETURNING gives back the rows exactly as they were just before disappearing. It's the only way to see them without having queried them beforehand.

But seeing them isn't keeping them. For that, the professional pattern combines INSERT ... SELECT (05-02) with the DELETE, inside a transaction:

Inside a transaction, either all three things happen or none of them does: it's impossible for you to delete without having archived. Notice one detail of the archive table's DDL: it carries no foreign keys. If it did, you couldn't archive a return whose order is also about to be deleted. Archive tables are deliberately lax.

RETURNING completes the trilogy: INSERT ... RETURNING gives you the generated id, UPDATE ... RETURNING the new value, DELETE ... RETURNING the row that's leaving. In all three cases it avoids an additional SELECT and the race condition that comes with it.

And we get to the heart of the matter.

• Hard delete: DELETE FROM products WHERE id = 13. The row disappears.
• Soft delete: UPDATE products SET active = FALSE WHERE id = 13. The row is still there, flagged as no longer current.

products.active and suppliers.active are exactly that. They aren't a design whim: they're the decision, taken in 01-05 and now explained, that in GreenStore nothing that has taken part in a commercial operation is ever deleted.

EcoNordic Supplies is inactive and keeps its four products in the catalogue (10, 13, 18 and 20). With a hard delete, either the RESTRICT FK would have prevented deleting it, or you'd have had to delete four products that are still being sold.

This is the drawback everybody underestimates:

Five categories, not six. Supplements disappears from the report because its only product (number 20) is inactive. And that disappearance depends entirely on somebody remembering to write WHERE p.active. Forget it once in a management report and you'll be counting references that are no longer sold.

That forgetfulness has a solution, and it's a view:

From then on, catalogue queries go against current_products and the filter is impossible to forget. Views are the canonical solution to soft delete's main drawback, and they're studied in lesson 10-01.

And a third way, increasingly common: soft delete with a date. Instead of a boolean, a nullable removed_date DATE column. It takes up the same space, it distinguishes "current" from "withdrawn" just as well (removed_date IS NULL) and on top of that it tells you when it happened, which is usually exactly what somebody will ask six months later. If you're designing from scratch, prefer it to the boolean.

There's one case where a soft delete isn't enough: personal data.

The European General Data Protection Regulation recognises the right to erasure (article 17, the so-called "right to be forgotten"): a person can demand that their personal data be removed, and flagging a row as inactive isn't removing it. The name, the email and the city are still in the table, in the backups and in the indexes.

In GreenStore, the customers table contains personal data: first name, surname, email and city. And its design already anticipates part of the problem:

Núria Bosch Ferrer (customer 13) is one of the three customers with no orders. Deleting her is clean:

But with a customer who has actually bought something, the conflict appears immediately:

Ana Belmonte Roca has two orders (6 and 18), and orders.customer_id is RESTRICT. Here two legitimate obligations collide:

The usual solution in real systems is neither to delete nor not to delete: it's to anonymise. The row is kept —and with it the order, the invoice and the totals— but the identifying data is replaced:

Order 6 still exists, the accounts add up, and the personal data has gone. The email is built with the id so as not to break the UNIQUE constraint, and with an invalid domain so it's impossible to send mail to that address by accident.

The reviews, on the other hand, are deleted: reviews.customer_id is declared ON DELETE CASCADE for exactly this reason, as you reasoned in exercise 1 of 01-05. A review is a personal opinion; an invoice is an accounting document.

⚖️ Important warning

The above is a technical explanation of common patterns, not legal advice. Retention periods, what counts as personal data, what degree of anonymisation is sufficient and which exceptions apply all depend on the legislation in force, on the sector and on the specific case.

Before implementing any policy for deleting or anonymising personal data on a real system, check it with your data protection officer or with legal counsel. A badly conceived DELETE can breach the GDPR; a well-intentioned one can breach accounting regulations. Security, permissions and access control over this data are studied in lesson 11-03.

It's happened. You've committed a DELETE you shouldn't have. What are the options?

PITR (Point-In-Time Recovery) is the technique that lets you say "give me the database exactly as it was at 17:42:30 yesterday". It works because PostgreSQL writes every change to a sequential log —the WAL, Write-Ahead Log— before applying it to the data files. With a base backup and the subsequent WAL, any intermediate instant can be reconstructed. It's also the mechanism that makes durability and replication possible, and it's studied along with the rest of the transactional model in module 9.

The practical conclusion, which doesn't depend on any technology:

There's no recovery without a backup. The only question that matters isn't "what do I do if I delete something by mistake?", but "when was the last backup taken and when was restoring it last tested?". A backup that has never been restored isn't a backup: it's an assumption.

For the course, your safety net is far simpler: greenstore.sql is idempotent, and rerunning it takes you back to the initial state in two seconds.

• Forgetting the WHERE. It empties the whole table with no warning. The same emblematic mistake as in UPDATE, with worse consequences.
• Trusting the DELETE N with CASCADE. It counts the rows you delete, not the ones the engine deletes down the chain. DELETE 1 can be four rows, or four million.
• Not checking what depends on the row before deleting it. \d table and its Referenced by section, in five seconds.
• Using TRUNCATE believing it's a fast DELETE. It doesn't accept WHERE, it doesn't fire row triggers, it doesn't return a count, it needs a different permission and it fails if there are FKs pointing at the table.
• Counting on TRUNCATE being transactional. In PostgreSQL it is; in MySQL it isn't, and there no ROLLBACK is possible.
• Repeating the target table in the USING. Silent Cartesian product, just as in UPDATE ... FROM.
• Deleting records with a commercial history. That's what active = FALSE is for. The RESTRICT FK will try to stop you; don't dodge it with CASCADE.
• Implementing a soft delete and forgetting the WHERE active. The report will keep working and will count discontinued products. Use a view (module 10).
• Believing active = FALSE satisfies the right to erasure. It doesn't: the personal data is still there. Anonymise or delete, depending on the case and with legal review.
• Confusing "it has no children today" with "it can be deleted". Product 19 can be deleted today; tomorrow, the moment somebody buys it, it can't.
• Tip: SELECT * before any DELETE. You want to see the complete row that's about to disappear, not just its id.
• Tip: always RETURNING * in a DELETE. It costs nothing and it leaves you a record of what you deleted, even if only in the console's history.
• Tip: prefer RESTRICT + explicit deletion to CASCADE. Three lines instead of one, in exchange for seeing exactly what you're destroying.
• Tip: when designing, use removed_date DATE instead of active BOOLEAN. It costs the same and it also tells you when.

Work on the freshly reloaded database and use BEGIN … ROLLBACK in every exercise.

For each of these five deletions, predict the result before running it: whether it works or fails, how many rows are removed in total and in which tables, and which ON DELETE action is involved. Then check it with counts before and after.

Management asks to withdraw every product of the inactive supplier from the catalogue (EcoNordic Supplies, id 5).

1. Check which they are and which of them have ever been sold.
2. Attempt a hard delete of all of them and explain what happens.
3. Propose and apply the correct solution, justifying why it's the correct one.
4. Write the current-catalogue query the website should use from now on, grouped by supplier.

A colleague wants to clean up the reviews of discontinued products and shows you this statement:

1. Find the error and explain what it would really do.
2. Write it correctly and say how many rows it would delete on the freshly reloaded database.
3. Rewrite it in a portable way, with no USING, saying which module covers that technique.
4. Discuss whether this cleanup is a good idea: what's lost and what's gained?

Solution 1

a) It fails. products.supplier_id is ON DELETE RESTRICT and EcoNordic has four products:

The supplier being active = FALSE changes nothing: soft delete and referential integrity are independent mechanisms.

b) It works, and it deletes 4 rows across 3 tables. Order 10 has 2 lines (ids 24 and 25) and 1 return (number 2, of €34.02), both with CASCADE:

And something that doesn't show up in any count: GreenStore's total revenue has just dropped from €727.95 to €679.68, because order 10 contributed €48.27. The shipping costs drop from €118.25 to €105.75. No message told you.

c) It fails, and it's the trick exercise. Category 6 (Supplements) looks empty because its only product, number 20, is discontinued. But products.category_id is RESTRICT and that row still exists:

Product 20 is active = FALSE, but it's still a row of products and the foreign key doesn't distinguish between active and inactive. A soft delete doesn't release referential constraints.

d) It works, 1 row. Hugo Iglesias Pardo (customer 14) is one of the three with no orders, has no reviews and has referred nobody:

customers: 15 → 14. No cascade, no SET NULL. It's the only one of the five deletions that's genuinely harmless.

e) It works, and it dismantles the org chart. Rosa Alcázar Vives (employee 1) handles no orders, but she's the manager of three people (2, 3 and 8), and employees.manager_id is SET NULL:

We go from 1 employee with no manager to 3. The hierarchy has been split into three subtrees and the company has been left with no general management in the data model. SET NULL doesn't complain: it does what it was told.

Summary:

Solution 2

Two of the four have been sold (10 and 18). The LEFT JOIN with COUNT(ol.id) is 04-05's exact pattern: if we'd used an INNER JOIN, precisely the two we care about would have disappeared.

It fails, and it fails entirely. The two that could have been deleted aren't deleted either: a DELETE is atomic, just like an INSERT or an UPDATE. The RESTRICT FK protects the billing history: without it, lines 11 and 39 (the detergent) and 23, 32 and 47 (the toothbrush) would have been left pointing at nothing, and the €727.95 of revenue could no longer be reconstructed.

Three updated, not four: product 20 was already inactive, and the AND active filter excludes it. That makes the statement idempotent (05-03): rerunning it would return UPDATE 0.

Why this is the correct solution, in three points: it keeps the billing history intact; it maintains referential integrity with no need to decide on cascades; and it's reversible with a SET active = TRUE if EcoNordic starts supplying again.

Four suppliers and 16 products, against the table's five suppliers and 20 products. Notice the two active filters: the product's and the supplier's. Forgetting either one would return a catalogue with references that can't be fulfilled. It's exactly the cost of a soft delete, and exactly what a view would solve (module 10).

Solution 3

1. The error. reviews appears twice: as the DELETE's target table and inside the USING. It's the same flaw as the UPDATE ... FROM in exercise 2 of 05-03. PostgreSQL treats the one in the USING as an independent instance, so the condition reviews.product_id = products.id is resolved against it and the target table is left uncorrelated. The result: if there's at least one review of an inactive product, every review in the table is deleted. With GreenStore's data there isn't one, so it would delete 0 — but that's pure luck, and the moment there was one, it would take all twelve.

2. The correct version:

Zero rows. The only inactive product is number 20 (Spirulina capsules), and it has no reviews at all — which is consistent with it never having been sold either. The check:

3. The portable version, with no USING:

It works the same way in PostgreSQL, MySQL, SQLite, SQL Server and Oracle. Subqueries are module 7, and this is exactly why they're studied: USING and FROM are proprietary extensions, the subquery is standard. Watch out for one detail inherited from 04-02: if the subquery could return a NULL, a NOT IN would silently give zero rows. With IN there's no problem, but it's worth keeping in mind.

4. Is it a good idea?

The verdict is no, it's almost never a good idea. Soft delete exists to preserve information, and hard-deleting the reviews of a withdrawn product destroys precisely what makes the withdrawal useful: knowing that this product averaged two stars and that's why it was withdrawn.

The correct alternative is not to delete anything and to filter on reading: the website shows the reviews of current products, and internal reports see them all. A WHERE p.active in the public query solves the problem without destroying a single piece of data. It's the same design decision as the whole of section 9, applied one level down.

And a legitimate exception: if those reviews contained personal data of somebody who has exercised their right to erasure, then they would have to be deleted — but the criterion would then be the customer, not the product, and reviews.customer_id is already declared ON DELETE CASCADE for exactly that.

DELETE closes the DML trilogy and raises the question the other two don't:

• The syntax DELETE FROM table WHERE ..., with all the weight on the WHERE, and the five-step protocol reinforced with a new one: count the dependent rows before deleting.
• DELETE against TRUNCATE: DML against DDL, WHERE against all-or-nothing, slow against instantaneous, with row triggers against without them, and —critically— transactional in PostgreSQL but not in MySQL. TRUNCATE doesn't run the ON DELETE actions: it fails if there are FKs pointing at the table.
• The three ON DELETE actions demonstrated with counts: RESTRICT prevents deleting product 1 with its is still referenced from table; CASCADE turns DELETE FROM orders WHERE id = 6 into four rows across three tables while reporting only one; SET NULL leaves Óscar's four orders with no sales rep (10 → 14 nulls) and three employees with no manager.
• The danger of CASCADE: invisible reach, chained propagation, declared far from whoever runs it. And the professional alternative, RESTRICT + explicit deletion, which costs three lines and shows you exactly what you're destroying.
• DELETE ... USING to delete according to another table, with the same rules as UPDATE ... FROM — except that here a 1:N matching isn't a problem.
• RETURNING to see the row before it disappears, and the INSERT ... SELECT + DELETE pattern inside a transaction to archive it properly.
• Soft delete against hard delete: products.active and suppliers.active are a soft delete. It preserves history, integrity and reversibility, in exchange for every query needing WHERE active — a problem module 10's views solve. And the third way, removed_date DATE, which also tells you when.
• Personal data: active = FALSE does not satisfy the right to erasure, anonymisation is the usual pattern when it collides with accounting obligations, and any personal-data deletion policy requires legal review.
• Recovery: ROLLBACK if you haven't committed, a backup or PITR if you have — and neither of the two exists if nobody prepared it beforehand.

You now know how to insert, modify and delete separately. What's missing is the operation real systems constantly need and none of the three solves: "insert this row if it doesn't exist, and update it if it does". Synchronising a catalogue with a supplier's file, recording the stock received for an item that may not even be in the system yet, saving the rating of a review the customer may have written before. In the next lesson, The UPSERT (MERGE) Statement, you'll see why the obvious solution —query and then decide— is wrong the moment there are two users at once, and the two ways PostgreSQL offers to solve it in a single atomic statement: INSERT ... ON CONFLICT and the standard's MERGE.