Jhn Jhn - 6 months ago 12
SQL Question

Cannot add or update a child row: a foreign key constraint fails (`dbName`.`#sql-e77_7`

I'm trying to add a column to a table and index it to another column. When executing the migration, it throws an error:

Cannot add or update a child row: a foreign key constraint fails (`dbName`.`#sql-e77_7`, CONSTRAINT `fkName` FOREIGN KEY (`column`) REFERENCES `targetTable` (`targetColumn`) ON DELETE CASCADE ON UPDATE CASC)


Can anyone explain to me how did that "#sql-e77_7" was generated?

Answer

You can't add or update a row if the parent is not found , you should insert the row containing the parent , then insert the child .

Here an example i have this two tables exercice and exercice_entite

CREATE TABLE exercice
(
  exercice_id serial NOT NULL,
  date_debut date,
  date_fin date,
  exercice_etat text,
  exercice_code text,
  CONSTRAINT exercice_pkey PRIMARY KEY (exercice_id)
)

CREATE TABLE exercice_entite
(
  id_exercice_entite serial NOT NULL,
  id_exercice_pere integer,
  code_entite_filiale character varying(10),
  etat_exercice_entite text,
  code_exercice_entite text,
  code_entite_mm character varying(10),
  CONSTRAINT exercice_entite_pkey PRIMARY KEY (id_exercice_entite),
  CONSTRAINT exercice_entite_code_entite_filiale_fkey FOREIGN KEY (code_entite_filiale)
      REFERENCES entite_filiale (code_entite_filiale) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT exercice_entite_code_entite_mm_fkey FOREIGN KEY (code_entite_mm)
      REFERENCES entite_mm (code_entite) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT exercice_entite_id_exercice_pere_fkey FOREIGN KEY (id_exercice_pere)
      REFERENCES exercice (exercice_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)

in this example parent table is exercice , child table is exercice_entite because each row in the table exercice_entite has a foreign key id_exercice_pere that reference to the table exercice .

before inserting a new row in exercice_entite mysql check if there is a row in exercice that have the exercice_id equal to the value of id_exercice_pere in the inserted row

Comments