Disable Doctrine foreign key constraint

Question

I have a relationship on one of my models:

/**
* @ORM\ManyToOne(targetEntity="Page", cascade="persist")
* @ORM\JoinColumn(name="page_id", referencedColumnName="id")
*/
private $parentPage;

And when I delete the parent page, I get this error:

Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint fails

Basically my models are a page, and page revision. When I delete the page I don't want to delete the revisions. I also want to keep the page_id on the page revisions (i.e. not set it to null).

How can I do this with Doctrine?

Answer 1

You can disable the exporting of foreign keys for specific models:

User:
  attributes:
    export: tables
  columns:

Now it will only export the table definition and none of the foreign keys. You can use: none, tables, constraints, plugins, or all.

Answer 2

By definition you cannot delete the record that the foreign key is pointing at without setting the key to null (onDelete="SET NULL") or cascading the delete operation (There are two options - ORM Level: cascade={"remove"} | database level: onDelete="CASCADE").
There is the alternative of setting a default value of a still existing record, but you have to do that manually, I don't think Doctrine supports this "out-of-the-box" (please correct me if I am wrong, but in this case setting a default value is not desired anyway).

This strictness is reflecting the concept of having foreign key constraints; like @Théo said:

a FK is to ensure data consistency.

Soft delete (already mentioned) is one solution, but what you could also do is add an additional removed_page_id column that you sync with the page_id just before you delete it in a preRemove event handler (life cycle callback). Whether such information has any value I wonder but I guess you have some use for it, otherwise you wouldn't ask this question.

I am definitely not claiming this is good practice, but it is at least something that you can use for your edge case. So something in the line of:

In your Revision:

/**
 * @ORM\ManyToOne(targetEntity="Page", cascade="persist")
 * @ORM\JoinColumn(name="page_id", referencedColumnName="id", onDelete="SET NULL")
 */
private $parentPage;

/**
 * @var int
 * @ORM\Column(type="integer", name="removed_page_id", nullable=true)
 */
protected $removedPageId;

And then in your Page:

/** 
 * @ORM\PreRemove 
 */
public function preRemovePageHandler(LifecycleEventArgs $args)
{
    $entityManager = $args->getEntityManager();
    $page = $args->getEntity();
    $revisions = $page->getRevisions();
    foreach($revisions as $revision){
        $revision->setRemovedPageId($page->getId());
        $entityManager->persist($revision);
    }
    $entityManager->flush();
}

Alternatively you could of course already set the correct $removedPageId value during construction of your Revision, then you don't even need to execute a life cycle callback on remove.

Answer 3

I solved this by overriding one doctrine class in symfony 4.3, it looks like this for me:

<?php declare(strict_types=1);

namespace App\DBAL;

use Doctrine\DBAL\Platforms\MySQLPlatform;

/**
 * Class MySQLPlatformService
 * @package App\DBAL
 */
class MySQLPlatformService extends MySQLPlatform
{
    /**
     * Disabling the creation of foreign keys in the database (partitioning is used)
     * @return false
     */
    public function supportsForeignKeyConstraints(): bool
    {
        return false;
    }

    /**
     * Disabling the creation of foreign keys in the database (partitioning is used)
     * @return false
     */
    public function supportsForeignKeyOnUpdate(): bool
    {
        return false;
    }
}