Cascade deletion complicate-referenced entities does not work [ForeignKeyConstraintViolationException]

[mtal]

[Doctrine\DBAL\Exception\ForeignKeyConstraintViolationException]             
  An exception occurred while executing 'DELETE FROM subitem WHERE id = ?' wi  
  th params [4]:                                                               
  SQLSTATE[23000]: Integrity constraint violation: 1451 Cannot delete or upda  
  te a parent row: a foreign key constraint fails (`shop`.`item`, CONSTRAINT   
  `FK_1F1B251EB3AE4213` FOREIGN KEY (`featured_item_id`) REFERENCES `subitem`  
   (`id`))                                                                     

is caused by

        $item = new Item();
        $sub1 = new Subitem();
        $sub2 = new Subitem();
        $item->addItem($sub1);
        $item->addItem($sub2);
        $item->setFeaturedItem($sub2);
        $em->persist($item);
        $em->flush();
        $em->remove($item);
        $em->flush();

Entity mappings:

use Doctrine\Common\Collections\ArrayCollection;
use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Entity
 */
class Item
{

    /**
     * @var integer
     *
     * @ORM\Column(name="id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    protected $id;

    /**
     * @var ArrayCollection
     *
     * @ORM\OneToMany(targetEntity="Subitem", mappedBy="item", cascade={"all"}, orphanRemoval=true)
     */
    protected $items;

    /**
     * @var Subitem
     *
     * @ORM\ManyToOne(targetEntity="Subitem")
     */
    protected $featuredItem;

    public function __construct()
    {
        $this->items = new ArrayCollection();
    }

    /**
     * @return int
     */
    public function getId()
    {
        return $this->id;
    }

    /**
     * @return ArrayCollection
     */
    public function getItems()
    {
        return $this->items;
    }

    public function addItem(Subitem $item)
    {
        $this->items[] = $item;
        $item->setItem($this);
    }

    public function removeItem(Subitem $item  = null) {
        if ($this->featuredItem === $item) {
            $this->featuredItem = null;
        }
        $this->items->removeElement($item);
    }

    /**
     * @return Subitem
     */
    public function getFeaturedItem()
    {
        return $this->featuredItem;
    }

    /**
     * @param Subitem $featuredItem
     */
    public function setFeaturedItem(Subitem $featuredItem)
    {
        $this->featuredItem = $featuredItem;
    }

}

/**
 * @ORM\Entity
 */
class Subitem
{

    /**
     * @var integer
     *
     * @ORM\Column(name="id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    protected $id;

    /**
     * @var Item
     *
     * @ORM\ManyToOne(targetEntity="Item", inversedBy="items")
     */
    protected $item;

    /**
     * @return integer
     */
    public function getId()
    {
        return $this->id;
    }

    /**
     * @return Item
     */
    public function getItem()
    {
        return $this->item;
    }

    /**
     * @param Item $item
     */
    public function setItem($item)
    {
        $this->item = $item;
    }

}

[Ocramius]

Seems normal to me:

$em->remove($item); item now marked for removal

$em->flush(); tries to delete the item, but it is still referenced by sub-items, which recursively are referenced by Item#featuredItem

In this scenario, you need to define a DB-level cascade operation via the @JoinColumn(onDelete="CASCADE") mapping.

[mtal]

DB-level cascade will not trigger lifecycle events.

If this scenario seem normal to you, I could give you another
Before marking item for deletion with $em->remove($item); manually delete subitems by $em->remove($sub1) and $em->remove($sub2) or by orphan ($item->removeItem($sub1); $item->removeItem($sub2);)

The flush is still throw ForeignKeyConstraintViolationException.

Entities cannot be removed at ORM level without calling flush more than 1 time. Is this normal?

[Ocramius]

DB-level cascade will not trigger lifecycle events.

Indeed, but a delete with a cascade won't trigger any deletes until mid-flush, so lifecycle events on some cascaded items will still work (first level of cascading)

[Ocramius]

Entities cannot be removed at ORM level without calling flush more than 1 time. Is this normal?

