MySQL - cannot add foreign key constraint (1215)

Question

I have created a SQL file using mysqldumnp (from MySQL 5.6.27) and am using it to re-create a database (to MySQL 5.7.9):

CREATE TABLE `my_table` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `email` varchar(255) DEFAULT NULL,
  `user_id` bigint(20) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `my_table_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

I get this error:

[Err] 1215 - Cannot add foreign key constraint

I then ran the query SHOW ENGINE INNODB STATUS and got the following form the 'LATEST FOREIGN KEY ERROR' heading:

2016-02-05 12:27:08 0x7f1b8f54b700 Error in foreign key constraint of table my_db/my_table: FOREIGN KEY (user_id) REFERENCES users (user_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT: Cannot resolve table name close to: (user_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT

I have also tried running this SQL command after having run set FOREIGN_KEY_CHECKS = 0; but I still get the same error.

This seemed to suggest that the problem wa that the users table is required to be in place before I can run this query. However, I have another part of the SQL dump (before this part) that has a foreign key constraint that reference the exact same table and column - users.user_id - and this runs without any errors.

So I am, 1) confused as to why this is happening and 2) curious to know if there is any method of instructing mysqldump to format the dump file in such a way that the commands to create the tables are first in the dump file and the commands that add the foreign keys come afterward, hence circumventing the issue with tables that do not exist.

Note: I have also used the Data Tranfer facility within Navicat to copy across the same database that the dump file contains and it ran ok. This seems to suggest that whatever routine Navicat is uaing mananges to deal with the constraints, whereas trying to import the dump file using commandline does not.

The table 'users' does not exist when the CREATE TABLE `my_table` ... command is run, so why would you want to see this? — JoeTidee, Feb 05 '16 at 13:33
As mentioned, there is a query in the dump file that also creates a foreign key with reference to users.user_id (and with a default of NULL) and it runs successfully, so this cannto be the case. If this IS the case, the why would mysqldump not add all the foreign key creations to the end of the dump file to avoid the problem that when re-creating the database all the tables are not going to be in place until the entire file contents are executed. — JoeTidee, Feb 05 '16 at 13:50

score 1 · Answer 1 · answered Jun 22 '16 at 16:19

1

Open the .sql backup file and move SQL scripts (create and inserts) of my_table after users and run the restore process again.

The user table must exist before create a referenced foreign key.

Maybe other tables will have the same problem, make sure the table dependencies are ok.

answered Jun 22 '16 at 16:19

Ivan Cachicatari

4,212
2
21
41

2

I'm surprised that MySQL dump does not sumpliy append the key contraint code to the end of the file. – JoeTidee Jun 23 '16 at 16:12

Doug Couvillion · Answer 2 · 2020-12-03T21:06:49.973

I've run into this problem and found it can be avoided by using the --single-transaction option when creating the MySQL dump file.

Here's an example, assuming you're on a Linux system:

$ mysqldump --databases YourDatabaseNamesHere \
            --user=YourUserNameHere \
            --password=YourPasswordHere \
            --single-transaction > dump.sql

Beware, using the --compact option prevents the dumpfile comments that ignore the foreign keys from being written to the file.

MySQL - cannot add foreign key constraint (1215)

2 Answers2