Is it possible to drop all foreign key constraints on a table at once in mySQL 5?

Question

In mySQL 5, is there a way to drop all foreign key constraints on a table with one SQL statement without referring to them by name?

I'm writing a DB update script, and unfortunately some of the sites had constraints created with "wrong" names. I'm trying to avoid going in and getting the actual constraint names from the DB and inserting them back into SQL statements.

Answer 1

I have a solution similar to Bing's answer, but it takes it one step further to be automatic and reusable in a procedure.

DROP PROCEDURE IF EXISTS dropForeignKeysFromTable;

delimiter ///
create procedure dropForeignKeysFromTable(IN param_table_schema varchar(255), IN param_table_name varchar(255))
begin
    declare done int default FALSE;
    declare dropCommand varchar(255);
    declare dropCur cursor for 
        select concat('alter table ',table_schema,'.',table_name,' DROP FOREIGN KEY ',constraint_name, ';') 
        from information_schema.table_constraints
        where constraint_type='FOREIGN KEY' 
            and table_name = param_table_name
            and table_schema = param_table_schema;

    declare continue handler for not found set done = true;

    open dropCur;

    read_loop: loop
        fetch dropCur into dropCommand;
        if done then
            leave read_loop;
        end if;

        set @sdropCommand = dropCommand;

        prepare dropClientUpdateKeyStmt from @sdropCommand;

        execute dropClientUpdateKeyStmt;

        deallocate prepare dropClientUpdateKeyStmt;
    end loop;

    close dropCur;
end///

delimiter ;

Since a prepared statement can only handle one statement at a time, the procedure loops through the foreign keys using a cursor and executes each drop one at a time.

To use the procedure on one of your tables just use the following, replacing table_schema and table_name with your values:

call dropForeignKeysFromTable('table_schema', 'table_name');