MySQL / Relational foundations and precise SQL / Lesson 5

Safe INSERT, UPDATE, and DELETE

Treat data changes as explicit, reviewable transitions and keep the shared fixture unchanged.

About 4 min readContent reviewed 2026-10-23

On this page

Lesson goals

Write scoped changes and inspect affected rows.
Distinguish transaction rollback, constraints, and application validation.

Define the intended transition

Use the unchanged shop fixture. A data-changing statement should identify the business object, its expected current state, and its intended new state. INSERT adds facts; UPDATE changes existing facts; DELETE removes them. Before changing rows, preview the predicate with SELECT. A preview is useful reasoning but is not a concurrency guarantee: another session may change the rows before your write.

Name every inserted column

An explicit column list makes an insert understandable and resilient to column-order changes. Supply legitimate referenced ids, not guessed display names. Multirow inserts reduce round trips but still require deciding whether all rows should succeed together. Database constraints remain necessary even after form validation, because imports, background workers, and concurrent requests can bypass a single form.

sql Practice an insertion without retaining it

START TRANSACTION;
INSERT INTO customers (id,name,email,city)
VALUES (4,'Sami','sami@example.test',NULL);
SELECT id,name FROM customers WHERE id=4;
ROLLBACK;

Expected output

id | name
4 | Sami

Inside this transaction the new row reads as 4/Sami. ROLLBACK removes it, returning the fixture to three customers. This demonstration assumes InnoDB and no intervening implicit-commit statement. It does not imply that every kind of server operation can be undone.

Make the predicate part of the invariant

Decreasing stock safely requires both product identity and sufficient stock in the same UPDATE. Reading stock first and later writing a computed value can lose a concurrent purchase. A guarded arithmetic update lets InnoDB coordinate writers. The application must inspect affected rows and treat zero as an unmet condition, not automatically as success. This is the foundation of optimistic state transitions.

sql Reserve two notebooks temporarily

START TRANSACTION;
UPDATE products SET stock=stock-2 WHERE id=1 AND stock>=2;
SELECT ROW_COUNT() AS changed_rows;
SELECT id,stock FROM products WHERE id=1;
ROLLBACK;

Expected output

changed_rows
1
id | stock
1 | 18

With the original stock of 20, one row changes and its stock becomes 18. ROW_COUNT is checked immediately after UPDATE, before another statement replaces that diagnostic. ROLLBACK restores 20. A quantity supplied by a caller must also be validated as a positive integer.

Deletion follows relationships

Deleting customer 1 fails while orders still reference that row, because the fixture has no cascading delete. This protects history. A deliberate business deletion may instead anonymize personal fields while retaining required accounting records. DELETE with a WHERE predicate is transactional in InnoDB; TRUNCATE is a DDL operation with different commit behavior and must not be treated as a faster interchangeable rollbackable DELETE.

Plan success and failure together

Use strict modes so invalid values fail rather than silently becoming plausible substitutes. Read error codes, constraint names, and the transaction state. A failed statement does not always roll back all earlier statements in a transaction; the application should explicitly roll back an aborted business operation. Keep payment notifications and other external effects outside uncommitted database assumptions, then design an outbox or equivalent delivery strategy when needed.

Common errors and fixes

All product stocks were changed.

Why it happens: UPDATE omitted its WHERE predicate.
How to fix it: Roll back the open transaction; if already committed, use an audited recovery plan.
Prevent it next time: Preview the exact predicate and verify expected affected-row bounds.

ERROR 1451 (23000) deleting a customer.

Why it happens: Referenced child orders still exist and the foreign key restricts deletion.
How to fix it: Choose a reviewed history-preserving workflow rather than disabling checks.
Prevent it next time: Document lifecycle rules for each relationship before writing deletion code.

ERROR 3819 (HY000) after a stock decrease.

Why it happens: The new stock violates CHECK (stock >= 0).
How to fix it: Use a positive requested quantity and a sufficient-stock predicate.
Prevent it next time: Enforce the invariant in both constraints and the write predicate.

Check your understanding

1. Reserve 21 notebooks from the original stock. Predict the guarded update result.

Show worked solution

UPDATE products SET stock=stock-21 WHERE id=1 AND stock>=21 affects zero rows because stock is 20. No negative row is created and no CHECK error is required. The application must report insufficient stock and avoid creating a successful reservation from that outcome.

2. Safely preview and rehearse marking order 102 paid.