Consistency

In this task, I explored the Consistency property of ACID using a simple wallet system. In payment applications like PhonePe, GPay, or Paytm, the data must always remain correct and valid. If consistency is not maintained, it may lead to negative balances, money loss, or incorrect account details.

Consistency means that the database should always move from one valid state to another valid state. It should never allow invalid data to be stored.

To understand this, I used the following accounts table:

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

CHECK (balance >= 0)
This rule ensures that no account balance can become negative.

Then I inserted sample data into the table.

INSERT INTO accounts (name, balance)
VALUES
('Alice', 1000),
('Bob', 500);
To see the current data, I ran:

SELECT * FROM accounts;
At this stage, Alice had 1000 and Bob had 500.

Testing Consistency by violating the rule
First, I tried to directly make Alice’s balance negative.

UPDATE accounts
SET balance = -200
WHERE id = 1;
PostgreSQL did not allow this update. It gave an error because the CHECK (balance >= 0) constraint was violated.

This shows that the database itself is protecting the data and not allowing an invalid state.

Trying to deduct more money than available
Next, I tried to deduct more money than Alice actually had.

UPDATE accounts
SET balance = balance - 2000
WHERE id = 1;
Since Alice had only 1000, this operation would make her balance negative. Again, PostgreSQL rejected the update because of the check constraint.

Testing inside a transaction
I also tested the same case inside a transaction block.

BEGIN;

UPDATE accounts
SET balance = balance - 2000,
last_updated = CURRENT_TIMESTAMP
WHERE id = 1;

UPDATE accounts
SET balance = balance + 2000,
last_updated = CURRENT_TIMESTAMP
WHERE id = 2;

COMMIT;
In this case, the first update itself failed because it would make Alice’s balance negative. Since the transaction failed, the database did not move to an invalid state.

When I checked the table again:

SELECT * FROM accounts;
the balances remained unchanged.

Handling consistency at application level
Even though the check constraint prevents negative balances, some rules should still be handled in the transaction logic or application code.

For example, before deducting money, we should verify that the sender has enough balance.

BEGIN;

UPDATE accounts
SET balance = balance - 200,
last_updated = CURRENT_TIMESTAMP
WHERE id = 1
AND balance >= 200;

UPDATE accounts
SET balance = balance + 200,
last_updated = CURRENT_TIMESTAMP
WHERE id = 2;

COMMIT;
Here, the condition:

AND balance >= 200
acts as a logical check before updating the account.