Monthly Archives: June 2009

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.

Saving EMF to XML

Lets say we want to serialize an EObject to XML. The code is given below. This will print the XML to the error stream.