Consistency in ACID means that integrity constraints must be maintained so that the table is consistent before and after the transaction. Integrity Constraints ensure that data is consistent and correct. Here are some kinds of integrity constraints,
- Domain Integrity: This ensures all the attributes in a table contain correct data.
- Entity Integrity: This ensures entity can be uniquely identify by a primary key.
- Referential Integrity: This ensures two tables can be connected through foreign key.
With the help of this we can ensure our data table is consistent. Let’s explore consistency with two kinds of example,
Consistency in data
You have two table users and payments. users table,
| id | name |
| 2442 | Mizanur Lahin |
| 2443 | Meherun Akter Runa |
payments table,
| id | user | amount |
| 3345 | 2442 | 120 |
| 3345 | 2443 | 150 |
| 3346 | 2444 | 200 |
As you can see, the id of the 2444 user does not exist in the users table, that means payments table is not consistent. Reason is referential integrity not maintained properly.
Consistency in read
In that case, think of database replication. You have one master database and several read-replica database. When something is changed in the master database, before putting new value into the replica databases, a transaction happens to a replica database. That time due to the increase in replication lag, the inconsistency happened.
We can fix that with, Synchronous Replication and Asynchronous Replication.