![]() ![]() 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 Comments
Leave a Reply. |