Jack Corbett (5/27/2010)
I should have quoted Steve's post asking if anyone's used it.
Oh, right, gotcha 🙂
Yes, for an interview answer cascading deletes with SET NULL would be the best answer. Even with this I'd rather enforce it in a stored procedure or business layer, if not using stored procedures. Of course both assume that the column in the child is nullable.
It also assumes that NULL and the DEFAULT value are valid parent keys...this usually prompts a long discussion about whether RI is violated by allowing this behaviour. My view is that if the FK relationship stays valid, there's RI. Not everyone agrees with me.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi