(doctrine2 + symfony2) cascading remove : integrity constraint violation 1451

Question

First, sorry for my poor English...

I got four entities : User, Application, Bundle & Entity. Here are their relations (with cascading persist & remove, see code below) :

User 1-n Application
Application 1-n Bundle
Bundle 1-n Entity

It's working fine. But an User can have two of his entities as default, and I need to access them directly.

So I add on User two fields, entity1 & entity2, with a 1-1 relation. And now my app crashes :

An exception occurred while executing 'DELETE FROM bundle WHERE id = ?' with params {"1":13}:

SQLSTATE[23000]: Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint fails (`misc`.`entity`, CONSTRAINT `FK_E284468F1FAD9D3` FOREIGN KEY (`bundle_id`) REFERENCES `bundle` (`id`))

I tried several things, including those founded in this post, but I wasn't able to fix it.

Any help be welcome, thanks in advance.

EDIT : I need to point out that User->Entity relations are optionnal : User's entity1 & entity2 can be null. The error happens even if they are null both.

Here are my entities definitions :

# User :
    /**
     * @ORM\OneToMany(targetEntity="\sfCommands\ContentBundle\Entity\Application", mappedBy="user", cascade={"remove"}, orphanRemoval=true)
     * @ORM\OrderBy({"name" = "ASC"})
     */
    protected $applications;

    /**
     * @ORM\OneToOne(targetEntity="\sfCommands\ContentBundle\Entity\Entity")
     * @ORM\JoinColumn(name="entity1_id", referencedColumnName="id")
     */
    private $entity1;

    /**
     * @ORM\OneToOne(targetEntity="\sfCommands\ContentBundle\Entity\Entity")
     * @ORM\JoinColumn(name="entity2_id", referencedColumnName="id")
     */
    private $entity2;

#Application :
    /**
     * @ORM\OneToMany(targetEntity="\sfCommands\ContentBundle\Entity\Bundle", mappedBy="application", cascade={"remove"}, orphanRemoval=true)
     * @ORM\OrderBy({"name" = "ASC"})
     */
    protected $bundles;

    /**
     * @ORM\ManyToOne(targetEntity="\sfCommands\UserBundle\Entity\User", inversedBy="applications", cascade={"persist"})
     * @ORM\JoinColumn(name="user_id", referencedColumnName="id")
     */
    protected $user;

#Bundle :
    /**
     * @ORM\ManyToOne(targetEntity="\sfCommands\ContentBundle\Entity\Application", inversedBy="bundles", cascade={"persist"})
     * @ORM\JoinColumn(name="application_id", referencedColumnName="id")
     */
    protected $application;

    /**
     * @ORM\OneToMany(targetEntity="\sfCommands\ContentBundle\Entity\Entity", mappedBy="bundle", cascade={"remove"}, orphanRemoval=true)
     * @ORM\OrderBy({"name" = "ASC"})
     */
    protected $entitys;

#Entity :
    /**
     * @ORM\ManyToOne(targetEntity="\sfCommands\ContentBundle\Entity\Bundle", inversedBy="entitys", cascade={"persist"})
     * @ORM\JoinColumn(name="bundle_id", referencedColumnName="id")
     */
    protected $bundle;

Did by any chance the bundle_id you tried to remove have entities coupled to it that were also an entity in any user field? (entity1_id or entity2_id) — Pankrates, Jan 10 '13 at 12:36
Remove fails even if Bundle's children (Entity) are not in user fields (entity1_id or entity2_id). Sounds like cascading not working at all. — bgaze, Jan 11 '13 at 06:23

score 40 · Accepted Answer · edited Mar 01 '19 at 07:52

So, thanks to this French forum, I fixed the problem.

I needed to add nullable=true & onDelete="SET NULL" in @ORM\JoinColumn

Here is the workable configuration, maybe it will help someone :

#User.
    /**
     * @ORM\OneToMany(targetEntity="\sfCommands\ContentBundle\Entity\Application", mappedBy="user", cascade={"remove"}, orphanRemoval=true)
     * @ORM\OrderBy({"name" = "ASC"})
     */
    protected $applications;

    /**
     * @ORM\OneToOne(targetEntity="\sfCommands\ContentBundle\Entity\Entity")
     * @ORM\JoinColumn(name="entity1_id", referencedColumnName="id", nullable=true, onDelete="SET NULL")
     */
    private $entity1;

    /**
     * @ORM\OneToOne(targetEntity="\sfCommands\ContentBundle\Entity\Entity")
     * @ORM\JoinColumn(name="entity2_id", referencedColumnName="id", nullable=true, onDelete="SET NULL")
     */
    private $entity2;

