删除mysql外键的表,删除数据库中的所有外键（MySql）

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.

解决方案

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'