CODEWITHSUNDEEP
mysqlforeign-keys
mseifert
mseifert

Reputation: 5670

Adding Foreign Key Fails in MySQL - errno 150 - using mysqldump

UPDATE: Darn - wouldn't you know it that the minute I posted I got the aha! The main table needs to come first in the creation.

UPDATE 2: For anyone following, the problem was with my mysqldump command containing the --compact option. This removed the mysqldump feature of including FOREIGN_KEY_CHECKS=0; at the top of the dump file. Now with compact removed, the problem is completely solved. No need to manually insert this in my dump file.

Can I specify the order of tables when doing a mysqldump?

I am uploading localhost MySQL database to my shared server. I get the following error trying to re-create a table with a foreign key . "#1005 - Can't create table 'username_test.gemdetail' (errno: 150)" The sql statement is below. When the Constraint clause is removed, the tables import just fine)

From the MySQL - FOREIGN KEY Constraints Documentation: If you re-create a table that was dropped, it must have a definition that conforms to the foreign key constraints referencing it. It must have the right column names and types, and it must have indexes on the referenced keys, as stated earlier. If these are not satisfied, MySQL returns error number 1005 and refers to error 150 in the error message.

I've checked for the following conditions (correct me please if I've missed something):

1) The two tables must be ENGINE=InnoDB. - YES 2) The two tables must have the same charset. - YES 3) The PK column(s) in the parent table and the FK column(s) must be the same data type. - YES 4) The PK column(s) in the parent table and the FK column(s), if they have a define collation type, must have the same collation type; - NOT DEFINED 5) If there is data already in the foreign key table, the FK column value(s) must match values in the parent table PK columns. - NO DATA 6) And the child table cannot be a temporary table. - N/A

I've also tried importing to a new empty database. Any ideas? Thanks in advance for your help.

The relevant part of the sql code that mysqldump generates (I've removed other columns & keys for readability sake but this stripped down code produces the same result):

DROP TABLE IF EXISTS `gemdetail`;
CREATE TABLE `gemdetail` (
  `gemdetailid` int(10) NOT NULL AUTO_INCREMENT,
  `gemid` int(10) NOT NULL,
  PRIMARY KEY (`gemdetailid`),
  KEY `gemid` (`gemid`),
  CONSTRAINT `gemdetail_ibfk_1` FOREIGN KEY (`gemid`) REFERENCES `gems` (`gemid`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=875 DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `gems`;
CREATE TABLE `gems` (
  `gemid` int(10) NOT NULL AUTO_INCREMENT,
  `userid` int(10) NOT NULL,
  PRIMARY KEY (`gemid`),
  KEY `userid` (`userid`)
) ENGINE=InnoDB AUTO_INCREMENT=528 DEFAULT CHARSET=utf8;

Lastly the Database version info:

Server: Localhost via UNIX socket Software: MySQL Software version: 5.5.32-log - MySQL Community Server (GPL) (BetterLinux 01 Sep 5 2013 23:04:40) Protocol version: 10

Upvotes: 0

Views: 1364

Answers (2)

Filipe Silva
Filipe Silva

Reputation: 21657

You have to create table gems first, since you are referencing it in gemdetail table, so it has to exist:

DROP TABLE IF EXISTS `gems`;
CREATE TABLE `gems` (
  `gemid` int(10) NOT NULL AUTO_INCREMENT,
  `userid` int(10) NOT NULL,
  PRIMARY KEY (`gemid`),
  KEY `userid` (`userid`)
) ENGINE=InnoDB AUTO_INCREMENT=528 DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `gemdetail`;
CREATE TABLE `gemdetail` (
  `gemdetailid` int(10) NOT NULL AUTO_INCREMENT,
  `gemid` int(10) NOT NULL,
  PRIMARY KEY (`gemdetailid`),
  KEY `gemid` (`gemid`),
  CONSTRAINT `gemdetail_ibfk_1` FOREIGN KEY (`gemid`) REFERENCES `gems` (`gemid`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=875 DEFAULT CHARSET=utf8;

sqlfiddle demo

To create the tables after a mysqldump and not give errors for foreign keys, open the dump file put this statement at the beginning:

SET FOREIGN_KEY_CHECKS=0;

Upvotes: 2

tanaydin
tanaydin

Reputation: 5316

you should create "gems" before "gemdetail", switch create queries.

Upvotes: 1

Related Questions