Paul White NZ (5/27/2010)
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.
I should have quoted Steve's post asking if anyone's used it.
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.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question