Menu
I am trying to delete a few records but am getting the following error:
Cannot delete or update a parent row: a foreign key constraint fails
The thing is, the foreign key constraint is failing for only 1 or 2 of my 100 records I wish to delete. I wish to write a query which deletes these 98-99 records, skipping the 1 or 2 which failed, which I can later manually inspect and delete/modify. Not stopping because of some single problematic record, but continuing with the others, ignoring that.
Is there a neat way to do this ?
366
You can drop a foreign key constraint using the following ALTER TABLE syntax: ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol; If the FOREIGN KEY clause defined a CONSTRAINT name when you created the constraint, you can refer to that name to drop the foreign key constraint.
Suppose there is a main table containing a primary key and there is another table which contains a foreign key to this main table. So if we delete the row of main table it will delete the child table also.
You have to LEFT JOIN the referencing table and add a condition saying that the row is missing in that table.
For example:
DELETE a FROM a
LEFT JOIN b ON b.a_id = a.id
WHERE b.a_id IS NULL;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
