What Is Data Consistency And Data Integrity In SQL?

In SQL for the integrity of data, following four conditions must be met:

Atomicity
Atomicity makes the data consistent. It is that either the transaction should be completed or it should not start at all that is it should not hang in the middle.

Consistency
Consistency is that when a transaction is taking place on a database item, then access to it should not be allowed to other users during that transaction.

Isolation
Isolation is that multiple transactions taking place on a single database item should not affect each other.

Durability
Durability is that if through one transaction a database item is updated, added or deleted, it is accessible to everyone. After a transaction completes successfully, it makes that system consistent and if the system crashes then the change in database item is not lost.

If the above listed four conditions are met only then there is data integrity.

In SQL data consistency is that whenever a transaction is performed, it sees a consistent database. During the transaction, if the database is not consistent, then there are two reasons for it, which are as follows:

Database Failure
Hardware failure
Software failure
Multiple Accesses

Data integrity and data consistency are a must for each other, as data integrity ensures data consistency and vice versa.

Data consistency

Execution of transaction in isolation preserves the consistency of the data. The data value stored in the database must satisfy certain consistency constraints. For example the balance on an account should not fall below $100. The DDL commands provide the facilities to specify such constraints. The database systems check these constraints every- time the database is updated. In general it can be an arbitrary predicate pertaining to the database. To check the consistency of database most of programmer depends on the constraints and these are usually costly to test.


Data integrity

The integrity of data refers to as "whole data at one place". The data is in the form of tables and each table has some attributes. These are the cases where we wish to ensure that a value that appears in one relation for given set of attributes should also appear for a certain set of attributes in another relation (referential integrity). Database modification can cause violation of integrity. When the integrity constraint is violated, the normal procedure rejects the action that has actually caused the violation.