DROP all foreign keys in MYSQL database

Question

Foreign keys are causing me too many problems modifying an database structure to meet new requirements - I want to modify primary keys but it seems I can't when foreign keys reference the table in question (I think because MySQL drops the table and re-creates).

So while I work on the DB, I'd like to simply remove all the foreign keys and re-create them later. Is there a neat way to do so?

Just an untested thought, but what about changing the tables from Innodb to MyISAM? Although the conversion back might be difficult... — Sablefoste, Dec 05 '12 at 22:08

score 80 · Answer 1 · edited Jul 04 '23 at 10:29

80

Run

SELECT concat('ALTER TABLE `', TABLE_NAME, '` DROP FOREIGN KEY `', CONSTRAINT_NAME, '`;') 
FROM information_schema.key_column_usage 
WHERE CONSTRAINT_SCHEMA = 'db_name' 
AND referenced_table_name IS NOT NULL;

and run the output.

edited Jul 04 '23 at 10:29

gam6itko

15,128
2
19
18

answered Dec 15 '14 at 09:52

Zoozy

1,215
13
16

4

_How_ can we run the output? e.g. in a script – Lightness Races in Orbit Jan 08 '15 at 16:54
@LightnessRacesinOrbit Redirect output to a sql file, and run the file. – Zoozy Jan 09 '15 at 06:50
7

And how do we redirect the output to an SQL file, and run the file, _from within an SQL statement_? – Lightness Races in Orbit Jan 09 '15 at 11:18
1

This seems like the correct answer, but for some reason this causes my MySQL server processes to crash. – Craig Jacobs Jun 23 '17 at 18:54
1

I know this is late, but working from MySQL Workbench I just had to run the query above and copy paste the output into a new script and run that script. That's how I "redirected output to sql file". – Zack Knopp Feb 14 '18 at 15:52
@Zoozy Do you have any solution for generating the foreign key definition(to basically revert what happens when you execute the output)? – james Oct 19 '18 at 20:58
1

This was useful to me. To complement this answer, I was having some statements appearing two or more times in the result, so I added `AS statement` to the field, and added `GROUP BY statement` at the end of the SELECT statement. – mikl Feb 22 '19 at 16:47
To run the output: You can do `mysql --execute "SELECT ..." --skip-column-names --batch > drop-fks.sql` to write it to an sql file, followed by `mysql < drop-fks.sql` to run it. – Dario Seidl May 03 '21 at 18:41
Adding `DISTINCT` keyword after `SELECT`, e.g., `SELECT DISTINCT concat( ...` will remove duplicated `ALTER TABLE` syntaxes. – Aryo Jan 16 '22 at 04:22

Mike Brant · Accepted Answer · 2015-12-18T14:56:58.433

You can simply issue the following command before any Alter Table statements you are going to make:

SET foreign_key_checks = 0;

This will turn off foreign key constraint checks for your database connection. You can then make your changes without needing to worry about constraints.

After you are done, don't forget to issue:

SET foreign_key_checks = 1;

To turn them back on.

Note that this will still not allow you to create a new foreign key constraint that would fail because the column data types don't match.

score 11 · Answer 3 · answered Jun 24 '15 at 15:11

Another version of Zoozy code, here you can select only a table:

SELECT concat('ALTER TABLE ', TABLE_NAME, ' DROP FOREIGN KEY ', CONSTRAINT_NAME, ';') 
FROM information_schema.key_column_usage 
WHERE CONSTRAINT_SCHEMA = 'YOUR DB HERE' 
AND TABLE_NAME='YOUR TABLE HERE' 
AND REFERENCED_TABLE_NAME IS NOT NULL;

Also with 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///

score 2 · Answer 4 · answered Apr 03 '18 at 19:44

If you want to do the same table across multiple databases, don't forget to put CONSTRAINT_SCHEMA in the output:

SELECT concat('ALTER TABLE ', CONSTRAINT_SCHEMA,'.',TABLE_NAME, ' DROP FOREIGN KEY ', CONSTRAINT_NAME, ';')
FROM information_schema.key_column_usage
WHERE CONSTRAINT_SCHEMA like 'your_db_prefix_%'
    AND TABLE_NAME='your_table'
    AND REFERENCED_TABLE_NAME IS NOT NULL;

DROP all foreign keys in MYSQL database

4 Answers4

Linked