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

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. – Chauncey McAskill May 14 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) – ypercubeᵀᴹ Jan 31 '18 at 8:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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