Database consistency

From ArticleWorld

A database system is said to be in a consistent state if it satisfies all known integrity constraints. The term integrity refers to the accuracy or correctness of data in the database. Two famous kinds of integrity constraints are entity integrity constraints (no primary key value can be null) and referential integrity constraints (a tuple in one relation that refers to another relation must refer to a tuple that exists in that relation).

Difference between consistency and correctness

It should be noted that consistent does not necessarily mean correct; however, a database that is in a correct state will always be consistent. A database is in a correct state if it is both consistent and if it accurately reflects the true state of affairs in the real world. We may also say that consistency is correctness only as far as the system is concerned.

The following example illustrates the difference: If a student's grade is erroneously entered as 'C' instead of 'A', the data is incorrect, but is consistent because the DBMS sees all integrity constraints being satisfied. Incorrect data such as these can only be detected manually – in this case the student may complain – and corrected later by updating the database. If the grade is entered as 'Z', then this error can be detected by the system because 'Z' is not a valid value for grade and hence an integrity constraint is violated.

Consistency as a desirable property of transactions

Consistency is one of the four desirable properties of transactions (logical units of work). The four desirable properties are mentioned below:

1. Atomicity: Transactions are atomic in the sense that they are either performed entirely or not performed at all.
2. Consistency: A transaction is said to be consistency preserving if its complete execution takes the database from one consistent state to another. That is, a database already in a consistent state is transformed to another consistent state at the end of the transaction. However, consistency may not be preserved at intermediate points during the execution of the transaction.
3. Isolation: If transactions execute simultaneously, they should execute in such a way that any given transaction's updates are concealed from all the others, until that transaction finally commits.
4. Durability: Any kind of failure should not cause committed transaction data to be erased.

These four properties are often referred to as the ACID properties.