How to detect a circular reference in a hierarchy table?

Question

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?

Answer 1

Here's a thought:

1. Add a column depth SMALLINT UNSIGNED NOT NULL DEFAULT '0'

2. 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

3. If rows affected drops to 0, you have no loops.

4. After awhile, any nodes in loop(s) will have large depth values.

Answer 2

You can't do this in MySQL without complex triggers. In PostgreSQL, because you have CHECK constraints, it's very simple though

CREATE TABLE MyTable (
  Id       INT NOT NULL PRIMARY KEY,
  ParentId INT NOT NULL,
  UNIQUE ( greatest(id,parentid), least(id,parentid) )
);