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.
package com.swayam.db.tools;
/**
*@author paawak
*/
public interface ConstraintViolationExceptionInterpretor {
void setExceptionMessage(String message);
String getConstrainedTableName();
String getForeignKeyColumnName();
String getReferencedTableName();
String getReferencedColumnName();
}
This is the implemenetation for MySQL:
package com.swayam.db.tools;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
/**
*
* @author paawak
*/
public class MySQLConstraintViolationExceptionInterpretor implements
ConstraintViolationExceptionInterpretor {
private String message;
public MySQLConstraintViolationExceptionInterpretor() {
}
public String getConstrainedTableName() {
String match = getFirstMatch("/w+");
if (match != null) {
match = match.substring(1);
}
return match;
}
public String getForeignKeyColumnName() {
String match = getFirstMatch("(`w+`)");
if (match != null) {
match = match.substring(2, match.length() - 2);
}
return match;
}
public String getReferencedColumnName() {
String match = getFirstMatch("(`w+`))");
if (match != null) {
match = match.substring(2, match.length() - 3);
}
return match;
}
public String getReferencedTableName() {
String prefix = "REFERENCES `";
String match = getFirstMatch(prefix + "w+`");
if (match != null) {
match = match.substring(prefix.length(), match.length() - 1);
}
return match;
}
public void setExceptionMessage(String message) {
this.message = message;
}
private String getFirstMatch(String regex) {
Pattern pattern = Pattern.compile(regex);
Matcher matcher = pattern.matcher(message);
if (matcher.find()) {
String match = matcher.group();
return match;
}
return null;
}
}
And finally the class that does the dirty job of recursively executing all the deletes:
package com.swayam.db.tools;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
/**
*@author paawak
*/
public class RecursiveDeleteUtil {
private final Connection con;
private final List deleteStatements;
private final ConstraintViolationExceptionInterpretor interpretor;
private String idColumn;
private String idValue;
public RecursiveDeleteUtil(Connection con,
ConstraintViolationExceptionInterpretor interpretor) {
this.con = con;
this.interpretor = interpretor;
deleteStatements = new ArrayList(1);
}
public void delete(String tableName, String column, String value) {
if (idColumn == null) {
idColumn = column;
idValue = value;
}
String delQuery = "DELETE FROM " + tableName + " WHERE " + column
+ "='" + value + "'";
deleteStatements.add(delQuery);
try {
Statement stat = con.createStatement();
int rowsAffected = stat.executeUpdate(delQuery);
if (rowsAffected > 0) {
// execute all stats on the stack
int lastIndex = deleteStatements.size() - 1;
for (int count = lastIndex; count >= 0; count--) {
stat.executeUpdate(deleteStatements.get(count));
}
}
stat.close();
} catch (SQLException e) {
String message = e.getMessage();
interpretor.setExceptionMessage(message);
String constrainedTableName = interpretor.getConstrainedTableName();
String foreignKeyColumnName = interpretor.getForeignKeyColumnName();
String referencedTableName = interpretor.getReferencedTableName();
String referencedColumnName = interpretor.getReferencedColumnName();
System.out.println("################# message = " + message
+ "nconstrainedTableName = " + constrainedTableName
+ ", foreignKeyColumnName = " + foreignKeyColumnName
+ ", referencedTableName = " + referencedTableName
+ ", referencedColumnName = " + referencedColumnName);
// select the offending value
String sel = "SELECT " + referencedColumnName + " FROM "
+ referencedTableName + " WHERE " + idColumn + "='"
+ idValue + "'";
System.out.println("################ select = " + sel);
try {
Statement selectStat = con.createStatement();
ResultSet res = selectStat.executeQuery(sel);
while (res.next()) {
String constraintVal = res.getString(1);
delete(constrainedTableName, foreignKeyColumnName,
constraintVal);
}
res.close();
selectStat.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
public static void main(String[] args) throws Exception {
Class.forName("com.mysql.jdbc.Driver").newInstance();
String conStr = "jdbc:mysql://localhost/dummy?user=root&password=dummy";
Connection con = DriverManager.getConnection(conStr);
new RecursiveDeleteUtil(con,
new MySQLConstraintViolationExceptionInterpretor()).delete(
"some_table", "id", "118");
}
}
I don't claim this to be very generic, but it works pretty well for me :). A great time saver for sure.