#1005 - Can't create table 'feedback.answer' (errno: 150)

Question

I am getting an error with mysql and I do not understand why:

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

CREATE SCHEMA IF NOT EXISTS `feedback` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `feedback` ;

-- -----------------------------------------------------
-- Table `feedback`.`application`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `feedback`.`application` (
  `application_id` INT NOT NULL AUTO_INCREMENT,
  `app_name` VARCHAR(45) NULL,
  PRIMARY KEY (`application_id`),
  UNIQUE INDEX `app_name_UNIQUE` (`app_name` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `feedback`.`user`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `feedback`.`user` (
  `user_id` INT NOT NULL AUTO_INCREMENT,
  `firstname` VARCHAR(45) NOT NULL,
  `lastname` VARCHAR(45) NULL,
  `email` VARCHAR(45) NOT NULL,
  `customer_length` VARCHAR(45) NULL,
  PRIMARY KEY (`user_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `feedback`.`users_has_application`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `feedback`.`users_has_application` (
  `user_id` INT NOT NULL,
  `application_id` INT NOT NULL,
  PRIMARY KEY (`user_id`, `application_id`),
  INDEX `fk_users_has_application_application1_idx` (`application_id` ASC),
  INDEX `fk_users_has_application_users_idx` (`user_id` ASC),
  CONSTRAINT `fk_users_has_application_users`
    FOREIGN KEY (`user_id`)
    REFERENCES `feedback`.`user` (`user_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_users_has_application_application1`
    FOREIGN KEY (`application_id`)
    REFERENCES `feedback`.`application` (`application_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `feedback`.`survey`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `feedback`.`survey` (
  `survey_id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  `description` VARCHAR(255) NULL,
  `is_active` TINYINT(1) NULL,
  PRIMARY KEY (`survey_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `feedback`.`question`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `feedback`.`question` (
  `question_id` INT NOT NULL,
  `question_text` VARCHAR(255) NULL,
  PRIMARY KEY (`question_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `feedback`.`option`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `feedback`.`option` (
  `option_id` INT NOT NULL AUTO_INCREMENT,
  `question_id` INT NOT NULL,
  `option_number` INT NOT NULL,
  `option_text` TEXT NULL,
  INDEX `fk_option_question1_idx` (`question_id` ASC),
  PRIMARY KEY (`option_id`),
  UNIQUE INDEX `uk_question_option_number_key` (`question_id` ASC, `option_number` ASC),
  CONSTRAINT `fk_option_question1`
    FOREIGN KEY (`question_id`)
    REFERENCES `feedback`.`question` (`question_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `feedback`.`answer`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `feedback`.`answer` (
  `answer_id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `option_id` INT NOT NULL,
  `date_submitted` DATETIME NOT NULL,
  PRIMARY KEY (`answer_id`),
  INDEX `fk_answer_user1_idx` (`user_id` ASC),
  INDEX `fk_answer_option1_idx` (`option_id` ASC),
  CONSTRAINT `fk_answer_user1`
    FOREIGN KEY (`user_id`)
    REFERENCES `feedback`.`user` (`user_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_answer_option1`
    FOREIGN KEY (`option_id`)
    REFERENCES `feedback`.`option` (`option_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `feedback`.`survey_has_question`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `feedback`.`survey_has_question` (
  `survey_id` INT NOT NULL,
  `question_id` INT NOT NULL,
  `question_number` INT NULL,
  PRIMARY KEY (`survey_id`, `question_id`),
  INDEX `fk_survey_has_question_question1_idx` (`question_id` ASC),
  INDEX `fk_survey_has_question_survey1_idx` (`survey_id` ASC),
  UNIQUE INDEX `unique_order_key` (`survey_id` ASC, `question_number` ASC),
  CONSTRAINT `fk_survey_has_question_survey1`
    FOREIGN KEY (`survey_id`)
    REFERENCES `feedback`.`survey` (`survey_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_survey_has_question_question1`
    FOREIGN KEY (`question_id`)
    REFERENCES `feedback`.`question` (`question_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

Error:

#1005 - Can't create table 'feedback.answer' (errno: 150)

I am creating my table from this as a template:

https://dba.stackexchange.com/questions/16002/survey-database-design-associate-an-answer-to-a-user/16047#16047

My thought process for adding answer_id to the answer table is that I want users to be able to fill out the same survey multiple times.

Why is the answer table throwing an error?

EDIT: Server version: 5.5.29-0ubuntu0.12.04.2 I am importing this using phpmyadmin

Maybe this question is helpful: http://stackoverflow.com/questions/1457305/mysql-creating-tables-with-foreign-keys-giving-errno-150 but I can't see any of the requirements that you're missing. — Barmar, Nov 15 '13 at 16:41
I wrote that in sqlfiddle without the creation of the feedback database and it works. I'm not sure what the problem can be with that. — Filipe Silva, Nov 15 '13 at 16:46
I also tried to recreate this in sqlfiddle and couldn't. Maybe it has something to do with the change to default character set or collation, since sqlfiddle won't let me fiddle with those. — Barmar, Nov 15 '13 at 16:50
I removed the schemma creation and references to feedback database and it created the whole thing: http://sqlfiddle.com/#!2/32d00 — Filipe Silva, Nov 15 '13 at 16:52

Dominic Larkin · Accepted Answer · 2015-07-25T20:37:50.257

1

Your code worked on MYSQL server 5.1 without errors.

A common cause of errno: 150 is when you create a FK constraint that references a PK that does not yet exist. Make sure both your "user" and "option" tables are created first before you create the "answer" table.

To help debug you can remove the FK constraints one at a time to see which one is triggering the problem.

If you execute the code in the order shown, I do not see any FK problems that would occur.

edited Jul 25 '15 at 20:37

answered Nov 15 '13 at 16:59

Dominic Larkin

1,184
3
10
18

Odd. Why would it not work for me? I updated the question with my server details. I still start the debug process on it and see what the issue is. – joshmmo Nov 15 '13 at 17:03

echo_Me · Answer 2 · 2013-11-15T17:03:08.257

0

try this

 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Table `feedback`.`application`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `application` (
`application_id` INT NOT NULL AUTO_INCREMENT,
`app_name` VARCHAR(45) NULL,
PRIMARY KEY (`application_id`),
UNIQUE INDEX `app_name_UNIQUE` (`app_name` ASC))
;

your working code in fiddle

edited Nov 15 '13 at 17:03

answered Nov 15 '13 at 16:57

echo_Me

37,078
5
58
78

Still same error using mysql 5.5.29 #1005 - Can't create table 'feedback.answer' (errno: 150) – joshmmo Nov 15 '13 at 17:08
Oh I used your code with mine. I misunderstood you wanted me to just use yours. Let me try it – joshmmo Nov 15 '13 at 17:19

#1005 - Can't create table 'feedback.answer' (errno: 150)

2 Answers2