#Application.
    /**
     * @ORM\OneToMany(targetEntity="\sfCommands\ContentBundle\Entity\Bundle", mappedBy="application", cascade={"remove"}, orphanRemoval=true)
     * @ORM\OrderBy({"name" = "ASC"})
     */
    protected $bundles;

    /**
     * @ORM\ManyToOne(targetEntity="\sfCommands\UserBundle\Entity\User", inversedBy="applications", cascade={"persist"})
     * @ORM\JoinColumn(name="user_id", referencedColumnName="id", nullable=true, onDelete="SET NULL")
     */
    protected $user;

#Bundle.
    /**
     * @ORM\ManyToOne(targetEntity="\sfCommands\ContentBundle\Entity\Application", inversedBy="bundles", cascade={"persist"})
     * @ORM\JoinColumn(name="application_id", referencedColumnName="id", nullable=true, onDelete="SET NULL")
     */
    protected $application;

    /**
     * @ORM\OneToMany(targetEntity="\sfCommands\ContentBundle\Entity\Entity", mappedBy="bundle", cascade={"remove"}, orphanRemoval=true)
     * @ORM\OrderBy({"name" = "ASC"})
     */
    protected $entitys;

#Entity.
    /**
     * @ORM\ManyToOne(targetEntity="\sfCommands\ContentBundle\Entity\Bundle", inversedBy="entitys", cascade={"persist"})
     * @ORM\JoinColumn(name="bundle_id", referencedColumnName="id", nullable=true, onDelete="SET NULL")
     */
    protected $bundle;

score 23 · Answer 2 · answered Mar 11 '15 at 04:58

23

Use onDelete="CASCADE" if you are using annotation

/**
 * @ORM\ManyToOne(targetEntity="Report", inversedBy="responses")
 * @ORM\JoinColumn(name="reportId", referencedColumnName="id",onDelete="CASCADE")
 */

Use onDelete: CASCADE if you are using yml

joinColumn:
      name: pid
      referencedColumnName: id
      onDelete: CASCADE

answered Mar 11 '15 at 04:58

Zaheer Babar

1,636
1
15
17

It was useful for me. I was facing this kind of issue and got fixed.. thanks – Samia Ruponti Apr 17 '15 at 04:15
Awesome ! – kabrice Mar 13 '17 at 20:44
This is not the right answer. `onDelete`, `orphanRemoval` and `cascade="remove"` are the three ways of doing what you need. See the differences [here](https://stackoverflow.com/questions/27472538/cascade-remove-vs-orphanremoval-true-vs-ondelete-cascade) – Jorj Aug 06 '18 at 09:04

score 3 · Answer 3 · answered Nov 12 '13 at 09:55

3

onDelete="CASCADE" also works fine. But don't forget to run app/console doctrine:schema:update --force before DB level changes will take an effect.

answered Nov 12 '13 at 09:55

basejumper

31
1

1

nullable=true & onDelete="SET NULL" combination in my case just allowed to delete only users, not their reservations, those it lead to orphan records in reservations table. Even setting cascade={"remove"} + orphanRemoval=true on OneToMany relation in User entity didn't help. So, my advice is to use onDelete="CASCADE", this trigger deletes user records as well as associated reservations without any problems – basejumper Nov 12 '13 at 10:43
But if you just remove the reference, not the User, onDelete does not triguer. In my case, I end up with a NULL in the weak entity's FK. that is supposed to be not Null. – juanmf Jul 15 '14 at 20:45

score 1 · Answer 4 · answered Jul 15 '14 at 20:56

orphanRemoval some times doesn't work because it depends on (gets schedulled in) PersistencCollection. And we might be calling ArrayCollection#removeElement().

Following is a snippet of the PersistencCollection#remove()

    if ($this->association !== null &&
        $this->association['type'] & ClassMetadata::TO_MANY &&
        $this->owner &&
        $this->association['orphanRemoval']) {
        $this->em->getUnitOfWork()->scheduleOrphanRemoval($removed);
    }

and ArrayCollection does not do that.

(doctrine2 + symfony2) cascading remove : integrity constraint violation 1451

4 Answers4

Linked