Consistency: Valid State to… — ACID Properties: The… | OneNoughtOne

0/278

Database Management SystemsACID Properties

ACID Properties: The Foundation of Reliable Transactions

LevelIntermediate

Duration60 mins

TopicACID Properties

2 / 5

Consistency: Valid State to Valid State

When Databases Lie About Reality

Your e-commerce database shows that a customer has 150 loyalty points. The customer service representative tells the customer they can redeem 100 points for a discount. The customer agrees. The transaction succeeds... but now the database shows -50 loyalty points. The business rule 'points cannot be negative' was violated. The database state no longer represents a valid reality. Or consider: an inventory system allows an order to proceed, deducting stock. The quantity stored becomes -3. The database has just asserted that you have negative three physical items on a warehouse shelf—an impossibility in reality. The system's data no longer corresponds to anything meaningful. Consistency is the ACID property that prevents such impossible states. It guarantees that every transaction takes the database from one valid state to another valid state—never into a configuration that violates the defined rules of correctness.

What You Will Learn

By the end of this page, you will understand consistency as the bridge between database operations and real-world correctness. You'll learn how database constraints enforce invariants, the division of responsibility between database and application, and how to design systems that maintain consistency under all conditions.

Defining Consistency: Valid States and Invariants

Among the ACID properties, consistency is perhaps the most nuanced and often misunderstood. It operates at the intersection of database mechanisms and application semantics, requiring a clear mental model to apply correctly. Formal Definition: A transaction exhibits consistency if it transforms the database from one valid state to another valid state, where validity is defined by a set of integrity constraints, invariants, and rules that the database must always satisfy. The key insight is that consistency is defined relative to rules of correctness . These rules can be: Database-level constraints — Primary keys, foreign keys, unique constraints, NOT NULL, CHECK constraints Application-level invariants — Business rules that the database cannot enforce directly Domain-level truths — Physical impossibilities (negative inventory), mathematical laws (account balance after debit equals prior balance minus debit amount)

Consistency vs. the Other ACID Properties

Atomicity, isolation, and durability are properties that the database system implements and guarantees. Consistency is different—it's a collaborative property. The database provides mechanisms (constraints, transactions) but the application must use them correctly. A database can be perfectly atomic, isolated, and durable while containing inconsistent data if the application fails to define or enforce necessary constraints.

The Two Faces of Consistency: Intra-Transaction Consistency — During a transaction, the database may temporarily violate constraints (for example, deducting from one account before crediting another creates a temporary imbalance). This is acceptable as long as the final committed state is valid. Inter-Transaction Consistency — Every committed transaction must leave the database in a valid state. If a transaction would violate any constraint, it must be aborted rather than committed. The key distinction: temporary violation during execution is acceptable; permanent violation after commit is not .

Consistency Throughout Transaction Lifecycle
Phase	Database State	Constraints	Acceptable?
Before Transaction	Valid (all constraints satisfied)	All enforced	Required
Mid-Transaction (deferred check)	May temporarily violate	Suspended until commit	Allowed
After Commit	Valid (all constraints satisfied)	All enforced	Required
After Abort	Identical to before transaction	All enforced	Required

Database Constraints: Declarative Correctness

The most powerful tool for enforcing consistency is the database constraint system. Constraints are declarations about what constitutes valid data; the database enforces them automatically on every modification. Why Declarative Constraints Matter: Constraints defined at the database level are: Always enforced — Every insert, update, or delete is checked, regardless of which application or user initiated it Consistently applied — No need to remember to validate in every code path; the database is the single enforcement point Transactionally safe — Constraint checking is part of the transaction; violations cause abort Self-documenting — The schema explicitly states what invariants must hold

database-constraints.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
-- Example: Comprehensive constraint definition for an order system
 
