Consistency in ACID Transactions
Consistency, the C in ACID, means a transaction can only take a database from one valid state to another. Every rule the database enforces, such as primary key uniqueness, foreign keys and check constraints, must still hold after the transaction commits; if any rule would be violated, the database rejects the commit and rolls the transaction back.
Consistency, the "C" in ACID, means a transaction can only take a database from one valid state to another valid state. Every rule the database has been told to enforce, primary key uniqueness, foreign key relationships, check constraints, must still hold true after the transaction commits, exactly as it held true before the transaction started.
What "valid state" actually means
A database defines validity through its schema and constraints: a NOT NULL column can never be empty, a foreign key must reference a row that actually exists, a CHECK (balance >= 0) constraint means balance can never go negative. Consistency says a transaction is never allowed to commit if doing so would violate any of these rules, the database simply refuses the commit and rolls the transaction back instead.
This is different from atomicity, which is about the transaction completing fully or not at all as a mechanical guarantee. Consistency is about the result of that transaction, whatever it does, always satisfying every rule the schema defines.
How it's enforced
- The application issues writes inside a transaction.
- Before allowing COMMIT, the database checks every constraint that applies to the rows being changed: uniqueness, foreign keys, check constraints, data types.
- If any constraint would be violated, the database rejects the commit and the transaction rolls back entirely, none of its changes take effect.
- If every constraint passes, the commit proceeds and the new state becomes visible.
A worked example
Suppose an orders table has a foreign key requiring every customer_id to exist in a customers table, and a check constraint requiring total_amount >= 0.
BEGIN;
INSERT INTO orders (customer_id, total_amount) VALUES (9999, -50);
COMMIT;
If customer 9999 doesn't exist, the foreign key constraint fails. Even if it did exist, the check constraint on total_amount >= 0 would fail since -50 is negative. Either violation causes the database to reject the transaction entirely, the orders table is left exactly as it was before this transaction ran, with no partial or invalid row inserted. Consistency is what makes that rejection automatic and guaranteed, rather than something the application has to remember to check itself.
Where this matters in real systems
Real production schemas lean heavily on the database itself enforcing consistency, rather than trusting application code alone, precisely because application code has bugs and a database constraint doesn't get accidentally skipped by one buggy code path. A foreign key constraint on an orders.customer_id column means it is genuinely impossible, at the database level, for an order to ever reference a customer that doesn't exist, regardless of what any application layer does or fails to check.
What interviewers actually ask about consistency
- "What does consistency actually guarantee?" A transaction can only move the database from one valid state to another, every constraint (uniqueness, foreign keys, checks) must hold both before and after.
- "How is consistency different from atomicity?" Atomicity is about the transaction completing entirely or not at all; consistency is about the resulting state always satisfying the schema's rules, whatever the transaction does.
- "Who enforces consistency, the application or the database?" Both can attempt to, but real database-level constraints (foreign keys, checks, uniqueness) are what guarantee it, since application code can have bugs that skip a check.
- "Give an example of a consistency violation the database would block." Inserting a row that references a foreign key value that doesn't exist, or that violates a CHECK constraint like a negative balance.
Key takeaways
- Consistency guarantees a transaction only moves the database between valid states, never leaving a rule-violating result behind.
- Validity is defined by the schema: constraints, foreign keys, uniqueness, and data types.
- If a transaction would violate any constraint, the database rejects the commit and rolls it back entirely.
- Real schemas rely on database-level constraints to guarantee consistency, since application code alone isn't trustworthy enough on its own.
More in ACID Properties
Browse all DBMS topics
Log in to save which topics you've read across every subject.
Frequently asked questions
What does consistency actually guarantee?
A transaction can only move the database from one valid state to another, every constraint (uniqueness, foreign keys, checks) must hold both before and after.
How is consistency different from atomicity?
Atomicity is about the transaction completing entirely or not at all; consistency is about the resulting state always satisfying the schema's rules, whatever the transaction does.
Who enforces consistency, the application or the database?
Both can attempt to, but real database-level constraints (foreign keys, checks, uniqueness) are what guarantee it, since application code can have bugs that skip a check.
Give an example of a consistency violation the database would block.
Inserting a row that references a foreign key value that doesn't exist, or that violates a CHECK constraint like a negative balance.