CODEWITHSUNDEEP
mysqlphpmyadmin
John
John

Reputation: 315

mySQL: Foreign Key constraint when importing database

I am exporting a mySQL database from one server to another. My export file contains all the table definitions, data, structure etc. All tables use the InnoDB engine and the utf8 charset. I am importing with the 'enable foreign key checks' switched off - my export file also has the line 'SET FOREIGN_KEY_CHECKS=0;'.

However, when I import the data, I get the error '#1215 - Cannot add foreign key constraint'

Here is the table definition in the input file:

CREATE TABLE IF NOT EXISTS UserTable (
     Index_i int(13) NOT NULL,
     UserUUID_vc varchar(36) DEFAULT NULL,
     AccountID_i int(13) DEFAULT NULL,
     FirstName_vc varchar(30) DEFAULT NULL,
     LastName_vc varchar(40) DEFAULT NULL,
     Password_vc varchar(255) DEFAULT NULL,
     Country_vc varchar(2) DEFAULT NULL,
     DateRegistered_dt timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
     DateAccountTypeChanged_dt timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
     DateAccountStatusChanged_dt timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
     DateAcceptedTandC_dt timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB AUTO_INCREMENT=165 DEFAULT CHARSET=utf8;

It hits this problem before it reads any of the statements that apply constraints such as FK's to the table. In effect it doesn't know if any of these columns are foreign keys or not when it triggers the error message.

Any ideas?

Upvotes: 5

Views: 7773

Answers (2)

pawanpandey392
pawanpandey392

Reputation: 899

If you're using terminal, login to mysql using your credentials with following command.

mysql -u[your_username] -p[your_password]

Set your mysql foreign key constraint checks to 0 using following command.

SET FOREIGN_KEY_CHECKS = 0;

Import your database using source command like below.

SOURCE /path/to/your/sql/file.sql

And set your mysql foreign key constraint checks back to 1 using following command.

SET FOREIGN_KEY_CHECKS = 1;

Hurrrrrraaaaah. Works like a charm. :)

P.S :- Tested on ubuntu 14.04, 16.04, 18.04

Upvotes: 11

Jose A Flores Beowulfdgo
Jose A Flores Beowulfdgo

Reputation: 168

Add it as checkbox - disable foregin key check for this query in phpmyadmin to allow make changes on database

Upvotes: 1

Related Questions