MySQL - Delete row that has a foreign key constraint which reference to itself

1. Besides disabling foreign keys which is is dangerous and can lead to inconsistencies, there are two other options to consider:

2. Modify the FOREIGN KEY constraints with the ON DELETE CASCADE option. I haven't tested all cases but you surely need this for the (owner_id) foreign key and possibly for the other as well.

   ALTER TABLE forum
       DROP FOREIGN KEY owner_id_frgn,
       DROP FOREIGN KEY parent_id_frgn ;
   ALTER TABLE forum
       ADD CONSTRAINT owner_id_frgn
           FOREIGN KEY (owner_id) 
           REFERENCES forum (id)
           ON DELETE CASCADE,
       ADD CONSTRAINT parent_id_frgn
           FOREIGN KEY (parent_id) 
           REFERENCES forum (id)
           ON DELETE CASCADE ;
   

   If you do this, then deleting a node and all the descendants from the tree is simpler. You delete a node and all descendants are deleted through the cascade actions:

   DELETE FROM forum
   WHERE id = 1 ;         -- deletes id=1 and all descendants
   

3. The issue that you stepped into is actually 2 issues. The first is that deleting from a table with self-referencing foreign key is not a serious problem for MySQL, as long as there is no row that references itself. If there is a row, as in your example, the options are limited. Either disable foreign keys or use CASCADE action. But if there are no such rows, deleting becomes a smaller problem.

   So, if we decide to store NULL instead of the same id in owner_id, then you could delete without disabling foreign keys and without cascades.

   You would then stumble upon the second problem! Running your query would raise a similar error:

   DELETE FROM forum 
   WHERE owner_id = 1 OR id = 1 ; 
   

   Error(s), warning(s):
   Cannot delete or update a parent row: a foreign key constraint fails (rextester.forum, CONSTRAINT owner_id_frgn FOREIGN KEY (owner_Id) REFERENCES forum (id))

   The reason for this error would be different than before though. It's because MySQL checks each constraint after every row is deleted and not (as it should) at th eend of the statement. So when a parent is deleted before its child is deleted, we get a foreign key constraint error.

   Fortunately, there is a simple solution for this, thnx to the nested set model and to that MySQL allows us to set an order for the deletes. We just have to order by nleft DESC or by nright DESC, which makes sure that all the children are deleted before a parent:

   DELETE FROM forum 
   WHERE owner_id = 1 OR id = 1 
   ORDER BY nleft DESC ; 
   

   Minor note, we could (or should) use a condition that considers the nested model as well. This is equivalent (and might use an index on (nleft, nright) to find which nodes to delete:

   DELETE FROM forum 
   WHERE nleft >= 1 AND nright <= 8 
   ORDER BY nleft DESC ; 
   

SET FOREIGN_KEY_CHECKS=0;
DELETE FROM forum WHERE Owner_Id = 1 ORDER BY nright;
SET FOREIGN_KEY_CHECKS=1;

just not forget in this case You must manually parse situations when parent_id show to 1, because You not use cascade