All 7 Constraints (Primary Key, Foreign Key, Check & Unique) questions
More in Constraints (Primary Key, Foreign Key, Check & Unique)
What is a foreign key constraint, and how does it help maintain data integrity between two related tables? What is a check constraint, and how do you use one to enforce a specific business rule at the database level? How is a unique constraint different from a primary key constraint, and when would you use one instead of the other? How do you temporarily disable a constraint to perform a bulk data load, and how do you safely re-enable it afterward? How would you add a new constraint to an existing table that already contains data, especially if some existing rows might violate that constraint?
Advertisement
SQL Server › Constraints (Primary Key, Foreign Key, Check & Unique)

How do cascading actions work with foreign key constraints, such as ON DELETE CASCADE, and what risks come with using them?

Advanced

Answer

Cascading actions automatically apply a related change to child rows when the parent row is modified or deleted, such as automatically deleting all of a customer's orders when that customer is deleted, which can be convenient but also risky, since a single delete statement could unexpectedly remove far more data than intended if not carefully planned.
CREATE TABLE Orders (
  OrderId INT PRIMARY KEY,
  CustomerId INT,
  FOREIGN KEY (CustomerId) REFERENCES Customers(CustomerId) ON DELETE CASCADE
);
Real-world example A team enables ON DELETE CASCADE between customers and orders so that removing a test customer account automatically cleans up all of their related test orders without requiring several separate delete statements.

Common follow-ups: What other cascading actions are available besides CASCADE?;How do you audit which tables have cascading deletes configured before making a risky change?

Normalization;Transactions & ACID
All 7 Constraints (Primary Key, Foreign Key, Check & Unique) questions