How can I temporarily disable a foreign key constraint in MySQL?
Try DISABLE KEYS
or
SET FOREIGN_KEY_CHECKS=0;
Make sure to
SET FOREIGN_KEY_CHECKS=1;
after.
How to disable and enable all constraints in table mysql incl. PK FK CHK UNI etc
Use the following to disable constraints:
-- disable UNIQ, PK, ...
ALTER TABLE <tablename> DISABLE KEYS;
-- diable FK
SET FOREIGN_KEY_CHECKS=0;
Check for instance this site for more examples. Restore with:
SET FOREIGN_KEY_CHECKS=1;
ALTER TABLE <tablename> ENABLE KEYS;
From the reference:
- foreign_key_checks
- DISABLE KEYS: " Use ALTER TABLE ... DISABLE KEYS to tell MySQL to stop updating nonunique indexes. ..."
p.s.: from InnoDB performance tuning tips
SET autocommit=0;
SET unique_checks=0;
SET foreign_key_checks=0;
How can foreign key constraints be temporarily disabled using T-SQL?
If you want to disable all constraints in the database just run this code:
-- disable all constraints
EXEC sp_MSforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
To switch them back on, run: (the print is optional of course and it is just listing the tables)
-- enable all constraints
exec sp_MSforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"
I find it useful when populating data from one database to another. It is much better approach than dropping constraints. As you mentioned it comes handy when dropping all the data in the database and repopulating it (say in test environment).
If you are deleting all the data you may find this solution to be helpful.
Also sometimes it is handy to disable all triggers as well, you can see the complete solution here.
Related Topics
The MySQL Extension Is Deprecated and Will Be Removed in the Future: Use MySQLi or Pdo Instead
Can't Connect to MySQL Server Error 111
Stored Procedure That Automatically Delete Rows Older Than 7 Days in MySQL
Error: Tcp Provider: Error Code 0X2746. During the SQL Setup in Linux Through Terminal
How to Change MySQL Table Names in Linux Server to Be Case Insensitive
Dplyr Left_Join by Less Than, Greater Than Condition
Gem Install: Failed to Build Gem Native Extension (Can't Find Header Files)
Activerecord Arel or Condition
How to Delete Duplicate Records in MySQL Database
Error When Trying to Install App With MySQL2 Gem
How to Select Rows With Max(Column Value), Partition by Another Column in MySQL
Why Is Select * Considered Harmful