13

I am working with:

  • mysql: stable 5.6.22 (bottled)
  • MySQLWorkBench 6.2

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:

  • mysql server is stopped and restarted again
  • workbench re-opened
  • Computer re-started

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

Manuel Jordan
  • 15,253
  • 21
  • 95
  • 158

2 Answers2

15

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. ;-)

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Juanmi Taboada
  • 539
  • 10
  • 25
0

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/

maxrzaw
  • 121
  • 5
  • 1
    I think you're misreading this: "Notice that setting `foreign_key_checks` to 1 does not trigger any validation of the existing table data. In other words, MySQL will not verify the consistency of the data that was added during the foreign key check disabled." It's not saying they _never_ get checked again; it's saying they're not rechecked immediately upon reenabling the flag. – shmosel Apr 15 '22 at 17:00