Removing children from entity with self-referencing One-to-Many association

Question

I have a self-referencing One-to-Many association on an entity (User) mapped by 'parent' and inversed by 'children'. I want to be able to remove users that are not parents. My entity is declared as follows.

class User implements UserInterface
{
   /**
    * @ORM\Column(name="id", type="smallint")
    * @ORM\Id
    * @ORM\GeneratedValue(strategy="AUTO")
    */
   protected $id;

   (...)

   /**
    * @ORM\OneToMany(targetEntity="User", mappedBy="parent")
    */
   protected $children;

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

   public function __construct()
   {
       $this->parentId = null; // Default value for column parent_id
       $this->children = new ArrayCollection();
   }
}

When I try to remove a child user that is not a parent, I am getting the following error.

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

I have tried doing a cascade delete, as follows.

/**
 * @ORM\OneToMany(targetEntity="User", mappedBy="parent", cascade={"persist", "remove"})
 */
protected $children;

Getting the same error either way. Any help?

Answer 1

Use this:

/**
* @ORM\ManyToOne(
*     targetEntity="User", 
*     inversedBy="children", 
*     cascade={"persist", "remove"}
* )
*
* @ORM\JoinColumn(name="parent_id", referencedColumnName="id", onDelete="SET NULL")
*/

What onDelete="SET NULL" does is that when you delete a parent, its child element will get NULL value in parent_id column. This happens on DB level so you must doctrine:schema:update.