How to disable or remove foreign key before importing database to phpmyadmin?

  • We can import any heavy database into phpmyadmin from command prompt. But many tables have foreign key and it is not allowed to insert a table having foreign key. So how to remove or disable foreign key in order to import database in phpmyadmin?

  • Ya, we can set foreign key check as disable before importing database into phpmyadmin.

    What we need to do is, edit that database file i.e. yourdatabase.sql and add 2 lines in it.

    1) Add in just beginning of database file

    SET FOREIGN_KEY_CHECKS = 0;

    2) Just before end of file at last line of file.

    SET FOREIGN_KEY_CHECKS = 1;

    First it will disable foreign key checks and in the end, it will enable it again.

  • GO to export in in phpmyadmin follow below step.

    enter image description here

    enter image description here

  • You can also set it from the commandline directly into the mysql command.

    mysql --init-command="SET SESSION FOREIGN_KEY_CHECKS=0;" ...
    

License under CC-BY-SA with attribution


Content dated before 7/24/2021 11:53 AM