Data integrity in sql Server

The ability to ensure that persisted data can be retrieved without error is central to the Information Architecture Principle, and it was the first major problem tackled by the database world. Without data integrity, a query’s answer cannot be guaranteed to be correct; consequently, there’s not much point in availability or performance. Data integrity can be defined in multiple ways:

Ø  Entity integrity

Ø  Domain integrity

Ø  Referential integrity

Ø  Transactional integrity

Entity integrity

Involves the structure (primary key and its attributes) of the entity. If the primary key is unique and all attributes are scalar and fully dependent on the primary key, then the integrity of the entity is good. In the physical schema, the table’s primary key enforces entity integrity.

Domain Integrity

Ensures that only valid data is permitted in the attribute. A domain is a set of possible values for a tribute, such as integers, bit values, or characters. Null ability (whether a null value is valid for an attribute) is also a part of domain integrity. In the physical schema, the data type and null ability of the row enforce domain integrity.

Referential integrity

Refers to the domain integrity of foreign keys. Domain integrity means that if an attribute has a value, then that value must be in the domain. In the case of the foreign key, the domain is the list of values in the related primary key. Referential integrity, therefore, is not an issue of the integrity of the primary key but of the foreign key.

Transactional integrity

Ensures that every logical unit of work, such as inserting 100 rows or updating 1,000 rows, is executed as a single transaction. The quality of a database product is measured by its transactions’ adherence to the ACID properties: atomic — all or nothing; consistent — the database begins and ends the transaction in a consistent state; isolated — one transaction does not affect another transaction; and durable — once committed always committed

Read ACID Properties: ACIDProperties

http://bageshkumarbagi-msbi.blogspot.in/2016/06/acid-properties.html