Consistency

Consistency

Consistency:

Consistency ensures that after every operation the database follows all rules and remains in a valid state.

CREATE 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 THE VALUES:

INSERT INTO accounts (name, balance) VALUES ('Alice', 1000), ('Bob', 500);

SHOW THE TABLE:

SELECT * FROM accounts;

Why this query:

id SERIAL - auto increment (1, 2, 3…)
name - user name
balance >= 0 - NO negative money
last_updated - automatically time save
Then we insert the values:
Alice - 1000
Bob - 500
This data will be used for testing money transfer transactions.

TRANSACTION:

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

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

Error:

Why this query:

The query fails because of the CHECK constraint (balance >= 0).
You can't the insert the negative value.
So the database does not allow invalid data.

BEGIN;

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

UPDATE accounts SET balance = balance + 200 WHERE name = 'Bob';

COMMIT;