Data Integrity

Enforcing data integrity guarantees the quality of the data in the database. For example, if an employee is entered with an employee ID value of 123, the database should not permit another employee to have an ID with the same value.
Entity Integrity

Entity integrity defines a row as a unique entity for a particular table. Entity integrity enforces the integrity of the identifier columns or the primary key of a table, through UNIQUE indexes, UNIQUE constraints or PRIMARY KEY constraints.

Domain Integrity

Domain integrity is the validity of entries for a specific column. You can enforce domain integrity to restrict the type by using data types, restrict the format by using CHECK constraints and rules, or restrict the range of possible values by using FOREIGN KEY constraints, CHECK constraints, DEFAULT definitions, NOT NULL definitions, and rules.

Referential Integrity

</ 2003 authoring ddue.schemas.microsoft.com xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5" 5:content>

Referential integrity preserves the defined relationships between tables when rows are entered or deleted. In SQL Server, referential integrity is based on relationships between foreign keys and primary keys or between foreign keys and unique keys, through FOREIGN KEY and CHECK constraints. Referential integrity makes sure that key values are consistent across tables. This kind of consistency requires that there are no references to nonexistent values and that if a key value changes, all references to it change consistently throughout the database.

When you enforce referential integrity, SQL Server prevents users from doing the following:

• Adding or changing rows to a related table if there is no associated row in the primary table.
  
  
• Changing values in a primary table that causes orphaned rows in a related table.
  
  
• Deleting rows from a primary table if there are matching related rows.

For example, with the Sales.SalesOrderDetail and Production.Product tables in the AdventureWorks database, referential integrity is based on the relationship between the foreign key (ProductID) in the Sales.SalesOrderDetail table and the primary key (ProductID) in the Production.Product table. This relationship makes sure that a sales order can never reference a product that does not exist in the Production.Product table.

User-Defined Integrity

</ 2003 authoring ddue.schemas.microsoft.com xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5" 5:content>

User-defined integrity lets you define specific business rules that do not fall into one of the other integrity categories. All the integrity categories support user-defined integrity. This includes all column-level and table-level constraints in CREATE TABLE, stored procedures, and triggers.

</ 2003 authoring ddue.schemas.microsoft.com 5:content>

</ 2003 authoring ddue.schemas.microsoft.com 5:content>