SELECT * FROM accounts;
Start by checking the current balances to understand the initial state of the system. This acts as a reference point before performing any operations. Without this, it becomes difficult to verify whether later changes are correct or not. It also helps in spotting unintended modifications after running test queries.
UPDATE accounts
SET balance = balance - 2000
WHERE name = 'Alice';
This operation intentionally tries to deduct more money than Alice has. The goal here is to push the system into an invalid state and see how it reacts. Since the balance would go below zero, it violates the rule defined in the table. This helps confirm whether the database itself enforces constraints or blindly accepts updates.
SELECT * FROM accounts;
After attempting the invalid update, checking the table again helps verify what actually happened. If the constraint is working correctly, the update should not have been applied at all. This step confirms whether the system prevents invalid data or allows partial corruption.
UPDATE accounts
SET balance = -100
WHERE name = 'Bob';
This directly assigns a negative value instead of calculating it. It skips any logical checks and forces a clearly invalid state. The idea is to test whether the database constraint alone is strong enough to block such direct manipulation. It ensures that even careless or malicious updates cannot break the data integrity.
The CHECK (balance >= 0) constraint prevents negative values. PostgreSQL immediately throws an error when this rule is violated. The update never gets committed, which keeps the data safe.
BEGIN;
UPDATE accounts
SET balance = balance - 2000
WHERE name = 'Alice';
COMMIT;
Here the same invalid operation is wrapped inside a transaction block. This tests whether constraints still apply within transactions. Even though transactions group multiple steps, the database should still reject invalid operations. If any statement fails, the entire transaction should not be committed.
SELECT * FROM accounts;
After the failed transaction, checking the table confirms that no changes were applied. Alice’s balance should remain the same as before. This proves that the system avoids partial updates and maintains consistency even when errors occur mid-process.
UPDATE accounts
SET balance = balance - 200
WHERE name = 'Alice' AND balance >= 200;
This introduces a safer way to handle updates by adding a condition. Instead of relying only on database constraints, the query itself ensures that the operation is valid before executing. This reduces the chances of hitting errors and improves efficiency. It shows how application-level logic can prevent bad queries from even reaching the constraint stage.
Constraints act as a final safety net at the database level. They guarantee that invalid data never gets stored. On the other hand, conditions in queries or application logic help avoid triggering those errors in the first place, making the system smoother.
SELECT * FROM accounts;
Finally, checking the table again ensures everything is still consistent. All balances should be valid and non-negative. This confirms that both database constraints and careful query design are working together to maintain data integrity.
Top comments (0)