Delete all foreign keys in database(MySql)

Question

I would like to rename a column in a table that is a foreign key to many tables. Apparently this is only possible if you delete the constraints, as I found out in this link.

I dont want to delete all the constratints manually is there a way to delete all the foreign key constraints in the database?

I have also tried SET FOREIGN_KEY_CHECKS=0; but I still cant rename the column.

Answer 1

You can use this SQL to generate ALTER TABLES (!!YOUR_SCHEMA_HERE!! needs to be replaced by your schema):

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_schema='!!YOUR_SCHEMA_HERE!!';

It will generate SQL like this:

alter table `viewpoint_test`.`answer_code` DROP FOREIGN KEY fk_answer_code_codebook_item1;
alter table `viewpoint_test`.`answer_code` DROP FOREIGN KEY fk_answer_code_questionary_answer1;
alter table `viewpoint_test`.`codebook` DROP FOREIGN KEY codebook_ibfk_1;
...

By "schema name", I mean your database name. It's the same thing.

Answer 2

You Can Try using As Like of Following ..

ALTER TABLE tableName
DROP FOREIGN KEY fieldName;
ADD FOREIGN KEY (newForignKeyFieldName);

Also you can try with Reference Key.As like .....

ALTER TABLE tableName
DROP FOREIGN KEY fieldName;
ADD FOREIGN KEY (newForignKeyFieldName)
REFERENCES anotherTableName(reference_id);

Answer 3

The following query will build the correct syntax automatically. Make sure you put your real DB schema name in the WHERE condition. Just execute each line returned and all your FOREIGN KEYS will be gone.

I leave the reverse (adding them back) as an exercise for you.

SELECT CONCAT("alter table ", TABLE_NAME," drop foreign key ", CONSTRAINT_NAME,"; ") AS runMe
FROM information_schema.key_column_usage 
WHERE TABLE_SCHEMA='MY_SCHEMA_NAME' AND CONSTRAINT_NAME <> 'PRIMARY';

If you need it to be schema independent, you can write: TABLE_SCHEMA=DATABASE() to get the current active DB name