Consistency

Today I explored how databases maintain correct data using consistency rules, especially in a wallet system like PhonePe or GPay.

I used the same accounts table where Alice has 1000 and Bob has 500. One important thing in this table is the condition that balance should never be negative. This rule is already defined in the table using a check condition.

First, I tried a normal update where I deduct a valid amount.

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

This worked fine because Alice still had a positive balance.

Then I tried something wrong. I attempted to deduct more money than Alice has.

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

This time, PostgreSQL did not allow the update. It gave an error because the balance would become negative. This showed that the database itself is enforcing the rule.

Next, I tried directly setting a negative balance.

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

Again, the database rejected the query. This is because of the check constraint defined in the table. So even if I try to manually break the rule, the database does not allow it.

After this, I understood that consistency is maintained in two ways.

One way is through database constraints. In this case, the check condition ensures that balance is always zero or positive. This is handled directly by PostgreSQL.

Another way is through application logic or transactions. For example, before transferring money, the application should check if the sender has enough balance. This avoids unnecessary errors and improves user experience.

So, constraints act as a safety layer, while application logic acts as a control layer.

I clearly understood that PostgreSQL does not allow invalid data to enter the system. Even if a developer makes a mistake, the database prevents wrong updates.

Overall, this helped me understand how consistency is maintained and why it is very important in financial systems where even a small mistake can cause serious problems.