无法添加或更新子行:外键约束失败(` update `.`#SQLE77_7‘)

Question

我试图将一列添加到表中，并将其索引到另一列。在执行迁移时，它会引发一个错误：

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)

有人能向我解释一下"#sql-e77_7“是如何生成的吗？

Answer 1

如果找不到父行，则不能添加或更新行，应插入包含父行的行，然后插入子行。

这里有一个例子，我有这两个表exercice和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
)

在本例中，父表是exercice，子表是exercice_entite，因为表exercice_entite中的每一行都有一个引用表exercice_entite的外键id_exercice_pere。

在exercice_entite中插入新行之前，检查是否有一行exercice_id等于插入行中id_exercice_pere的值