Understanding Consistency in ACID with Simple Examples

When learning the ACID properties of a database, many developers find Consistency confusing. A common misconception is that consistency means “the data should be the same everywhere.” In reality, it means something different.

In this article, we’ll understand what Consistency really means, why it is important, and look at a few simple examples.

---

What is Consistency?

Consistency means that a transaction must follow all the rules defined in the database.

A transaction should move the database from one valid state to another valid state. If a transaction breaks any database rule, the entire transaction fails or is rolled back.

In simple words:

Consistency ensures that the database never stores invalid data.

---

What Does “Valid State” Mean?

A valid state is a database that satisfies all its defined rules, such as:

• NOT NULL constraints
• UNIQUE constraints
• PRIMARY KEY constraints
• FOREIGN KEY constraints
• CHECK constraints
• Business rules

Before the transaction starts, the database is valid.

After the transaction completes successfully, it must still be valid.

---

Example 1: NOT NULL Constraint

Suppose we have an Employee table.

CREATE TABLE Employee (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

Now execute the following query:

INSERT INTO Employee(id, name)
VALUES (101, NULL);

What happens?

The name column is defined as NOT NULL.

Since the transaction is trying to insert a NULL value, it violates the database rule.

The database rejects the transaction.

No row is inserted.

The database remains in a valid state.

---

Example 2: UNIQUE Constraint

Suppose the email column must be unique.

CREATE TABLE Users (
    id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE
);

Existing data:

ID	Email
1	john@example.com

Now execute:

INSERT INTO Users(id, email)
VALUES (2, 'john@example.com');

Since the email already exists, the UNIQUE constraint is violated.

The transaction fails.

The database still contains only valid data.

---

Example 3: Foreign Key Constraint

Suppose we have two tables.

Department
-------------
id
name

Employee
-------------
id
name
department_id

department_id is a foreign key.

Now suppose only departments 1, 2, and 3 exist.

If we try:

INSERT INTO Employee
VALUES (101, 'Prakash', 20);

Department 20 doesn’t exist.

The foreign key rule is violated.

The transaction is rejected.

Again, the database stays consistent.

---

Example 4: Bank Transfer

This is the classic example used in interviews.

Suppose:

Before transaction:

Account A = ₹5000
Account B = ₹3000

Total Money = ₹8000

Transfer ₹1000 from A to B.

After successful transaction:

Account A = ₹4000
Account B = ₹4000

Total Money = ₹8000

The total amount remains unchanged.

The transaction follows the business rule.

The database remains consistent.

Now imagine only the debit operation succeeds.

Account A = ₹4000
Account B = ₹3000

Total = ₹7000 ❌

This is an invalid state.

To prevent this, the transaction is rolled back.

---

What Happens When a Rule is Violated?

Whenever a transaction breaks a database rule:

• The transaction fails.
• The changes are rolled back.
• The database returns to its previous valid state.

This guarantees data integrity.

---

Consistency vs Data Integrity

Many developers use these terms interchangeably.

• Data Integrity is the goal.
• Consistency is the ACID property that helps achieve that goal during transactions.

Consistency ensures that every committed transaction preserves data integrity.

---

Key Points to Remember

• Every transaction must follow all database rules.
• The database moves from one valid state to another valid state.
• Invalid data is never committed.
• If any rule is violated, the transaction is rolled back.
• Consistency protects the integrity of your data.

---

Interview Definition

Consistency is the ACID property that ensures every transaction follows all database constraints, relationships, and business rules. If a transaction violates any rule, it is rolled back, keeping the database in a valid and consistent state.

---

Quick Summary

Before Transaction	Transaction	After Transaction
Database is valid	Rules are checked	Database remains valid

Keyword: Data Integrity

---

Conclusion

Consistency is all about maintaining the correctness of the database. Every transaction must obey the database’s constraints and business rules. If any operation attempts to store invalid data, the database rejects the transaction and restores the previous valid state. This guarantees that only valid and reliable data is ever committed.