I have a Java back end that connects to a SingleStore database, I have a table that has parent and child records. I need to make sure the user doesn’t create a circular reference as demonstrated by the 3 rows shown below:
id: 1 parent_id: 3 id: 2 parent_id: 1 id: 3 parent_id: 2
Its the kind of thing I’d solve in SQL normally like in this example, but all my research seems to say that MySQL doesn’t support a recursive join. The SingleStore DB works off the MySQL driver, so I think my only option is to do this in Java.
Has anyone seen an example of how to do this in Java? My first thought would be to start with the record they’re planning to update, and compare each child up the chain and make sure we don’t see the same Id occurring again.