Consistency of Database

It's time discuss another ACID property : Consistency

As mentioned in older post, Consistency ensures that the database always stays in a valid state before and after each transaction with respect to the rules and constraints of the database.

To understand it more clearly, we will take an example of database of company which stores the data related to his employees in EMP table.

Now, clearly this company is strict about the age of its employees. It wants its employees to be of age >= 17 and less than 50.No one can work in the company whose age is out of this range.  So, there is a constraint applied over the table as highlighted yellow in the picture.

Now if the database is consistent, it must satisfy this constraint.And Any transaction or data operation which violated this state should de failed or undone(rolled back).

e.g. If we try to insert a record into the EMP table where age is out of this range like 15 or 55, it must fail or roll-back the running transaction/data operation.In other words, the database is consistent with the rules/constraints of database.

Here is a good article explaining this topic and other ACID rules of Database.

The ACIDic nature of a Database

Now, this concept is not specific to relational database, but it must be known as it's a basic principle applied practically on all kinds of databases. So, here we go. Let's discuss some ACIDic properties of a database.

Well just to have a clear start, we are not talking about stuff which somehow relates to "this".

The four letters of the word ACID denote the set of four properties.

[You can click on them to see more on each properties in my other posts.]

• Atomicity It means that a database is said to be atomic if each transaction on the database follows  "all or none" rule.
• Consistency It means that a database is consistent (in a valid state) before and after each transaction with respect to the rules and constraints applied on the database.
• Isolation Any two or more transactions on a database are isolated from each other. i.e. they don't interfere with each other.
• Durability This property ensures that any transaction committed on the database is not lost in case of events like error, power crash, etc.