Enforcing data integrity involves preserving the consistency and correctness of the data stored in a database by validating the contents of individual fields, verifying field values with respect to one another, validating data in one file or table as compared to another file or table, and verifying that a database is successfully and accurately updated for each transaction. An important step in planning tables is deciding how to enforce the integrity of their data.
Data integrity falls into four categories: entity integrity, domain integrity, referential integrity, and user-defined integrity.
Earlier releases of SQL Server enforced data integrity by using user-defined datatypes, defaults, rules, triggers, and stored procedures. SQL Server 6.0 supports a variety of table-definition extensions that achieve the same results without requiring separate objects (rules and defaults) or additional steps to "bind" these objects to various columns or user-defined datatypes.
| Integrity type | Former options | SQL Server 6.0 options |
|---|---|---|
| Entity | Unique indexes | PRIMARY KEY UNIQUE KEY IDENTITY property |
| Domain | Datatypes, Defaults, Rules | DEFAULT constraint FOREIGN KEY constraint CHECK constraint |
| Referential | Triggers | FOREIGN KEY constraint CHECK constraint |
| User-defined | Rules, Triggers Stored procedures | All column- and table-level constraints in CREATE TABLE |