MySQL Workbench. ERROR 1452: Cannot add or update a child row: a foreign key constraint fails. Operation failed

Andrey Kogut picture Andrey Kogut · Dec 12, 2016 · Viewed 7.6k times · Source

I can not bind a subordinate (Table administrator) and the main table (the department) by a foreign key. It displays the following message:

Executing:

ALTER TABLE `grocery_supermarket_manager`.`administrator` 
ADD CONSTRAINT `AdministratorDepartment_FK`
  FOREIGN KEY (`id_department`)
  REFERENCES `grocery_supermarket_manager`.`department` (`id_department`)
  ON DELETE CASCADE
  ON UPDATE CASCADE;

Operation failed: There was an error while applying the SQL script to the database.

ERROR 1452: Cannot add or update a child row: a foreign key constraint fails (`grocery_supermarket_manager`.`#sql-9ac_8`, CONSTRAINT `AdministratorDepartment_FK` FOREIGN KEY (`id_department`) REFERENCES `department` (`id_department`) ON DELETE CASCADE ON UPDATE CASCADE)
SQL Statement:
ALTER TABLE `grocery_supermarket_manager`.`administrator` 
ADD CONSTRAINT `AdministratorDepartment_FK`
  FOREIGN KEY (`id_department`)
  REFERENCES `grocery_supermarket_manager`.`department` (`id_department`)
  ON DELETE CASCADE
  ON UPDATE CASCADE

Column name: "id_department" table "Department" have:

  • Datatype - INT(10)
  • Storage: Primary Key, Not Null, Unique, Unsigned, Auto increment.

Column name: "id_department" table "Administrator" have:

  • Datatype - INT(10)
  • Storage: Not Null, Unsigned.

Answer

Mike Lischke picture Mike Lischke · Dec 13, 2016

Make sure the current data in your table fullfills the constraint you are adding. Looks like you have administrators with invalid department ids.