Is it possible to drop all foreign key constraints

问题:

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.

回答1:

You can surely select * the table to a temp table, drop and recreate it, then copy back.

回答2:

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');

回答3:

Here's a select that creates complete drop and create statements for all relations on a table. Not automatic in any way but easy enough to copy/paste from..

SELECT `DROP`,`CREATE` FROM (
SELECT
CONCAT("ALTER TABLE `", `K`.`TABLE_NAME`, "` DROP FOREIGN KEY `", `K`.`CONSTRAINT_NAME`, "`;") "DROP", 
CONCAT("ALTER TABLE `",
`K`.`TABLE_NAME`,
"` ADD CONSTRAINT ",
"`fk_",
`K`.`TABLE_NAME`,
"_",
`K`.`REFERENCED_TABLE_NAME`,
"1",
"` FOREIGN KEY (`",
`K`.`COLUMN_NAME`,
"`) REFERENCES ",
"`",
`K`.`REFERENCED_TABLE_SCHEMA`,
"`.`",
`K`.`REFERENCED_TABLE_NAME`,
"` (`",
`K`.`REFERENCED_COLUMN_NAME`,
"`) ON DELETE ",
`C`.`DELETE_RULE`,
" ON UPDATE ",
`C`.`UPDATE_RULE`,
";") "CREATE" 
FROM `information_schema`.`KEY_COLUMN_USAGE` `K`
LEFT JOIN `information_schema`.`REFERENTIAL_CONSTRAINTS` `C` USING (`CONSTRAINT_NAME`)
WHERE `K`.`REFERENCED_TABLE_SCHEMA` = "your_db"
AND `K`.`REFERENCED_TABLE_NAME` = "your_table") AS DropCreateConstraints

I used it to drop all relations, alter data types on the keys and then restore the relations with foreign key names á la MySQL Workbench. Might be helpful to someone..

回答4:

In your script you can always add SET FOREIGN_KEY_CHECKS=0 if you just want to get around the constraints.

Also, I have always deleted constraints on a per constraint basis using:

ALTER TABLE <table_name> DROP FOREIGN KEY <key_name>;

I don't think you can do all of them at once and I could not find any examples that show you can.

回答5:

You can use this simple bash script:

Run:

sh deleteForeignKeys.sh

and deleteForeignKeys.sh is

#!/bin/bash

dbname="databasename"
table="tablename"

mysqlconn="mysql -u username -ppassword -h host"

tableschema=$($mysqlconn -N -e "SHOW CREATE TABLE $dbname.$table")

#Foreign Keys
fks=$(echo $tableschema | grep -oP '(?<=CONSTRAINT `).*?(?=` FOREIGN KEY)')

for fk in $fks; 
do
    # DROP FOREIGN KEY 
    $mysqlconn -e "ALTER TABLE $dbname.$table DROP FOREIGN KEY $fk";
done