SQL DELETE — Remove Rows Safely

Table Hierarchy & Schema

Database → tables

CREATE TABLE (reference)json

1CREATE TABLE users (
2  user_id   INTEGER PRIMARY KEY,
3  email     VARCHAR(255) NOT NULL UNIQUE,
4  is_active BOOLEAN NOT NULL DEFAULT TRUE
5);
6
7CREATE TABLE orders (
8  order_id     INTEGER PRIMARY KEY,
9  customer_id  INTEGER NOT NULL,
10  status       VARCHAR(20) NOT NULL DEFAULT 'pending',
11  total_amount DECIMAL(10, 2) NOT NULL,
12  FOREIGN KEY (customer_id) REFERENCES users (user_id)
13);

Entity-Relationship Diagram

Delete parent only after children removed

PK/FK Delete Flow

FK blocks deleting user with orders

Sample Data

usersjson

1| user_id | email               | is_active |
2|---------|---------------------|-----------|
3| 1       | [email protected]   | true      |
4| 2       | [email protected]     | false     |
5| 3       | [email protected]   | true      |

ordersjson

1| order_id | customer_id | status   | total_amount |
2|----------|-------------|----------|--------------|
3| 1001     | 1           | paid     | 49.99        |
4| 1002     | 1           | shipped  | 12.50        |
5| 1003     | 3           | pending  | 8.00         |

Delete Specific Rows

Remove Carol's pending orderjson

1DELETE FROM orders
2WHERE order_id = 1003;

FK-Safe User Delete

Remove Alice and her orders (two steps)json

1DELETE FROM orders WHERE customer_id = 1;
2
3DELETE FROM users WHERE user_id = 1;

Bob is easy

Bob has no orders — you can DELETE FROM users WHERE user_id = 2 directly.

DELETE vs TRUNCATE

Statement	Scope	WHERE?	Typical use
DELETE FROM orders WHERE …	Matching rows only	Yes	Remove specific bad rows
DELETE FROM orders	All rows (slow, logged)	No (omit WHERE)	Empty table with triggers/rollback
TRUNCATE orders	All rows (fast reset)	No	Wipe staging table — use with extreme care

Danger: Missing WHERE

Deletes every orderjson

1DELETE FROM orders;
2-- All three sample orders gone

Preview with SELECT

SELECT * FROM orders WHERE order_id = 1003; before delete confirms the target row.

Constraints Reminder

Constraint	Role on DELETE
PRIMARY KEY (user_id / order_id)	Uniquely identifies each row; join anchor
FOREIGN KEY (customer_id)	Order must reference a valid users.user_id
NOT NULL	Required columns cannot be missing on INSERT/UPDATE
UNIQUE (email)	No duplicate emails in users
DEFAULT (status, is_active)	Value applied when column omitted on INSERT

Common Mistakes

Deleting parent before child → FK violation.
Omitting WHERE → accidental full-table wipe.
Using TRUNCATE on production without backup.

Practice Prompts

Write the two statements to delete Bob (no orders) safely.
Delete all pending orders without touching paid/shipped ones.
Explain why DELETE FROM users WHERE user_id = 1 fails before deleting orders.

Try These Tools

SQL Validator

Validate DELETE syntax

Open tool

SQL Compare

Diff scripts before/after delete

Open tool

Continue Learning

UPDATE — Changing Data

Modify before deciding to delete

INSERT INTO

Restore data after testing deletes

Frequently Asked Questions

What does DELETE do?

Removes rows from a table matching the WHERE clause. Without WHERE, all rows are deleted.

Why delete orders before users?

orders.customer_id references users.user_id. Deleting a user while orders still point to them violates the foreign key.

DELETE vs TRUNCATE?

DELETE removes rows one-by-one (can use WHERE, fires triggers). TRUNCATE clears the whole table quickly — usually no WHERE, not always rollback-friendly.

Can DELETE be undone?

Inside a transaction you can ROLLBACK. After COMMIT, recovery requires backups — treat DELETE as permanent.

What about ON DELETE CASCADE?

A FK option that auto-deletes child rows when parent is deleted. Our sample schema uses plain FK — manual order-first delete.