CODEWITHSUNDEEP
mysqlforeign-keysconstraints
Brian Boyle
Brian Boyle

Reputation: 2879

MySQL - Foreign Key Constraint Error

I've seen this problem loads of time on forums, but I can't seem to solve the issue for my particular situation. I'm getting the following error on a simple insert. 

Cannot add or update a child row: a foreign key constraint fails (`bag_track`.`job`, CONSTRAINT `fk_JOB_FLIGHT1` FOREIGN KEY (`FLIGHT_ID`) REFERENCES `FLIGHT` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION)

Here are my table definitions: Flight table

CREATE  TABLE IF NOT EXISTS `BAG_TRACK`.`FLIGHT` (
 `ID` INT NOT NULL AUTO_INCREMENT ,
`FLIGHT_NUM` VARCHAR(15) NOT NULL ,
`ORIGIN` VARCHAR(45) NOT NULL ,
`ARRIVAL_TIME` DATETIME NOT NULL ,
`STATUS` VARCHAR(45) NOT NULL ,
`CAROUSEL_ID` INT NOT NULL ,
`GATE_ID` INT NOT NULL ,
`AIRCRAFT_TYPE_ID` INT NOT NULL ,
PRIMARY KEY (`ID`) ,
INDEX `fk_FLIGHT_CAROUSEL1` (`CAROUSEL_ID` ASC) ,
INDEX `fk_FLIGHT_GATE1` (`GATE_ID` ASC) ,
INDEX `fk_FLIGHT_AIRCRAFT_TYPE1` (`AIRCRAFT_TYPE_ID` ASC) ,
CONSTRAINT `fk_FLIGHT_CAROUSEL1`
FOREIGN KEY (`CAROUSEL_ID` )
REFERENCES `BAG_TRACK`.`CAROUSEL` (`ID` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_FLIGHT_GATE1`
FOREIGN KEY (`GATE_ID` )
REFERENCES `BAG_TRACK`.`GATE` (`ID` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_FLIGHT_AIRCRAFT_TYPE1`
FOREIGN KEY (`AIRCRAFT_TYPE_ID` )
REFERENCES `BAG_TRACK`.`AIRCRAFT_TYPE` (`ID` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8;

Job Table

    CREATE  TABLE IF NOT EXISTS `BAG_TRACK`.`JOB` (
  `ID` INT NOT NULL AUTO_INCREMENT ,
  `STATUS` VARCHAR(45) NOT NULL ,
  `LONGITUDE` FLOAT NULL ,
  `LATITUDE` FLOAT NULL ,
  `SPEED` VARCHAR(45) NULL ,
  `JOB_DATE` DATE NULL ,
  `START_TIME` TIME NULL ,
  `TOTAL_TIME` DOUBLE NULL ,
  `COMPLETION_DATE` DATE NULL ,
  `USER_ID` INT NOT NULL ,
  `ROUTE_ID` INT NOT NULL ,
  `FLIGHT_ID` INT NOT NULL ,
  PRIMARY KEY (`ID`, `FLIGHT_ID`) ,
  INDEX `fk_JOB_USER1` (`USER_ID` ASC) ,
  INDEX `fk_JOB_ROUTE1` (`ROUTE_ID` ASC) ,
  INDEX `fk_JOB_FLIGHT1` (`FLIGHT_ID` ASC) ,
  CONSTRAINT `fk_JOB_USER1`
    FOREIGN KEY (`USER_ID` )
    REFERENCES `BAG_TRACK`.`USER` (`ID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_JOB_ROUTE1`
    FOREIGN KEY (`ROUTE_ID` )
    REFERENCES `BAG_TRACK`.`ROUTE` (`ID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_JOB_FLIGHT1`
    FOREIGN KEY (`FLIGHT_ID` )
    REFERENCES `BAG_TRACK`.`FLIGHT` (`ID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

I'm trying this simple insert

 insert into job (status, USER_ID, ROUTE_ID, FLIGHT_ID) VALUES ('UNLOADING', 1, 1, 2)

and getting the error above. I have data in my flight table with the primary key of 1. I've examined everything on my DB for typos etc and I'm at a loss to see what the problem isI'd be very grateful is anyone can help.

Thanks

B

Upvotes: 1

Views: 664

Answers (1)

Aurelio De Rosa
Aurelio De Rosa

Reputation: 22172

Both your tables must use innoDb engine as you can read from the official doc.

Both tables must be InnoDB tables and they must not be TEMPORARY tables.

For the Flight table you have ENGINE = MyISAM instead.

Upvotes: 2

Related Questions