Symfony : how to add non nullable field to an entity with entries

Question

I have an entity Topic with attributes set previously (id, name), and the Topic table is now filled. Since the project evolves, i need a new attribute catalog. But the thing is this attribute needs to be non null.

class Topic
{
  /**
   * @var integer
   *
   * @ORM\Column(name="id", type="integer")
   * @ORM\Id
   * @ORM\GeneratedValue(strategy="IDENTITY")
   */
  private $id;

  /**
   * @var \MyBundle\Entity\Catalog
   * @ORM\ManyToOne(targetEntity="MyBundle\Entity\Catalog")
   * @ORM\JoinColumns({
   *   @ORM\JoinColumn(name="catalogId", referencedColumnName="id", nullable=false)
   * })
   * @Assert\NotBlank()
   */
  private $catalog;

  /**
   * @var string
   *
   * @ORM\Column(name="name", type="string", length=255, nullable=false)
   * @Assert\NotBlank()
   */
  private $name;
}

The doctrine:schema:update --dump-sql is ok :

ALTER TABLE topic ADD catalogId INT NOT NULL;
ALTER TABLE topic ADD CONSTRAINT FK_9D40DE1B19B71A2D FOREIGN KEY (catalogId) REFERENCES catalog (id);
CREATE INDEX IDX_9D40DE1B19B71A2D ON topic (catalogId);

The problem is if i do a doctrine:schema:update --force i have an error stating that i can't.

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`simspeaker`.`#sql-3d2  
  _71`, CONSTRAINT `FK_9D40DE1B19B71A2D` FOREIGN KEY (`catalogId`) REFERENCES `catalog` (`id`))

The only way i manage to get around this error is doing things not in one step :

1. i set the catalog field to nullable
2. i set a default catalog value to the the whole table
3. i set the catalog field to non nullable

This not optimal, at best. Is there a way to perform this kind of modification in "one pass" ?

Answer 1

There doesn't seem to be an answer here and this is the first result on google, so I'll just write a quick one.

In your migration:

• first add the new fields as nullable
• update the new fields with the appropriate data
• change the new fields to not nullable

voila.

I'll add one caveat, in the middle step, write SQL queries, don't use doctrine as this will cause headaches in the future. The Entity might change in a way that becomes incompatible with your code. Then your migrations will break, and you'll be sad. I don't want you to be sad.