Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

mysql disable referential integrity

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?

like image 222
opensas Avatar asked Mar 31 '12 16:03

opensas


People also ask

How do you turn off referential integrity?

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.

How do I disable foreign key checks in MySQL?

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.

How to turn off foreign key constraint?

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.


2 Answers

This should work:

SET @@foreign_key_checks = 0;

DROP TABLE IF EXISTS company;
DROP TABLE IF EXISTS computer;

SET @@foreign_key_checks = 1;
like image 90
BluesRockAddict Avatar answered Sep 24 '22 17:09

BluesRockAddict


SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS company;

DROP TABLE IF EXISTS computer;

SET FOREIGN_KEY_CHECKS = 1;
like image 26
ruakh Avatar answered Sep 21 '22 17:09

ruakh