The Doctrine of Multiple Unilateral / Multidirectional Bidirectional Violation of Integrity Constraints

Question

In the latest Doctrine on Symfony2, trying to work out several bi-directional relationships between two objects.

The owner’s owner object has one mailing address and then several secondary addresses in the collection, and I delete () the Person, I want all his addresses to be deleted as well (but deleting the address should not delete the Person), but I get this error -

An exception occurred while executing 'DELETE FROM address WHERE id = ?' with params {"1":"fb5e47de-2651-4069-b85e-8dbcbe8a6c4a"}: [PDOException] SQLSTATE[23000]: Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint fails (`db`.`address`, CONSTRAINT `FK_633704 C29C1004E` FOREIGN KEY (`person_id`) REFERENCES `person` (`id`)) 

in

 class Person { /** * @var Address postalAddress * * @ORM\OneToOne(targetEntity="Address", cascade={"all"}, orphanRemoval=true) * @ORM\JoinColumn(onDelete="cascade") */ private $postalAddress; /** * @var \Doctrine\Common\Collections\Collection otherAddresses * * @ORM\OneToMany(targetEntity="Address", mappedBy="person", cascade={"all"}, orphanRemoval=true) */ private $otherAddresses; } class Address { /** * @var Person person * * @ORM\ManyToOne(targetEntity="Person", inversedBy="postalAddress, otherAddresses") * @ORM\JoinColumn(nullable=false) */ private $person; } 

I thought it was possible because

 inversedBy="postalAddress, otherAddresses" 

I do not think multiple inversedBy is supported; then i also tried to change

 @ORM\JoinColumn(nullable=false) 

to be null but i still get the error.

This is obviously not about the trivial example of Person / Address, but about something more complex, but it was my best attempt at abstraction.

I'm sure I missed something obvious. Can anyone help?

Answer 1

Broken Relational Definition

Although what you are doing may make sense from a purely logical point of view, it is not from the point of view of relational data and, in particular, does not make sense from the point of view of the Doctrine.

The doctrine tries to maintain 3 different relationships:

• Address (own side) [bidirectional] $ person --Many:One--> $ otherAddresses Person
• Address ( --Many:One--> ) [bidirectional] $ person --Many:One--> $ postalAddress Person
• Person (own party) [unidirectional] $ postalAddress --One:One--> $ id Address

Do you see the problem?

Use relational standards to solve this problem.

A simple solution here is to use a VERY general design scheme for the installation of the primary collection. Essentially, you only need one relation:

• Address (own side) [bidirectional] $ person --Many:One--> $ otherAddresses Person

Then add a property to the address that defines this address as the primary one. Programmatically handle this in Person and Address objects:

 Class Person { . . . public function getPrimaryAddress() { if (null === $this->primaryAddress) { foreach($this->getOtherAddresses() as $address) { if ($address->isPrimary()) { $this->primaryAddress = $address; break; } } } return $this->primaryAddress; } // similar for the setter, update the old address as not primary if there is one, set the new one as primary. } 

Use two different relationships, but do not cross flows

If you maintain a one-to-one relationship with UNIDIRECTIONAL from person to address, the problem is resolved by itself.

• Address (own side) [bidirectional] $ person --Many:One--> $ otherAddresses Person
• Person (Owner) [Unidirectional] $ postalAddress --One:One--> Address

You still have problems because Doctrine will complain if: - the primary ( PostalAddress ) address does not have both sides for the Many: One parameter. (therefore, your "main" address must also be in the $otherAddresses collection). - an attempt to delete or cascade deletions and updates will lead to a conflict between these two relationships, which "cross the flows" of the relational restrictions of the doctrine, so you have to process these operations programmatically.