Time Complexity: CASCADE delete preview
O(n)
0
0
CASCADE delete preview in SQL - Time & Space Complexity
Understanding Time Complexity
When we delete a record that has related records in other tables, the database may delete those related records automatically using CASCADE delete.
We want to understand how the time to delete grows as the number of related records increases.
Scenario Under Consideration
Analyze the time complexity of this SQL snippet using CASCADE delete.
-- Assume tables: Orders and OrderItems
-- OrderItems has a foreign key to Orders with ON DELETE CASCADE
DELETE FROM Orders
WHERE OrderID = 123;
This deletes one order and automatically deletes all its related order items.
Identify Repeating Operations
Look for repeated actions caused by the CASCADE delete.
- Primary operation: Deleting each related record in the child table (OrderItems).
- How many times: Once for each related order item linked to the deleted order.
How Execution Grows With Input
The time to delete grows with the number of related records to remove.
| Input Size (n) | Approx. Operations |
|---|---|
| 10 related items | Deleting 1 order + 10 order items |
| 100 related items | Deleting 1 order + 100 order items |
| 1000 related items | Deleting 1 order + 1000 order items |
Pattern observation: The total work increases roughly in direct proportion to the number of related records.
Final Time Complexity
Time Complexity: O(n)
This means the time to complete the delete grows linearly with the number of related records that must be deleted.
Common Mistake
[X] Wrong: "Deleting one record is always a quick, constant-time operation regardless of related data."
[OK] Correct: Because CASCADE delete removes all related records, the time depends on how many related records exist, not just the one main record.
Interview Connect
Understanding how CASCADE delete affects performance shows you can think about real database behaviors beyond simple queries.
This skill helps you explain and predict how data changes impact system speed in practical situations.
Self-Check
"What if the foreign key was set to SET NULL instead of CASCADE? How would the time complexity change when deleting a record?"