What to do if mysql show foreign keys?
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.
If mysql show foreign keys - 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 My_Table_Constraint;