FK Issue

HeidiSQL 9.4.0.5169

I export my database to a single non-compressed SQL file.

When I try to re-import without any changes I get errors. Examining the code I find this:

-- Dumping structure for table capertime_emotivesoftware.emo_models
DROP TABLE IF EXISTS `emo_models`;
CREATE TABLE IF NOT EXISTS `emo_models` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `WebsiteID` int(11) NOT NULL,
  `ManufacturerID` int(11) NOT NULL,
  `ModelID` int(11) NOT NULL,
  `Name` varchar(50) NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `FK_emo_models_sys_websites` (`WebsiteID`),
  CONSTRAINT `FK_emo_models_emo_manufacturers` FOREIGN KEY (`ManufacturerID`) REFERENCES `emo_manufacturers` (`ManufacturerID`),
  CONSTRAINT `FK_emo_models_sys_websites` FOREIGN KEY (`WebsiteID`) REFERENCES `sys_websites` (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=587 DEFAULT CHARSET=utf8;

It complains there is an issue with creating foreign keys. Looking at the above SQL there appears to be a KEY... line missing for one of the foreign keys.

I don't really have any idea why this line would be missing from the export though.

I hope this helps.

Chris.

Is that all that the message says, "errors"?

What do you mean with missing KEY?

Please check if your referenced tables are exist. I see that export file that generated by HeidiSQL have some bug. Whereas it create constraint at first, meanwhile referenced table not created yet.

If you compare HeidiSQL's backup file with PhpMyAdmin's backup file, you will see this different.

Imaging that you have big tables table1, table2 and table3. Table3 has FK to table1.

1. table1 is dumped
2. while table2 has being dumped, a few records were inserted in table1 and table3.
3. table3 is dumped

So you ended up with a dump where you have a few records in table3 that are pointed to missed records in table1.

To be on a safe side you should use mysqldump with --lock-all-tables options specified.