Why is my cascade not working? - Integrity constraint violation

Question

I have an parent and child entities, parent being an image, and child being a vote for the image

class Image {

    /**
     * @ORM\Id
     * @ORM\Column(type="integer")
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    protected $id;
    /**
     * @ORM\Column(type="string", length=255, name="file_name")
     * @var string $fileName
     */
    protected $fileName;

    /**
     * @ORM\Column(type="string", length=255, name="title", nullable=true)
     * @var string $title
     */
    protected $title = null;

    /**
     * @ORM\Column(type="string", length=255, name="author", nullable=true)
     * @var string $author
     */
    protected $author = null;

    /**
     * @ORM\Column(type="datetime", name="upload_at")
     */
    protected $uploadDate;

    /**
     * @ORM\Column(type="boolean", name="in_pool")
     */
    protected $inPool;

    /**
    * One image has many votes
    * @ORM\OneToMany(targetEntity="Vote", mappedBy="image")
    */
    private $votes;

Child:

class Vote
{
    /**
     * @ORM\Id
     * @ORM\Column(type="integer")
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    protected $id;
    /**
     * @ORM\ManyToOne(targetEntity="Image", inversedBy="votes")
     * @ORM\JoinColumn(onDelete="CASCADE")
     */
    protected $image;

    /**
     * @ORM\Column(type="datetime", name="date")
     */
    protected $date;

    /**
     * This is internal variable, it does not have to be mapped into database field
     * @var int Counter of votes for an Image
     */
    private $counter;

As you can see I have the @ORM\JoinColumn(onDelete="CASCADE") set in the entity.

However when I try to remove an image which has votes I get this:

An exception occurred while executing 'DELETE FROM image WHERE id = ?' with params [24]:

SQLSTATE[23000]: Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint fails (konkurs.vote, CONSTRAINT FK_5A1085643DA5256D FOREIGN KEY (image_id) REFERENCES image (id))

The code used to delete an image based on id looks like this:

/**
* @Route("/admin/removeimage/{id}", name="admin-remove-image")
*/
public function adminRemoveImage($id)
{

    $em = $this->getEm();
    $image = $em->getRepository('AppBundle:Image')->findOneById($id);
    $fileName = $image->getFileName();

    $em->remove($image);
    $em->flush();

    unlink('uploads/'.$fileName);
    unlink('media/cache/my_thumb/uploads/'.$fileName);

    return $this->redirectToRoute('admin-panel');
}

---

Could anyone tell me why does this relation keep failing?

UPDATE Here is the output of using SHOW CREATE TABLE vote

| vote | CREATE TABLE vote ( id int(11) NOT NULL AUTO_INCREMENT, date datetime NOT NULL, image_id int(11) DEFAULT NULL, PRIMARY KEY (id), KEY IDX_5A1085643DA5256D (image_id), CONSTRAINT FK_5A1085643DA5256D FOREIGN KEY (image_id) REFERENCES image (id) ) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |

Answer 1

Adding @ORM\JoinColumn(onDelete="CASCADE") is a mapping for the tables that doctrine manages via doctrine:schema:update or doctrine:schema:create.

The cascade foreign key reference is not automatically applied to the database table column, unless the above commands are issued.

By default, when creating a foreign key reference, it is set to ON DELETE RESTRICT.

To resolve your issue run the following SQL commands:

ALTER TABLE `vote` DROP FOREIGN KEY `FK_5A1085643DA5256D`;

ALTER TABLE `vote` ADD CONSTRAINT `FK_5A1085643DA5256D` FOREIGN KEY (`image_id`) REFERENCES `image` (`id`) ON DELETE CASCADE;

Which will be the same as running:

php bin/console doctrine:schema:update --force

However the schema update may cause irreversible changes/damage to your database structure.

See: http://symfony.com/doc/current/doctrine.html#creating-the-database-tables-schema

I also suggest running php bin/console doctrine:schema:update --dump-sql to determine other potential misconfigurations from editing the doctrine entity mappings.

---

Alternatively as @Cerad mentioned, you can tell Doctrine to manage the relationships, without relying on foreign key references, by using cascade={"remove"} like so.

class Image 
{
    //...

    /**
    * One image has many votes
    * @ORM\OneToMany(targetEntity="Vote", mappedBy="image", cascade={"remove"})
    */
    private $votes;
}

This will tell symfony to issue a delete statement for the Vote entity collection associated with the Image entity when the Image entity is removed.

One caveat is that this only works when issuing commands against the Entity manager, and not the DBAL query builder.

Reference: http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/working-with-objects.html#removing-entities