Cascading Foreign Keys: I Was Against Them… Until I Finally Understood Why
- Guy Glantser
- Mar 24
- 3 min read
For years, I was against cascading foreign keys.
Not in a strong, dogmatic way - more like a gut feeling. It just felt wrong.
The problem was… I couldn’t clearly explain why to customers.
Sure, I had the usual arguments:
“It can create a lot of locks across multiple tables.”
“It smells like a design issue.”
But deep down, I knew something was missing in my explanation.
Because if you do need to delete related data across multiple tables… you’re going to take those locks anyway, right?
So what’s the real problem?
The Missing Piece (Thanks to Erik Darling)
Then I came across Erik Darling’s post: https://www.brentozar.com/archive/2018/11/adventures-in-foreign-keys-a-cascade-of-badness/
And that’s where it finally clicked.
The key insight:
SQL Server effectively escalates to SERIALIZABLE behavior during cascading operations.
Why? Because it must guarantee referential integrity across multiple tables during the delete.
That means:
It has to ensure no new matching rows appear mid-operation
It has to maintain a consistent view across all related tables
It ends up taking stronger and longer-lasting locks than you might expect
And suddenly, cascading FKs are not just “convenient automation” - they are hidden, high-impact transactions.
Cascading Foreign Keys - The Real “Ouch” Scenario
Now let’s take this one step further.
Imagine:
You have a foreign key with ON DELETE CASCADE
But… there is no supporting index on the child table
What happens when you delete a row from the parent?
SQL Server needs to find all matching rows in the child table.
Without an index?
👉 It scans the entire table
👉 And because of the cascading + serializable behavior…
👉 It can take an exclusive lock on the whole table
Yes - the entire child table can get locked.
That’s not just inefficient. That’s a production incident waiting to happen.
“But We Need Cascades…”
Let’s be fair.
If your business logic requires:
Deleting a parent
Automatically deleting all related rows
Then cascading FKs can make sense.
But now we understand the trade-off:
You are not just simplifying code
You are introducing complex, high-impact, multi-table transactional behavior inside the engine
And that behavior is:
Harder to see
Harder to control
Easier to underestimate
Indexing Foreign Keys Is Not Optional (Especially Here)
It’s generally a good practice to index foreign keys.
But after understanding cascading behavior…
👉 It becomes critical when using ON DELETE CASCADE.
Without it:
You risk full table scans
You increase lock duration and scope
You dramatically increase blocking risk
Finding the Dangerous Ones
We have a script in our community repo that finds foreign keys without supporting indexes:
It’s not new, but it still does the job.
Now, with this new perspective, you can tweak it to focus specifically on cascading FKs:
WHERE ForeignKeys.delete_referential_action = 1That will highlight:
👉 Foreign keys with ON DELETE CASCADE
👉 That don’t have proper indexes
A Quick Reality Check
I recently ran this against a customer database that uses cascading deletes heavily.
I found:
31 foreign keys with ON DELETE CASCADE and no supporting index
Yeah… 😅
That’s not a theoretical problem anymore. That’s a backlog.
So, Where Do I Stand Today?
I’m no longer “against” cascading foreign keys.
But I see them very differently now:
They are not just a convenience feature
They are a powerful mechanism with serious locking implications
And they require intentional design and proper indexing
If you use them:
Know what SQL Server is doing under the hood
Make sure every FK is properly indexed
And be extra careful in high-concurrency systems
Final Thoughts
Sometimes, we resist something because it “feels wrong.”
But the real value comes when we can explain why.
For me, the turning point wasn’t locking itself - it was understanding that cascading operations push SQL Server into behavior that’s much closer to SERIALIZABLE than most people realize.
And once you see that…
You stop thinking of cascades as harmless - and start treating them with the respect they deserve.