You have a cyclic constraint there: that's not something the ORM can solve for you right now

[mtal]

You have a cyclic constraint there: that's not something the ORM can solve for you right now

But it solve insertions properly by preassigning NULL in INSERT query and assigning generated identy in update query.

So it should do the same at deletion in reverse order. Update column to NULL before deleting.

[Ocramius]

Update column to NULL before deleting.

That isn't currently done by the ORM as far as I know (I would have to check the test suite, but no time ATM), mostly because the column may be non-nullable

[mtal]

That isn't currently done by the ORM as far as I know (I would have to check the test suite, but no time ATM), mostly because the column may be non-nullable

In this case it is impossible. If it is non-nullable you cannot insert cyclic referenced entity. So first flush for persisting will fail.

[Ocramius]

Good point indeed. I suggest digging in the existing test suite for similar cases though, as this behavior may indeed be improved.

[guilhermeblanco]

@mtal I'd ask you to try the same case using latest master.
I refactored the CommitOrderCalculator which should address the cyclic insertion. There's one use case though that was not covered (as it was way too hard to address and test), and I wanna confirm this is the same case you just found out.

I also implemented a couple changes in the OrphanRemoval that should properly remove orphaned on *ToMany and might be related.

Can you please test this against master and comment back? We might extract a sample TestCase from this for us to look if both situations didn't cover your issue.

[mtal]

Delete order is changed but issue is still persist. Exception is still thrown.

 [Doctrine\DBAL\Exception\ForeignKeyConstraintViolationException]             
  An exception occurred while executing 'DELETE FROM item WHERE id = ?' with   
  params [2]:                                                                  
  SQLSTATE[23000]: Integrity constraint violation: 1451 Cannot delete or upda  
  te a parent row: a foreign key constraint fails (`shop`.`subitem`, CONSTRAI  
  NT `FK_C6AE5795126F525E` FOREIGN KEY (`item_id`) REFERENCES `item` (`id`))

The commit order cannot fix it. Before deleting row, another that references it, must be updated. Referencing column must be updated to null.

I refactored the CommitOrderCalculator which should address the cyclic insertion.

The cyclic insertion was work properly before. The issue in impossibility to delete inside single flush.

[mtal]

And without actual (persisted cyclic references) deletion still fails. Commit order calculated incorrectly.

Look at this case:

        $em->beginTransaction();
        $item = new Item();
        $sub = new Subitem();
        $item->addItem($sub);
        $subsub = new Subsubitem();
        $sub->setSub($subsub);
        $em->persist($item);
        $em->flush();
        $em->remove($item);
        $em->flush();
        $em->rollback();

Exception
[Doctrine\DBAL\Exception\ForeignKeyConstraintViolationException] An exception occurred while executing 'DELETE FROM item WHERE id = ?' with params [3]: SQLSTATE[23000]: Integrity constraint violation: 1451 Cannot delete or upda te a parent row: a foreign key constraint fails (shop.subitem, CONSTRAI NTFK_C6AE5795126F525EFOREIGN KEY (item_id) REFERENCESitem(id))

The database log

           19 Query START TRANSACTION
           19 Query INSERT INTO subsubitem (id) VALUES (null)
           19 Query INSERT INTO item (featured_item_id) VALUES (NULL)
           19 Query INSERT INTO subitem (item_id, sub_id) VALUES (3, 1)
           19 Query DELETE FROM item WHERE id = 3

The 'subitem' and 'subsubitem' must be deleted before item.

Mapping:

<?php
use Doctrine\Common\Collections\ArrayCollection;
use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Entity
 */
class Item
{

    /**
     * @var integer
     *
     * @ORM\Column(name="id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    protected $id;

    /**
     * @var ArrayCollection
     *
     * @ORM\OneToMany(targetEntity="Subitem", mappedBy="item", cascade={"all"}, orphanRemoval=true)
     */
    protected $items;

    /**
     * @var Subitem
     *
     * @ORM\ManyToOne(targetEntity="Subitem")
     */
    protected $featuredItem;

