Hugo Kornelis continues a series on execution plans:
In the previous part, we looked at foreign key checking when data is inserted. Inserts in the referencing (child) table can, by definition, not cause foreign key violations, so no checking is done in those cases. But for inserts in the referenced (parent) table, SQL Server needs to check that the inserted value does indeed exist. We saw how both the Nested Loops and the Merge Join operators have some specific optimizations to do that check as efficient as possible; we also saw how the Assert operator does the actual checking.
When deleting data, the reverse is true. We can delete rows from the child table without verification, but deletes from the parent table are subject to a check.
And those deletes can get crazy expensive.