Remove rows from child table with Class Table Inheritance in Doctrine

Question

I have made a really basic example with two models.

"Singer" extends from "Person"

I am using class table Inheritance with these two models:

<?php
namespace models;

/**
 * @Table(name="persons")
 * @entity
 * @InheritanceType("JOINED")
 * @DiscriminatorColumn(name="type", type="string")
 * @DiscriminatorMap({"singer" = "\models\Singer"})
 */
abstract class Person {

    /**
     * @Id
     * @Column(type="integer", nullable=false, name="id_person")
     * @GeneratedValue(strategy="AUTO")
     */
    protected $id;

    /** @column(type="string", nullable=false) */
    protected $name;

The singer model looks like:

namespace models;

/**
 * @Table(name="singers")
 * @entity
 */
class Singer extends Person{

    /** @column(type="string", nullable=false) */
    protected $gender;
}

Workflow

Consider this scenario.

1. In the db I have these rows:

   persons table:

   id_person | name | type
   -----------------------
   1           john   singer
   

   singers table:

   id_person | gender
   ------------------
   1           pop  
   

2. I proceed to remove this singer:

   $singer = $em->find('models\Singer', 1);
   $em->remove($singer);
   $em->flush();
   

3. After execute the code above, I check again the database and I found this:

   persons table:

   id_person | name | type
   -----------------------
   (empty)
   

   singers table:

   id_person | gender
   ------------------
   1           pop  
   

   As you note, the row from child table was not removed as expected.

4. So, after searching in doctrine's documentation, it states:

   When you do not use the SchemaTool to generate the required SQL you should know that deleting a class table inheritance makes use of the foreign key property ON DELETE CASCADE in all database implementations. A failure to implement this yourself will lead to dead rows in the database.

   So, I proceed to alter persons table as below:

   ALTER TABLE persons 
   ADD CONSTRAINT fk_persons_1
   FOREIGN KEY (id_person)
   REFERENCES singers (id_person)
   ON DELETE CASCADE
   ON UPDATE NO ACTION;
   

Now, the problems get complicated:

• when I remove a singer, the information still there, even the persons table was altered in order to delete from singers table too.

• When I try to insert a new singer like

  $singer = new \models\Singer('Zara', 'rock');
  $em->persist($singer);
  $em->flush();
  

  it throws an exception:

  Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`practica`.`persons`, CONSTRAINT `fk_persons_1` FOREIGN KEY (`id_person`) REFERENCES `singers` (`id_person`) ON DELETE CASCADE ON UPDATE NO ACTION)' in /var/www/html/doctrine/vendor/doctrine/dbal/lib/Doctrine/DBAL/Statement.php:138 Stack trace: #0 /var/www/html/doctrine/vendor/doctrine/dbal/lib/Doctrine/DBAL/Statement.php(138): PDOStatement->execute(NULL) #1 /var/www/html/doctrine/vendor/doctrine/orm/lib/Doctrine/ORM/Persisters/JoinedSubclassPersister.php(165): Doctrine\DBAL\Statement->execute() #2 /var/www/html/doctrine/vendor/doctrine/orm/lib/Doctrine/ORM/UnitOfWork.php(929): Doctrine\ORM\Persisters\JoinedSubclassPersister->executeInserts() #3 /var/www/html/doctrine/vendor/doctrine/orm/lib/Doctrine/ORM/UnitOfWork.php(318): Doctrine\ORM\UnitOfWork->executeInserts(Object(Doctrine\ORM\Mapping\ClassMetadata)) #4 /var/www/html/doctrine/vendor/doct in /var/www/html/doctrine/vendor/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php on line 47
  

So, basically, all I need is to remove the information in child table too in MySQL db. But I do not get it.

Answer 1

Try reversing the FOREIGN KEY i.e. remove it from your persons table and add it to your singers table

ALTER TABLE singers 
ADD CONSTRAINT fk_singers_1
FOREIGN KEY (id_person)
REFERENCES persons (id_person)
ON DELETE CASCADE
ON UPDATE NO ACTION;