SQL Server Interview Question #40

What are CASCADE actions in a foreign key?

Data Types, Keys & Constraints Junior Beginner

Detailed Explanation

Foreign-key referential actions determine what SQL Server should do to child rows when a referenced parent key is deleted or updated.

Important options include NO ACTION, CASCADE, SET NULL, and SET DEFAULT. ON DELETE CASCADE automatically deletes related child rows when the parent row is deleted. It should be used only when that behavior matches the domain because an unintended parent deletion can otherwise remove a large hierarchy of dependent records.

In production systems, cascade behavior should be designed deliberately and coordinated with Entity Framework Core relationship configuration.

Code Example

ALTER TABLE OrderItems
ADD CONSTRAINT FK_OrderItems_Orders
FOREIGN KEY (OrderId)
REFERENCES Orders(Id)
ON DELETE CASCADE;