consistency

Let us start with the accounts table, which stores user balance information.

CREATE TABLE accounts (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
balance INT NOT NULL CHECK (balance >= 0),
last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Here, the most important rule is the CHECK constraint on balance. It ensures that the balance can never go below zero. This is a database-level safety mechanism that protects against invalid data.

Now, insert some sample data.

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

At this stage, both users have valid balances, and the system is consistent.

Now consider a situation where Alice tries to send more money than she has. For example, deducting 1500 from Alice’s account.

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

PostgreSQL will reject this operation because it violates the CHECK constraint (balance >= 0). The database will throw an error and prevent the update from happening. This shows that the database itself enforces rules to maintain valid data.

Next, consider directly trying to set a negative balance.

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

Again, PostgreSQL will block this operation because it breaks the CHECK constraint. This confirms that constraints act as a safety net at the database level.

However, not all problems can be solved using constraints alone. Consider a money transfer between two users. A transfer involves two steps: deducting money from one account and adding it to another. If one step succeeds and the other fails, the system becomes inconsistent.

To handle this, we use transactions.

BEGIN;
UPDATE accounts SET balance = balance - 300 WHERE name = 'Alice';
UPDATE accounts SET balance = balance + 300 WHERE name = 'Bob';
COMMIT;

If both operations succeed, the transaction is committed and the system remains consistent. But if any error occurs in between, we can roll back the entire transaction.

ROLLBACK;

This ensures that either both operations happen or none happen, preventing partial updates.

Now consider a failure scenario where the first query runs but the second fails due to some error. Without a transaction, Alice’s balance would be reduced but Bob would not receive the money. This leads to money loss. With transactions, the system automatically rolls back and restores the original state.

From these observations, we can clearly distinguish two types of rules.

Application or transaction-level rules handle complex operations like money transfers. These ensure that multiple related operations are executed safely and completely.