CODEWITHSUNDEEP
mysqlmysql-workbench
Manuel Jordan
Manuel Jordan

Reputation: 16301

'SET foreign_key_checks = 1' does not work again

I am working with:

I have the following sequence

DROP TABLE IF EXISTS invoicedetail;
DROP TABLE IF EXISTS invoiceheader;
DROP TABLE IF EXISTS product;

It fails when I work through Java (JDBC) and fails through the MySQLWorkBench too, the error message is about the FK Child constraints (I don't have the exact error message), well through Google I did a research and I have found the same valid solution twice.

Well I did the following:

SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS invoicedetail;
DROP TABLE IF EXISTS invoiceheader;
DROP TABLE IF EXISTS product;
SET FOREIGN_KEY_CHECKS=1;

Again it works, just playing, I tried to execute again

DROP TABLE IF EXISTS invoicedetail;
DROP TABLE IF EXISTS invoiceheader;
DROP TABLE IF EXISTS product;

Being sure that SELECT @@FOREIGN_KEY_CHECKS; returns 1

Well the original error message does not appear again.

Again I did a research on Google and I found the following:

Well it says: It is session-based

Well, even when:

and SELECT @@FOREIGN_KEY_CHECKS; returns 1

The original error message does not appear again.

Therefore seems 'SET foreign_key_checks = 1' does not work how is expected or an extra configuration/instruction is needed

I want be able to disable and enable that FK security constraint anytime

Upvotes: 13

Views: 12383

Answers (2)

maxrzaw
maxrzaw

Reputation: 143

I had a similar issue. It turns out that any rows you modified after “SET foreign_key_checks = 0;” will not be checked in the future, even after “SET foreign_key_checks = 1;”

Found here: https://www.mysqltutorial.org/mysql-disable-foreign-key-checks/

Upvotes: 0

Juanmi Taboada
Juanmi Taboada

Reputation: 545

I had a similar problem with PHPMyAdmin. After all, the problem was that PHPMyAdmin was adding (by default) the "FOREIGNK_KEY_CHECKS=1".

When I was querying the server with PHPMyAdming the option "Enable foreign key checks" was checked at the end of the page, so none of the "SET FOREIGN_KEY_CHECKS=0" inside my query was actually working.

Please, make sure the solution to your problem is not something so silly like this. ;-)

Upvotes: 15

Related Questions