SQL Server Interview Question #39

What is referential integrity?

Data Types, Keys & Constraints Junior Beginner

Quick Interview Answer

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.

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);