Are there any difference between data integrity and data consistency?

Question

I'm a little confused about data consistency and data integrity. From Oracle Database Concepts:

data integrity
--------------
Business rules that dictate the standards for acceptable data. These rules
are applied to a database by using integrity constraints and triggers to
prevent invalid data entry.

From Wikipedia

Consistency
-----------
Consistency states that only valid data will be written to the database.

So what's the difference between data consistency and data integrity?

Thanks in advance.

Answer 1

They are not only different, they are orthogonal.

Inconsistency:
A DB that reported employee Joe Shmoe's department as Sales but that didn't list Joe Shmoe among the employees in the Sales department would be inconsistent.
It's a logical property of the DB, independent of the actual data.

Integrity:
A DB that reported jOe SaleS to be a member of the Shmoe department would lack integrity.
jOe SaleS isn't a valid employee name and Shmoe isn't a valid department.
That's not logically invalid, but it is invalid relative to the rules that govern data content.

Answer 2

Data can be entirely consistent and entirely wrong. Data integrity is more about the quality of data and goes well beyond data base management systems. DBMSs provide data consistency tools that can help with data integrity; they are one piece of the larger problem. Integrity constraints and triggers help ensure that a DBMS does not degrade (or, if you're a pessimist, further degrade) the integrity of the data that arrives.

The way to parse the Oracle verbiage is that integrity constraints and triggers are a way to implement a subset of the business rules that define data integrity.

Answer 3

Very roughly, my 2c:

Integrity = strong types, no illegal values as determined by the data model & constraints, foriegn keys, unique constraints and stuff like that.

Consistency = being able to read only committed data a given point in time, not the intermediate steps.

Answer 4

From here

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.

You can read first chapter for ACID.

Answer 5

1. Consistency: You don’t have dates, number and letters in same column but only one of them. This is forsed by defining column type, eg. column id number (5) means there won’t be dates nor letters in it and numbers greater then 99999
2. Integrity: If you have emplooyes table and departments table and department_id column in both of them (with meaning of department_id in witch employee is working) there won’t be any employee working in department that doesen’t exists in departments table. This is forsed by referencal integrity constraint.