DROP all foreign keys in MYSQL database
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.
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///
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.
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;