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

Question

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:

Answer 1

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.

Answer 2

Philipxy thank you for your help on this problem. You were correct in the comment that the foreign key for bidder was wrong. For some reason mysql workbench generated the columns in the code in the wrong order. The code provided by mysqlworkbench is below:

  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,

And the following code works properly:

  CONSTRAINT `fk_assignment_bidder1`
    FOREIGN KEY (`bidder_simulation_id` , `bidder_idx`)
    REFERENCES `kffg_simulations`.`bidder` (`simulation_id` , `idx`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,

As far as I can tell I had the indexing setup properly but the code was generate in the wrong order.

(I used mysql workbench forward engineer to generate the sql script. I used the gui to create the relationships between the bid table, bidder, and item_round_status. Since both of those tables have simulationid as pk it duplicated that column. When I manually adjusted the foreign key for bidder to reference the simulationid column generated for item_round_status it changed the indices related to that foreign key. I manually changed them to the proper indices but it seems to ignore my changes when generating the script causing the error.)