mysql disable referential integrity

mysql constraints referential-integrity
opensas picture opensas · Mar 31, 2012 · Viewed 7.7k times · Source

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?

Answer

BluesRockAddict picture BluesRockAddict · Mar 31, 2012

This should work:

SET @@foreign_key_checks = 0;

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

SET @@foreign_key_checks = 1;

Related questions

How to truncate a foreign key constrained table?

Why doesn't a TRUNCATE on mygroup work? Even though I have ON DELETE CASCADE SET I get: ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint (mytest.instance, CONSTRAINT instance_ibfk_1 FOREIGN KEY (GroupID) REFERENCES mytest.mygroup (ID)) …

Read More
MySQL Removing Some Foreign keys

I have a table whose primary key is used in several other tables and has several foreign keys to other tables. CREATE TABLE location ( locationID INT NOT NULL AUTO_INCREMENT PRIMARY KEY ... ) ENGINE = InnoDB; CREATE TABLE assignment ( assignmentID INT NOT …

Read More
How to remove constraints from my MySQL table?

I want to remove constraints from my table. My query is: ALTER TABLE `tbl_magazine_issue` DROP CONSTRAINT `FK_tbl_magazine_issue_mst_users` But I got an error: #1064 - You have an error in your SQL syntax; check the …

Read More