13

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?
RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536
user325643
  • 131
  • 1
  • 2
  • 4

5 Answers5

7

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.

RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536
2

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;
RLF
  • 14,035
  • 2
  • 34
  • 47
Alex
  • 21
  • 1
1

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

StanleyJohns
  • 5,982
  • 2
  • 25
  • 44
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';
siliconrockstar
  • 101
  • 1
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
;
carlososuna86
  • 11
  • 1