Design a Reliable Wallet Transfer System with ACID Guarantees pt - 2 (Consistency)

Consistency - ensures the database always moves from one valid state to another while follow the rules, constraint, etc

• the 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 );
  the accounts table is created successfully

• then the dummy data is added to the table
  INSERT INTO accounts (name, balance) VALUES ('Alice', 1000), ('Bob', 500);

• Now the initial balance in both accounts are Alice=1000 & Bob=500

• to try test if the consistency(balance >= 0) works properly
  UPDATE accounts
  SET balance = -100
  WHERE name = 'Alice';
  the above query will throw an error, and the update will be rejected.

• to try test if a amt more than balance can be deducted
  BEGIN;

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

COMMIT;
If query fails because resulting balance would be negative then consistency works properly

• Consistency is maintained through two layers: Database-Level Constraints Application / Transaction Logic

Consistency guarantees that every transaction respects the rules of the system.