Symfony2, DoctrineFixturesBundle, can't load fixtures due to foreign key constraint

Question

I have a Company entity where each company has another parent company in a hierarchical tree structure.

Everything works fine in the application so I'm sure my Entity classes are correct.

The problem is, if there is already content in the database then doing

doctrine:fixtures:load

gives this error:

[PDOException]                                                                                                                                                                                                                                              
SQLSTATE[23000]: Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint fails

Im fairly sure the problem is that the load:fixtures has to truncate the table, but it cant without getting this error.

Im not sure how to resolve this without hacking something in to Doctrine to disable key constraints before the purge. Not really a long term solution.

The other relationships in the data structure dont cause a problem as doctrine seems to purge in the right order to avoid the problems, but with the Company table being self referencing it falls over.

Heres my entity.

class Company
{
/**
 * @var integer $id
 *
 * @ORM\Id
 * @ORM\Column(name="id", type="integer")
 * @ORM\GeneratedValue(strategy="AUTO")
 */
protected $id;

/**
* @var string $name
* @ORM\Column(type="string", length=100)
*/    
protected $name;

/**
 * @ORM\ManyToOne(targetEntity="Company", inversedBy="children")
 * @ORM\JoinColumn(name="parent_id", referencedColumnName="id")
 */
protected $parent;

/* other properties here..... */

}

Im using Symfony 2.0.7 and the latest deps, and MySQL 5.5

Answer 1

I just needed to properly set the behaviour of OnDelete.

It defaults to RESTRICT, which explains the error being thrown. Explicitly setting it to CASCADE or SET NULL allows doctrine to empty the table with no errors.

In my case I didnt want removing a parent to cause the deletion of the children, so I used SET NULL so that the relationships would just be removed instead.

/**
 * @ORM\ManyToOne(targetEntity="Company", inversedBy="children")
 * @ORM\JoinColumn(name="parent_id", referencedColumnName="id", onDelete="set null")
 */
protected $parent;