database 数据完整性和数据一致性之间有什么区别吗？

Are there any difference between data integrity and data consistency?

提问by Just a learner

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

我对data consistency和有点困惑data integrity。来自Oracle 数据库概念：

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 consistencyand data integrity?

那么data consistency和之间有什么区别data integrity？

Thanks in advance.

提前致谢。

回答by Jim Balter

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.

它们不仅不同，而且是正交的。

不一致：
将员工 Joe Shmoe 的部门报告为 Sales 但没有在 Sales 部门的员工中列出 Joe Shmoe 的数据库会不一致。
它是数据库的逻辑属性，独立于实际数据。

诚信：
报告 joe SaleS 是 Shmoe 部门成员的数据库将缺乏诚信。
joe Sales 不是有效的员工姓名，Shmoe 不是有效的部门。
这在逻辑上不是无效的，但相对于管理数据内容的规则来说是无效的。

回答by lll

Very roughly, my 2c:

非常粗略，我的 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.

一致性 = 只能读取给定时间点提交的数据，而不是中间步骤。

回答by Ted Hopp

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.

数据可能完全一致，也可能完全错误。数据完整性更多地与数据质量有关，远远超出数据库管理系统。DBMS 提供有助于数据完整性的数据一致性工具；它们是更大问题的一部分。完整性约束和触发器有助于确保 DBMS 不会降低（或者，如果您是悲观主义者，则进一步降低）到达的数据的完整性。

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.

解析 Oracle 语言的方式是完整性约束和触发器是实现定义数据完整性的业务规则子集的一种方式。

回答by berkay

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.

孤立地执行事务可以保持数据的一致性。存储在数据库中的数据值必须满足一定的一致性约束。例如，账户余额不应低于 100 美元。DDL 命令提供了指定此类约束的工具。每次更新数据库时，数据库系统都会检查这些约束。通常，它可以是与数据库有关的任意谓词。大多数程序员检查数据库的一致性取决于约束，而这些通常测试成本很高。

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 chapterfor ACID.

您可以阅读ACID 的第一章。

回答by Marija Milojkovic

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.

1. 一致性：同一列中没有日期、数字和字母，而只有其中之一。这是通过定义列类型来实现的，例如。列 id 号 (5) 表示其中没有日期或字母以及大于 99999 的数字
2. 完整性：如果你有emplooyes 表和departments 表和department_id 列（在女巫员工工作中department_id 的含义），则不会有任何在部门工作的员工不存在于部门表中。这是由参照完整性约束引起的。