Search code examples
mysqlforeign-keysmysql-error-150

mysql error 150 from referencing same foreign key column in two tables

I have looked through quite a few posts but havent found the solution for my problem. My suspicion is the error stems from me trying to use a single column to reference the same primary key column in two different tables. Specifically the bid table has the foreign key simulation_id that is also present in the bidder and item_round_status tables. the bid table references the foreign keys of both of these tables but I would like to use only one simulation_id column in the table. Is this the source of the Error 150 problem?

-- -----------------------------------------------------
-- Table `kffg_simulations`.`item_round_status`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `kffg_simulations`.`item_round_status` (
  `simulation_id` INT NOT NULL ,
  `round` INT NOT NULL ,
  `clock_item_id` INT NOT NULL ,
  `posted_price` BIGINT NOT NULL ,
  `clock_price` BIGINT NOT NULL ,
  PRIMARY KEY (`simulation_id`, `round`, `clock_item_id`)  ,
  INDEX `fk_item_round_status_clock_item1_idx` (`clock_item_id` ASC)  ,
  INDEX `fk_item_round_status_simulation1_idx` (`simulation_id` ASC)  ,
  CONSTRAINT `fk_item_round_status_clock_item1`
    FOREIGN KEY (`clock_item_id`)
    REFERENCES `kffg_simulations`.`clock_item` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_item_round_status_simulation1`
    FOREIGN KEY (`simulation_id`)
    REFERENCES `kffg_simulations`.`simulation` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `kffg_simulations`.`bidder`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `kffg_simulations`.`bidder` (
  `simulation_id` INT NOT NULL ,
  `idx` INT NOT NULL ,
  `bidder_strategy_id` INT NOT NULL ,
  `budget` BIGINT NOT NULL ,
  PRIMARY KEY (`simulation_id`, `idx`)  ,
  INDEX `fk_bidder_simulation1_idx` (`simulation_id` ASC)  ,
  INDEX `fk_bidder_bidder_strategy1_idx` (`bidder_strategy_id` ASC)  ,
  CONSTRAINT `fk_bidder_simulation1`
    FOREIGN KEY (`simulation_id`)
    REFERENCES `kffg_simulations`.`simulation` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_bidder_bidder_strategy1`
    FOREIGN KEY (`bidder_strategy_id`)
    REFERENCES `kffg_simulations`.`bidder_strategy` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `kffg_simulations`.`bid_type`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `kffg_simulations`.`bid_type` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`id`)  )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `kffg_simulations`.`bid_status`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `kffg_simulations`.`bid_status` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `description` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`id`)  )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `kffg_simulations`.`bid`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `kffg_simulations`.`bid` (
  `simulation_id` INT NOT NULL ,
  `item_round_status_round` INT NOT NULL ,
  `clock_item_id` INT NOT NULL ,
  `bidder_idx` INT NOT NULL ,
  `quantity` INT NOT NULL ,
  `price` INT NOT NULL ,
  `bid_type_id` INT NOT NULL ,
  `switch_to_pea_category_id` INT NOT NULL ,
  `backstop` BIGINT NULL ,
  `bid_status_id` INT NOT NULL ,
  `processed_demand` INT NOT NULL ,
  PRIMARY KEY (`simulation_id`, `item_round_status_round`, `clock_item_id`, `bidder_idx`, `quantity`)  ,
  INDEX `fk_bid_item_round_status1_idx` (`simulation_id` ASC, `item_round_status_round` ASC, `clock_item_id` ASC)  ,
  INDEX `fk_bid_bidder1_idx` (`simulation_id` ASC, `bidder_idx` ASC)  ,
  INDEX `fk_bid_bid_type1_idx` (`bid_type_id` ASC)  ,
  INDEX `fk_bid_pea_category1_idx` (`switch_to_pea_category_id` ASC)  ,
  INDEX `fk_bid_bid_status1_idx` (`bid_status_id` ASC)  ,
  CONSTRAINT `fk_bid_item_round_status1`
    FOREIGN KEY (`simulation_id` , `item_round_status_round` , `clock_item_id`)
    REFERENCES `kffg_simulations`.`item_round_status` (`simulation_id` , `round` , `clock_item_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_bid_bidder1`
    FOREIGN KEY (`bidder_idx` , `simulation_id`)
    REFERENCES `kffg_simulations`.`bidder` (`idx` , `simulation_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_bid_bid_type1`
    FOREIGN KEY (`bid_type_id`)
    REFERENCES `kffg_simulations`.`bid_type` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_bid_pea_category1`
    FOREIGN KEY (`switch_to_pea_category_id`)
    REFERENCES `kffg_simulations`.`pea_category` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_bid_bid_status1`
    FOREIGN KEY (`bid_status_id`)
    REFERENCES `kffg_simulations`.`bid_status` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

Updated to show error message:

------------------------
LATEST FOREIGN KEY ERROR
------------------------
170604 21:52:27 Error in foreign key constraint of table kffg_simulations/bid:

    FOREIGN KEY (`simulation_id` , `item_round_status_round` , `clock_item_id`)
    REFERENCES `kffg_simulations`.`item_round_status` (`simulation_id` , `round` , `clock_item_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_bid_bidder1`
    FOREIGN KEY (`bidder_idx` , `simulation_id`)
    REFERENCES `kffg_simulations`.`bidder` (`idx` , `simulation_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_bid_bid_type1`
    FOREIGN KEY (`bid_type_id`)
    REFERENCES `kffg_simulations`.`bid_type` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_bid_pea_category1`
    FOREIGN KEY (`switch_to_pea_category_id`)
    REFERENCES `kffg_simulations`.`pea_category` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_bid_bid_status1`
    FOREIGN KEY (`bid_status_id`)
    REFERENCES `kffg_simulations`.`bid_status` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB:
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
See http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html
for correct foreign key definition.

Also updated with uml diagram: UML diagram of three relevant tables

Solution

  • Foreign Key Usage and Error Information gives info on FKs (foreign keys).

    you can obtain a detailed explanation of the most recent InnoDB foreign key error by checking the output of SHOW ENGINE INNODB STATUS.

    InnoDB permits a foreign key to reference any index column or group of columns. However, in the referenced table, there must be an index where the referenced columns are listed as the first columns in the same order.

    In bid:

    FOREIGN KEY (`bidder_idx` , `simulation_id`)
REFERENCES `kffg_simulations`.`bidder` (`idx` , `simulation_id`)

    The "referenced table" here is bidder, the "referenced columns" list is (idx , simulation_id).

    Cannot find an index in the referenced table where the
referenced columns appear as the first columns,

    Sure enough, in bidder the closest we find is:

    PRIMARY KEY (`simulation_id`, `idx`)  ,

    which implicitly declares a default unique not null index, but like all the other indexes doesn't start with the FK's column list.