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. But if there are no such rows, deleting becomes a smaller problem. Either disable foreign keys or use CASCADE action. If there is a row, as in your example, the options are limited. 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. The issue that you stepped into is actually 2 issues. WHERE id = 1 - deletes id=1 and all descendants You delete a node and all descendants are deleted through the cascade actions: DELETE FROM forum If you do this, then deleting a node and all the descendants from the tree is simpler. I haven't tested all cases but you surely need this for the (owner_id) foreign key and possibly for the other as well. Modify the FOREIGN KEY constraints with the ON DELETE CASCADE option. I created a demo of this scenario: !9/fd1b1īesides disabling foreign keys which is is dangerous and can lead to inconsistencies, there are two other options to consider:
#TABLEPLUS SEE FIELD RESTRICTIONS UPDATE#
My question is: How can I prevent this foreign key constraint circularity and delete a row that reference to itself? Is there a way to do it without first having to update the the Owner_Id of the root row to NULL? The reason is that the first row, which is the root node ( Id=1), also has the same value in its Owner_Id field ( Owner_Id=1), and it causing the query to fail due to the foreign key constraint. Cannot delete or update a parent row: a foreign key constraint fails ( forumTbl, CONSTRAINT Owner_Id_frgn FOREIGN KEY ( Owner_Id) REFERENCES forumTbl ( Id) ON DELETE NO ACTION ON UPDATE NO ACTION)
The above query fails with the following error:Įrror Code: 1451.
Example: DELETE FROM forumTbl WHERE Owner_Id = 1 ORDER BY nright My problem occurs when I try to delete all the rows under the same Owner_Id in a single query.
Note that the first row is the root message, and the tree of this post can be displayed as: - SELECT * FROM forumTbl WHERE Owner_Id = 1 ORDER BY nleft | Id | Owner_Id | Parent_Id | nleft | nright | nlevel | Now, the table is looking something like this: + - + - + - + - + - + - + Parent_Id (FOREIGN KEY REFERENCES TO Id).Owner_Id (FOREIGN KEY REFERENCES TO Id).The following is a simplified structure of the table: The messages hierarchy strucrue is implement using a Nested set model. I have a table in which I store all the forum messages posted by the users on my website.
0 kommentar(er)