    public function __construct()
    {
        $this->items = new ArrayCollection();
    }

    /**
     * @return int
     */
    public function getId()
    {
        return $this->id;
    }

    /**
     * @return ArrayCollection
     */
    public function getItems()
    {
        return $this->items;
    }

    public function addItem(Subitem $item)
    {
        $this->items[] = $item;
        $item->setItem($this);
    }

    public function removeItem(Subitem $item) {
        if ($this->featuredItem === $item) {
            $this->featuredItem = null;
        }
        $this->items->removeElement($item);
    }

    /**
     * @return Subitem
     */
    public function getFeaturedItem()
    {
        return $this->featuredItem;
    }

    /**
     * @param Subitem $featuredItem
     */
    public function setFeaturedItem(Subitem $featuredItem = null)
    {
        $this->featuredItem = $featuredItem;
    }

}

/**
 * @ORM\Entity
 */
class Subitem
{

    /**
     * @var integer
     *
     * @ORM\Column(name="id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    protected $id;

    /**
     * @var Item
     *
     * @ORM\ManyToOne(targetEntity="Item", inversedBy="items")
     */
    protected $item;

    /**
     * @var Subsubitem
     *
     * @ORM\OneToOne(targetEntity="Subsubitem", cascade={"all"})
     */
    protected $sub;

    /**
     * @return integer
     */
    public function getId()
    {
        return $this->id;
    }

    /**
     * @return Item
     */
    public function getItem()
    {
        return $this->item;
    }

    /**
     * @param Item $item
     */
    public function setItem($item)
    {
        $this->item = $item;
    }

    /**
     * @return Subsubitem
     */
    public function getSub()
    {
        return $this->sub;
    }

    /**
     * @param Subsubitem $sub
     */
    public function setSub(Subsubitem $sub = null)
    {
        $this->sub = $sub;
    }

}

/**
 * @ORM\Entity
 */
class Subsubitem
{

    /**
     * @var integer
     *
     * @ORM\Column(name="id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    protected $id;

    /**
     * @return int
     */
    public function getId()
    {
        return $this->id;
    }

}

[idchlife]

Guys, any info on resolving this issue? I have user->galleries->photos->images and I have foreign key constraint error mentioned in topic when trying to remove user.

I should make onDelete="CASCADE" in this case or remove everything by hand?

UPDATE: onDelete="CASCADE" does not event make difference for migration to database, so I cannot use this.

[slince]

up

[Ocramius]

@slince that doesn't help - open a PR with a reproducible failing test case

[slince]

@Ocramius

Hi, the problem I encountered is the same as this one

[Ocramius]

@slince yes, but upvoting doesn't get anywhere on a project without dedicated staff. You gotta help yourself too ;-)

[wilariz]

Hi, are there a solution for this problem? recently I update mi libraries and a update process trigger this error.
Checking the class UnitOfWork, I have the list of entities to delete ( var $collectionDeletions), in mi case I have 3 entities(a parent and two children), the problem that I found is in the order to delete, it try to delete the parent first, and children after, it acoording to the order of the list.

my mapping aparently is ok, "persist" and "remove" from owner and onDelete:Cascade from other side.

Any idea about solution?

[mpdude]

Update 2023:

To summarize what has been written above, the problem is not finding an insert order for the cyclic associations. The UoW is able to solve this by scheduling "extra updates". This is possible since all associations are NULLable (by default!).

The problem lies in the DELETE operation, where the ORM does currently not schedule extra updates before the DELETE to break association cycles.

A failing test showing the lack of this feature can be found in #10548.

But even when the user configures @JoinColumn(onDelete="CASCADE")] to make the DBMS null out the foreign keys, it is important that the commit order takes this into consideration and schedules deletions appropriately. It is not currently doing this, which is demonstrated in #10566.

[mpdude]

Update 2024:

The ORM is not (yet?) able to perform extra UPDATEs to NULL out associations referring to entities that are about to be removed; but, a database-level ON DELETE SET NULL can be used for that.

Since #10566, the ORM should schedule removals in the appropriate order.