Learning the importance of consistency in a DB

After understanding Atomicity, I explored another important concept: Consistency. Consistency ensures that the database always stays in a valid state(i.e no invalid data)

Creating a base 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 ('Christina', 1000), ('Sharon', 500);

Trying to Break the Rule (Negative Balance)

UPDATE accounts
SET balance = balance - 1500
WHERE name = 'Christina';

What Happened?

PostgreSQL rejected it:

ERROR: new row violates check constraint "balance >= 0"

Balance does not change.

Direct Invalid Update

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

Again, it fails due to the CHECK constraint

What I Learned

The rule CHECK (balance >= 0) is enforced by the database schema
PostgreSQL automatically blocks invalid data
This is how Consistency is maintained

DEV Community