Recursively deleting a row in a table having Foreign Key Constraints

An oft encountered and irritating scenario while working with a database is when we come across a certain row which we want to delete, but cannot as it has recursive Foreign Key Constraints. Today I got a particularly nasty scenario as we are using Teneo. It has around four levels of Foreign Key Constraints. So, I decided to write this utility so that given a table name, column name and value, it deletes the row and all the Foreign Key Constrained data in order. I do this by making sense of the error message that I get when trying to delete a row with Foreign Key Constraints.

It looks something like this with MySQL:

Cannot delete or update a parent row: a foreign key constraint fails (some_database/user, CONSTRAINT my_constraint FOREIGN KEY (id) REFERENCES employee  (id))

To deal with this, I have defined this interface to extract meaningful information from the error message, so that I can delete the rogue row of the rogue table first.

This is the implemenetation for MySQL:

And finally the class that does the dirty job of recursively executing all the deletes:

I don’t claim this to be very generic, but it works pretty well for me :). A great time saver for sure.

Leave a Reply

Your email address will not be published. Required fields are marked *