Safe Bank Transfer with Transactions
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
Create the accounts table
Create an accounts table with a balance column that cannot go negative (CHECK constraint).
CodeCREATE 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
Insert accounts
Insert two accounts: one with $1000 and one with $500.
CodeINSERT INTO accounts (name, balance) VALUES ('Alice', 1000.00), ('Bob', 500.00);Expected result
Two accounts are created with starting balances.
- 3
Perform a safe transfer
Transfer $300 from Alice to Bob in a transaction. If either update fails, the whole transaction rolls back.
CodeBEGIN; -- 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
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.
CodeBEGIN; 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
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
- Transactions
Learn about transactions.
- ACID Properties
Learn ACID guarantees.
Related Glossary
- Transaction
All-or-nothing operations.
- ACID
Transaction guarantees.
- ROLLBACK
Undoing transactions.
Related Glossary
A beginner-friendly glossary explains jargon in plain English — variables, functions, DOM, Promise, and more.
- Transaction
All-or-nothing operations.
- ACID
Transaction guarantees.
- ROLLBACK
Undoing transactions.
Decode error messages
Plain-English explanations of common errors — what they mean, why they happen, and how to fix them.