What is Database Consistency?

Database consistency governs the most crucial aspects of the database, availability of correct information at the right time by the validation rules specified in the beginning is what decides the data integrity, and accuracy of the data stored.

In this article, we’ll be exploring consistency in the context of a relational database, why it’s crucial to be maintained, its real-world examples, and what features of an RDBMS help maintain a consistent database.

What is Consistency in Database?

Consistency refers to the correctness & presence of the most recently updated data at any given moment in the database. The idea of having all-time access to purely consistent data is at the core of every relational database, it helps in maintaining the data integrity & accuracy as well.

Consistency implies that any changes made in the database must adhere to the rules specified in the beginning depending on the constraints, cascades, triggers, etc. It also means that consistency will enforce a mechanism where any changes made in any replica of the database, will be mirrored in all other replicas to ensure the correctness and accuracy of the database even if it’s accessed in any region of the world.

Types of Consistency

Depending on the use case, there are mainly two types of consistency parameters for every type of database

1. Strong Consistency

Strong consistency refers to the AVAILABILITY of the correct & accurate data to all the users interacting with the database at any given moment of time from any part of the world. In this, all the nodes (or replicas) will contain exactly the same data at any given time. This type of consistency is facilitated in SQL Database which functions of ACID Properties.

Example: Banking Application where real-world money is credited and debited in real-time and must be shown accurately to the user.

2. Weak Consistency

Weak consistency refers to the “eventually accurate information” but doesn’t guarantee its correctness immediately unlike Strong consistency. In this, it’s not always guaranteed, that the primary replica will have the same updated information just like other replicas.

If your use case doesn’t deal with real-time data analysis or requires your users to have access to partially correct information for a while, you can opt for NoSQL Databases that work on BASE Properties.

Constraints in RDBMS for maintaining consistency

1. Check Constraint: Check Constraint in database ensures that any information before getting inserted adheres to the rules applied to it, there by ensuring the correctness of the data.
Ex : A value of age column must be in some range (greater than 0, less than 60, etc.)

2. Key Constraint: Key constraint generally ensure the uniqueness of the database, and maintain its integrity by preventing the insertion of redundant data. There are different types of key constraints : Primary Key, Foreign Key, Composite key etc.

3. Cascade: Cascade option is associated with the foreign key constraint & it is the mechanism implemented to ensure a specific action (typically update or delete) is taken automatically just in case related records undergo some changes. For example, if there are two entities ‘Post‘ and ‘Comments’ made on that post, then if a post is deleted than all the comments associated with that post must also be deleted from the database, to ensure consistency & avoid redundancy.

4. Trigger: Trigger as its name indicates are nothing but SQL queries that are executed automatically in reaction to specific events or actions typically when new data is inserted, updated or deleted.

5. Atomicity: Any transaction taking place in a database is always going to be FULLY executed or it won’t be executed at all. For example, below scenarios will never take place while making a transaction form one account to another.

• Amount is debited from one account but is never credited to another.
• Amount is credited from one account but was never debited from another.

Real World Example of Consistency

Let’s look at the real world example of how a transaction is made in a bank to ensure data consistency across both the accounts.

Consistency

Step-1: Before transaction 1, the total amount in Account-A is 1000 and in Account-B is also 1000.S
Step-2: After Transaction 1, the total amount in Account-A will be 800 and in Account-B its 1200. As 200 is sent to Account-B in Transaction-1.
Step-3: Similar steps happen in Transaction-2.

Frequently Asked Questions on Consistency in DBMS – FAQs

How does constraints helps in maintaining consistency?

Constraints enforces rules and validation that any data before insertion or updation will be checked if it adheres to these rules or not. Thereby, defining consistency & upholding data integrity.

What is eventual consistency?

Eventual consistency refers to the behaviour of data to be “eventually consistent” (after some time) across all the nodes in different region rather than immediately reflecting the newly made changes.

How is consistency managed across ACID and BASE based databases?

ACID based database are best utilised when your use case deals with real time data and any changes made, or newly inserted must be immediately reflected to the user, typically where there cannot be any room for error. ACID Properties provides atomicity, consistent state, Isolated execution and durable property for every transaction taking place in it, to maintain data integrity.

BASE based database are best utilised when our business use doesn’t involve updates to be reflected in all the nodes of our database immediately. Its main idea for consistency is: “Eventual consistency”, and not “immediate” .