2

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?

2 Answers 2

2

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.
1
  • Thanks. It worked for me without the WHERE child.id != 0. May 14, 2021 at 17:05
0

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) )
);
1
  • 1
    How does UNIQUE ( greatest(id,parentid), least(id,parentid) ) saves us from the example posted? (2,4), (3,2), (4,3) Jan 31, 2018 at 8:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.