In the ACID acronym for database transactions, what does the C (consistency) actually guarantee, and who decides what 'consistent' means?
answer
- Valid state → valid state
- Only enforces what you declared
- Not the C in CAP
- PK / UNIQUE / NOT NULL / CHECK / FK / trigger
- Leans on A + I to hold up
basics
~20 sC means a transaction moves the database from one valid state to another: every declared integrity rule (primary key, unique, not null, check, foreign key) holds at commit. The engine enforces the declared rules; the developer decides which rules to declare.
solid answer
~60 sConsistency in ACID is **invariant preservation**. The database has declared integrity rules: primary keys, UNIQUE, NOT NULL, CHECK, foreign keys, plus anything asserted by triggers. The guarantee is that if the database satisfied all of them before a transaction, it satisfies all of them after it commits. If a statement would break one, the engine raises an error and that transaction cannot commit in the violating state. Two clarifications matter in an interview. First, this is not the C in CAP — CAP consistency is about replicas agreeing on what a read returns, a completely different property. Second, the engine cannot invent invariants; it enforces only what you declared. 'An order's total must equal the sum of its lines' is a consistency rule only if you encoded it as a constraint or trigger. Otherwise buggy application code can commit nonsense and ACID was never violated. So C is a joint contract: the developer states the invariants, and the engine refuses to let any transaction commit while one is broken.
go deeper
Define it crisply — valid state to valid state, enforced via PK/UNIQUE/NOT NULL/CHECK/FK — and note it is not the CAP C.
Add the shared-responsibility point: the engine enforces only declared rules, so an invariant that lives only in service code is not an ACID invariant.
Explain how C depends on A and I, and argue about which invariants belong in the schema versus the application for a real system.
Frame it as a system boundary question: where the authoritative invariants live when several services, batch jobs, and future teams write to the same tables, and what that costs in flexibility and migration effort.
## The definition A transaction takes the database from state S1 to state S2. Consistency says: if S1 satisfies every declared integrity constraint, then S2 does too. A transaction may pass through intermediate states that look wrong to a human (money debited but not yet credited), but at the moment it commits, every declared rule must hold. This is why C is often described as the weakest and least interesting letter of ACID by database theorists — it is not an independent mechanism so much as the statement that constraint checking participates in the transaction. ## What counts as a declared invariant - **NOT NULL** — the column must have a value. - **PRIMARY KEY** — unique and not null; identifies a row. - **UNIQUE** — no two rows share the value (null handling varies by engine; most treat nulls as distinct). - **CHECK** — an arbitrary boolean predicate over a row, e.g. `amount > 0` or `status IN ('NEW','PAID')`. - **FOREIGN KEY** — a referencing value must exist in the referenced table (referential integrity), plus the declared action on update/delete. - **Triggers / assertions** — hand-written rules for anything the declarative forms cannot express. Anything outside this list is not an ACID invariant. It is an application rule that the engine has never heard of. ## Who is responsible The division is: **the application defines the invariant, the engine enforces it.** The engine has no idea that your inventory count should never go negative unless you wrote `CHECK (qty >= 0)`. This is why 'consistency' failures in production almost never look like an ACID bug. They look like a missing constraint. Codd's relational model treats integrity as part of the schema, not part of the code, precisely because schema-level rules cannot be forgotten by a new service, a batch job, or a DBA running a one-off UPDATE. ## Not the C in CAP CAP's consistency (properly linearizability) is about what a read observes across replicas. ACID's consistency is about integrity rules within a single logical database. A single-node database with no replicas still has ACID consistency, and CAP consistency is not even a meaningful question for it. Candidates who conflate the two usually then say something confused like 'we relaxed C for availability', which in ACID terms would mean 'we allowed foreign keys to be broken', which is not what they mean. ## Relationship to the other letters Consistency leans on the other three. Atomicity gives it the escape hatch: a transaction that would violate a rule can be rolled back wholesale rather than leaving a half-applied state. Isolation is what makes it safe under concurrency — a uniqueness rule enforced only by an application 'check then insert' breaks the moment two sessions interleave, because each session's check happened before the other's insert. Durability is what makes the consistent state survive a crash. Practically, C is what atomicity, isolation, and constraint checking buy you together. ## What happens on violation The engine raises an integrity error. In most engines the failing *statement* is rolled back, and depending on the engine and error type the transaction is either left open, marked failed, or aborted; the commit itself can also fail when deferred constraints are checked at COMMIT time. Either way the violating state never becomes visible or durable. Applications should catch specific integrity error codes (unique violation vs foreign key violation) rather than parsing messages. ## Interview framing A strong answer is three sentences: C means committed states satisfy all declared integrity rules; the engine enforces only what is declared, so it is a shared responsibility; and it is unrelated to the C in CAP. Then name the constraint types.
- If the engine only enforces declared constraints, can an application bug violate ACID consistency?No — by definition it cannot. If the invariant was never declared, writing bad data breaks a business rule but not a database constraint, so the transaction is perfectly ACID-consistent. This is exactly why teams push invariants down into the schema: an undeclared rule is enforced only as reliably as every code path that touches the table.
- How is ACID consistency different from CAP consistency?ACID consistency is integrity-rule preservation inside one logical database. CAP consistency is a read property across replicas — roughly, every read sees the latest acknowledged write. They share a word and nothing else; a single-node engine can be fully ACID-consistent while CAP consistency is not even applicable.
The engine is a form validator, not an auditor. It rejects any form that breaks a rule you wrote on the form; it has no opinion about rules you never wrote down.
saying these in an interview costs you the question
- Saying the C in ACID is the same as the C in CAP
- Claiming the database guarantees 'the data is correct' with no mention of declared constraints
- Believing consistency means all readers see the same data at the same time
- Saying constraints are only checked at COMMIT (most are checked much earlier)
- Treating C as a mechanism of its own rather than something built on atomicity, isolation and constraint checking