Databases are all about saving data. With DBMS and RDBMS, the entire data became relational and all the records became related to each other as in the real world. So came into existence the concepts of primary keys, foreign keys, foreign key constraints and whole bunch of other terms like composite keys, referential integrity, indexes and what not.
So coming back to the objective of pinning down this post, some days ago I came across the requirement of deleting some user records from the user table. As soon as I tried deleting the records, I came across the error of referential integrity where in I was greeted with the error that child records were there and hence the delete operation was not allowed.
So I searched for a shortcut which could let me do my task. And I came across this …
SET foreign_key_checks = 0;
DELETE FROM users where id > 45;
SET foreign_key_checks = 1;
By setting the foreign key check to 0, I was able to update / delete my users table. Once I was done with my operations on the user table, I reset the key check to 1 again and everything is back in place now.
You can always drop a FOREIGN KEY the usual way ..
ALTER TABLE users DROP FOREIGN KEY <foreign_key_name>
As correctly pointed out by tboehm in the comments, I disabled the foreign key checks for some requirement of mine. It is absolutely important to think about the repercussions of this. It is always advisable to start with the child entries and then proceed for the parents. The sole purpose of this article is to make you aware of the option which disables the foreign key constraints.
I guess other available options and arguments for ALTERING TABLES can be found here ..