What is Consistency in DBMS: Definition and Examples

In DBMS, consistency means that a database remains in a valid state before and after a transaction. It ensures that all rules, constraints, and data integrity are preserved, so no invalid data is saved.

How It Works

Consistency in a database means that any transaction you perform must take the database from one valid state to another valid state. Imagine a bank account: if you transfer money, the total amount before and after the transfer should still be correct and follow all rules, like not allowing negative balances.

When a transaction starts, the database checks all rules like unique IDs, foreign keys, or data types. If the transaction tries to break any rule, it will be stopped and undone, so the database stays consistent. This way, the data is always reliable and trustworthy.

Example

This example shows a simple bank transfer where consistency ensures the total money stays the same.

sql

BEGIN TRANSACTION;

-- Deduct 100 from Alice's account
UPDATE accounts SET balance = balance - 100 WHERE name = 'Alice';

-- Add 100 to Bob's account
UPDATE accounts SET balance = balance + 100 WHERE name = 'Bob';

-- Check if any balance is negative
IF EXISTS (SELECT * FROM accounts WHERE balance < 0)
BEGIN
    ROLLBACK TRANSACTION;
    PRINT 'Transaction failed: Negative balance not allowed';
END
ELSE
BEGIN
    COMMIT TRANSACTION;
    PRINT 'Transaction successful';
END;

Output

Transaction successful

When to Use

Consistency is crucial whenever you want to keep your data accurate and reliable. Use it in banking systems, online shopping carts, or any system where data rules must never be broken.

For example, in an e-commerce site, consistency ensures that product stock counts never go below zero and orders are recorded correctly. It helps avoid errors like selling more items than available or losing customer data.

Key Points

• Consistency ensures database rules and constraints are always met.
• It prevents invalid or corrupt data from being saved.
• Transactions either fully succeed or fail to keep data valid.
• It is part of the ACID properties in databases.

Key Takeaways

Consistency guarantees the database stays valid before and after transactions.

It enforces all data rules and constraints to prevent errors.

Transactions that break rules are rolled back to keep data reliable.

Consistency is essential for trustworthy and accurate database operations.