Mysql: Delete a row when a foreign key constraint is not allowing it

Sometimes when you work in development mode you would like to delete a row but a foreign key constraint might prevent you from doing so. Imagine that you have a table customer and a related table order. It’s possible that you will have a foreign key constraint that prevents deleting orders of specific customer without using the delete cascade option(i.e. to delete the customer as well as the orders). If for some reason, for the sake of testing something in development you want to achive this you can use the FOREIGN_KEY_CHECKS flag and disable it until you delete your order. An example with customer and order will be:

SET FOREIGN_KEY_CHECKS=0;
UPDATE customer set order_id = null WHERE id = 21;
DELETE FROM `order` WHERE id = 1; 
SET FOREIGN_KEY_CHECKS=1;

Running this query will actually allow you to delete the desired row despite having a foreign key constraing.

Leave a Reply

Your email address will not be published. Required fields are marked *