ASSIGNMENT 35

*Using the given accounts table, test how PostgreSQL maintains data consistency by attempting operations that violate rules such as making the balance negative. Perform invalid updates like deducting more money than available or setting a negative balance, observe the errors produced, and explain whether these failures are due to database constraints or transaction logic. Finally, analyze how consistency is ensured and differentiate between rules enforced by the database schema and those handled at the application level.
*

Introduction

In a digital wallet system (like PhonePe/GPay/Paytm), consistency ensures that the database always remains in a valid state.

For example:

A user should never have a negative balance
Invalid transactions must be rejected
Only valid data should be stored

PostgreSQL enforces this using constraints, while additional rules are handled through transaction logic.

Given Table

Code

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

Key Rule

CHECK (balance >= 0)

Ensures balance is never negative

Step 1: Insert Dummy Data

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

Step 2: Try Invalid Operation (Over-Deduction)
Code

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

Explanation

Deduction makes balance = -500
Violates CHECK (balance >= 0)

Database blocks the update

Step 3: Directly Setting Negative Balance

Code

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

Some rules must be handled manually:

Example (Safe Transfer Logic)
BEGIN;

• Check balance first

SELECT balance FROM accounts WHERE name='Alice';

• Only proceed if sufficient balance UPDATE accounts SET balance = balance - 500 WHERE name='Alice' AND balance >= 500;

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

COMMIT;

Final Analysis

Observations

PostgreSQL prevents invalid states using constraints
Invalid operations are rejected immediately
Database ensures consistency at data level

Limitations

Database doesn’t understand business logic fully
Needs application checks for:
Sufficient balance before transfer
Fraud detection
Transaction validation

Conclusion

Consistency is a shared responsibility

Database --> enforces structural rules
Application --> enforces logical rules