CODEWITHSUNDEEP
mysql
TBA
TBA

Reputation: 1187

MySql Finding a tree structure using Reference Keys

We have a parent table which stores the user details. Since now we were doing soft delete. However due to some legal commitments, we are forced to do a hard delete for the user details.

So the problem is the main table is referenced many places. We could able to find all the referenced tables with the following query in MySQL

USE information_schema;
SELECT TABLE_NAME, Column_Name,Constraint_Name
FROM
  KEY_COLUMN_USAGE
WHERE
  REFERENCED_TABLE_NAME = 'ProjectUser'
  AND REFERENCED_COLUMN_NAME = 'userid'
  AND TABLE_SCHEMA = 'testproduct';

It was a success as we got all tables around 45. However the real problem is, it is possible that the child table of "ProjectUser" also may referenced somewhere else.

For example, one of the child table UserAddress is used as foriegn Key for some other table. How can I query to bring all the tables, which reference ProjectUser, and their child tables and grand child tables?

Upvotes: 0

Views: 64

Answers (1)

cs_alumnus
cs_alumnus

Reputation: 1649

There is no query that's going to get you out of this mess. You can write a program to successively run queries to build this structure, but you will not be able to do it all in one query.

1) Use mysqldump, write something to parse the dump and build the tree

2) Use a tool to visualize the schema such as (schemaspy)[http://schemaspy.sourceforge.net/] or (mysql workbench)[http://www.mysql.com/products/workbench/]

3) Take @AlmaDo's suggestion seriously and add ON DELETE CASCADE. You can drop an existing foreign key and readd it using the later table function. If you have problems re adding the foreign key due to constraint violation, disable keys while you re add the foreign key.

Upvotes: 1

Related Questions