I have an InnoDB MySQL database with a table that needs to be able to connect to one of 26 other tables via a foreign key. Each record will only connect to one of these 26 at a time. The table will probably consist of no more than 10,000 records. Is there an alternative way to do this?
-- -----------------------------------------------------
-- Table `db_mydb`.`tb_job`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `db_mydb`.`tb_job` (
`job_id` INT(11) NOT NULL AUTO_INCREMENT ,
// Removed 26 other fields that the table requires
`job_foreignkey_a_id` INT(11) NULL DEFAULT NULL ,
`job_foreignkey_b_id` INT(11) NULL DEFAULT NULL ,
`job_foreignkey_c_id` INT(11) NULL DEFAULT NULL ,
// Removed the other 23 foreign keys fields that are the same
PRIMARY KEY (`job_id`) ,
CONSTRAINT `fka_tb_job_tb`
FOREIGN KEY (`job_foreignkey_a_id` )
REFERENCES `db_mydb`.`tb_foreignkey_a` (`foreignkey_a_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fkb_tb_job_tb`
FOREIGN KEY (`job_foreignkey_b_id` )
REFERENCES `db_mydb`.`tb_foreignkey_b` (`foreignkey_b_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fkc_tb_job_tb`
FOREIGN KEY (`job_foreignkey_c_id` )
REFERENCES `db_mydb`.`tb_foreignkey_c` (`foreignkey_c_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
// Removed the other 23 foreign keys constraints that are the same
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
CREATE INDEX `fka_tb_job_tb` ON `db_mydb`.`tb_job` (`job_foreignkey_a_id` ASC) ;
CREATE INDEX `fkb_tb_job_tb` ON `db_mydb`.`tb_job` (`job_foreignkey_b_id` ASC) ;
CREATE INDEX `fkc_tb_job_tb` ON `db_mydb`.`tb_job` (`job_foreignkey_c_id` ASC) ;
// Removed the other 23 foreign keys indexes that are the same