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

@Ocramius commented on GitHub (Feb 14, 2016):

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.

@Ocramius commented on GitHub (Feb 14, 2016): 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")`](https://github.com/doctrine/doctrine2/blob/788143dc0313c7522820ebf6057f73881e7190a3/lib/Doctrine/ORM/Mapping/JoinColumn.php#L51) mapping.

admin commented

@mtal commented on GitHub (Feb 14, 2016):

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?

@mtal commented on GitHub (Feb 14, 2016): 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?

admin commented

@Ocramius commented on GitHub (Feb 14, 2016):

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 commented on GitHub (Feb 14, 2016): > 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)

admin commented

2026-01-22 14:56:45 +01:00

@Ocramius commented on GitHub (Feb 14, 2016):

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

@Ocramius commented on GitHub (Feb 14, 2016): > 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

admin commented

@mtal commented on GitHub (Feb 14, 2016):

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.

@mtal commented on GitHub (Feb 14, 2016): > 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.

admin commented

@Ocramius commented on GitHub (Feb 14, 2016):

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

@Ocramius commented on GitHub (Feb 14, 2016): > 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

admin commented

@mtal commented on GitHub (Feb 14, 2016):

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.

@mtal commented on GitHub (Feb 14, 2016): > 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.

admin commented

@Ocramius commented on GitHub (Feb 14, 2016):

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

@Ocramius commented on GitHub (Feb 14, 2016): Good point indeed. I suggest digging in the existing test suite for similar cases though, as this behavior may indeed be improved.

admin commented

@guilhermeblanco commented on GitHub (Feb 15, 2016):

@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.

@guilhermeblanco commented on GitHub (Feb 15, 2016): @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.

admin commented

2026-01-22 14:56:47 +01:00

@mtal commented on GitHub (Feb 16, 2016):

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 commented on GitHub (Feb 16, 2016): 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.

admin commented

@mtal commented on GitHub (Feb 16, 2016):

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;
    }

}

@mtal commented on GitHub (Feb 16, 2016): And without actual (persisted cyclic references) deletion still fails. Commit order calculated incorrectly. Look at this case: ``` PHP $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 NT`FK_C6AE5795126F525E`FOREIGN KEY (`item_id`) REFERENCES`item`(`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 <?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; } } ```

admin commented

@idchlife commented on GitHub (Sep 19, 2016):

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.

@idchlife commented on GitHub (Sep 19, 2016): 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.

admin commented

@slince commented on GitHub (Dec 12, 2017):

up

@slince commented on GitHub (Dec 12, 2017): up

admin commented

@Ocramius commented on GitHub (Dec 12, 2017):

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

@Ocramius commented on GitHub (Dec 12, 2017): @slince that doesn't help - open a PR with a reproducible failing test case

admin commented

@slince commented on GitHub (Dec 12, 2017):

@Ocramius

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

@slince commented on GitHub (Dec 12, 2017): @Ocramius Hi, the problem I encountered is the same as this one

admin commented