How to persist self referencing Doctrine entities?

Question

I have two entities that reference each other and have not null constraints, and am trying to persist them with the following script:

$parentEntity = new \App\Entity\ParentEntity();
$childEntity = new \App\Entity\ChildEntity();
$childEntity->setParentEntity($parentEntity);
$parentEntity->setRootChildEntity($childEntity);

$this->entityManager->persist($parentEntity);
$this->entityManager->persist($childEntity);

printf('$parentEntity->getId(): %s'.PHP_EOL, $parentEntity->getId());
printf('$childEntity->getId(): %s'.PHP_EOL, $childEntity->getId());
printf('$parentEntity->getRootChildEntity()->getId(): %s'.PHP_EOL, $parentEntity->getRootChildEntity()->getId());
printf('$childEntity->getParentEntity()->getId(): %s'.PHP_EOL, $childEntity->getParentEntity()->getId());

$this->entityManager->flush();

When executing the script, both entities are populated with integer IDs after being persisted (obtained by the SELECT NEXTVAL queries), but when flushed, instead NULL is used and I get a not null violation.

If I was just getting a foreign key constraint, I would understand why and I could get around it by making the constraints deferrable or bypassing them by temporarily disabling triggers.

But not so with a null violation. Why is this happening? Without removing the not null constraint which I wish to keep, is there a way around it?

doctrine.DEBUG: SELECT NEXTVAL('parent_entity_id_seq') [] []
doctrine.DEBUG: SELECT NEXTVAL('child_entity_id_seq') [] []

$parentEntity->getId(): 1
$childEntity->getId(): 1
$parentEntity->getRootChildEntity()->getId(): 1
$childEntity->getParentEntity()->getId(): 1

doctrine.DEBUG: "START TRANSACTION" [] []
doctrine.DEBUG: INSERT INTO parent_entity (id, root_child_entity_id) VALUES (?, ?) {"1":1,"2":null} []
doctrine.DEBUG: "ROLLBACK" [] []
console.CRITICAL: Error thrown while running command "app:add-tenant". Message: "An exception occurred while executing a query: SQLSTATE[23502]: Not null violation: 7 ERROR:  null value in column "root_child_entity_id" of relation "parent_entity" violates not-null constraint DETAIL:  Failing row contains (1, null)." {"exception":"[object] (Doctrine\\DBAL\\Exception\\NotNullConstraintViolationException(code: 7): An exception occurred while executing a query: SQLSTATE[23502]: Not null violation: 7 ERROR:  null value in column \"root_child_entity_id\" of relation \"parent_entity\" violates not-null constraint\nDETAIL:  Failing row contains (1, null). at /srv/api/vendor/doctrine/dbal/src/Driver/API/PostgreSQL/ExceptionConverter.php:51)\n[previous exception] [object] (Doctrine\\DBAL\\Driver\\PDO\\Exception(code: 7): SQLSTATE[23502]: Not null violation: 7 ERROR:  null value in column \"root_child_entity_id\" of relation \"parent_entity\" violates not-null constraint\nDETAIL:  Failing row contains (1, null). at /srv/api/vendor/doctrine/dbal/src/Driver/PDO/Exception.php:26)\n[previous exception] [object] (PDOException(code: 23502): SQLSTATE[23502]: Not null violation: 7 ERROR:  null value in column \"root_child_entity_id\" of relation \"parent_entity\" violates not-null constraint\nDETAIL:  Failing row contains (1, null). at /srv/api/vendor/doctrine/dbal/src/Driver/PDO/Statement.php:101)","command":"app:add-tenant","message":"An exception occurred while executing a query: SQLSTATE[23502]: Not null violation: 7 ERROR:  null value in column \"root_child_entity_id\" of relation \"parent_entity\" violates not-null constraint\nDETAIL:  Failing row contains (1, null)."} []

My entities:

<?php

namespace App\Entity;

