Menu
I want to drop all the schemas in a mysql db, and I don't want to be bothered with referential integrity errors
in h2 it's done like this
SET REFERENTIAL_INTEGRITY FALSE;
drop table if exists company;
drop table if exists computer;
SET REFERENTIAL_INTEGRITY TRUE;
How can it be achieved in mysql?
222
To turn off referential integrity, you have to disable or remove foreign key constraints. Try running this in Query Analyzer saving the results to a file. Then, run the file in Query Analyzer. Do the same with this script to re-enable the constraints.
You can disable foreign key check in MySQL by setting the system variable foreign_key_checks to 0. However, please note, after you enable foreign key checks, MySQL will not re-validate your existing data that you added after disabling foreign key check. It will only check any new additions/updates to your database.
To disable a foreign key constraint for INSERT and UPDATE statements. In Object Explorer, expand the table with the constraint and then expand the Keys folder. Right-click the constraint and select Modify. In the grid under Table Designer, select Enforce Foreign Key Constraint and select No from the drop-down menu.
This should work:
SET @@foreign_key_checks = 0;
DROP TABLE IF EXISTS company;
DROP TABLE IF EXISTS computer;
SET @@foreign_key_checks = 1;
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS company;
DROP TABLE IF EXISTS computer;
SET FOREIGN_KEY_CHECKS = 1;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
