Jack Corbett (5/27/2010)
I've never used Cascading updates or deletes. I think that there is a reason for the FK and that I shouldn't just blindly update or delete child information. If there is a business reason for deleting the parent then there should be business rules enforced in the application for it. I don't think I should be able to do the delete outside the application in that case.
I tend to agree, but this was an interview question to check technical knowledge and/or lateral thinking.
I've only ever used SET NULL, and both times were a work around in a 3rd party system.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi