I am developing web based application where I needs to delete parent entries if user thinks it will no need to maintain. If parent entry doesn't have any record in child table then entry will get deleted but if it has records in child table then I wants to show error message with entry exists in which table.
I am following MVC architecture using jsp and servlet.Answer1:
Notice the following, if your tables are well defined I mean, the child records must reference the parent records (read about FOREIGN KEY). Then, if you try to delete the parent record you have many options:
1- You can allow the default mode: You can't delete the parent without delete the child first (I think this is what you want from the description).
2- You can "say" to mysql to delete all the child records when you delete the parents.
For more information about this, read this post: <a href="https://stackoverflow.com/questions/6720050/foreign-key-constraints-when-to-use-on-update-and-on-delete" rel="nofollow">Foreign key constraints: When to use ON UPDATE and ON DELETE</a>
Assuming you want to follow the first option, when you want to delete the parent key two things may happen.
1- There's no child record, and therefor everything goes how you expect.
2- You have child records referencing the parent record you want to delete. In this case, mysql won't delete the parent record, and will return you a error with code 1217 (Cannot delete or update a parent row: a foreign key constraint fails). Then you can detect the error. And do what you pretend to do with it, in this case to present a error message to the client. To show the message, here's a copy of the comment I post before I wrote this answer:<blockquote>
Once detected, there are two options, to send the error to the jsp page. You can send it using the user session, or, when you are redirecting to the jsp page, you can send an attribute in the url like www.my-app./jsppage?error=1. Then the jsp page gets the attribute and display the proper error message.</blockquote>