导入数据库时导致外键约束失败,导出实体失败
问题描述:
我有一个大数据库导入(大约350 Mb)。当试图导入该数据库使用命令行的MySQL,我有这样的错误:导入数据库时导致外键约束失败,导出实体失败
ERROR 1452 (23000) at line 5118296: Cannot add or update a child row: a foreign key
constraint fails (`tutotour_tharmobeta1`.`#sql-2039_195b4`, CONSTRAINT
`FK_D367B678BF396750` FOREIGN KEY (`id`) REFERENCES `reponse` (`id`) ON DELETE CASCADE)
在此行中的SQL文件,我有:
--
-- Contraintes pour la table `reponse`
--
ALTER TABLE `reponse`
ADD CONSTRAINT `FK_5FB6DEC75DDDBC71` FOREIGN KEY (`error`) REFERENCES
`errorcsv` (`id`),
ADD CONSTRAINT `FK_5FB6DEC78D93D649` FOREIGN KEY (`user`) REFERENCES `user`
(`id`),
ADD CONSTRAINT `FK_5FB6DEC7D6ADE47F` FOREIGN KEY (`epreuve`) REFERENCES
`banque_epreuve_temporaire` (`id`),
ADD CONSTRAINT `FK_5FB6DEC7DCC6487D` FOREIGN KEY (`passage_id`) REFERENCES
`passer_colle` (`id`);
--
-- Contraintes pour la table `reponse_qc`
--
ALTER TABLE `reponse_qc`
ADD CONSTRAINT `FK_D367B678B6F7494E` FOREIGN KEY (`question`) REFERENCES
`qc` (`id`),
ADD CONSTRAINT `FK_D367B678BF396750` FOREIGN KEY (`id`) REFERENCES `reponse`
(`id`) ON DELETE CASCADE;
--
-- Contraintes pour la table `reponse_qr`
--
ALTER TABLE `reponse_qr`
ADD CONSTRAINT `FK_B9D7968A8D5FE0A2` FOREIGN KEY (`idCorrecteur`) REFERENCES
`tuteur` (`id`),
ADD CONSTRAINT `FK_B9D7968ABF396750` FOREIGN KEY (`id`) REFERENCES `reponse`
(`id`) ON DELETE CASCADE,
ADD CONSTRAINT `reponse_qr_ibfk_2` FOREIGN KEY (`idColle`) REFERENCES
`colle_qr` (`id`);
如下我的实体定义:
效应初探实体:
/**
* Réponse
*
* @ORM\Table("reponse")
* @ORM\Entity(repositoryClass="PACES\ColleBundle\Repository\ReponseRepository")
* @ORM\InheritanceType("JOINED")
* @ORM\DiscriminatorColumn(name="discr", type="string")
* @ORM\DiscriminatorMap({"reponse"="Reponse", "reponseQC"="ReponseQC",
"reponseQR"="ReponseQR"})
*/
class Reponse
{
/**
* @var integer
*
* @ORM\Column(name="id", type="integer")
* @ORM\Id
* @ORM\GeneratedValue(strategy="AUTO")
*/
private $id;
/**
* @ORM\ManyToOne(targetEntity="\PACES\UserBundle\Entity\User", inversedBy="reponses", cascade={"persist"})
* @ORM\JoinColumn(name="user", referencedColumnName="id", nullable=true)
*/
private $user;
/**
* @ORM\ManyToOne(targetEntity="\PACES\ColleBundle\Entity\ErrorCSV", inversedBy="reponses", cascade={"persist"})
* @ORM\JoinColumn(name="error", referencedColumnName="id")
*/
private $error;
/**
* @var boolean
* @ORM\Column(name="archive" , type="boolean" , nullable=false, options={"default" = false})
*/
private $archive;
/**
* @ORM\Column(name="banque", type="boolean", options={"default" = false})
*/
protected $banque;
/**
* @var \DateTime
*
* @ORM\Column(name="date", type="datetime", nullable=true)
*/
private $date;
/**
* @ORM\ManyToOne(targetEntity="PACES\ColleBundle\Entity\PasserColle", inversedBy="reponses", cascade={"persist"})
*/
private $passage;
/**
* @ORM\ManyToOne(targetEntity="PACES\BanqueBundle\Entity\BanqueEpreuveTemporaire", inversedBy="reponses", cascade={"persist"})
* @ORM\JoinColumn(name="epreuve", referencedColumnName="id")
*/
private $epreuveTemporaire;
ReponseQC:
/**
* ReponseQC
*
* @ORM\Table("reponse_qc")
* @ORM\Entity(repositoryClass="PACES\ColleBundle\Repository\ReponseQCRepository")
*/
class ReponseQC extends Reponse
{
/**
* @ORM\ManyToOne(targetEntity="QC", inversedBy="reponses", cascade={"persist"})
* @ORM\JoinColumn(name="question", referencedColumnName="id")
*/
private $question;
当我导入相同的数据库之前,一切正常,我不认为我对结构做了任何更改。
答
尝试一些类似这样的
$connection = $em->getConnection();
$connection->beginTransaction();
try {
$connection->query('SET FOREIGN_KEY_CHECKS=0');
/* run your query here */
$connection->query('SET FOREIGN_KEY_CHECKS=1');
$connection->commit();
}
catch (\Exception $e) {
$connection->rollback();
}
希望这有助于
亚历Cosoi
您可能要解释你的答案了一下,你会受到downvotes发生前。 – svgrafov
这就是我首先想到的,但我认为这是一种解决方法。它并没有真正解决问题...我忘了说我直接用命令行mysql导入数据库 –
@svgrafov您尝试运行的查询被外键约束阻止。我不知道这个约束是什么,因为我没有完整的数据库镜像,但是如果你确定你的查询没问题,你可以告诉mysql停止检查这种类型的约束,直到你运行你的查询,通过调用$ connection-> query('SET FOREIGN_KEY_CHECKS = 0');在运行查询之前。在$ connection-> query('SET FOREIGN_KEY_CHECKS = 1')之后再打开该检查。如果你认为这没有什么帮助,而我试图帮助你需要一个投票,那么你可以随意做到这一点。 –