SQL Server Interview Question #39

What is referential integrity?

Data Types, Keys & Constraints Junior Beginner

Detailed Explanation

Referential integrity ensures that relationships between tables remain valid. In SQL Server, foreign-key constraints are the primary mechanism for enforcing it.

For example, if Products.CategoryId references Categories.Id, SQL Server can prevent a product from referencing a category that does not exist. It can also prevent deletion of a referenced parent row unless the relationship permits an action such as cascading deletion.

Referential integrity prevents orphaned and inconsistent relational data.

Code Example

ALTER TABLE Products
ADD CONSTRAINT FK_Products_Categories
FOREIGN KEY (CategoryId)
REFERENCES Categories(Id);