ACID: Consistency

Consistency in ACID means that a successful transaction preserves the rules that define valid database state. A transaction begins with valid data and, if it commits, leaves valid data behind. If its changes would violate an enforced rule, the database rejects the statement or transaction.

This definition is narrower than "the data is correct." A database cannot infer every business rule, and it cannot detect that an application charged the wrong price when that value still satisfies every declared constraint. Consistency depends on which invariants the schema and transaction actually enforce.

Describe Invariants Before Tables

An invariant is a rule that must remain true. For a simple order system, examples include:

• every order belongs to an existing customer;
• every order line belongs to an existing order;
• line quantity is greater than zero;
• stored prices are non-negative integers;
• an order number is unique;
• an order status belongs to a known set;
• a paid order has a payment reference.

Writing these rules first makes schema review concrete. The team can decide whether each rule belongs in a type, constraint, transaction, application service, or combination.

Put Stable Data Rules In The Schema

Database constraints protect every writer: the web application, a queue worker, an import script, a maintenance command, and an administrator running SQL.

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    order_number VARCHAR(40) NOT NULL UNIQUE,
    customer_id INTEGER NOT NULL,
    status VARCHAR(20) NOT NULL CHECK (status IN ('pending', 'paid', 'cancelled')),
    payment_reference VARCHAR(100),
    FOREIGN KEY (customer_id) REFERENCES customers(id),
    CHECK (status <> 'paid' OR payment_reference IS NOT NULL)
);

CREATE TABLE order_lines (
    id INTEGER PRIMARY KEY,
    order_id INTEGER NOT NULL,
    product_name VARCHAR(255) NOT NULL,
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    unit_price_pence INTEGER NOT NULL CHECK (unit_price_pence >= 0),
    FOREIGN KEY (order_id) REFERENCES orders(id)
);

This schema expresses required values, uniqueness, valid ranges, relationships, and one conditional rule. It prevents several invalid states without depending on one PHP code path.

Constraint syntax and enforcement differ across engines. SQLite requires foreign-key enforcement to be enabled per connection. MySQL behavior depends on table engine and version. Check production documentation and test migrations against the real engine.

Application Validation Still Matters

A constraint is a final storage boundary, not a complete user experience. PHP should validate input before persistence when it can provide a specific and useful message.

<?php

declare(strict_types=1);

function normaliseQuantity(mixed $value): int
{
    if (!is_int($value) || $value < 1) {
        throw new InvalidArgumentException('Quantity must be a positive integer.');
    }

    return $value;
}

The database still keeps CHECK (quantity > 0). Validation can be bypassed by another writer, contain a bug, or race with another request. The two layers have different responsibilities.

Do not duplicate volatile product policy in a difficult schema constraint merely to move all logic into SQL. Stable structural invariants fit constraints well. Rules depending on external services, changing promotions, or complex workflows often belong in application code, with stored state designed so invalid transitions remain difficult.

Consistency Does Not Mean Correct Business Intent

Suppose PHP accidentally calculates a total of 1,900 pence instead of 1,990. The value is a non-negative integer, so the schema may accept it. ACID consistency has not failed; the application supplied a valid but incorrect value.

Tests, code review, reconciliation, audit records, and domain modeling address correctness beyond schema validity. Avoid claiming that ACID verifies business calculations.

The same issue appears with wrong identifiers. A foreign key proves that customer 42 exists, not that the authenticated user is authorized to place an order for customer 42. Authorization remains an application rule.

Preserve Relationships With Foreign Keys

Foreign keys prevent orphan records and define allowed delete or update behavior. Choose that behavior deliberately.

FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE

Cascade deletion can be correct for rows that have no meaning without their parent, such as temporary order drafts. It may be dangerous for financial or audit records that must be retained. Alternatives include RESTRICT, NO ACTION, or a soft-deletion workflow.

A foreign key also requires compatible column types and an indexed referenced key. It does not ensure that two related rows belong to the same tenant unless tenant ownership is represented in the key or another enforceable rule.

Use Unique Constraints For Real Uniqueness

Checking availability in PHP is not sufficient:

Request A: SELECT finds no user@example.com
Request B: SELECT finds no user@example.com
Request A: INSERT succeeds
Request B: INSERT also attempts to succeed

A unique constraint arbitrates the race. PHP can perform an early check for convenience, but it must still handle the constraint violation from the insert.

Case sensitivity and collation matter. Email identity rules, Unicode normalization, trailing spaces, and database collation can affect what counts as equal. A common design stores a normalized lookup value in a dedicated column and applies the unique constraint there, while retaining a display form separately when needed.

Cross-Column And Cross-Row Invariants

A CHECK constraint can compare columns in one row. The paid-order example requires a payment reference when status is paid. Some databases restrict the expressions allowed, and checks do not normally query other rows.

Cross-row rules need another mechanism. Examples include ensuring account balances sum correctly, preventing overlapping bookings, or allowing only one active subscription per user.

Possible protections include:

