Detect circular dependency before insert

Question

I'm having 2 MySQL tables

Person Table
============
Name
----
person1
person2
person3

Person Dependencies
===================
person   dependency_person
--------------------------
person1  person2
person2  person3
person3  person1

in the Person Dependencies table, is it possible to check for a circular dependency before inserting a row like in the third row(person3 person1)

you need [recursive queries](https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query) for that — Iłya Bursov, Apr 16 '22 at 00:19

score 0 · Answer 1 · answered Apr 16 '22 at 01:18

You can try to use ROW_NUMBER window function with LEAD window function to make the number of relation rows then do self-join

WITH CTE AS (
  SELECT Name, ROW_NUMBER() OVER(ORDER BY Name) rn
  FROM Person
)
SELECT c1.Name,c2.Name 
FROM CTE c1
INNER JOIN (
  SELECT Name,rn,LEAD(rn,1,1) OVER(ORDER BY Name) n_rn
  FROM CTE
) c2
ON c1.rn = c2.n_rn

sqlfiddle

Detect circular dependency before insert

1 Answers1