DB-Import canceled: "Cannot add foreign key constraint"

Question

I'm trying to move a magento shop to my server. Unfortunatly I'm not a backend developer - only CSS, HTML and other frontend stuff. Hopefully someone can give me an advice on what to do.

I got a db dump from an agency which I wanted to import and stops with the error message:

BigDump: Staggered MySQL Dump Importer v0.36b Processing file: 2018-03-27_XXXXXXXXX.sql

Starting from line: 1

Error at the line 385: ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Api Sessions';

Query: CREATE TABLE `api_session` (
`user_id` int(10) unsigned NOT NULL COMMENT 'User id',
`logdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Login date',
`sessid` varchar(40) DEFAULT NULL COMMENT 'Sessioin id',
KEY `IDX_API_SESSION_USER_ID` (`user_id`),
KEY `IDX_API_SESSION_SESSID` (`sessid`),
CONSTRAINT `FK_API_SESSION_USER_ID_API_USER_USER_ID` FOREIGN KEY (`user_id`) REFERENCES `api_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Api Sessions'

MySQL: Cannot add foreign key constraint

I really have no clue on what to do but I'm willing to learn what to do. Can anyone give me a hint what the problem is?

===============

I managed to import the DB with another Script. I can't offer a solution it just works now.

Is the api_user table already in place when you run this query? — mike.k, Mar 29 '18 at 16:46
Check that all the conditions here https://dev.mysql.com/doc/refman/5.5/en/create-table-foreign-keys.html have been met. — P.Salmon, Mar 30 '18 at 08:37

score 0 · Answer 1 · answered Mar 29 '18 at 16:50

I'd guess the table it references, "api_user" does not exist at this time? Notably more likely since api_user is alphabetically after api_sessions.

You could probably try separating the structure from the data (if your import contains any data) and just run the structure repeatedly in "import and continue on error" mode, hoping it will build the api_user and in a later pass api_sessions. This won't work if you have circular dependancies though.

You could try something like this How to temporarily disable a foreign key constraint in MySQL? as a better solution probably... Assuming my speculation is correct. Check what it's trying to reference in the foreign key constraint though.

DB-Import canceled: "Cannot add foreign key constraint"

1 Answers1