Transactions and Data Integrity

Why transactions matter

A transaction groups multiple statements so they either all succeed together or none of them take effect at all — critical whenever one logical action needs to touch more than one row or more than one table.

BEGIN TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;

If the second UPDATE failed partway through — a network drop, a constraint violation, a server crash — issuing ROLLBACK instead of COMMIT undoes both statements together. The money never disappears from account 1 without also appearing in account 2; the database guarantees it’s always one or the other, never a half-finished state.

The ACID properties, briefly

Transactions are usually described by four guarantees, known by the acronym ACID: Atomicity (all-or-nothing, as shown above), Consistency (the database always moves from one valid state to another), Isolation (concurrent transactions don’t interfere with each other’s intermediate results), and Durability (once committed, a change survives even a server crash immediately afterward). You don’t need to memorize these terms, but recognizing them will help when reading database documentation later.

Constraints enforce integrity automatically

CREATE TABLE enrollments (
  student_id INTEGER NOT NULL REFERENCES students(id),
  course_id INTEGER NOT NULL REFERENCES courses(id),
  UNIQUE (student_id, course_id)
);

NOT NULL, REFERENCES (a foreign key constraint), and UNIQUE stop bad data from ever being written in the first place, rather than relying entirely on application code to catch every possible case — a UNIQUE constraint here, for example, makes it physically impossible for the same student to enroll in the same course twice, no matter what bugs might exist in the application calling this table.

You’ve completed the course

From your first SELECT to window functions, performance tuning, and transactions — you now have a professional-level SQL foundation. Take the certification assessment next, or apply to the Data Analyst Internship to work with real datasets.