MySQL – How to Check Foreign Keys Related to a Table

constraintdrop-tableforeign keyMySQL

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.

Best Answer

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;

References: 1 & 2.

Related Solutions

MySQL Foreign Key – Preventing Deletion of Empty Table

You shouldn't be able to drop a table that is the parent of another dependent table, even if it has no data. Because if you drop the parent, then you will never be able to insert any data in the child. You must first drop any child tables that reference enterprise_rma.

Something doesn't match your description, though. You said you were having trouble dropping the table, but your example about the foreign key checks shows you using DELETE.

DELETE is not the same as DROP TABLE. After DELETE, there are no rows in the table, but the table still exists. After DROP TABLE, the table doesn't exist, i.e. it doesn't show up if you run SHOW TABLES.

Mysql – Change Storage Engine on Referenced Tables

You need to disable foreign key checks and unique checks

#!/bin/bash
DATABASES=$(mysql -u XXXXX -pXXXXX --skip-column-names -e 'select distinct(table_schema) from information_schema.tables where table_schema not in ("mysql","information_schema","performance_schema")')
for D in $DATABASES
do
   TABLES=$(mysql -u XXXXX -pXXXXX --skip-column-names -e 'select table_name from information_schema.tables where table_schema="'$D'" and engine="InnoDB"')
   for T in $TABLES
   do
      echo "ALTERING TABLE $T"
      SQL="SET FOREIGN_KEY_CHECKS = 0; SET UNIQUE_CHECKS = 0;"
      SQL="${SQL} ALTER TABLE ${T} ENGINE=MYISAM"
      mysql -u XXXXX -pXXXXX -D${D} -e "${SQL}"
   done
done

Here is a more concise way: Reload data with InnoDB Disabled

#!/bin/bash
DATABASES=$(mysql -u XXXXX -pXXXXX --skip-column-names -e 'select distinct(table_schema) from information_schema.tables where table_schema not in ("mysql","information_schema","performance_schema")')
mysqldump -u XXXXX -pXXXXX --single-transaction --add-drop-database -B ${DATABASES} > AllMyData.sql
mysql -u XXXXX -pXXXXX -e"SET GLOBAL innodb_fast_shutdown = 0"
service mysql restart --skip-innodb
mysql -u XXXXX -pXXXXX < AllMyData.sql

Best Answer

Related Solutions

MySQL Foreign Key – Preventing Deletion of Empty Table

Mysql – Change Storage Engine on Referenced Tables

Related Question