Dropping unnamed foreign key constraints in MySQL

If you're simply amending a live database, this isn't a problem. However, when you need statically define operations with database change management tools like Liquibase or Flyway, using raw SQL change scripts, then it becomes more arduous.

The problem resides around DDLs such as ALTER TABLE requiring non-dynamic strings - you can't use subqueries or variables. This makes it difficult to use with fixed SQL files.

I chose to introduce a Stored Proc to handle the task. This example is from Liquibase:

--changeset x:1 dbms:mysql splitStatements:false

CREATE PROCEDURE drop_unnamed_fk_constraint
(
IN tableName CHAR(100),
IN columnName CHAR(100),
IN refTableName CHAR(100)
)
BEGIN
SET @sql := (SELECT CONCAT('ALTER TABLE ', table_name, ' DROP FOREIGN KEY ', constraint_name)
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
WHERE TABLE_NAME = tableName 
AND COLUMN_NAME = columnName 
AND REFERENCED_TABLE_NAME = refTableName);

PREPARE drop_unnamed_fk_constraint_stmt FROM @sql;
EXECUTE drop_unnamed_fk_constraint_stmt;
DEALLOCATE PREPARE drop_unnamed_fk_constraint_stmt;
END 

--changeset x:2 dbms:mysql

CALL drop_unnamed_fk_constraint('Table', 'column', 'ReferencedTable');

If you're also using H2 for testing, then you can emulate the Stored Procedure with a (less than delightful) user-defined function:

--changeset x:1 dbms:h2 splitStatements:false failOnError:false

CREATE ALIAS drop_unnamed_fk_constraint AS $$
void dropConstraint(Connection conn, String tableName, String columnName, String refTableName) throws Exception {
ResultSet rs = conn.createStatement().executeQuery("SELECT CONCAT('ALTER TABLE " + tableName + " DROP FOREIGN KEY ', constraint_name) " +
        "FROM information_schema.constraints " +
        "WHERE table_name = '" + tableName.toUpperCase() + "' " +
        "AND column_list = '" + columnName.toUpperCase() + "' " +
        "AND constraint_type = 'REFERENTIAL' LIMIT 1");
    rs.next();
    conn.createStatement().executeUpdate(rs.getString(1));
}
$$;

Note that not all information_schema tables presently exist in H2, so I'm using constraints instead.