To disable foreign key constraints when you want to truncate a table:
and remember to enable it when you’re done:
Or you can use
ALTER TABLE table_name DISABLE KEYS;
Again, remember to enable if thereafter:
ALTER TABLE table_name ENABLE KEYS;
DISABLE KEYS does not work on InnoDB tables as it works properly for MyISAM.
ON DELETE SET NULL
If you don’t want to turn key checking on and off, you can permanently modify it to
ON DELETE SET NULL:
Delete the current foreign key first:
ALTER TABLE table_name1 DROP FOREIGN KEY fk_name1; ALTER TABLE table_name2 DROP FOREIGN KEY fk_name2;
Then add the foreign key constraints back
ALTER TABLE table_name1 ADD FOREIGN KEY (table2_id) REFERENCES table2(id) ON DELETE SET NULL; ALTER TABLE tablename2 ADD FOREIGN KEY (table1_id) REFERENCES table1(id) ON DELETE SET NULL;
Need a good GUI Tool for MySQL? TablePlus is a modern, native tool with an elegant UI that allows you to simultaneously manage multiple databases such as MySQL, PostgreSQL, SQLite, Microsoft SQL Server and more.
Not on Mac? Download TablePlus for Windows.
On Linux? Download TablePlus for Linux
Need a quick edit on the go? Download TablePlus for iOS.