How to find dependencies on a table in mysql 5.0?

Question

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?

Answer 1

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.

Answer 2

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