Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint-CodePudding

My userland code:-

$projects = $this->doctrine->getRepository(Project::class)->findBy(['deletionDate' => new DateTime('today   364 day')]);

foreach($projects as $project){
    $project = $this->entityManager->find('App\Entity\Project', $project->getId());
    $this->entityManager->remove($project);
}
$this->entityManager->flush();

Here's the error:

An exception occurred while executing a query: SQLSTATE[23000]: Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint fails (`foo`.`entry`, CONSTRAINT `FK_2B219D70166D1F9C` FOREIGN KEY (`project_id`) REFERENCES `project` (`id`))

and here's what I'm attempting:-

class Entry
{
   /**
     * @ORM\ManyToOne(targetEntity=Project::class, inversedBy="entries")
     * @ORM\JoinColumn(name="project_id", referencedColumnName="id", onDelete="CASCADE")
     *
     * @Assert\NotBlank
    */
    public ?Project $project;
}

class Project
{
  /**
  * @ORM\OneToMany(targetEntity="Entry", mappedBy="project", cascade={"remove"})
  */
  public Collection $entries;

}

CodePudding user response：

This has nothing to do with Doctrine itself but with the general database rule - you cannot delete rows that other rows in the database depend on.

Now, something that caught my eye was:

@ORM\JoinColumn(name="project_id", referencedColumnName="id", onDelete="CASCADE")

Namely, you have onDelete="CASCADE", however:

`FK_2B219D70166D1F9C` FOREIGN KEY (`project_id`) REFERENCES `project` (`id`))

tells us a whole different story.

It seems that your database and your model are not in sync. Do you manage that by migrations? If so, did you run all of them?

You could try to:

php bin/console doctrine:migrations:diff

which will generate a single migration containing all the differences.

Be careful, inspect detected changes and apply them after making any necessary adjustments.

Update:

Given that you do not manage DB changes via migrations of any sort, the only way would be to execute the ALTER query by hand in order to fix this issue.

Something like this:

ALTER TABLE entry DROP FOREIGN KEY FK_2B219D70166D1F9C;
ALTER TABLE entry ADD FOREIGN KEY FK_2B219D70166D1F9C(project_id)
            REFERENCES project(id) ON DELETE CASCADE;