7

I am trying to drop a table in MySQL but its complaining about "Cannot delete or update a parent row: a foreign key constraint fails"...

Questions

  • Is there a way to find out all the table dependencies and objects dependent on it?
  • For Sybase, DBArtisan gives a really easy way to find dependecies. Is there any such tool for MySQL?
5

From MySQL 5.0 on, one place you can check is information_schema.table_constraints

For example, to get the number of foreign key relationships for a given table (mydb.mytable), run this:

SELECT COUNT(1) FROM information_schema.table_constraints
WHERE table_schema = 'mydb'
AND table_name = 'mytable'
AND constraint_type='FOREIGN KEY';

From MySQL 5.1 on, you could also use information_schema.referential_constraints. Here is that table layout:

mysql> show create table information_schema.referential_constraints\G
*************************** 1. row ***************************
       Table: REFERENTIAL_CONSTRAINTS
Create Table: CREATE TEMPORARY TABLE `REFERENTIAL_CONSTRAINTS` (
  `CONSTRAINT_CATALOG` varchar(512) DEFAULT NULL,
  `CONSTRAINT_SCHEMA` varchar(64) NOT NULL DEFAULT '',
  `CONSTRAINT_NAME` varchar(64) NOT NULL DEFAULT '',
  `UNIQUE_CONSTRAINT_CATALOG` varchar(512) DEFAULT NULL,
  `UNIQUE_CONSTRAINT_SCHEMA` varchar(64) NOT NULL DEFAULT '',
  `UNIQUE_CONSTRAINT_NAME` varchar(64) DEFAULT NULL,
  `MATCH_OPTION` varchar(64) NOT NULL DEFAULT '',
  `UPDATE_RULE` varchar(64) NOT NULL DEFAULT '',
  `DELETE_RULE` varchar(64) NOT NULL DEFAULT '',
  `TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
  `REFERENCED_TABLE_NAME` varchar(64) NOT NULL DEFAULT ''
) ENGINE=MEMORY DEFAULT CHARSET=utf8
1 row in set (0.01 sec)

Just inspect table_name and referenced_table_name columns.

1

You can use Toad for MySQL, which is free. With Toad, you can create visual ER diagrams and find your dependencies.

0

I tested this solution on 5.5, but it should also work from 5.1 onward.

This query can show you all the references to your table:

Just remember to replace 'mydb' and 'mytable' with the ones that you need. It is also possible to define this as a VIEW for future uses.

SELECT rc.`CONSTRAINT_CATALOG`   AS `ChildTable_Catalog`
, rc.`CONSTRAINT_SCHEMA`         AS `ChildTable_Schema`
, rc.`TABLE_NAME`                AS `ChildTable`
, rc.`CONSTRAINT_NAME`           AS `ChildTable_ForeignKey`
, GROUP_CONCAT(DISTINCT fk.`COLUMN_NAME` ORDER BY fk.`ORDINAL_POSITION` ASC) AS `ChildTable_ForeignKey_Columns`
, rc.`UNIQUE_CONSTRAINT_CATALOG` AS `ParentTable_Catalog`
, rc.`UNIQUE_CONSTRAINT_SCHEMA`  AS `ParentTable_Schema`
, rc.`REFERENCED_TABLE_NAME`     AS `Parent_Table`
, rc.`UNIQUE_CONSTRAINT_NAME`    AS `ParentTable_UniqueKey`
, GROUP_CONCAT(DISTINCT uk.`COLUMN_NAME` ORDER BY fk.`ORDINAL_POSITION` ASC) AS `ParentTable_UniqueKey_Columns`
-- constraint relation
FROM INFORMATION_SCHEMA.`REFERENTIAL_CONSTRAINTS` AS rc
-- foreign key 
INNER JOIN INFORMATION_SCHEMA.`KEY_COLUMN_USAGE` AS fk 
    ON rc.`CONSTRAINT_CATALOG`        = fk.`CONSTRAINT_CATALOG`
   AND rc.`CONSTRAINT_SCHEMA`         = fk.`CONSTRAINT_SCHEMA`
   AND rc.`TABLE_NAME`                = fk.`TABLE_NAME`
   AND rc.`CONSTRAINT_NAME`           = fk.`CONSTRAINT_NAME`
-- unique key
INNER JOIN INFORMATION_SCHEMA.`KEY_COLUMN_USAGE` AS uk 
    ON rc.`UNIQUE_CONSTRAINT_CATALOG` = uk.`CONSTRAINT_CATALOG`
   AND rc.`UNIQUE_CONSTRAINT_SCHEMA`  = uk.`CONSTRAINT_SCHEMA`
   AND rc.`REFERENCED_TABLE_NAME`     = uk.`TABLE_NAME`
   AND rc.`UNIQUE_CONSTRAINT_NAME`    = uk.`CONSTRAINT_NAME`
-- optional filter condition
WHERE rc.`UNIQUE_CONSTRAINT_SCHEMA` = 'mydb'
  AND rc.`REFERENCED_TABLE_NAME`    = 'mytable'
-- necessary grouping parameters
GROUP BY rc.`CONSTRAINT_CATALOG`
, rc.`CONSTRAINT_SCHEMA` 
, rc.`TABLE_NAME`
, rc.`CONSTRAINT_NAME`
, rc.`UNIQUE_CONSTRAINT_CATALOG`
, rc.`UNIQUE_CONSTRAINT_SCHEMA`
, rc.`REFERENCED_TABLE_NAME`
, rc.`UNIQUE_CONSTRAINT_NAME`
-- optional ordering parameters
ORDER BY rc.`CONSTRAINT_CATALOG` ASC
, rc.`CONSTRAINT_SCHEMA` ASC
, rc.`REFERENCED_TABLE_NAME` ASC
, rc.`TABLE_NAME` ASC
;
0

You can use this for search your foreign key and Target_Table. All you need is change the where clause to find your specific key.

select distinct ref.referenced_table_name Source_Table,
                tab.constraint_name,
                tab.constraint_type,
                tab.table_name  Target_Table
from information_schema.table_constraints tab,
     information_schema.referential_constraints ref
where tab.constraint_name = ref.constraint_name;
0

Also of use when you want to know what ancillary tables reference a table's FK.

SELECT
TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
REFERENCED_TABLE_NAME = 'your_table_name';

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.