Module 04 - Data Integrity
Constraints are rules attached to columns that stop invalid data from ever being saved. We've already used NOT NULL, UNIQUE, PRIMARY KEY, and FOREIGN KEY throughout earlier modules.
This lesson covers the ones we've only touched briefly: CHECK, DEFAULT, and what actually happens to related rows when a referenced row is deleted or updated.
CREATE TABLE books (
id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(255) NOT NULL,
isbn VARCHAR(13) NOT NULL UNIQUE,
price DECIMAL(6,2) NOT NULL CHECK (price > 0),
in_stock BOOLEAN DEFAULT TRUE
);| Constraint | Enforces |
|---|---|
CHECK (condition) | Value must satisfy a condition before it's saved |
DEFAULT value | Fallback value used when none is provided |
ON DELETE RESTRICT | Blocks deleting a parent row with existing children |
ON DELETE CASCADE | Deletes child rows automatically along with the parent |
ON DELETE SET NULL | Clears the child's foreign key instead of deleting it |
ON UPDATE CASCADE | Updates child foreign keys automatically if the parent key changes |
A transaction is a group of SQL operations that execute as a single unit — either all of them succeed, or none of them do. If anything fails partway through, everything gets undone, leaving the database exactly as it was before.
START TRANSACTION;
UPDATE accounts
SET balance = balance - 1000
WHERE id = 1;
UPDATE accounts
SET balance = balance + 1000
WHERE id = 2;
COMMIT;START TRANSACTION;
UPDATE accounts
SET balance = balance - 1000
WHERE id = 1;
UPDATE accounts
SET balance = balance + 1000
WHERE id = 2;
ROLLBACK;