Mysql: Delete all foreign keys in database

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.

I dont want to delete all the constraints 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.

 Answers

Executing the following query

select * from information_schema.key_column_usage

will show you all the constraints (with the column name, constraint type, table and schema) that exist in your database. You'll notice these columns:

CONSTRAINT_CATALOG
CONSTRAINT_SCHEMA
CONSTRAINT_NAME
TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
COLUMN_NAME
ORDINAL_POSITION
POSITION_IN_UNIQUE_CONSTRAINT
REFERENCED_TABLE_SCHEMA
REFERENCED_TABLE_NAME
REFERENCED_COLUMN_NAME

Then, if you're planning to delete each constraint you have referencing your column, you should consider the REFERENCED_* columns and run something like:

DELETE FROM information_schema.key_column_usage 
WHERE 
    REFERENCED_TABLE_SCHEMA='myschema'
    AND
    REFERENCED_TABLE_NAME='mytable'
    AND
    REFERENCED_COLUMN_NAME='mycolumn'

http://dev.mysql.com/doc/refman/5.1/en/key-column-usage-table.html

---

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