• a unique or exclusion constraint;
• an atomic conditional update;
• a transaction with an appropriate lock;
• a carefully reviewed trigger;
• serializable isolation;
• a redesigned schema that makes the invalid state unrepresentable.

Prefer a direct database feature when it clearly expresses the invariant. Application-only read-then-write checks can race.

Keep Derived Data Consistent

Storing both source values and their derived total creates another invariant. If an order line stores quantity, unit price, and line total, then line_total = quantity * unit_price must remain true.

One option is not storing the derived value. Calculate it when reading. Another is a generated column if the database supports the required expression. A third is updating all related values through one controlled transaction and testing the invariant.

Denormalized totals can improve reporting performance, but they increase consistency work. Document the source of truth, update path, repair strategy, and acceptable delay.

Transactions Preserve Multi-Step Rules

Constraints validate individual statements or the final transaction state, depending on the engine and constraint. Transactions let several changes establish a valid result together.

A transfer subtracts from one account and adds to another. A non-negative balance check prevents overdraft, while the transaction keeps both sides together. If the combined-balance invariant is important, the update sequence and audit records must preserve it.

Some databases support deferrable constraints, allowing temporarily inconsistent intermediate statements as long as the constraint is satisfied before commit. This is useful for certain key swaps and circular relationships, but it should be explicit. Most PHP code should avoid unnecessary temporary invalidity.

ACID Consistency Is Not CAP Consistency

Distributed-systems discussions use consistency differently. CAP consistency broadly concerns whether clients observe a single, current view across nodes during network partitions. Replication discussions use terms such as eventual consistency, read-your-writes, monotonic reads, and replication lag.

A primary database can preserve ACID constraints while an asynchronous replica serves stale data. Both statements can be true:

• the committed transaction preserved local database invariants;
• a later read from a replica did not yet include that commit.

When designing read/write splitting, name the requirement. A user redirected immediately after updating their profile may require read-your-writes and should read from the primary or use a consistency token. A public product catalogue may tolerate brief lag.

Handle Constraint Failures At The Right Boundary

PDO commonly reports database constraint violations through PDOException. Driver error codes differ, so portable translation needs care. Do not turn every database exception into "already exists." A connection failure, syntax error, and unique violation have different meanings.

A repository or persistence adapter can translate a known unique violation into an application-specific exception such as EmailAlreadyRegistered. Unexpected errors should retain their diagnostic context and reach logging or error monitoring at the application boundary.

Never expose raw SQL, connection details, or driver messages to users. Return a safe message while preserving structured diagnostics internally.

Repair And Reconciliation Are Still Necessary

Schema constraints prevent newly written invalid states that they cover. They do not automatically repair legacy rows created before a migration, values imported while checks were disabled, or inconsistencies in external systems.

A safe constraint migration often follows this sequence:

1. Measure existing violations.
2. Decide how each category will be repaired or quarantined.
3. Backfill normalized or missing values.
4. Add the constraint using an engine-appropriate online strategy.
5. Validate it.
6. Monitor future failures.

Reconciliation jobs compare related systems or recompute invariants. Payments, inventory, and accounting commonly need them because no single local constraint covers the entire real-world workflow.

Test Consistency At Several Boundaries

Test application validation for useful messages. Test the database constraint directly so a bypassing writer is still rejected. Test a concurrent or duplicate operation where the invariant involves a race.

<?php

declare(strict_types=1);

$pdo = new PDO('sqlite::memory:');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->exec('CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    sku TEXT NOT NULL UNIQUE,
    price_pence INTEGER NOT NULL CHECK (price_pence >= 0)
)');

$insert = $pdo->prepare('INSERT INTO products (sku, price_pence) VALUES (:sku, :price)');
$insert->execute(['sku' => 'BOOK-1', 'price' => 499]);

try {
    $insert->execute(['sku' => 'BOOK-1', 'price' => 599]);
} catch (PDOException $exception) {
    echo 'Duplicate rejected', PHP_EOL;
}

// Prints:
// Duplicate rejected

Do not assert only the error message; driver wording can vary. Assert the resulting rows and, where available, a stable SQLSTATE or application exception.

Review Checklist

For each important rule, ask:

• Is the invariant written in business language?
• Can a schema type or constraint express it?
• Does PHP validation provide a useful early message?
• Can concurrent writers race around the check?
• Does the rule span columns, rows, services, or replicas?
• Is derived data necessary, and what repairs it?
• Are constraint errors translated narrowly?
• Does a migration account for existing violations?
• Is "consistency" being used in the ACID or distributed-systems sense?

What You Should Be Able To Do

After this lesson, you should be able to turn business invariants into appropriate types, constraints, and transaction logic. You should understand why validation and database enforcement complement one another, why unique constraints close races, and why foreign-key behavior must be chosen deliberately.

You should also distinguish ACID consistency from replica and CAP consistency, identify rules that cannot be enforced by a simple row constraint, and design tests and repair processes that prove stored data remains within its declared valid states.