How to disable foreign key check on database import via console?

Question

I know I can import a database dump like this:

mysql -u {{username}} -p {{target_database}} < myDump.sql

e.g.

mysql -u root -p theDatabase < myDump.sql

But how to import a database with foreign key check turned off?

I searched the mysql help for helpful parameters, but I found none:

mysql --help | findstr "foreign"

Returns nothing

Possible duplicate of [How to temporarily disable a foreign key constraint in MySQL?](https://stackoverflow.com/questions/15501673/how-to-temporarily-disable-a-foreign-key-constraint-in-mysql) — Nico Haase, Jun 20 '18 at 16:13

score 10 · Accepted Answer · edited Jun 20 '18 at 16:20

10

First edit your database file and put SET FOREIGN_KEY_CHECKS = 0; at beginning and SET FOREIGN_KEY_CHECKS = 1; at the end of the database file.

This will help turn off the foreign key checks while restoring and it will also turn on the checks after restoration.

edited Jun 20 '18 at 16:20

Robert Mennell

answered Jun 20 '18 at 16:13

Ranvir

Does this work with any user or it requires some special privileges or root access? – hypercube Jan 11 '21 at 09:40
@hypercube The foreign_key_checks system variable requires the SUPER privilege to dynamically set its global value at runtime. The foreign_key_checks system variable does not require any special privilege to dynamically set its session value at runtime. – Ranvir Aug 07 '23 at 05:27

1 Answers1