Integrity constraint violation, foreign key constraint fails

Question

I have 3 entity : Invoice, InvoiceItemService, Asset.

First, I create an Invoice and an InvoiceItemService, and I link them together with a ManyToOne relation on InvoiceItemService side (so InvoiceItemService is the owner side).

Then, I can create an Asset, from the Invoice object. An asset is kind of a copy of an Invoice, with a negative total. When I create an Asset, I link the Invoice's InvoiceItemService to the Asset too, with a ManyToOne relation between InvoiceItemService and Asset.

When I delete an Invoice, everything works fine, Invoice, Asset and InvoiceItemService are deleted with a cascade={"remove"} option.

When I delete an Asset, I would like only the asset to be deleted. But I get this foreign key error.

Here are my entities :

class Invoice { /** * @ORM\OneToMany(targetEntity="Evo\BackendBundle\Entity\Asset", mappedBy="invoice", cascade={"remove"}) */ protected $assets; /** * @ORM\OneToMany(targetEntity="Evo\BackendBundle\Entity\InvoiceItemService", mappedBy="invoice", cascade={"persist", "remove"}) */ protected $services; } class Asset { /** * @ORM\ManyToOne(targetEntity="Evo\BackendBundle\Entity\Invoice", inversedBy="assets") * @ORM\JoinColumn(nullable=false) * @Exclude */ protected $invoice; /** * @ORM\OneToMany(targetEntity="Evo\BackendBundle\Entity\InvoiceItemService", mappedBy="asset") */ protected $services; } class InvoiceItemService extends InvoiceItem { /** * @ORM\ManyToOne(targetEntity="Evo\BackendBundle\Entity\Invoice", inversedBy="services") * @ORM\JoinColumn(nullable=false, onDelete="CASCADE") * @Type("Evo\BackendBundle\Entity\Invoice") * @Exclude */ protected $invoice; /** * @ORM\ManyToOne(targetEntity="Evo\BackendBundle\Entity\Asset", inversedBy="services") * @ORM\JoinColumn(nullable=true, onDelete="SET NULL") * @Type("Evo\BackendBundle\Entity\Asset") * @Exclude */ protected $asset; }

Are there other entities that might rely on an Asset for a foreign key? Your error may be entirely unrelated to the relationship between these three you posted here. — Peter Bailey
– Peter Bailey, Commented Sep 15, 2014 at 14:14
i'm pretty sure the problem is here, according to the SQL error : a foreign key constraint fails (`evotest`.`sf_invoices_items_services`, CONSTRAINT `FK_38F5C7765DA1941` FOREIGN KEY (`asset_id`) REFERENCES `sf_assets` (`id`)) Actually, i would need a doctrine option to set asset_id to NULL in InvoiceItemService, when an asset is deleted. tried @ORM\JoinColumn(nullable=true, onDelete="SET NULL") in InvoiceItemService#asset property, but error still there — VaN
– VaN, Commented Sep 15, 2014 at 14:52
It's up to you to remove any InvoiceItemService items that reference the asset. The D2 delete cascade is not smart enough to go back upstream and delete. — Cerad
– Cerad, Commented Sep 15, 2014 at 15:05

Peter Bailey · Accepted Answer · 2014-09-15 16:37:12Z

As Cerad mentioned in the comments above, you'll need to implement this behavior yourself.

Since you'll need access to the entity manager, you can't do this with a simple lifecycle callback on the Asset entity itself. Instead, you'll need to register a service to listen for the event and perform the action at that point.

Example implementation

app/config.yml

services: your.bundle.association.manager: class: Your\Bundle\Model\AssociationManager tags: - { name: doctrine.event_listener, event: preRemove }

And then, the service class itself (unstested - might have bugs)

namespace Your\Bundle\Model; use Doctrine\ORM\Event\LifecycleEventArgs; use Doctrine\ORM\EntityManager; use Your\Bundle\Entity\Asset; class AssociationManager { public function preRemove(LifecycleEventArgs $args) { if ($entity instanceof Asset) { $this->removeAssetAssociations($asset, $args->getEntityManager()); return; } } protected function removeAssetAssociations(Asset $asset, EntityManager $em) { foreach ($asset->getServices() as $invoiceItemService) { $invoiceItemService->asset = null; $em->persist($invoiceItemService); } } }

Hope this helps.

ok great. The key was to understand I had to unset these relationships manually. I already had an event listener set up on Asset removal, so it was piece of cake. Actually, I only use your foreach() part, everything else was already coded. day after day, i find doctrine more and more amazing, but I think I found here one of its limitations, seems Doctrine can't automatically deal with relationship detachments. Anyway, thank you for your reply. Thanks to Cerad too. +1 for both of you.

Collectives™ on Stack Overflow

Integrity constraint violation, foreign key constraint fails

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related