update cascade in self referencing table - best practice?

Question

It seems that it not possible to set an update cascade on a self-referencing table.

As a consequence, it is not possible to simply rename a parent node. The only workaround I can see is to create a new entry, and then to re-link all child nodes (and probably data from other tables), and then to delete the old entry.

As this is rather complicated, is there probably a better solution, which I cannot see in the moment?

Answer 1

The simplest (I guess that depends on your specific case) solution would probably be to create a IDENTITY primary key and reference/self reference on that instead of the name which is then a simple field.

That will allow you to rename nodes without affecting any dependant nodes, and will make it much less complicated to update the structure while still maintaining foreign key relationships from other tables.