MySQL error code (Cannot add or update child row..)

Question

Here is one of my tables:

CREATE TABLE IF NOT EXISTS `mydb`.`Branch` (
`branchID` CHAR(5) NOT NULL,
`branchAddress` VARCHAR(40) NULL,
`branchPhoneNo` VARCHAR(20) NULL,
`productCode` CHAR(5) NULL,
PRIMARY KEY (`branchID`))
ENGINE = InnoDB;

and here are the inserts for that table:

START TRANSACTION;
USE `mydb`;
INSERT INTO `mydb`.`Branch` (`branchID`, `branchAddress`, `branchPhoneNo`, 
`productCode`) VALUES ('BR198', 'Waterford', '051786796', 'P1234');
INSERT INTO `mydb`.`Branch` (`branchID`, `branchAddress`, `branchPhoneNo`, 
`productCode`) VALUES ('BR221', 'Cork', '021475859', 'P5678');
INSERT INTO `mydb`.`Branch` (`branchID`, `branchAddress`, `branchPhoneNo`, 
`productCode`) VALUES ('BR330', 'Waterford', '051846784', 'P1122');
INSERT INTO `mydb`.`Branch` (`branchID`, `branchAddress`, `branchPhoneNo`, 
`productCode`) VALUES ('BR38', 'Dublin', '01784767', 'P3344');
INSERT INTO `mydb`.`Branch` (`branchID`, `branchAddress`, `branchPhoneNo`, 
`productCode`) VALUES ('BR001', 'Longford', '041875589', 'P5566');

COMMIT;

And here is my second table:

CREATE TABLE IF NOT EXISTS `mydb`.`Employee` (
`empNo` CHAR(5) NOT NULL,
`name` VARCHAR(25) NULL,
`department` VARCHAR(10) NULL,
`Branch_branchID` CHAR(5) NOT NULL,
`Manager_managerID` CHAR(5) NOT NULL,
PRIMARY KEY (`empNo`),
INDEX `fk_Employee_Branch1_idx` (`Branch_branchID` ASC),
INDEX `fk_Employee_Manager1_idx` (`Manager_managerID` ASC),
CONSTRAINT `fk_Employee_Branch1`
FOREIGN KEY (`Branch_branchID`)
REFERENCES `mydb`.`Branch` (`branchID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_Employee_Manager1`
FOREIGN KEY (`Manager_managerID`)
REFERENCES `mydb`.`Manager` (`managerID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;

And here are the inserts for that table as well:

START TRANSACTION;
USE `mydb`;
INSERT INTO `mydb`.`Employee` (`empNo`, `name`, `department`, 
`Branch_branchID`, `Manager_managerID`) VALUES ('EM092', 'Dami Kerry', 
'Garden', 'BR198', 'M3321');
INSERT INTO `mydb`.`Employee` (`empNo`, `name`, `department`, 
`Branch_branchID`, `Manager_managerID`) VALUES ('EM782', 'Donna Kinsella', 
'Living', 'BR221', 'M7789');
INSERT INTO `mydb`.`Employee` (`empNo`, `name`, `department`, 
`Branch_branchID`, `Manager_managerID`) VALUES ('EM109', 'Georgina Hughes', 
'Bathroom', 'BR330', 'M9034');
INSERT INTO `mydb`.`Employee` (`empNo`, `name`, `department`, 
`Branch_branchID`, `Manager_managerID`) VALUES ('EM728', 'Joe Bloggs', 
'Kitchen', 'BR938', 'M2945');
INSERT INTO `mydb`.`Employee` (`empNo`, `name`, `department`, 
`Branch_branchID`, `Manager_managerID`) VALUES ('EM211', 'Michael Carroll', 
'Bedroom', 'BR001', 'M0921');

COMMIT;

Here is the error message I keep getting:

Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (mydb.employee, CONSTRAINT fk_Employee_Branch1 FOREIGN KEY (Branch_branchID) REFERENCES branch (branchID) ON DELETE NO ACTION ON UPDATE NO ACTION)

I am pretty much new to MySql as you can probably tell. Please, any bit of help/advise would be very much appreciated!

Thanks in advance!

This `BranchID = BR938` does not exist on the `Branch` table. I think you typoed `BR38` on the `Branch` table. — SS_DBA, Mar 02 '18 at 18:33
@WEI_DBA It worked! Thank you sooo much..currently trying to fix a few other errors now. Would you happen to know what an 'Out of range value' error means please? Thanks again! — , Mar 02 '18 at 18:42

score 0 · Accepted Answer · answered Mar 02 '18 at 18:48

0

check the queries.

Cannot add or update a child row: a foreign key constraint fails

the error appears when you try to delete a record that has dependency on another table, or when you try to add a record with a non-existent dependency on another table.

I saw the insert queries and found the Pk "BR38" in table "Branch" is not equal to the Fk "BR938" you trying to insert in Employee:

INSERT INTO `mydb`.`Branch` (`branchID`, `branchAddress`, `branchPhoneNo`, 
`productCode`) VALUES ('BR38', 'Dublin', '01784767', 'P3344');

INSERT INTO `mydb`.`Employee` (`empNo`, `name`, `department`, 
`Branch_branchID`, `Manager_managerID`) VALUES ('EM728', 'Joe Bloggs', 
'Kitchen', 'BR938', 'M2945');

Correct the FK in the query and Let me know if it worked.

answered Mar 02 '18 at 18:48

Joshua Joel Cleveland

488
5
11

Yup it did indeed work. Currently trying to fix all other errors, Would you happen to know what an 'Out of range value' error means please? Thanks again! – Mar 02 '18 at 18:50
check the length of the string or number you trying to insert. if it is greater than the length of the column it will throw an error. You can not insert 11 characters in a 10 characters lenght column – Joshua Joel Cleveland Mar 02 '18 at 18:58
That makes alot of sense, I'll check for that now. Also, one last question please I promise, what does the error message 'Data truncated for column column_name..' mean please? – Mar 02 '18 at 19:02
look at this post it might help you https://stackoverflow.com/questions/18089240/data-truncated-for-column – Joshua Joel Cleveland Mar 02 '18 at 19:16

MySQL error code (Cannot add or update child row..)

1 Answers1