orm: Cascade deletion complicate-referenced entities does not work [ForeignKeyConstraintViolationException]
[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;
}
}
About this issue
- Original URL
- State: open
- Created 8 years ago
- Comments: 18 (9 by maintainers)
Commits related to this issue
- Failing test: UoW unable to break cycles when removing entities without DB-level cascade This adds a failing test case for #5665. In this example, we have a cyclic association between three entities... — committed to mpdude/doctrine2 by mpdude a year ago
- Commit order for removals has to consider `SET NULL`, not `nullable` When computing the commit order for entity removals, we have to look out for `@ORM\JoinColumn(onDelete="SET NULL")` to find places... — committed to mpdude/doctrine2 by mpdude a year ago
- Commit order for removals has to consider `SET NULL`, not `nullable` When computing the commit order for entity removals, we have to look out for `@ORM\JoinColumn(onDelete="SET NULL")` to find places... — committed to mpdude/doctrine2 by mpdude a year ago
- Commit order for removals has to consider `SET NULL`, not `nullable` When computing the commit order for entity removals, we have to look out for `@ORM\JoinColumn(onDelete="SET NULL")` to find places... — committed to mpdude/doctrine2 by mpdude a year ago
- Commit order for removals has to consider `SET NULL`, not `nullable` When computing the commit order for entity removals, we have to look out for `@ORM\JoinColumn(onDelete="SET NULL")` to find places... — committed to mpdude/doctrine2 by mpdude a year ago
- Commit order for removals has to consider `SET NULL`, not `nullable` When computing the commit order for entity removals, we have to look out for `@ORM\JoinColumn(onDelete="SET NULL")` to find places... — committed to mpdude/doctrine2 by mpdude a year ago
- Commit order for removals has to consider `SET NULL`, not `nullable` (#10566) When computing the commit order for entity removals, we have to look out for `@ORM\JoinColumn(onDelete="SET NULL")` to fi... — committed to doctrine/orm by mpdude a year ago
@slince yes, but upvoting doesn’t get anywhere on a project without dedicated staff. You gotta help yourself too 😉
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 byItem#featuredItem
In this scenario, you need to define a DB-level cascade operation via the
@JoinColumn(onDelete="CASCADE")
mapping.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.up