Ensuring Data Consistency in a Digital Wallet System
In this experiment, I focused on understanding how a database maintains data consistency, especially in a financial system like a digital wallet (PhonePe/GPay type). The key goal was to ensure that invalid states — such as negative account balances — are never allowed.
Initial Setup
I used the same accounts table with a constraint to prevent negative balances:
```sql id="a1b2c3"
CREATE TABLE accounts (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
balance INT NOT NULL CHECK (balance >= 0),
last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO accounts (name, balance)
VALUES
('Alice', 1000),
('Bob', 500);
To verify:
```sql id="d4e5f6"
SELECT * FROM accounts;
Initial balances:
- Alice → 1000
- Bob → 500
Attempt 1: Deduct More Than Available Balance
I tried to deduct more money than Alice has:
```sql id="g7h8i9"
UPDATE accounts
SET balance = balance - 1500
WHERE name = 'Alice';
---
### Observation
PostgreSQL throws an error like:
```plaintext
ERROR: new row for relation "accounts" violates check constraint "accounts_balance_check"
Observation
Again, PostgreSQL prevents the update with a constraint violation.
✔ No invalid data is stored
✔ Database enforces the rule strictly
Attempt 3: Inside a Transaction
```sql id="m4n5o6"
BEGIN;
UPDATE accounts
SET balance = balance - 2000
WHERE name = 'Alice';
COMMIT;
---
### Observation
The transaction fails and is **automatically rolled back**
✔ No partial update
✔ Balance remains valid
---
### Key Observations
* The database **does not allow negative balances**
* All invalid operations are **blocked immediately**
* Errors occur due to **CHECK constraint**, not transaction logic
---
### Understanding Consistency
This demonstrates the **Consistency** property of ACID:
* Database always moves from one valid state to another
* Invalid states (like negative balance) are never allowed
* Constraints ensure rules are enforced automatically
---
### Schema-Level vs Application-Level Rules
#### Enforced by Database (Schema Level)
* `CHECK (balance >= 0)`
* Prevents negative balances
* Automatically enforced by PostgreSQL
#### Must be Handled by Application
* Preventing overdraft before transaction
* Validating user input
* Avoiding duplicate transactions
* Business rules like daily transfer limits
---
### Conclusion
Through this experiment, I observed that PostgreSQL strictly enforces data integrity using constraints. Even when I attempted invalid operations, the database rejected them and maintained a consistent state.
This highlights the importance of combining **database-level constraints** with **application-level logic** to build reliable systems. In real-world financial applications, both layers work together to ensure that data remains accurate, secure, and consistent at all times.
Top comments (0)