Doctrine multiple OneToMany/ManyToOne bidirectional Integrity constraint violation

Question

In latest Doctrine on Symfony2 trying to work out multiple bidirectional relationship between two objects.

Person owner object has one postal Address and then multiple secondary Addresses in a collection, and I remove() the Person, I want all of it's addresses to also be deleted (but removing an address should not remove a Person), but I'm getting 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 might because the

inversedBy="postalAddress, otherAddresses"

I don't think multiple inversedBy is supported; then I also tried to change

@ORM\JoinColumn(nullable=false)

to be nullable, but I still get the error.

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

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

Answer 1

Broken Relational Definition

While what you are doing may make sense from a purely logical standpoint, it does not from a relational-data standpoint, and particularly makes no sense from Doctrine's perspective.

Doctrine is trying to maintain 3 different relationships:

• Address (owning-side) [bi-directional] $person --Many:One--> $otherAddresses Person
• Address (owning-side) [bi-directional] $person --Many:One--> $postalAddress Person
• Person (owning-side) [uni-directional] $postalAddress --One:One--> $id Address

You see the problem?

Use relational standards for solving this problem.

The simple solution here is to use the VERY common design pattern of setting a primary for a collection. In essence, you only need one relationship:

• Address (owning-side) [bi-directional] $person --Many:One--> $otherAddresses Person

Then, add to address a property which defines that address to be the primary. Programattically handle this in the Person and Address entities:

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 don't cross the streams

If you keep the One-to-One relationship UNIDIRECTIONAL from Person to Address, the problem solves itself.

• Address (owning-side) [bi-directional] $person --Many:One--> $otherAddresses Person
• Person (owning-side) [uni-directional] $postalAddress --One:One--> Address

You're still going to have trouble here though, because Doctrine will complain if: - the main (PostalAddress) address doesn't have both sides for the Many:One defined. (so your "main" address will also have to be in the $otherAddresses collection). - attempting to remove or cascade deletes and updates will result in those two relationships conflicting, "crossing streams" of doctrine's relational constraints, so you'll have to programatically handle those operations.