How to detect a circular reference in a hierarchy table? I have table like this:

CREATE TABLE MyTable (
    Id INT NOT NULL PRIMARY KEY,
    ParentId INT NOT NULL
);

ALTER TABLE MyTable ADD FOREIGN KEY (ParentId) REFERENCES MyTable(Id);
select * from MyTable
desc MyTable

INSERT INTO MyTable (Id, ParentId) VALUES (0, 0);
 INSERT INTO MyTable (Id, ParentId) VALUES    (1, 0);
 INSERT INTO MyTable (Id, ParentId) VALUES    (2, 4);
 INSERT INTO MyTable (Id, ParentId) VALUES    (3, 2);
 INSERT INTO MyTable (Id, ParentId) VALUES    (4, 3);


How to find a circular reference in table references?
Here's a thought:


Add a column depth SMALLINT UNSIGNED NOT NULL DEFAULT '0'
Repeatedly bump depth to be at least 1 more than the parent:

UPDATE MyTable AS child
  JOIN MyTable AS parent ON child.ParentId = parent.Id
  SET child.depth = GREATEST(child.depth, parent.depth + 1)
  WHERE child.Id != 0;   -- the root says at depth=0
If rows affected drops to 0, you have no loops.
After awhile, any nodes in loop(s) will have large depth values.