Cannot add or update child row. A foreign key constraint failed

Question

What I'm trying to do

I'm trying to edit a previously set NOT NULL field to accept NULL values.

And I'm getting this error when I try to insert a record:

A foreign key constraint failed.

---

There are two tables in my schema , one called Customer and another called referralAgent, having an one-to-many relationship defined between them, using the referralAgentId as a foreign key for the customer table.

Although there are not any NOT NULL constraints in the fields of the parent table i.e customer , i can't insert a NULL value in the foreign key field.

---

And the CREATE TABLE code:

---

CREATE TABLE IF NOT EXISTS `watson_schema`.`referralagent` (
  `idReferralAgent` INT(11) NOT NULL,
  `firstName` VARCHAR(45) NOT NULL,
  `lastName` VARCHAR(45) NOT NULL,
  `test` VARCHAR(45) NULL DEFAULT NULL,
  PRIMARY KEY (`idReferralAgent`))
ENGINE = InnoDB
AUTO_INCREMENT = 6
DEFAULT CHARACTER SET = latin1;


CREATE TABLE IF NOT EXISTS `watson_schema`.`customer` (
  `idUser` INT(11) NOT NULL AUTO_INCREMENT,
  `firstName` VARCHAR(45) NOT NULL,
  `lastName` VARCHAR(45) NOT NULL,
  `address` VARCHAR(80) NOT NULL,
  `postCode` VARCHAR(45) NOT NULL,
  `profession` VARCHAR(45) NOT NULL,
  `phoneNumWork` VARCHAR(16) NOT NULL,
  `phoneNumMobile` VARCHAR(16) NOT NULL,
  `phoneNumHome` VARCHAR(16) NOT NULL,
  `nationality` INT(11) NOT NULL,
  `countryResidence` INT(11) NOT NULL,
  `countryBirth` INT(11) NOT NULL,
  `entityStatusIdStatus` INT(11) NOT NULL,
  `ReferralAgent_idReferralAgent` INT(11) NULL DEFAULT NULL,
  PRIMARY KEY (`idUser`),
  INDEX `fk_Customers_nationality1_idx` (`nationality` ASC),
  INDEX `fk_Customers_Country1_idx` (`countryResidence` ASC),
  INDEX `fk_Customers_Country2_idx` (`countryBirth` ASC),
  INDEX `fk_Customer_EntityStatus1_idx` (`entityStatusIdStatus` ASC),
  INDEX `fk_Customer_ReferralAgent1_idx` (`ReferralAgent_idReferralAgent` ASC),
  CONSTRAINT `fk_Customers_Country1`
    FOREIGN KEY (`countryResidence`)
    REFERENCES `watson_schema`.`country` (`idCountry`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Customers_Country2`
    FOREIGN KEY (`countryBirth`)
    REFERENCES `watson_schema`.`country` (`idCountry`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Customers_nationality1`
    FOREIGN KEY (`nationality`)
    REFERENCES `watson_schema`.`nationality` (`idNationality`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Customer_EntityStatus1`
    FOREIGN KEY (`entityStatusIdStatus`)
    REFERENCES `watson_schema`.`entitystatus` (`idStatus`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Customer_ReferralAgent1`
    FOREIGN KEY (`ReferralAgent_idReferralAgent`)
    REFERENCES `watson_schema`.`referralagent` (`idReferralAgent`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 21
DEFAULT CHARACTER SET = utf8;

INSERT INTO `watson_schema`.`customer`
(`idUser`,
`firstName`,
`lastName`,
`address`,
`postCode`,
`profession`,
`phoneNumWork`,
`phoneNumMobile`,
`phoneNumHome`,
`nationality`,
`countryResidence`,
`countryBirth`,
`entityStatusIdStatus`,
`ReferralAgent_idReferralAgent`)
VALUES
("22",
"Giannis",
"Komikos",
"Theofilou",
"2242",
"Coffeeman",
"22345672",
"99874568",
"22454547",
"6",
"2",
"3",
"5",
"");

Answer 1

You are not inserting null. You are inserting an empty string. Either leave ReferralAgent_idReferralAgent out of the insert statement OR change the last value in the insert statement from "" to NULL. The insert fails because there is no key in the parent table that equates to "".