How to check foreign keys related to a table

How to see foreign keys related to a table in MySql?

Background : I wanted to drop a table in MySql which has a foreign key constraint. When I do it I get this:

Error Code: 1217. Cannot delete or update a parent row: a foreign key constraint fails


How can I drop foreign keys related to the table leaving others.

Firstly, find out your FOREIGN KEY constraint name in this way:

SELECT
  TABLE_NAME,
  COLUMN_NAME,
  CONSTRAINT_NAME,   -- <<-- the one you want! 
  REFERENCED_TABLE_NAME,
  REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
  REFERENCED_TABLE_NAME = 'My_Table';

You can also add (to the WHERE clause) if you have more than one table called My_Table in different schemas.

AND TABLE_SCHEMA = 'My_Database';

And then you can remove the named constraint in the following way:

ALTER TABLE My_Table DROP FOREIGN KEY FK_constraint_name;

The FK_constraint_name is obtained from the CONSTRAINT_NAME field highlighted in the query above.

References: 1 & 2.

StackExchange Database Administrators Q#102371, answer score: 79