Friendly errors on foreign key violations?

frankhoffy posted on Friday, June 11, 2010

I have 3 tables:  Note, Issue, and NoteIssue.  If the user attempts to delete an Issue, I would like to display a friendly message if there is a foreign key violation in the NoteIssue table.  The foreign key error message from the database is pretty ugly.  I was wondering how some of you have dealt with this in your apps.

Marjon1 replied on Saturday, June 12, 2010

You really have a couple of options, depending on how your objects are used and the methods you use to delete them

1. You can catch the exception within the relevant dataportal_XYZ method, if it is a foreign key exception then throw a new exception with a more friendly method otherwise just re-throw the original exception. 
    
2. This sounds like a business rule were you don't want to allow a user to delete an issue there is a note related to that issue. If you have an instance of an issue and then call delete you could override the delete method to check if there are notes (assuming it knows or a command object would be required) and then throw an error. If you use a factory method to delete the issue without having an actual instance then you could do the same thing, but in the factory method (using a command object). This would prevent the foreign key constraint from being triggered.

Hopefully the two suggestions above make some sense, we need a bit more information about your actual classes not the database structure to be able to provide a more detailed / appropriate response. Normally if the user could delete the issue, you would need to ensure that any foreign keys were removed either in the dataportal_delete method, stored procedure or using cascade delete.

frankhoffy replied on Monday, June 14, 2010

I think option #2 is probably going to be the best fit for my situation.  Thanks for your help on this.