Dropping and adding a foreign key

[lurtnowski]

Im trying to empty a few tables and I had to remove the foreign key constraint first , so I try to add it back after truncating the tables, like

ALTER TABLE racks DROP FOREIGN KEY rack_type_id;
ALTER TABLE blade_servers DROP FOREIGN KEY blade_server_type_id;
ALTER TABLE blade_servers DROP FOREIGN KEY rack_id;
ALTER TABLE chassises DROP FOREIGN KEY chassis_type_id;
ALTER TABLE chassises DROP FOREIGN KEY rack_id;
ALTER TABLE kvms DROP FOREIGN KEY kvm_type_id;
ALTER TABLE kvms DROP FOREIGN KEY rack_id;
ALTER TABLE monitors DROP FOREIGN KEY monitor_type_id;
ALTER TABLE monitors DROP FOREIGN KEY rack_id;
ALTER TABLE network_panels DROP FOREIGN KEY network_panel_type_id;
ALTER TABLE network_panels DROP FOREIGN KEY rack_id;
ALTER TABLE network_standards DROP FOREIGN KEY network_standard_type_id;
ALTER TABLE network_standards DROP FOREIGN KEY rack_id;
ALTER TABLE shelfs DROP FOREIGN KEY rack_id;
ALTER TABLE servers DROP FOREIGN KEY server_type_id;
ALTER TABLE servers DROP FOREIGN KEY rack_id;
ALTER TABLE periphial_storages DROP FOREIGN KEY periphial_storage_type_id;
ALTER TABLE periphial_storages DROP FOREIGN KEY rack_id;

TRUNCATE TABLE racks;
TRUNCATE TABLE blade_servers;
TRUNCATE TABLE chassises;
TRUNCATE TABLE kvms;
TRUNCATE TABLE monitors;
TRUNCATE TABLE network_panels;
TRUNCATE TABLE network_standards;
TRUNCATE TABLE shelfs;
TRUNCATE TABLE servers;
TRUNCATE TABLE periphial_storages;

ALTER TABLE racks ADD FOREIGN KEY (rack_type_id) REFERENCES rack_types(rack_type_id);
ALTER TABLE blade_servers ADD FOREIGN KEY (blade_server_type_id) REFERENCES blade_server_types(blade_server_type_id);
ALTER TABLE blade_servers ADD FOREIGN KEY (rack_id) REFERENCES racks(rack_id);
ALTER TABLE chassises ADD FOREIGN KEY (chassis_type_id) REFERENCES chassis_types(chassis_type_id);
ALTER TABLE chassises ADD FOREIGN KEY (rack_id) REFERENCES racks(rack_id);
ALTER TABLE kvms ADD FOREIGN KEY (kvm_type_id) REFERENCES kvm_types(kvm_type_id);
ALTER TABLE kvms ADD FOREIGN KEY (rack_id) REFERENCES racks(rack_id);
ALTER TABLE monitors ADD FOREIGN KEY (monitor_type_id) REFERENCES monitor_types(monitor_type_id);
ALTER TABLE monitors ADD FOREIGN KEY (rack_id) REFERENCES racks(rack_id);
ALTER TABLE network_panels ADD FOREIGN KEY (network_panel_type_id) REFERENCES network_panel_types(network_panel_type_id);
ALTER TABLE network_panels ADD FOREIGN KEY (rack_id) REFERENCES racks(rack_id);
ALTER TABLE network_standards ADD FOREIGN KEY (network_standard_type_id) REFERENCES network_standard_types(network_standard_type_id);
ALTER TABLE network_standards ADD FOREIGN KEY (rack_id) REFERENCES racks(rack_id);
ALTER TABLE shelfs ADD FOREIGN KEY (rack_id) REFERENCES racks(rack_id);
ALTER TABLE servers ADD FOREIGN KEY (server_type_id) REFERENCES server_types(server_type_id);
ALTER TABLE servers ADD FOREIGN KEY (rack_id) REFERENCES racks(rack_id);
ALTER TABLE periphial_storages ADD FOREIGN KEY (periphial_storage_type_id) REFERENCES periphial_storage_types(periphial_storage_type_id);
ALTER TABLE periphial_storages ADD FOREIGN KEY (rack_id) REFERENCES racks(rack_id);

but,

and it exists

[rpkamp]

This is easier:

SET foreign_key_checks = 0;

TRUNCATE TABLE racks;
TRUNCATE TABLE blade_servers;
TRUNCATE TABLE chassises;
TRUNCATE TABLE kvms;
TRUNCATE TABLE monitors;
TRUNCATE TABLE network_panels;
TRUNCATE TABLE network_standards;
TRUNCATE TABLE shelfs;
TRUNCATE TABLE servers;
TRUNCATE TABLE periphial_storages;

SET foreign_key_checks = 1;

Do make sure all tables that might be referencing each other are truncated though, or MySQL will get mad at you for having invalid foreign keys.