Skip to content

Safe Bank Transfer with Transactions

Medium ~20 min

What you'll build

A SQL script that creates an accounts table and performs a safe money transfer between two accounts using a transaction with proper error handling.

Goals

  • Create an accounts table with balance tracking
  • Use BEGIN and COMMIT to wrap a transfer
  • Use ROLLBACK to undo on failure
  • Add a CHECK constraint to prevent negative balances

Prerequisites

  • INSERT and UPDATE basics
  • Understanding of transactions
  • ACID properties

Steps

  1. 1

    Create the accounts table

    Create an accounts table with a balance column that cannot go negative (CHECK constraint).

    Code
    CREATE TABLE accounts (
      id       SERIAL PRIMARY KEY,
      name     TEXT NOT NULL,
      balance  NUMERIC(10,2) NOT NULL DEFAULT 0 CHECK (balance >= 0)
    );

    Expected result

    The accounts table is created with a non-negative balance constraint.

  2. 2

    Insert accounts

    Insert two accounts: one with $1000 and one with $500.

    Code
    INSERT INTO accounts (name, balance) VALUES
      ('Alice', 1000.00),
      ('Bob', 500.00);

    Expected result

    Two accounts are created with starting balances.

  3. 3

    Perform a safe transfer

    Transfer $300 from Alice to Bob in a transaction. If either update fails, the whole transaction rolls back.

    Code
    BEGIN;
    
    -- Deduct from Alice
    UPDATE accounts SET balance = balance - 300.00 WHERE name = 'Alice';
    
    -- Add to Bob
    UPDATE accounts SET balance = balance + 300.00 WHERE name = 'Bob';
    
    -- Verify: total should be unchanged
    SELECT SUM(balance) AS total FROM accounts;
    
    COMMIT;

    Expected result

    Alice's balance is $700, Bob's is $800, total is still $1500.

  4. 4

    Handle a failed transfer

    Try to transfer $2000 from Alice (who only has $700 now). The CHECK constraint prevents the negative balance, so we roll back.

    Code
    BEGIN;
    
    UPDATE accounts SET balance = balance - 2000.00 WHERE name = 'Alice';
    -- This fails: CHECK (balance >= 0) violated!
    
    -- Roll back to undo any changes
    ROLLBACK;
    
    -- Verify balances are unchanged
    SELECT name, balance FROM accounts;

    Expected result

    The transaction is rolled back. Balances remain $700 and $800.

  5. 5

    Add indexes to optimize queries

    Create indexes on frequently queried columns to improve query performance. Indexes are the primary means of database performance optimization, but be mindful of write overhead.

    Code
    -- Create indexes on frequently filtered columns
    CREATE INDEX idx_users_email ON users(email);
    CREATE INDEX idx_orders_user_id ON orders(user_id);
    CREATE INDEX idx_orders_created_at ON orders(created_at DESC);
    
    -- Verify the index is used
    EXPLAIN SELECT * FROM orders WHERE user_id = 42;

    Expected result

    EXPLAIN output shows an index scan using idx_orders_user_id instead of a full table scan.

Try it yourself

What you learned

You implemented a safe financial transaction using BEGIN, COMMIT, and ROLLBACK, and saw how CHECK constraints protect data integrity — the foundation of ACID-compliant systems.

Related Resources

Related Lessons

Related Glossary

Related Glossary

A beginner-friendly glossary explains jargon in plain English — variables, functions, DOM, Promise, and more.

Decode error messages

Plain-English explanations of common errors — what they mean, why they happen, and how to fix them.

View errors

← Back to topic