Consistency

#database #postgres #sql

SELECT * FROM accounts;

Start by seeing current balances to know the baseline. This helps compare what changes after each operation.

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

This attempts to reduce balance below zero. It tests whether the database allows invalid balance updates.

SELECT * FROM accounts;

Check if the update actually went through. The expectation is that constraint should block it.

UPDATE accounts 
SET balance = -100 
WHERE name = 'Bob';

sql

This bypasses calculation and forces an invalid state. Helps confirm if schema-level checks are enforced.

The CHECK (balance >= 0) constraint prevents negative values. PostgreSQL throws an error before committing the change.

BEGIN;

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

COMMIT;

Even inside a transaction, constraint violation should stop execution. Transaction will not commit successfully.

SELECT * FROM accounts;

Balances should remain unchanged since invalid operation failed. Confirms no partial updates happened.

UPDATE accounts 
SET balance = balance - 200 
WHERE name = 'Alice' AND balance >= 200;

Adds logic to prevent invalid deduction before it happens. This is handled at query/application level.

Constraints act as a final safety net at database level. Application logic prevents bad operations earlier, reducing errors.

SELECT * FROM accounts;

Ensure all balances are valid and non-negative. Confirms system maintains consistency through both rules and checks.

DEV Community