Transactions and constraints: keeping your data correct

A transaction groups several statements so they succeed or fail together. Constraints are rules the database checks on every write.

ACID in one line each

• Atomicity: all the statements in a transaction happen, or none do.
• Consistency: every transaction moves the data from one valid state to another; constraints are checked.
• Isolation: transactions running at the same time do not see each other's half-finished work.
• Durability: once COMMIT returns, the change survives a crash.

ROLLBACK undoes everything since BEGIN

CREATE TABLE accounts (name text PRIMARY KEY, balance int NOT NULL CHECK (balance >= 0));
INSERT INTO accounts VALUES ('Asha', 500), ('Ravi', 100);
BEGIN;
UPDATE accounts SET balance = balance - 300 WHERE name = 'Asha';
UPDATE accounts SET balance = balance + 300 WHERE name = 'Ravi';
ROLLBACK;
SELECT name, balance FROM accounts ORDER BY name;

Asha|500
Ravi|100

Replace ROLLBACK with COMMIT and both updates stay. Either way, nobody ever sees money leave Asha without reaching Ravi.

Constraints you should use

• PRIMARY KEY: unique and not null; identifies each row.
• FOREIGN KEY: the value must exist in another table. It prevents orphan rows, such as a mark for a student who does not exist.
• UNIQUE: no two rows may share the value (for example, an email address).
• CHECK: any condition, such as balance >= 0 above.
• NOT NULL: the column must have a value.

CREATE TABLE students (roll_no int PRIMARY KEY, email text UNIQUE NOT NULL);
CREATE TABLE marks (roll_no int REFERENCES students(roll_no), score int CHECK (score BETWEEN 0 AND 100));
INSERT INTO students VALUES (1, 'a@college.edu');
INSERT INTO marks VALUES (1, 88);
SELECT count(*) FROM marks;

1

Inserting a mark for roll number 2, or a score of 120, would be refused by the database itself, whatever the application code does. That is why constraints beat checks written only in the front end.