Mysql – Yet another “ERROR 1005 can’t create table” question

foreign keyinnodbMySQL

Well, I've been looking at the ERROR 1005 questions, but I haven't been able to see my error among the issues addressed by those questions. This code is generated by MySQL Workbench under Linux. Can anyone with sharp eyes spot the error? It occurs when trying to create AB_test_questions.

For context, table ABs refers to subjects of an A/B test, and AB_test_questions represents questions which compare two subjects against each other (hence two foreign keys referencing the ABs table).

enter image description here

CREATE TABLE IF NOT EXISTS `db`.`ABs` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `text` VARCHAR(255) NOT NULL,
  `image` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COMMENT = 'represents the subject of an AB test';


CREATE TABLE IF NOT EXISTS `db`.`AB_test_questions` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `AB1` INT NOT NULL,
  `AB2` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `AB1_idx` (`AB1` ASC),
  INDEX `AB2_idx` (`AB2` ASC),
  CONSTRAINT `fk_AB_test_questions_ABs_id1`
    FOREIGN KEY (`AB1`)
    REFERENCES `db`.`ABs` (`id`)
    ON DELETE SET NULL
    ON UPDATE CASCADE,
  CONSTRAINT `fk_AB_test_questions_ABs_id2`
    FOREIGN KEY (`AB2`)
    REFERENCES `db`.`ABs` (`id`)
    ON DELETE SET NULL
    ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COMMENT = 'represents an AB test question';

Best Answer

You have specified a cascade ON DELETE SET NULL, but the relevant key field is set to NOT NULL. You can fix this by either changing your cascade or setting the field to allow NULL values.

Ref:- http://verysimple.com/2006/10/22/mysql-error-number-1005-cant-create-table-mydbsql-328_45frm-errno-150/