Consistency guarantees that a transaction moves the database from one valid state to another, respecting every constraint the schema declares — foreign keys, unique indexes, check constraints, triggers. It's the one ACID letter that isn't really enforced by the transaction manager alone; it's a joint responsibility between the database's declared constraints and the application code writing correct logic within a transaction.
CREATE TABLE orders (
id INT PRIMARY KEY,
customer_id INT NOT NULL REFERENCES customers(id), -- foreign key constraint
total DECIMAL(10,2) NOT NULL CHECK (total >= 0), -- check constraint
status VARCHAR(20) NOT NULL DEFAULT 'pending'
);
-- Rejected by the database — no customer with id 9999 exists.
INSERT INTO orders (id, customer_id, total) VALUES (1, 9999, 50.00);
-- Rejected by the database — total can't be negative.
INSERT INTO orders (id, customer_id, total) VALUES (2, 1, -20.00);
A subtlety worth internalizing: consistency, in the ACID sense, is about the database's own declared rules — not "the data means what the business intended." A transaction can be perfectly ACID-consistent (no constraint violations) while still being logically wrong in a way no CHECK constraint could ever catch, like transferring the wrong dollar amount between two accounts that both exist and both allow negative balances. ACID's Consistency guarantee is a floor, not a substitute for correct application logic — which is exactly the gap that unit tests, code review, and (per the SOLID section below) well-structured business logic exist to close.