Enforcing data integrity ensures 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 allow another employee to have an ID with the same value. If you have an employee_rating column intended to have values ranging from 1 to 5, the database should not accept a value of 6. If the table has a dept_id column that stores the department number for the employee, the database should allow only values that are valid for the department numbers in the company.
Two important steps in planning tables are to identify valid values for a column and to decide how to enforce the integrity of the data in the column. Data integrity falls into four categories:
There are several ways of enforcing each type of integrity.
| Integrity type | Recommended options |
|---|---|
| Entity | PRIMARY KEY constraint UNIQUE constraint IDENTITY property |
| Domain | DEFAULT definition FOREIGN KEY constraint CHECK constraint NOT NULL |
| Referential | FOREIGN KEY constraint CHECK constraint |
| User-defined | All column- and table-level constraints in CREATE TABLE Stored Procedures Triggers |
Entity integrity defines a row as a unique entity for a particular table. Entity integrity enforces the integrity of the identifier column(s) or the primary key of a table (through indexes, UNIQUE constraints, PRIMARY KEY constraints, or IDENTITY properties).
Domain integrity is the validity of entries for a given column. You can enforce domain integrity by restricting the type (through data types), the format (through CHECK constraints and rules), or the range of possible values (through FOREIGN KEY constraints, CHECK constraints, DEFAULT definitions, NOT NULL definitions, and rules).
Referential integrity preserves the defined relationships between tables when records are entered or deleted. In Microsoft® SQL Server™, referential integrity is based on relationships between foreign keys and primary keys or between foreign keys and unique keys. Referential integrity ensures that key values are consistent across tables. Such consistency requires that there be 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:
For example, with the sales and titles tables in the pubs database, referential integrity is based on the relationship between the foreign key (title_id) in the sales table and the primary key (title_id) in the titles table.
User-defined integrity allows you to define specific business rules that do not fall into one of the other integrity categories. All of the integrity categories support user-defined integrity.
| Using Constraints, Defaults, and Null Values | Specifying a Column Data Type |