mysql create table script fails - error 150

Question

I forward engineered a script from a model I created in WorkBench, but the script keeps failing. It fails on the second create table:

CREATE TABLE IF NOT EXISTS  `myDatabase`.`UserProfile` (

 `ProfileId` INT NOT NULL AUTO_INCREMENT ,
 `FirstName` VARCHAR( 45 ) NULL ,
 `LastName` VARCHAR( 45 ) NULL ,
 `Gender` CHAR( 1 ) NULL ,
 `DOB` DATETIME NULL ,
 `HairColor` VARCHAR( 20 ) NULL DEFAULT  'No Answer',
 `EyeColor` VARCHAR( 20 ) NULL DEFAULT  'No Answer',
 `Height` VARCHAR( 10 ) NULL DEFAULT  'No Answer',
 `Weight` VARCHAR( 45 ) NULL DEFAULT  'Average',
 `UserId` VARCHAR( 45 ) NOT NULL ,
PRIMARY KEY (  `ProfileId` ) ,
CONSTRAINT  `FK_User_Profile` FOREIGN KEY (  `UserId` ) REFERENCES  `OurAgreement`.`UserAccount`     (
`UserId`
) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = INNODB;

I believe error 150 is a foreign key issue. The table associated with this FK is create first in the script, so it exists before this constraint is attempted. Here is the DDL for that table:

CREATE  TABLE IF NOT EXISTS `mydatabase`.`UserAccount` (
  `UserId` INT NOT NULL AUTO_INCREMENT ,
  `Login` VARCHAR(20) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL ,
  `Password` CHAR(64) NOT NULL ,
  `Email` VARCHAR(45) NULL ,
  PRIMARY KEY (`UserId`))
ENGINE = InnoDB;

Any ideas what is going on?

EDIT============================

table with 2 FKs:

CREATE TABLE IF NOT EXISTS  `MyDatabase`.`Answer` (

 `AnswerId` INT NOT NULL ,
  `QuestionId` INT NOT NULL ,
 `ProfileId` INT NOT NULL ,
PRIMARY KEY (  `AnswerId` ) ,
INDEX  `ProfileId_idx` (  `ProfileId` ASC ) ,
INDEX  `QuestionId_idx` (  `QuestionId` ASC ) ,
CONSTRAINT  `FK_Question_Answer` FOREIGN KEY (  `QuestionId` ) REFERENCES  `MyDatabase`.`Question` (
`QuestionId`
) ON DELETE NO ACTION ON UPDATE CASCADE ,
CONSTRAINT  `FK_Answer_Profile` FOREIGN KEY (  `ProfileId` ) REFERENCES  `MyDatabase`.`UserProfile` (
`ProfileId`
) ON DELETE NO ACTION ON UPDATE CASCADE
) ENGINE = INNODB;

hjpotter92 · Accepted Answer · 2013-04-12 22:43:24Z

2

If

`UserId` INT NOT NULL AUTO_INCREMENT

is in your original table, you need exact same syntax in derived table:

`UserId` VARCHAR( 45 ) NOT NULL

should be

`UserId` INT NOT NULL

Also, you need to index the field UserId in both tables. Since it is PRIMARY in one, so no need there. In another add:

INDEX `uid`( `UserId` )

answered Apr 12, 2013 at 22:43

hjpotter92

81.1k36 gold badges148 silver badges188 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Sebas Over a year ago

here you go, the second part will do it.

hjpotter92 Over a year ago

@Sebas Both of them are necessary ^_^

Sebas Over a year ago

yes you are right, I actually meant the second part would finish solving the problem.

BattlFrog Over a year ago

Thank you guys. I knew about the datatypes having to match, must say I am red faced about that. Didn't know about the need to index on the foreign table, so thank you. If I have a table with 2 FKs in it, would I then have 2 indexes plus the PK on that? Example added above.

hjpotter92 Over a year ago

@teahou Yes. All FK must be indexed in both tables.

Mike · Accepted Answer · 2013-04-12 22:48:54Z

0

In

CREATE TABLE IF NOT EXISTS  `myDatabase`.`UserProfile`

you reference table in database OurAgreement:

REFERENCES  `OurAgreement`.`UserAccount`

But the table UserAccount you create earlier is in mydatabase:

CREATE  TABLE IF NOT EXISTS `mydatabase`.`UserAccount`

answered Apr 12, 2013 at 22:48

Mike

1,3322 gold badges11 silver badges14 bronze badges

Collectives™ on Stack Overflow

mysql create table script fails - error 150

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related