TAGS :Viewed: 8 - Published at: a few seconds ago

[ Deleting row with a foreign key constraint ]

I have a MySQL table called 'node', with a foreign key named 'parentNodeId' which references another row in this table; this way, I can have nodes within other nodes through a foreign key.

Ok then, the problem is when I want to delete a row in this table, a SQL error outputs:

Cannot delete or update a parent row: a foreign key constraint fails (PFeCommerce.node, CONSTRAINT FK_857FE84548C76A8E FOREIGN KEY (parentNodeId) REFERENCES node (id))

So I go to the row that I want to delete, and I set the field 'parentNodeId' with a NULL value. But the error still happening.

What am I doing wrong?

Answer 1

Thre problem is not with you wanting to delete a child node, but that the node that you want to delete is a parent node for another element

See 2 cases:

parent node // this one has id = 1
   child node // this one has parentNodeID set to 1

if you delete the child node, there is no reason for error as only the child node depends on the parent node, no other data depends on child node

in your case:

parent node // id = 1
   child node   // this is the one you want to delete, parentNodeId = 1
      another child node 

now what your are doing, is you delete "child node" but another node (another child node) has its id in the parentNodeId field so if you would delete the "child node", there would be a relation pointing to non-existing record, which causes an error due to key constraint