Consistency

Understanding Consistency through a Simple Wallet System

Introduction

When we use payment apps, we always expect our balance to be correct. We never imagine seeing a negative balance or invalid data in our account.

Behind the scenes, the database ensures that all data always follows certain rules. This is called Consistency in ACID properties.

Consistency ensures that the database always remains in a valid state. If any operation tries to break the rules, the database rejects it.

To understand this better, I experimented with a simple wallet system using PostgreSQL and tried performing operations that violate these rules.

Setting up the table

I started by creating a basic table to store account details.

CREATE TABLE accounts (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
balance INT NOT NULL CHECK (balance >= 0),
last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Then I inserted two users:

INSERT INTO accounts (name, balance)
VALUES
('Alice', 1000),
('Bob', 500);

Checking the table:

SELECT * FROM accounts ORDER BY id;

At this point:

• Alice has 1000
• Bob has 500

The important rule here is:

CHECK (balance >= 0)

This ensures that no account can have a negative balance.

Trying a valid update

First, I performed a normal valid operation.

UPDATE accounts
SET balance = balance - 200
WHERE id = 1;

Checking the table:

SELECT * FROM accounts ORDER BY id;

Now:

• Alice has 800
• Bob has 500

This works because the balance is still valid (not negative).

Resetting the data

Before testing invalid cases, I reset the balances.

UPDATE accounts SET balance = 1000 WHERE id = 1;
UPDATE accounts SET balance = 500 WHERE id = 2;

Trying to break consistency (negative balance)

Now I tried to directly assign a negative balance.

UPDATE accounts
SET balance = -100
WHERE id = 1;

This resulted in an error because the database does not allow values that violate the constraint.

After checking the table:

SELECT * FROM accounts ORDER BY id;

• Alice still has 1000
• Bob still has 500

The update was rejected completely.

Trying to deduct more than available balance

Next, I tried to deduct more money than Alice actually has.

UPDATE accounts
SET balance = balance - 1500
WHERE id = 1;

This again resulted in an error.

Checking the table:

SELECT * FROM accounts ORDER BY id;

• Alice = 1000
• Bob = 500

The database did not allow the balance to go negative.

Trying the same inside a transaction

I also tested the same scenario inside a transaction.

BEGIN;

UPDATE accounts
SET balance = balance - 1500
WHERE id = 1;

This failed immediately.

Then I ran:

ROLLBACK;

Checking the table again:

SELECT * FROM accounts ORDER BY id;

• Alice = 1000
• Bob = 500

Even inside a transaction, the constraint prevented invalid data.

What I observed

From these tests, I understood that:

• The database enforces rules defined in the schema
• Invalid updates are rejected immediately
• The balance never becomes negative
• The database always maintains a valid state

However, I also noticed that:

• The database only checks constraints like balance >= 0

• It does not automatically handle business rules like:

  • validating transfer amount
  • ensuring sender and receiver exist
  • preventing incorrect transfers

These must be handled using application logic or transactions.

Conclusion

This experiment helped me understand how consistency is maintained in a database system.

Consistency ensures that the database always remains in a valid state by enforcing rules like constraints. Even if an invalid operation is attempted, the database prevents it from being applied.

In real-world systems like digital wallets, this plays a crucial role in preventing incorrect balances and maintaining data integrity.

While constraints ensure data validity, additional logic is required to enforce complete business correctness.

Consistency, along with other ACID properties, forms the foundation for building reliable financial systems.