How to delete records by disabling foreign key checks or constraints in mysql

If you have a foreign key constraints on your table, you can’t delete records from parent table, but you can delete from child table. The main reason we use the foreign key constraints is to maintain data integrity.

Some times you many need to disable this kind of enforcing against some operations like delete. To do so, you have to set mysql server system variable foreign_key_checks

Now, you can delete or update with out any restriction because of foreign key constraint. But you will mess up the data integrity.

You can reset it back to enforce for integrity

There are global and local system variables. If you set the global variables it applies to all clients which are connected. If you change the local variable it applies to that current client session only. Here is how you can query local and global variable.

To sent global variable you just need to add global , where to set local you need to local. By default local or session variable will be changed

To set global variable

NOTE: Make sure you to revert the variable back to original state to maintain data integrity in your database.

 

Leave a Reply

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