use App\Repository\ParentEntityRepository;
use Doctrine\ORM\Mapping as ORM;

#[ORM\Entity]
class ParentEntity
{
    #[ORM\Id]
    #[ORM\GeneratedValue]
    #[ORM\Column(type: 'integer')]
    private $id;

    #[ORM\ManyToOne(targetEntity: ChildEntity::class)]
    #[ORM\JoinColumn(nullable: false)]
    private $rootChildEntity;

    public function getId(): ?int
    {
        return $this->id;
    }

    public function getRootChildEntity(): ?ChildEntity
    {
        return $this->rootChildEntity;
    }

    public function setRootChildEntity(?ChildEntity $rootChildEntity): self
    {
        $this->rootChildEntity = $rootChildEntity;

        return $this;
    }
}

<?php

namespace App\Entity;

use App\Repository\ChildEntityRepository;
use Doctrine\ORM\Mapping as ORM;

#[ORM\Entity]
class ChildEntity
{
    #[ORM\Id]
    #[ORM\GeneratedValue]
    #[ORM\Column(type: 'integer')]
    private $id;

    #[ORM\ManyToOne(targetEntity: ParentEntity::class)]
    #[ORM\JoinColumn(nullable: false)]
    private $parentEntity;

    public function getId(): ?int
    {
        return $this->id;
    }

    public function getParentEntity(): ?ParentEntity
    {
        return $this->parentEntity;
    }

    public function setParentEntity(?ParentEntity $parentEntity): self
    {
        $this->parentEntity = $parentEntity;

        return $this;
    }
}

which generates the following SQL:

doctrine.DEBUG: CREATE SEQUENCE child_entity_id_seq INCREMENT BY 1 MINVALUE 1 START 1 [] []
doctrine.DEBUG: CREATE SEQUENCE parent_entity_id_seq INCREMENT BY 1 MINVALUE 1 START 1 [] []
doctrine.DEBUG: CREATE TABLE child_entity (id INT NOT NULL, parent_entity_id INT NOT NULL, PRIMARY KEY(id)) [] []
doctrine.DEBUG: CREATE INDEX IDX_677D8034706E52B3 ON child_entity (parent_entity_id) [] []
doctrine.DEBUG: CREATE TABLE parent_entity (id INT NOT NULL, root_child_entity_id INT NOT NULL, PRIMARY KEY(id)) [] []
doctrine.DEBUG: CREATE INDEX IDX_413B87AEE5B68E27 ON parent_entity (root_child_entity_id) [] []
doctrine.DEBUG: ALTER TABLE child_entity ADD CONSTRAINT FK_677D8034706E52B3 FOREIGN KEY (parent_entity_id) REFERENCES parent_entity (id) NOT DEFERRABLE INITIALLY IMMEDIATE [] []
doctrine.DEBUG: ALTER TABLE parent_entity ADD CONSTRAINT FK_413B87AEE5B68E27 FOREIGN KEY (root_child_entity_id) REFERENCES child_entity (id) NOT DEFERRABLE INITIALLY IMMEDIATE [] []

Answer 1

Circular entities with all IDs defined as not-nullable are impossible to persist sequentionally when immediate consistency is enforced. This is by default in almost every DB vendor.

So firstly you need to defer the consistency - see http://dbadailystuff.com/deferred-constraints-in-postgresql . For PostgreSQL and Doctrine ORM specifically, this can you fixed by specifying deferrable option for the foreign key, see https://github.com/doctrine/dbal/blob/e839cecc812fd04ec6c39fc865302b6a322d967d/src/Platforms/PostgreSQLPlatform.php#L439 .

and secondly you need to convince Doctrine ORM to insert the parent row with the ID of the child row. I doubt there is a direct support because the 1st point is very inconsistent across DB vendors, but feel free to open a ticket at https://github.com/doctrine/orm/issues , the authors may advice you how to enforce Doctrine ORM to insert the child ID immediately at least.