Referential Integrity

Referential integrity preserves the defined relationships between tables when records are entered or deleted. In SQL Server, referential integrity is based on foreign to primary key or unique key relationships. Referential integrity ensures that key values are consistent. 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:

• Adding records to a related table when there is no associated record in the primary table.
• Changing values in a primary table that would result in "orphaned" records in a related table.
• Deleting records from a primary table when there are matching related records.

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, as shown in the following illustration: