40

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.

49

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

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

ALTER TABLE My_Table DROP FOREIGN KEY My_Table_Constraint;

References: 1 & 2.

  • 1
    Thanks for the edit - I'll reciprocate by +1'ing your question! :-) – Vérace May 24 '15 at 18:35
  • 14
    Also, SHOW CREATE TABLE My_Table;. – Rick James Jun 7 '15 at 3:30
  • 2
    @RickJames that only shows constraints on other tables that My_Table has. Question asks for constraints on My_Table that other tables have. – ADTC Oct 26 '17 at 23:01
  • just for people only glancing over this: That statement will list all constraints referencing a table by the specified name across the entire database server. Limit it to the relevant database by adding AND TABLE_SCHEMA = 'My_Database'; – Steffen Winkler Apr 9 at 13:24
3

Editied the Query above. Changed Referenced Table name to Table Name as Referenced Table name is the table which is being referenced and hence the result of the original query wont show you the foreign keys on your table.

SELECT
  TABLE_NAME,
  COLUMN_NAME,
  CONSTRAINT_NAME,   -- <<-- the one you want! 
  REFERENCED_TABLE_NAME,
  REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
  Table_name = 'case_qualitycontrolcase' and constraint_name = f

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.