CA 35 – Consistency (ACID)

This exercise was about Consistency in databases using the same wallet system accounts table. Atomicity was about all or nothing transactions, but Consistency is about making sure the data always follows the rules defined in the database. The database should never go into an invalid state.

In our accounts table we already have a constraint:

balance INT NOT NULL CHECK (balance >= 0)

This means balance should never become negative. So the database itself is enforcing a rule.

First I checked the initial data.

SELECT * FROM accounts;

Alice -> 1000
Bob -> 500

Everything is valid.

Then I tried to deduct more money than Alice has.

BEGIN;

UPDATE accounts
SET balance = balance - 1500
WHERE name = 'Alice';

COMMIT;

This failed and PostgreSQL showed an error saying the check constraint failed because balance cannot be negative. So the database did not allow the update. This shows consistency is maintained by constraints.

Then I tried directly setting a negative balance.

UPDATE accounts
SET balance = -200
WHERE name = 'Bob';

Again this failed because of the CHECK constraint. So we cannot manually put invalid data also.

Then I tested inside a transaction.

BEGIN;

UPDATE accounts
SET balance = balance - 200
WHERE name = 'Alice';

UPDATE accounts
SET balance = balance + 200
WHERE name = 'Bob';

COMMIT;

This worked because no rule was broken and balances were still valid.

Then I understood something important. Consistency is maintained in two ways:

Database level (constraints like CHECK, NOT NULL, PRIMARY KEY)
Application level (logic like cannot transfer if balance is low)

For example database prevents negative balance using CHECK constraint. But database will not automatically check if Alice has enough balance before transfer unless we write logic for that in transaction or application code.

So consistency means before transaction and after transaction, the database must be in a valid state and all rules must be satisfied.