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>
I disabled the foreign key checks for some requirement of mine working on a test data set. It is absolutely important to think about why you are doing this and its repercussions. This could be used to bring some of your DB records in the correct state but should not become a part of your daily trouble-shooting. Foreign key constraints exist to enforce referential data integrity. In most cases, if you are trying to remove records, it is a good practice to process child records first and then go for the parents. The sole purpose of this article is to make you aware of the option which disables the foreign key constraints. Once done, do not forget to go back to the foreign key constrained world of data.
Other available options and arguments for ALTERING TABLES can be found here ..
Ciao!!!
{ problem: 'solved' }
Make sure you absolutely know what you are doing if you are temporarily disabling the foreign keys. That may be a bad habit to get into. If you make a mistake while deleting there could be no going back.
I would suggest spending the time to write an actual script to delete the records from the child up to the parent. This is the whole reason for the referential checks on integrity.
Do as I say, not as I do: thanks for the tip.
Easy to be a critic, but when using foreign keys building reverse cascading constraints, it is often a requirement to disable FOREIGN_KEY_CHECKS until all tables are written, when doing so you use START TRANSACTION with commit and/or rollback. Then makes it safe providing your script handles failures well. Certainly a bit of code to pay attention to and test heavily from the start.
Ahhh yes, so much rope. Foreign keys exist largely to keep folks from hanging themselves and generally if you need to turn them off you are doing something wrong. Restores and backups might be the possible exception but generally the folks doing that work know what there doing. I’d say, look once, twice, three times at the order at which you are adding and deleting records and consider those error messages your friend, not your enemy. If you have to turn off a check to update data, chances are your actions have a high risk of causing data corruption.
Missing @@ before variable name. Should be:
SET @@foreign_key_checks = 0;
Useful, thanks
Great.
Very useful
Thanks
One useful use-case for this is when you’re restoring data from a backup or doing an import. mysqldump outputs tables in alphabetical order, which means when you go to restore the dump, it’ll just run through all the tables in that order.
But that is often the wrong order when considering foreign keys and so on. So usually what I do is stick “SET foreign_key_checks = 0;” at the top of my dump, then do the import.
thank
@ssobczak not according to http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html…
Is there a way to drop tables that reference each other without “set foreign_key_checks=0”?
Drop the references, then the tables.
Thanks.
Good morning!
Take a dose of levitra 25 to 60 minutes before you wish to have sex with your partner.buying generic..
Pa!!!!
____________________________
generic cheap
I am part of a group of volunteers that is starting a new scheme in our neighbourhood in Denver. One of the community projects that we are going to to initiate relatates to your blog, and therefor some of the information here is of value for us and I just wanted so say thank you for that.
This is very useful when you’re creating a script for backup your database.
When using the statement “SHOW CREATE TABLE” to dump your tables , it include the CONSTRAINS and generate a error when you try to restore because of the tables order, here is where disabling the foreing checks it’s helpful.
oh …you saved me…I updated one row in production database by mistake but
SET foreign_key_checks = 0;
worked for me 🙂
LOVE this about MySQL!!! Way easier to deal with loading and re-loading data than SQL Server! 🙂
I had problems with the mysqlimport and foreign keys. When you insert all the data with .TXT files you can’t set parameter to mysqlimport that it would disable foreign key checks. I found that you can still set foreign_key_checks global system variable to off/zero. Here is full example: http://www.hackyours.com/2014/01/import-mysql-dump-folder-with-script.html
this link is not active now… please provide some other link or the content.
Thank you very much!
hello everyone here
I need the how to activate mysql
tnx
Thanks…