Loading learning content...
Consider a banking system where account balances can go negative, an inventory system where quantities become negative, or a social network where users can follow themselves infinitely. These absurdities represent consistency violations—states that are technically possible to represent in a database but are fundamentally invalid according to business rules.
Consistency in ACID is the property that ensures every transaction takes the database from one valid state to another valid state. It's the guardian of correctness, the enforcer of business rules, and the keeper of invariants that define what 'makes sense' in your data model.
But here's what makes consistency uniquely complex: unlike Atomicity, Isolation, and Durability (which are entirely the database's responsibility), Consistency is a shared responsibility between the database and the application. The database provides mechanisms; the application must use them correctly.
This page explores consistency from the ground up: the formal definition, the critical distinction between database consistency and application consistency, the constraint mechanisms databases provide (primary keys, foreign keys, check constraints, triggers), referential integrity rules, and the deep interconnections between consistency and the other ACID properties. You'll understand both what databases guarantee and what remains your responsibility.
Consistency in ACID has a specific, formal meaning that's often confused with other uses of the term 'consistency' in distributed systems. Let's be precise:
ACID Consistency (What This Page Covers):
A transaction is consistent if, assuming the database starts in a valid state, it leaves the database in a valid state after the transaction completes. 'Valid state' means all defined integrity constraints, triggers, and cascades have been satisfied.
Distributed Systems Consistency (Different Concept):
In distributed systems (CAP theorem, eventual consistency), 'consistency' refers to whether all nodes see the same data at the same time. This is NOT what ACID consistency means—that's more related to ACID's Isolation property.
For this discussion, we focus exclusively on ACID consistency: the guarantee that transactions preserve data validity.
| Aspect | ACID Consistency | Distributed Consistency |
|---|---|---|
| Focus | Data validity and business rules | Data visibility across nodes |
| Concern | Is the data correct? | Is the data the same everywhere? |
| Guarantee | Invariants are preserved | All readers see the same write |
| Enforcement | Constraints, triggers, application logic | Replication protocols, consensus |
| Example failure | Negative inventory count | Node A shows balance $100, Node B shows $200 |
When system design interviews mention 'consistency,' always clarify what type they mean. ACID Consistency, CAP Consistency, Read Consistency, and Eventual Consistency are all different concepts with different implications. Conflating them leads to fundamental misunderstandings.
At the heart of consistency is the concept of an invariant—a property that must always be true about the data. Invariants define the 'rules of the world' your database represents.
Examples of Invariants:
Banking System:
E-commerce System:
Social Network:
12345678910111213141516171819202122
-- Invariant: Total money in system is conserved-- Before any transaction, this query returns X-- After any valid transaction, it must still return X-- (unless we're explicitly depositing/withdrawing from outside) SELECT SUM(balance) AS total_moneyFROM accounts; -- A transfer transaction preserves this invariant:BEGIN TRANSACTION; UPDATE accounts SET balance = balance - 100 WHERE id = 'A'; -- -100 UPDATE accounts SET balance = balance + 100 WHERE id = 'B'; -- +100COMMIT; -- Net: 0 -- An invalid transaction would violate it:BEGIN TRANSACTION; UPDATE accounts SET balance = balance - 100 WHERE id = 'A'; -- -100 -- Oops, forgot to credit!COMMIT; -- Net: -100 ❌ -- This is why we wrap related operations in transactions-- CONSISTENCY + ATOMICITY together prevent partial updatesThe Consistency Contract:
The database promises: "If you give me data that satisfies the invariants and write transactions that, if executed completely, would maintain those invariants, then after every committed transaction the invariants will still hold."
Notice the conditions: the database isn't magic. If you start with invalid data, or write transactions that violate invariants when executed completely, the database can't save you. It can only enforce the constraints you define and ensure atomicity of your transaction logic.
Databases provide multiple mechanisms to enforce consistency automatically. These constraints are evaluated at transaction commit time (or earlier), and if any constraint is violated, the entire transaction is aborted.
1. Primary Key Constraints
Every table should have a primary key—a column or set of columns that uniquely identifies each row. Primary keys enforce entity integrity: each thing in the database is distinct and identifiable.
12345678910111213141516171819
-- Primary key ensures no two users have the same IDCREATE TABLE users ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), email VARCHAR(255), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP); -- Attempting to insert duplicate ID failsINSERT INTO users (id, email) VALUES ('abc-123', 'alice@example.com');INSERT INTO users (id, email) VALUES ('abc-123', 'bob@example.com');-- ERROR: duplicate key value violates unique constraint "users_pkey" -- Composite primary keys for relationship tablesCREATE TABLE order_items ( order_id UUID NOT NULL, product_id UUID NOT NULL, quantity INTEGER NOT NULL, PRIMARY KEY (order_id, product_id) -- Combination must be unique);2. Unique Constraints
Beyond primary keys, unique constraints ensure that specific columns (or combinations) have distinct values across all rows. Unlike primary keys, columns with unique constraints can contain NULL (in most databases, multiple NULLs are allowed).
123456789101112
-- Email must be unique across all usersALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE (email); -- Attempting duplicate email failsINSERT INTO users (email) VALUES ('alice@example.com');INSERT INTO users (email) VALUES ('alice@example.com');-- ERROR: duplicate key value violates unique constraint "users_email_unique" -- Partial unique constraints (PostgreSQL)-- Only enforce uniqueness for non-deleted recordsCREATE UNIQUE INDEX users_email_active ON users (email) WHERE deleted_at IS NULL;3. Foreign Key Constraints
Foreign keys enforce referential integrity—ensuring that references between tables are always valid. You cannot create an order for a non-existent customer, nor delete a customer who has orders (depending on the action specified).
12345678910111213141516171819
CREATE TABLE orders ( id UUID PRIMARY KEY, customer_id UUID NOT NULL, total DECIMAL(10, 2) NOT NULL, -- Enforce referential integrity CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE RESTRICT -- Prevent deleting customer with orders ON UPDATE CASCADE -- Update order if customer ID changes); -- Referential actions:-- RESTRICT: Prevent the parent operation (default)-- CASCADE: Apply the same operation to child rows-- SET NULL: Set child FK to NULL (column must allow NULL)-- SET DEFAULT: Set child FK to its default value-- NO ACTION: Similar to RESTRICT, but checked at end of transactionCHECK (age >= 0 AND age <= 150).Check constraints allow you to enforce arbitrary business rules directly in the database. They're evaluated whenever a row is inserted or updated, and violations abort the transaction.
Advantages of Database-Level Check Constraints:
123456789101112131415161718192021222324252627282930313233343536373839
CREATE TABLE products ( id UUID PRIMARY KEY, name VARCHAR(255) NOT NULL, price DECIMAL(10, 2) NOT NULL, discount_price DECIMAL(10, 2), quantity INTEGER NOT NULL, status VARCHAR(50) NOT NULL, weight_kg DECIMAL(8, 3), -- Price must be positive CONSTRAINT chk_price_positive CHECK (price > 0), -- Discount price must be less than regular price CONSTRAINT chk_discount_valid CHECK (discount_price IS NULL OR discount_price < price), -- Quantity cannot be negative CONSTRAINT chk_quantity_non_negative CHECK (quantity >= 0), -- Status must be one of predefined values CONSTRAINT chk_status_valid CHECK (status IN ('draft', 'active', 'discontinued', 'out_of_stock')), -- Weight must be positive if specified CONSTRAINT chk_weight_positive CHECK (weight_kg IS NULL OR weight_kg > 0), -- Complex multi-column constraint -- If status is 'active', must have quantity > 0 CONSTRAINT chk_active_has_stock CHECK (status != 'active' OR quantity > 0)); -- Attempting invalid data fails immediatelyINSERT INTO products (id, name, price, quantity, status)VALUES (gen_random_uuid(), 'Widget', -10.00, 5, 'active');-- ERROR: new row for relation "products" violates check constraint "chk_price_positive"Check constraints are row-level—they can only reference columns in the current row. They cannot query other tables or reference other rows. For cross-row or cross-table validations, you need triggers or application logic. Example: 'User can have maximum 3 active subscriptions' requires counting rows, which check constraints cannot do.
When constraints aren't expressive enough, triggers provide arbitrary programmable logic that executes automatically before or after data modifications. Triggers can enforce complex invariants that span multiple tables or rows.
When to Use Triggers:
12345678910111213141516171819202122232425262728293031
-- Invariant: orders.total must equal sum of order_items.subtotal-- Trigger ensures this is always true CREATE OR REPLACE FUNCTION update_order_total()RETURNS TRIGGER AS $$DECLARE new_total DECIMAL(10, 2);BEGIN -- Calculate the correct total SELECT COALESCE(SUM(quantity * unit_price), 0) INTO new_total FROM order_items WHERE order_id = COALESCE(NEW.order_id, OLD.order_id); -- Update the order UPDATE orders SET total = new_total WHERE id = COALESCE(NEW.order_id, OLD.order_id); RETURN NEW;END;$$ LANGUAGE plpgsql; -- Trigger on INSERT, UPDATE, or DELETE of order_itemsCREATE TRIGGER trg_update_order_totalAFTER INSERT OR UPDATE OR DELETE ON order_itemsFOR EACH ROWEXECUTE FUNCTION update_order_total(); -- Now ANY change to order_items automatically updates the order total-- This invariant cannot be violated by any application code12345678910111213141516171819202122232425262728293031323334
-- Invariant: Users can have at most 5 active projectsCREATE OR REPLACE FUNCTION check_project_limit()RETURNS TRIGGER AS $$DECLARE active_count INTEGER;BEGIN -- Only check on INSERT or when status becomes 'active' IF TG_OP = 'INSERT' OR (TG_OP = 'UPDATE' AND NEW.status = 'active' AND OLD.status != 'active') THEN SELECT COUNT(*) INTO active_count FROM projects WHERE user_id = NEW.user_id AND status = 'active'; -- Include the new row in count for insert IF TG_OP = 'INSERT' AND NEW.status = 'active' THEN active_count := active_count + 1; END IF; IF active_count > 5 THEN RAISE EXCEPTION 'User cannot have more than 5 active projects. Current: %', active_count USING ERRCODE = 'check_violation'; END IF; END IF; RETURN NEW;END;$$ LANGUAGE plpgsql; CREATE TRIGGER trg_check_project_limitBEFORE INSERT OR UPDATE ON projectsFOR EACH ROWEXECUTE FUNCTION check_project_limit();Triggers execute synchronously within the transaction. Complex trigger logic can significantly impact performance, especially on high-volume tables. Triggers that query other large tables are particularly problematic. Always benchmark trigger performance and consider whether application-level validation or eventual consistency might be more appropriate for non-critical invariants.
Here's the truth that database purists sometimes overlook: not all consistency can or should be enforced in the database. Many business rules are:
These rules become application-level consistency—the application's responsibility to enforce.
12345678910111213141516171819202122232425262728293031
// Complex business rule that can't easily live in databaseasync function createSubscription(userId: string, planId: string) { // Check external payment service const paymentMethod = await stripeClient.getDefaultPaymentMethod(userId); if (!paymentMethod) { throw new Error('No valid payment method on file'); } // Check business rules against external state const plan = await prisma.plan.findUnique({ where: { id: planId } }); if (plan.isEnterprise && !await hasEnterpriseLicense(userId)) { throw new Error('Enterprise license required for this plan'); } // Check time-dependent rule if (plan.isPromotional && !isPromotionActive(plan.promotionCode)) { throw new Error('This promotional plan has expired'); } // Check quota across services const currentSeats = await getActiveSeatsFromHRSystem(userId); if (currentSeats > plan.maxSeats) { throw new Error(`Plan allows ${plan.maxSeats} seats but you have ${currentSeats}`); } // All checks passed—now we can create in database // The database enforces what it can (FK to valid plan, etc.) return prisma.subscription.create({ data: { userId, planId, status: 'active' } });}The best approach is defense in depth: put what you can in the database, but always validate in the application too. Database constraints catch bugs that slip through application code. Application validation catches issues that can't be expressed as constraints. Together, they form a robust defense against data corruption.
Consistency doesn't exist in isolation—it depends on the other ACID properties to function correctly, and they depend on it. Understanding these interdependencies is crucial for system design.
Consistency + Atomicity:
Without atomicity, consistency is impossible to guarantee. Consider a bank transfer that debits one account and credits another. If the operation is interrupted halfway:
Consistency + Isolation:
Isolation protects consistency during concurrent access. Without isolation:
Consistency + Durability:
Durability ensures that once a transaction commits (having achieved a consistent state), that consistency persists across failures:
1234567891011121314151617181920212223242526272829
┌──────────────────────────────────────────────────────────────────┐│ ACID SYNERGY │├──────────────────────────────────────────────────────────────────┤│ ││ ATOMICITY ──────────► CONSISTENCY ││ "All or nothing" ensures "Valid states only" ││ ││ Without atomicity, partial transactions violate invariants ││ │├──────────────────────────────────────────────────────────────────┤│ ││ ISOLATION ──────────► CONSISTENCY ││ "Independent execution" protects "Valid states only" ││ ││ Without isolation, concurrent transactions corrupt each other ││ │├──────────────────────────────────────────────────────────────────┤│ ││ DURABILITY ──────────► CONSISTENCY ││ "Commits survive" preserves "Valid states only" ││ ││ Without durability, consistent state is lost on crash ││ │├──────────────────────────────────────────────────────────────────┤│ ││ CONSISTENCY is the GOAL. ││ Atomicity, Isolation, and Durability are the MECHANISMS. ││ │└──────────────────────────────────────────────────────────────────┘Some database theorists argue that 'C' doesn't belong in ACID because it's fundamentally different. A, I, and D are pure database guarantees. C is a shared responsibility that depends on the application defining correct constraints and transactions. The database can only enforce what you specify—it cannot invent your business rules.
Despite database protections, consistency violations happen in production. Understanding common causes helps you design more robust systems.
Common Causes of Consistency Violations:
123456789101112131415161718192021222324252627282930313233
// ❌ VULNERABLE: Time-of-check vs time-of-use (TOCTOU)async function purchaseItem(userId: string, itemId: string) { // Check inventory (TIME OF CHECK) const item = await prisma.item.findUnique({ where: { id: itemId } }); if (item.quantity < 1) { throw new Error('Out of stock'); } // Network delay, slow processing, etc. // Another request might decrement quantity HERE // Decrement inventory (TIME OF USE) // But quantity might now be 0! await prisma.item.update({ where: { id: itemId }, data: { quantity: { decrement: 1 } } });} // ✅ CORRECT: Atomic check-and-updateasync function purchaseItemSafe(userId: string, itemId: string) { // Atomic: only decrement if quantity > 0 const result = await prisma.item.updateMany({ where: { id: itemId, quantity: { gt: 0 } }, data: { quantity: { decrement: 1 } } }); if (result.count === 0) { throw new Error('Out of stock'); } // Success: we only get here if we actually decremented}Consistency is the promise that your data always makes sense—that business rules are upheld and invalid states are rejected. But unlike other ACID properties, consistency is a partnership between you and the database.
You now understand Consistency in depth—from invariants and database constraints through triggers and application-level enforcement, to its interdependence with other ACID properties. Next, we'll explore Isolation: how databases handle concurrent transactions without letting them interfere with each other.