After exploring how transactions work for money transfers, this one goes a level deeper. The question here is not just about what happens when a transaction fails, it is about understanding why it fails and who is responsible for catching the problem. Is it the database stopping you or is it your own code?
This distinction matters more than it sounds.
Quick Recap of the 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
);
INSERT INTO accounts (name, balance)
VALUES ('Alice', 1000), ('Bob', 500);
The line that does all the heavy lifting here is CHECK (balance >= 0). That single constraint is PostgreSQL's built in promise that no account will ever hold a negative balance. It does not matter what query you write or who runs it. The database will refuse it.
Test 1 — Directly Setting a Negative Balance
The most straightforward violation. Just try to set Alice's balance to a negative number directly.
UPDATE accounts
SET balance = -100
WHERE name = 'Alice';
PostgreSQL throws an error immediately.
ERROR: new row for relation "accounts" violates check constraint "accounts_balance_check"
Alice's balance stays at 1000. The update never happened. The database caught it before it could touch the data.
Test 2 — Overdrafting Through a Deduction
This is more realistic. Alice has 1000 and we try to deduct 1500 from her.
UPDATE accounts
SET balance = balance - 1500
WHERE name = 'Alice';
PostgreSQL evaluates balance - 1500 which gives negative 500 and immediately checks it against the constraint. It fails. Same error, same result. Alice still has 1000.
The constraint does not care whether you typed a negative number directly or arrived at one through math. If the result is below zero it gets rejected.
Test 3 — Overdraft Inside a Transaction
Now let us put the same bad deduction inside a transaction and see if wrapping it in BEGIN and COMMIT changes anything.
BEGIN;
UPDATE accounts
SET balance = balance - 1500
WHERE name = 'Alice';
UPDATE accounts
SET balance = balance + 1500
WHERE name = 'Bob';
COMMIT;
The first UPDATE fails because of the CHECK constraint. PostgreSQL immediately aborts the transaction. The second UPDATE never even runs. COMMIT does nothing because the transaction is already in a failed state. Everything rolls back.
Alice stays at 1000. Bob stays at 500.
Test 4 — Trying to Force It with a Manual COMMIT After Error
Some people assume that if they just push through and call COMMIT after an error, maybe the second update will save. It does not work that way.
BEGIN;
UPDATE accounts
SET balance = balance - 1500
WHERE name = 'Alice';
-- Error happens here, transaction is now aborted
UPDATE accounts
SET balance = balance + 1500
WHERE name = 'Bob';
COMMIT;
Once PostgreSQL hits an error inside a transaction block the entire session for that transaction is marked as aborted. Every statement after the error returns this:
ERROR: current transaction is aborted, commands ignored until end of transaction block
You cannot sneak anything through after a failure. The only way out is ROLLBACK.
Database Constraint vs Application Logic — The Real Difference
This is the part worth thinking about the most.
The CHECK constraint on balance is enforced by the database itself. It does not matter what application is talking to the database, what language it is written in, or who is running the query. PostgreSQL will always block a negative balance. You cannot bypass it even if you tried.
But there are things the database cannot catch on its own. For example, what if Alice tries to send money to herself? What if someone sends zero rupees? What if the same transfer request comes in twice because of a network retry? These are logic problems that the database schema cannot express as simple constraints. They have to be handled in your application code before the query even runs.
So the right mental model is this. The database is your last line of defence. It catches the things that should never happen under any circumstance. Your application code is the first line of defence. It catches the things that are technically valid SQL but make no sense in your business logic.
Both layers need to exist. Relying only on application logic is dangerous because bugs happen. Relying only on database constraints is not enough because constraints cannot understand your business rules.
What I Took Away
The CHECK constraint feels like a small thing when you first see it but after running these tests it feels essential. It is the kind of protection that works silently in the background and only shows up when something goes wrong, which is exactly when you need it most.
The bigger lesson though is about layers. Real payment systems are not safe because of one clever piece of code. They are safe because multiple layers are each doing their own job. Constraints at the database level, validation at the application level, and transaction blocks tying it all together.
Top comments (0)