Disable Foreign Key checks or constraint in Mysql

 

Referential integrity/relation is one of the best feature for database architecuture. But in certain case we need to clear or disable these checks otherwise we can’t do normal modification within our data because if tow tables are following primary key and foreign key constraint then we must have to follow these relation otherwise we may get database errors.

The easiest way to disable foreign key is below command.

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.

Related posts:

The following two tabs change content below.

Chandra Shekhar

Consultant at Infogain
Chandra Shekhar is a LAMP Developer,Thinker,Blogger and currently working as Magento(Ecommerce) specialist.. I am always open to learn new things in LAMP as well as other newer technology. In the journey of my last 9 years of web and product development, I have decided to share my knowledge and experience to Open Source Community in 2011. In this regard I have started CuteHits.com. Since then I am actively sharing my knowledge on various LAMP technology through CuteHits.com’s technical ... Read More about Chandra Shekhar