How to Recursively Query Referential Constraints in MySQl 8.0.23?-CodePudding

I am trying to write a recursive query in MySql 8.0.23 to return direct and indirect foreign key relationships pointing to a specified table. Beginning with table T1, the result set should reveal that each of T2 and T3 point to T1 (via an established constraint), that T4 points to T2, that T5 and T6 point to T3, etc.

Following is the query I'm using, which hangs / seems to runaway forever:

WITH RECURSIVE CTE AS (
    SELECT REFERENTIAL_CONSTRAINTS.REFERENCED_TABLE_NAME, REFERENTIAL_CONSTRAINTS.TABLE_NAME, 0 AS DEPTH 
      FROM information_schema.REFERENTIAL_CONSTRAINTS       
     WHERE REFERENTIAL_CONSTRAINTS.CONSTRAINT_SCHEMA = 'MY_SCHEMA' AND REFERENTIAL_CONSTRAINTS.REFERENCED_TABLE_NAME = 'TABLE_A'
             
UNION ALL 
            
    SELECT REFERENTIAL_CONSTRAINTS.REFERENCED_TABLE_NAME, REFERENTIAL_CONSTRAINTS.TABLE_NAME, CTE.DEPTH   1 
      FROM CTE 
INNER JOIN information_schema.REFERENTIAL_CONSTRAINTS ON CTE.TABLE_NAME = REFERENTIAL_CONSTRAINTS.REFERENCED_TABLE_NAME 
) 

   SELECT * 
     FROM CTE;

I would appreciate any advice on what I'm doing wrong. Thanks.

CodePudding user response：

Maybe you have a table that references itself or a wider loop, you have to keep track of the tables you already checked, like in table_list below :

WITH RECURSIVE CTE AS
(
    SELECT
      concat('`', rc.REFERENCED_TABLE_NAME, '`', '<-', '`', rc.TABLE_NAME, '`') as table_list,
      rc.REFERENCED_TABLE_NAME,
      rc.TABLE_NAME,
      0 AS DEPTH
    FROM information_schema.REFERENTIAL_CONSTRAINTS rc
    WHERE rc.REFERENCED_TABLE_NAME = 'TABLE_A'
    UNION ALL
    SELECT
      concat(table_list, '<-', '`', rc.TABLE_NAME, '`') as table_list,
      rc.REFERENCED_TABLE_NAME,
      rc.TABLE_NAME,
      CTE.DEPTH   1
    FROM CTE
      INNER JOIN information_schema.REFERENTIAL_CONSTRAINTS rc ON CTE.TABLE_NAME = rc.REFERENCED_TABLE_NAME
    WHERE
      (rc.table_name != rc.REFERENCED_TABLE_NAME and table_list not like concat('`%<-', '`', rc.TABLE_NAME, '`%'))
      or (rc.table_name = rc.REFERENCED_TABLE_NAME and table_list not like concat('%`', rc.TABLE_NAME, '`', '<-', '`', rc.TABLE_NAME, '`%'))
)
SELECT
  *
FROM CTE;

db<>fiddle