CREATE TABLE customers (
    customer_id     SERIAL PRIMARY KEY,          -- Entity integrity
    email           VARCHAR(255) NOT NULL UNIQUE, -- Domain & key constraint
    name            VARCHAR(100) NOT NULL,        -- Domain constraint
    loyalty_points  INTEGER NOT NULL DEFAULT 0 
                    CHECK (loyalty_points >= 0),  -- Business rule constraint
    created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
 
CREATE TABLE products (
    product_id      SERIAL PRIMARY KEY,
    sku             VARCHAR(50) NOT NULL UNIQUE,
    name            VARCHAR(200) NOT NULL,
    price           DECIMAL(10, 2) NOT NULL 
                    CHECK (price > 0),            -- Positive price required
    stock_quantity  INTEGER NOT NULL DEFAULT 0
                    CHECK (stock_quantity >= 0)   -- No negative inventory
);
 
CREATE TABLE orders (
    order_id        SERIAL PRIMARY KEY,
    customer_id     INTEGER NOT NULL 
                    REFERENCES customers(customer_id), -- Referential integrity
    order_date      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    status          VARCHAR(20) NOT NULL DEFAULT 'pending'
                    CHECK (status IN ('pending', 'confirmed', 'shipped', 
                                      'delivered', 'cancelled')),
    total_amount    DECIMAL(12, 2) NOT NULL
                    CHECK (total_amount >= 0)
);
 
CREATE TABLE order_items (
    item_id         SERIAL PRIMARY KEY,
    order_id        INTEGER NOT NULL 
                    REFERENCES orders(order_id) ON DELETE CASCADE,
    product_id      INTEGER NOT NULL 
                    REFERENCES products(product_id),
    quantity        INTEGER NOT NULL 
                    CHECK (quantity > 0),          -- Must order at least 1
    unit_price      DECIMAL(10, 2) NOT NULL 
                    CHECK (unit_price >= 0),
    
    UNIQUE (order_id, product_id)  -- One line item per product per order
);

Types of Database Constraints

•PRIMARY KEY — Enforces entity integrity. Every row has a unique identifier; no duplicate or NULL keys allowed. Fundamentally distinguishes one entity from another.
•FOREIGN KEY — Enforces referential integrity. Values must reference existing rows in the parent table. Prevents orphaned records that reference non-existent entities.
•UNIQUE — Enforces candidate key constraints. Alternative attributes that must be unique across all rows (e.g., email addresses, product SKUs).
•NOT NULL — Enforces mandatory attributes. The database rejects any attempt to store NULL where values are required.
•CHECK — Enforces domain constraints and simple business rules. Values must satisfy a boolean expression (e.g., quantity > 0, status IN ('A', 'B', 'C')).
•EXCLUSION — Prevents overlapping values (PostgreSQL). Useful for scheduling constraints like 'no two meetings can occupy the same room at the same time.'

Push Constraints Down to the Database

Any invariant that CAN be expressed as a database constraint SHOULD be. Database constraints are the last line of defense against inconsistent data, regardless of bugs in application logic, direct SQL access, data migrations, or future applications that share the database. Never assume 'the application will prevent this'—define it in the schema.

Constraint Timing: Immediate vs. Deferred

Some transactions require temporarily violating constraints before reaching a consistent final state. Consider inserting two rows that reference each other—a circular dependency. No matter which you insert first, a foreign key will fail if constraints are checked immediately. Databases address this with constraint timing modes :

Immediate Constraints

•Checked after each statement
•Violations abort immediately
•Fastest feedback for debugging
•Cannot handle circular dependencies
•Default behavior in most databases

Deferred Constraints

•Checked only at COMMIT time
•Temporary violations allowed
•Handles circular references
•Harder to debug (violation appears later)
•Must explicitly configure

deferred-constraints.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
-- Create a deferrable foreign key constraint
CREATE TABLE departments (
    dept_id     SERIAL PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    manager_id  INTEGER REFERENCES employees(emp_id) 
                DEFERRABLE INITIALLY DEFERRED
);
 
CREATE TABLE employees (
    emp_id      SERIAL PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    dept_id     INTEGER REFERENCES departments(dept_id) 
                DEFERRABLE INITIALLY DEFERRED
);
 
-- Now we can insert with circular references
BEGIN;
    -- Insert department with manager_id that doesn't exist yet
    INSERT INTO departments (dept_id, name, manager_id) 
    VALUES (1, 'Engineering', 100);
    
    -- Insert employee with dept_id, also referencing the employee as manager
    INSERT INTO employees (emp_id, name, dept_id) 
    VALUES (100, 'Alice Smith', 1);
    
    -- At COMMIT, both FKs are checked and satisfied
COMMIT;
 
-- Alternative: Set constraint mode within a transaction
BEGIN;
    SET CONSTRAINTS ALL DEFERRED;
    -- ... operations that temporarily violate constraints ...
COMMIT;

Deferred Constraints Require Discipline

While deferred constraints are powerful, they delay error detection. A violation discovered at COMMIT may be far from the statement that caused it, making debugging difficult. Use deferred constraints only when necessary (circular dependencies, bulk operations) and prefer immediate constraints for regular operations.

Application-Level Consistency: Beyond Database Constraints

Database constraints handle structural and simple domain rules effectively. But many business invariants are too complex or contextual for declarative constraints. These require application-level enforcement . Examples of Application-Level Invariants:

•Cross-Table Derived Rules — 'Sum of order_items.quantity * order_items.unit_price must equal orders.total_amount'
•Temporal Business Rules — 'A subscription cannot be cancelled within 30 days of purchase unless processing fee is paid'
•External State Dependencies — 'Payment must be authorized by external gateway before order can be confirmed'
•Rate Limits and Quotas — 'A user cannot create more than 10 orders per hour'
•Complex State Machines — 'Order status can only transition from pending→confirmed→shipped→delivered, or any state→cancelled'

Enforcing Application-Level Consistency: The key is to treat the transaction boundary as the consistency boundary. All checks must occur within the same transaction as the modifications:

application-consistency.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
def process_order_cancellation(order_id: str, reason: str) -> bool:
    """
    Cancels an order, enforcing complex business rules.
    All checks and modifications occur in a single transaction.
    """
    conn = get_db_connection()
    
    try:
        conn.begin_transaction()
        
        # Lock the order row to prevent concurrent modifications
        order = conn.execute("""
            SELECT order_id, status, created_at, total_amount, customer_id
            FROM orders 
            WHERE order_id = %s 
            FOR UPDATE
        """, (order_id,)).fetchone()
        
        if not order:
            conn.rollback()
            raise OrderNotFoundError(f"Order {order_id} not found")
        
        # Application-level invariant: Valid status transitions
        current_status = order['status']
        valid_transitions = {
            'pending': ['cancelled'],
            'confirmed': ['cancelled', 'shipped'],
            'shipped': ['delivered'],
            'delivered': [],  # Terminal state
            'cancelled': [],  # Terminal state
        }
        
        if 'cancelled' not in valid_transitions.get(current_status, []):
            conn.rollback()
            raise InvalidStateTransitionError(
                f"Cannot cancel order in status '{current_status}'"
            )
        
        # Application-level invariant: 30-day cancellation window
        order_age = datetime.now() - order['created_at']
        if order_age > timedelta(days=30) and current_status != 'pending':
            conn.rollback()
            raise CancellationWindowExpiredError(
                f"Order is {order_age.days} days old, cancellation window expired"
            )
        
        # All checks passed—perform the cancellation
        conn.execute("""
            UPDATE orders 
            SET status = 'cancelled', 
                cancelled_at = CURRENT_TIMESTAMP,
                cancellation_reason = %s
            WHERE order_id = %s
        """, (reason, order_id))
        
        # Restore inventory
        conn.execute("""
            UPDATE products p
            SET stock_quantity = stock_quantity + oi.quantity
            FROM order_items oi
            WHERE oi.order_id = %s AND oi.product_id = p.product_id
        """, (order_id,))
        
        # Refund loyalty points
        conn.execute("""
            UPDATE customers 
            SET loyalty_points = loyalty_points + %s
            WHERE customer_id = %s
        """, (order['points_used'], order['customer_id']))
        
        conn.commit()
        return True
        
    except Exception as e:
        conn.rollback()
        raise
    finally:
        conn.close()

Check-Then-Act Must Be Atomic

A common bug is checking a condition, then performing an action based on it, without holding a lock between the check and the action. Another transaction could change the state between your check and your action. Always use SELECT ... FOR UPDATE or serializable isolation when implementing check-then-act patterns.

Triggers and Stored Procedures: Server-Side Consistency

When application-level invariants are critical and must never be bypassed, database triggers and stored procedures provide server-side enforcement. These execute within the database engine, regardless of which client initiated the operation. Triggers for Derived Data Consistency:

triggers-for-consistency.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
-- Trigger to maintain order total as sum of line items
-- This enforces: orders.total_amount = SUM(order_items.quantity * unit_price)
 
CREATE OR REPLACE FUNCTION update_order_total()
RETURNS TRIGGER AS $$
BEGIN
    -- Recalculate the order total whenever items change
    UPDATE orders
    SET total_amount = (
        SELECT COALESCE(SUM(quantity * unit_price), 0)
        FROM order_items
        WHERE order_id = COALESCE(NEW.order_id, OLD.order_id)
    )
    WHERE order_id = COALESCE(NEW.order_id, OLD.order_id);
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER trg_order_items_update_total
AFTER INSERT OR UPDATE OR DELETE ON order_items
FOR EACH ROW
EXECUTE FUNCTION update_order_total();
 
-- Trigger to enforce stock never goes negative
CREATE OR REPLACE FUNCTION check_stock_availability()
RETURNS TRIGGER AS $$
DECLARE
    available_stock INTEGER;
BEGIN
    SELECT stock_quantity INTO available_stock
    FROM products
    WHERE product_id = NEW.product_id
    FOR UPDATE;  -- Lock the product row
    
    IF available_stock < NEW.quantity THEN
        RAISE EXCEPTION 'Insufficient stock for product %. Available: %, Requested: %',
            NEW.product_id, available_stock, NEW.quantity;
    END IF;
    
    -- Deduct stock
    UPDATE products
    SET stock_quantity = stock_quantity - NEW.quantity
    WHERE product_id = NEW.product_id;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER trg_check_stock
BEFORE INSERT ON order_items
FOR EACH ROW
EXECUTE FUNCTION check_stock_availability();

When to Use Triggers for Consistency

•Derived Data Synchronization — Maintaining computed columns, aggregates, or denormalized copies that must always reflect current source data
•Cross-Table Constraints — Enforcing rules that span multiple tables and cannot be expressed as simple CHECK constraints
•Audit Trails — Automatically recording who changed what, when, guaranteeing that no modification escapes logging
•Multi-Application Environments — When multiple applications access the same database and all must follow the same rules
•Legacy System Integration — Adding consistency enforcement to systems where modifying all application code is impractical

Triggers Are Powerful but Dangerous

Triggers execute implicitly, which can lead to surprising behavior. Cascading triggers, circular trigger invocations, and performance impacts from row-level triggers on bulk operations are common problems. Document triggers thoroughly, test them rigorously, and consider whether the complexity is worth the benefit versus application-level enforcement.

Consistency Models: Strong vs. Eventual

In distributed systems and modern database architectures, 'consistency' takes on additional meanings beyond the ACID definition. Understanding these different consistency models is essential for architecting systems correctly. Strong Consistency (ACID Consistency): After a transaction commits, all subsequent reads will see the committed values. There is a single, authoritative state of the database that all observers agree upon. This is what traditional RDBMS provide and what we've been discussing.

Comparison of Consistency Models
Model	Guarantee	Trade-off	Use Case
Strong Consistency	All readers see the same data immediately after commit	Lower availability, higher latency in distributed scenarios	Financial systems, inventory, anything requiring immediate accuracy
Eventual Consistency	All replicas will eventually converge to the same value	Higher availability, lower latency, but stale reads possible	Social media feeds, product catalogs, analytics
Causal Consistency	Operations that are causally related are seen in order	Middle ground between strong and eventual	Collaborative editing, messaging systems
Read-Your-Writes	A client always sees its own writes	Simpler than full strong consistency	User preferences, session data

ACID Consistency vs. CAP Consistency

The 'C' in ACID (database constraints and invariants) is different from the 'C' in CAP theorem (all nodes seeing the same data at the same time). ACID consistency is about correctness; CAP consistency is about agreement across distributed replicas. They're related but distinct concepts. Don't conflate them.

Implications for System Design: Choosing a consistency model is a fundamental architectural decision: Single-node databases typically provide strong consistency automatically Replicated databases may offer tunable consistency (PostgreSQL synchronous replication, MySQL Group Replication) NoSQL databases often default to eventual consistency for performance (Cassandra, DynamoDB) but may offer stronger options Microservices crossing database boundaries often settle for eventual consistency plus compensating actions The key question: What happens if a user sees stale data? If the answer is 'financial loss, legal liability, or safety risk,' you need strong consistency. If the answer is 'minor user annoyance,' eventual consistency may be acceptable.

Consistency Design Patterns

Several established patterns help maintain consistency in complex systems:

Patterns for Data Consistency

•Single Source of Truth — Designate one authoritative store for each piece of data. Derived data is computed from this source, never edited directly. Eliminates divergence between copies.
•Event Sourcing — Store immutable events rather than mutable state. Current state is computed by replaying events. Provides perfect audit trail and makes consistency violations easier to detect and repair.
•Domain-Driven Design Aggregates — Define consistency boundaries around business entities. All invariants within an aggregate are enforced within a single transaction. Cross-aggregate consistency is eventual.
•Validation Layers — Implement validation at multiple levels: UI (fast feedback), application (business rules), database (non-negotiable constraints). Defense in depth.
•Immutable Data Structures — Instead of updating records, insert new versions. The current version is always the most recent. Simplifies concurrent access and provides built-in history.

aggregate-pattern.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
class Order:
    """
    Order aggregate: consistency boundary for order-related invariants.
    All modifications go through aggregate methods that enforce rules.
    """
    
    def __init__(self, order_id: str, customer_id: str):
        self.order_id = order_id
        self.customer_id = customer_id
        self.items: List[OrderItem] = []
        self.status = 'pending'
        self._total = Decimal('0.00')
    
    @property
    def total(self) -> Decimal:
        """Total is always derived from items—never set directly."""
        return sum(item.quantity * item.unit_price for item in self.items)
    
    def add_item(self, product_id: str, quantity: int, unit_price: Decimal):
        """Add item with validation."""
        if self.status != 'pending':
            raise InvalidOperationError("Cannot add items to non-pending order")
        if quantity <= 0:
            raise ValidationError("Quantity must be positive")
        if unit_price < 0:
            raise ValidationError("Price cannot be negative")
        
        # Check for duplicate product
        existing = next((i for i in self.items if i.product_id == product_id), None)
        if existing:
            existing.quantity += quantity
        else:
            self.items.append(OrderItem(product_id, quantity, unit_price))
    
    def confirm(self):
        """Confirm order with all necessary checks."""
        if self.status != 'pending':
            raise InvalidOperationError(f"Cannot confirm order in status {self.status}")
        if not self.items:
            raise ValidationError("Cannot confirm empty order")
        
        # Invariant: order must have calculated totals
        if self.total <= 0:
            raise ValidationError("Order total must be positive")
        
        self.status = 'confirmed'
    
    # The repository saves the entire aggregate in a single transaction
    # guaranteeing all invariants are satisfied

The Aggregate as Transaction Boundary

In Domain-Driven Design, an aggregate defines the transactional consistency boundary. All invariants within the aggregate are enforced atomically. Changes to different aggregates may have eventual consistency between them. This pattern scales well because it limits the scope of each transaction.

Database Management SystemsACID Properties

ACID Properties: The Foundation of Reliable Transactions

LevelIntermediate

Duration60 mins

TopicACID Properties

2 / 5

Consistency: Valid State to Valid State

When Databases Lie About Reality

Your e-commerce database shows that a customer has 150 loyalty points. The customer service representative tells the customer they can redeem 100 points for a discount. The customer agrees. The transaction succeeds... but now the database shows -50 loyalty points. The business rule 'points cannot be negative' was violated. The database state no longer represents a valid reality. Or consider: an inventory system allows an order to proceed, deducting stock. The quantity stored becomes -3. The database has just asserted that you have negative three physical items on a warehouse shelf—an impossibility in reality. The system's data no longer corresponds to anything meaningful. Consistency is the ACID property that prevents such impossible states. It guarantees that every transaction takes the database from one valid state to another valid state—never into a configuration that violates the defined rules of correctness.

What You Will Learn

By the end of this page, you will understand consistency as the bridge between database operations and real-world correctness. You'll learn how database constraints enforce invariants, the division of responsibility between database and application, and how to design systems that maintain consistency under all conditions.

Defining Consistency: Valid States and Invariants

Among the ACID properties, consistency is perhaps the most nuanced and often misunderstood. It operates at the intersection of database mechanisms and application semantics, requiring a clear mental model to apply correctly. Formal Definition: A transaction exhibits consistency if it transforms the database from one valid state to another valid state, where validity is defined by a set of integrity constraints, invariants, and rules that the database must always satisfy. The key insight is that consistency is defined relative to rules of correctness . These rules can be: Database-level constraints — Primary keys, foreign keys, unique constraints, NOT NULL, CHECK constraints Application-level invariants — Business rules that the database cannot enforce directly Domain-level truths — Physical impossibilities (negative inventory), mathematical laws (account balance after debit equals prior balance minus debit amount)

Consistency vs. the Other ACID Properties

Atomicity, isolation, and durability are properties that the database system implements and guarantees. Consistency is different—it's a collaborative property. The database provides mechanisms (constraints, transactions) but the application must use them correctly. A database can be perfectly atomic, isolated, and durable while containing inconsistent data if the application fails to define or enforce necessary constraints.

The Two Faces of Consistency: Intra-Transaction Consistency — During a transaction, the database may temporarily violate constraints (for example, deducting from one account before crediting another creates a temporary imbalance). This is acceptable as long as the final committed state is valid. Inter-Transaction Consistency — Every committed transaction must leave the database in a valid state. If a transaction would violate any constraint, it must be aborted rather than committed. The key distinction: temporary violation during execution is acceptable; permanent violation after commit is not .

Consistency Throughout Transaction Lifecycle
Phase	Database State	Constraints	Acceptable?
Before Transaction	Valid (all constraints satisfied)	All enforced	Required
Mid-Transaction (deferred check)	May temporarily violate	Suspended until commit	Allowed
After Commit	Valid (all constraints satisfied)	All enforced	Required
After Abort	Identical to before transaction	All enforced	Required

Database Constraints: Declarative Correctness

The most powerful tool for enforcing consistency is the database constraint system. Constraints are declarations about what constitutes valid data; the database enforces them automatically on every modification. Why Declarative Constraints Matter: Constraints defined at the database level are: Always enforced — Every insert, update, or delete is checked, regardless of which application or user initiated it Consistently applied — No need to remember to validate in every code path; the database is the single enforcement point Transactionally safe — Constraint checking is part of the transaction; violations cause abort Self-documenting — The schema explicitly states what invariants must hold

database-constraints.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
-- Example: Comprehensive constraint definition for an order system
 
CREATE TABLE customers (
    customer_id     SERIAL PRIMARY KEY,          -- Entity integrity
    email           VARCHAR(255) NOT NULL UNIQUE, -- Domain & key constraint
    name            VARCHAR(100) NOT NULL,        -- Domain constraint
    loyalty_points  INTEGER NOT NULL DEFAULT 0 
                    CHECK (loyalty_points >= 0),  -- Business rule constraint
    created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
 
CREATE TABLE products (
    product_id      SERIAL PRIMARY KEY,
    sku             VARCHAR(50) NOT NULL UNIQUE,
    name            VARCHAR(200) NOT NULL,
    price           DECIMAL(10, 2) NOT NULL 
                    CHECK (price > 0),            -- Positive price required
    stock_quantity  INTEGER NOT NULL DEFAULT 0
                    CHECK (stock_quantity >= 0)   -- No negative inventory
);
 
CREATE TABLE orders (
    order_id        SERIAL PRIMARY KEY,
    customer_id     INTEGER NOT NULL 
                    REFERENCES customers(customer_id), -- Referential integrity
    order_date      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    status          VARCHAR(20) NOT NULL DEFAULT 'pending'
                    CHECK (status IN ('pending', 'confirmed', 'shipped', 
                                      'delivered', 'cancelled')),
    total_amount    DECIMAL(12, 2) NOT NULL
                    CHECK (total_amount >= 0)
);
 
CREATE TABLE order_items (
    item_id         SERIAL PRIMARY KEY,
    order_id        INTEGER NOT NULL 
                    REFERENCES orders(order_id) ON DELETE CASCADE,
    product_id      INTEGER NOT NULL 
                    REFERENCES products(product_id),
    quantity        INTEGER NOT NULL 
                    CHECK (quantity > 0),          -- Must order at least 1
    unit_price      DECIMAL(10, 2) NOT NULL 
                    CHECK (unit_price >= 0),
    
    UNIQUE (order_id, product_id)  -- One line item per product per order
);

Types of Database Constraints

•PRIMARY KEY — Enforces entity integrity. Every row has a unique identifier; no duplicate or NULL keys allowed. Fundamentally distinguishes one entity from another.
•FOREIGN KEY — Enforces referential integrity. Values must reference existing rows in the parent table. Prevents orphaned records that reference non-existent entities.
•UNIQUE — Enforces candidate key constraints. Alternative attributes that must be unique across all rows (e.g., email addresses, product SKUs).
•NOT NULL — Enforces mandatory attributes. The database rejects any attempt to store NULL where values are required.
•CHECK — Enforces domain constraints and simple business rules. Values must satisfy a boolean expression (e.g., quantity > 0, status IN ('A', 'B', 'C')).
•EXCLUSION — Prevents overlapping values (PostgreSQL). Useful for scheduling constraints like 'no two meetings can occupy the same room at the same time.'

Push Constraints Down to the Database

Any invariant that CAN be expressed as a database constraint SHOULD be. Database constraints are the last line of defense against inconsistent data, regardless of bugs in application logic, direct SQL access, data migrations, or future applications that share the database. Never assume 'the application will prevent this'—define it in the schema.

Constraint Timing: Immediate vs. Deferred

Some transactions require temporarily violating constraints before reaching a consistent final state. Consider inserting two rows that reference each other—a circular dependency. No matter which you insert first, a foreign key will fail if constraints are checked immediately. Databases address this with constraint timing modes :

Immediate Constraints

•Checked after each statement
•Violations abort immediately
•Fastest feedback for debugging
•Cannot handle circular dependencies
•Default behavior in most databases

Deferred Constraints

•Checked only at COMMIT time
•Temporary violations allowed
•Handles circular references
•Harder to debug (violation appears later)
•Must explicitly configure

deferred-constraints.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
-- Create a deferrable foreign key constraint
CREATE TABLE departments (
    dept_id     SERIAL PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    manager_id  INTEGER REFERENCES employees(emp_id) 
                DEFERRABLE INITIALLY DEFERRED
);
 
CREATE TABLE employees (
    emp_id      SERIAL PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    dept_id     INTEGER REFERENCES departments(dept_id) 
                DEFERRABLE INITIALLY DEFERRED
);
 
-- Now we can insert with circular references
BEGIN;
    -- Insert department with manager_id that doesn't exist yet
    INSERT INTO departments (dept_id, name, manager_id) 
    VALUES (1, 'Engineering', 100);
    
    -- Insert employee with dept_id, also referencing the employee as manager
    INSERT INTO employees (emp_id, name, dept_id) 
    VALUES (100, 'Alice Smith', 1);
    
    -- At COMMIT, both FKs are checked and satisfied
COMMIT;
 
-- Alternative: Set constraint mode within a transaction
BEGIN;
    SET CONSTRAINTS ALL DEFERRED;
    -- ... operations that temporarily violate constraints ...
COMMIT;

Deferred Constraints Require Discipline

While deferred constraints are powerful, they delay error detection. A violation discovered at COMMIT may be far from the statement that caused it, making debugging difficult. Use deferred constraints only when necessary (circular dependencies, bulk operations) and prefer immediate constraints for regular operations.

Application-Level Consistency: Beyond Database Constraints

Database constraints handle structural and simple domain rules effectively. But many business invariants are too complex or contextual for declarative constraints. These require application-level enforcement . Examples of Application-Level Invariants:

•Cross-Table Derived Rules — 'Sum of order_items.quantity * order_items.unit_price must equal orders.total_amount'
•Temporal Business Rules — 'A subscription cannot be cancelled within 30 days of purchase unless processing fee is paid'
•External State Dependencies — 'Payment must be authorized by external gateway before order can be confirmed'
•Rate Limits and Quotas — 'A user cannot create more than 10 orders per hour'
•Complex State Machines — 'Order status can only transition from pending→confirmed→shipped→delivered, or any state→cancelled'

Enforcing Application-Level Consistency: The key is to treat the transaction boundary as the consistency boundary. All checks must occur within the same transaction as the modifications:

application-consistency.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
def process_order_cancellation(order_id: str, reason: str) -> bool:
    """
    Cancels an order, enforcing complex business rules.
    All checks and modifications occur in a single transaction.
    """
    conn = get_db_connection()
    
    try:
        conn.begin_transaction()
        
        # Lock the order row to prevent concurrent modifications
        order = conn.execute("""
            SELECT order_id, status, created_at, total_amount, customer_id
            FROM orders 
            WHERE order_id = %s 
            FOR UPDATE
        """, (order_id,)).fetchone()
        
        if not order:
            conn.rollback()
            raise OrderNotFoundError(f"Order {order_id} not found")
        
        # Application-level invariant: Valid status transitions
        current_status = order['status']
        valid_transitions = {
            'pending': ['cancelled'],
            'confirmed': ['cancelled', 'shipped'],
            'shipped': ['delivered'],
            'delivered': [],  # Terminal state
            'cancelled': [],  # Terminal state
        }
        
        if 'cancelled' not in valid_transitions.get(current_status, []):
            conn.rollback()
            raise InvalidStateTransitionError(
                f"Cannot cancel order in status '{current_status}'"
            )
        
        # Application-level invariant: 30-day cancellation window
        order_age = datetime.now() - order['created_at']
        if order_age > timedelta(days=30) and current_status != 'pending':
            conn.rollback()
            raise CancellationWindowExpiredError(
                f"Order is {order_age.days} days old, cancellation window expired"
            )
        
        # All checks passed—perform the cancellation
        conn.execute("""
            UPDATE orders 
            SET status = 'cancelled', 
                cancelled_at = CURRENT_TIMESTAMP,
                cancellation_reason = %s
            WHERE order_id = %s
        """, (reason, order_id))
        
        # Restore inventory
        conn.execute("""
            UPDATE products p
            SET stock_quantity = stock_quantity + oi.quantity
            FROM order_items oi
            WHERE oi.order_id = %s AND oi.product_id = p.product_id
        """, (order_id,))
        
        # Refund loyalty points
        conn.execute("""
            UPDATE customers 
            SET loyalty_points = loyalty_points + %s
            WHERE customer_id = %s
        """, (order['points_used'], order['customer_id']))
        
        conn.commit()
        return True
        
    except Exception as e:
        conn.rollback()
        raise
    finally:
        conn.close()

Check-Then-Act Must Be Atomic

A common bug is checking a condition, then performing an action based on it, without holding a lock between the check and the action. Another transaction could change the state between your check and your action. Always use SELECT ... FOR UPDATE or serializable isolation when implementing check-then-act patterns.

Triggers and Stored Procedures: Server-Side Consistency

When application-level invariants are critical and must never be bypassed, database triggers and stored procedures provide server-side enforcement. These execute within the database engine, regardless of which client initiated the operation. Triggers for Derived Data Consistency:

triggers-for-consistency.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
-- Trigger to maintain order total as sum of line items
-- This enforces: orders.total_amount = SUM(order_items.quantity * unit_price)
 
CREATE OR REPLACE FUNCTION update_order_total()
RETURNS TRIGGER AS $$
BEGIN
    -- Recalculate the order total whenever items change
    UPDATE orders
    SET total_amount = (
        SELECT COALESCE(SUM(quantity * unit_price), 0)
        FROM order_items
        WHERE order_id = COALESCE(NEW.order_id, OLD.order_id)
    )
    WHERE order_id = COALESCE(NEW.order_id, OLD.order_id);
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER trg_order_items_update_total
AFTER INSERT OR UPDATE OR DELETE ON order_items
FOR EACH ROW
EXECUTE FUNCTION update_order_total();
 
-- Trigger to enforce stock never goes negative
CREATE OR REPLACE FUNCTION check_stock_availability()
RETURNS TRIGGER AS $$
DECLARE
    available_stock INTEGER;
BEGIN
    SELECT stock_quantity INTO available_stock
    FROM products
    WHERE product_id = NEW.product_id
    FOR UPDATE;  -- Lock the product row
    
    IF available_stock < NEW.quantity THEN
        RAISE EXCEPTION 'Insufficient stock for product %. Available: %, Requested: %',
            NEW.product_id, available_stock, NEW.quantity;
    END IF;
    
    -- Deduct stock
    UPDATE products
    SET stock_quantity = stock_quantity - NEW.quantity
    WHERE product_id = NEW.product_id;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER trg_check_stock
BEFORE INSERT ON order_items
FOR EACH ROW
EXECUTE FUNCTION check_stock_availability();

When to Use Triggers for Consistency

•Derived Data Synchronization — Maintaining computed columns, aggregates, or denormalized copies that must always reflect current source data
•Cross-Table Constraints — Enforcing rules that span multiple tables and cannot be expressed as simple CHECK constraints
•Audit Trails — Automatically recording who changed what, when, guaranteeing that no modification escapes logging
•Multi-Application Environments — When multiple applications access the same database and all must follow the same rules
•Legacy System Integration — Adding consistency enforcement to systems where modifying all application code is impractical

Triggers Are Powerful but Dangerous

Triggers execute implicitly, which can lead to surprising behavior. Cascading triggers, circular trigger invocations, and performance impacts from row-level triggers on bulk operations are common problems. Document triggers thoroughly, test them rigorously, and consider whether the complexity is worth the benefit versus application-level enforcement.

Consistency Models: Strong vs. Eventual

In distributed systems and modern database architectures, 'consistency' takes on additional meanings beyond the ACID definition. Understanding these different consistency models is essential for architecting systems correctly. Strong Consistency (ACID Consistency): After a transaction commits, all subsequent reads will see the committed values. There is a single, authoritative state of the database that all observers agree upon. This is what traditional RDBMS provide and what we've been discussing.

Comparison of Consistency Models
Model	Guarantee	Trade-off	Use Case
Strong Consistency	All readers see the same data immediately after commit	Lower availability, higher latency in distributed scenarios	Financial systems, inventory, anything requiring immediate accuracy
Eventual Consistency	All replicas will eventually converge to the same value	Higher availability, lower latency, but stale reads possible	Social media feeds, product catalogs, analytics
Causal Consistency	Operations that are causally related are seen in order	Middle ground between strong and eventual	Collaborative editing, messaging systems
Read-Your-Writes	A client always sees its own writes	Simpler than full strong consistency	User preferences, session data

ACID Consistency vs. CAP Consistency

The 'C' in ACID (database constraints and invariants) is different from the 'C' in CAP theorem (all nodes seeing the same data at the same time). ACID consistency is about correctness; CAP consistency is about agreement across distributed replicas. They're related but distinct concepts. Don't conflate them.

Implications for System Design: Choosing a consistency model is a fundamental architectural decision: Single-node databases typically provide strong consistency automatically Replicated databases may offer tunable consistency (PostgreSQL synchronous replication, MySQL Group Replication) NoSQL databases often default to eventual consistency for performance (Cassandra, DynamoDB) but may offer stronger options Microservices crossing database boundaries often settle for eventual consistency plus compensating actions The key question: What happens if a user sees stale data? If the answer is 'financial loss, legal liability, or safety risk,' you need strong consistency. If the answer is 'minor user annoyance,' eventual consistency may be acceptable.

Consistency Design Patterns

Several established patterns help maintain consistency in complex systems:

Patterns for Data Consistency

•Single Source of Truth — Designate one authoritative store for each piece of data. Derived data is computed from this source, never edited directly. Eliminates divergence between copies.
•Event Sourcing — Store immutable events rather than mutable state. Current state is computed by replaying events. Provides perfect audit trail and makes consistency violations easier to detect and repair.
•Domain-Driven Design Aggregates — Define consistency boundaries around business entities. All invariants within an aggregate are enforced within a single transaction. Cross-aggregate consistency is eventual.
•Validation Layers — Implement validation at multiple levels: UI (fast feedback), application (business rules), database (non-negotiable constraints). Defense in depth.
•Immutable Data Structures — Instead of updating records, insert new versions. The current version is always the most recent. Simplifies concurrent access and provides built-in history.

aggregate-pattern.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
class Order:
    """
    Order aggregate: consistency boundary for order-related invariants.
    All modifications go through aggregate methods that enforce rules.
    """
    
    def __init__(self, order_id: str, customer_id: str):
        self.order_id = order_id
        self.customer_id = customer_id
        self.items: List[OrderItem] = []
        self.status = 'pending'
        self._total = Decimal('0.00')
    
    @property
    def total(self) -> Decimal:
        """Total is always derived from items—never set directly."""
        return sum(item.quantity * item.unit_price for item in self.items)
    
    def add_item(self, product_id: str, quantity: int, unit_price: Decimal):
        """Add item with validation."""
        if self.status != 'pending':
            raise InvalidOperationError("Cannot add items to non-pending order")
        if quantity <= 0:
            raise ValidationError("Quantity must be positive")
        if unit_price < 0:
            raise ValidationError("Price cannot be negative")
        
        # Check for duplicate product
        existing = next((i for i in self.items if i.product_id == product_id), None)
        if existing:
            existing.quantity += quantity
        else:
            self.items.append(OrderItem(product_id, quantity, unit_price))
    
    def confirm(self):
        """Confirm order with all necessary checks."""
        if self.status != 'pending':
            raise InvalidOperationError(f"Cannot confirm order in status {self.status}")
        if not self.items:
            raise ValidationError("Cannot confirm empty order")
        
        # Invariant: order must have calculated totals
        if self.total <= 0:
            raise ValidationError("Order total must be positive")
        
        self.status = 'confirmed'
    
    # The repository saves the entire aggregate in a single transaction
    # guaranteeing all invariants are satisfied

The Aggregate as Transaction Boundary

In Domain-Driven Design, an aggregate defines the transactional consistency boundary. All invariants within the aggregate are enforced atomically. Changes to different aggregates may have eventual consistency between them. This pattern scales well because it limits the scope of each transaction.

Consistency: Valid State to Valid State

When Databases Lie About Reality

Defining Consistency: Valid States and Invariants

Database Constraints: Declarative Correctness

Constraint Timing: Immediate vs. Deferred

Application-Level Consistency: Beyond Database Constraints

Triggers and Stored Procedures: Server-Side Consistency

Consistency Models: Strong vs. Eventual

Consistency Design Patterns

There’s more to understand.

Consistency: Valid State to Valid State

When Databases Lie About Reality

Defining Consistency: Valid States and Invariants

Database Constraints: Declarative Correctness

Constraint Timing: Immediate vs. Deferred

Application-Level Consistency: Beyond Database Constraints

Triggers and Stored Procedures: Server-Side Consistency

Consistency Models: Strong vs. Eventual

Consistency Design Patterns

There’s more to understand.