Data Integrity

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